Repeat the value from the previous record

  • Thread starter Thread starter Dante Huapaya
  • Start date Start date
D

Dante Huapaya

Hi all

I have a table with the following information (this come like this)

Account Number Date Amount
RZ2565 05/05/2008 258.50
06/05/2008 3255.65
12/07/2008 15.25
30/08/2008 1250.00
RZ1234 01/01/2008 25.65
25/08/2008 1456.65
15/09/2008 125.89
RZ0025 25/05/2007 13.65
01/01/2008 1235.00
15/08/2008 415.30

But I want by query something like this, it is possible?

Account Number Date Amount
RZ2565 05/05/2008 258.50
RZ2565 06/05/2008 3255.65
RZ2565 12/07/2008 15.25
RZ2565 30/08/2008 1250.00
RZ1234 01/01/2008 25.65
RZ1234 25/08/2008 1456.65
RZ1234 15/09/2008 125.89
RZ0025 25/05/2007 13.65
RZ0025 01/01/2008 1235.00
RZ0025 15/08/2008 415.30

Thanks for your help

Dante Huapaya
 
Do you mean that you have records in your table with no account number,
relying on the order of records in the table? That's 'Not A Good Idea',
because, by definition, records in a relational database table are
_unordered_, and you have to order them by using the ORDER BY clause of the
SQL which retrieves them.

It may appear that they are ordered, because Access has to show them in
_some_ order, which will be "the easiest for Access to retrieve". You may
be able to take advantage of Access' default ordering, and recover... but
you should always enter the identifying information (e.g., account number)
in the future.

You can write VBA code using DAO (Data Access Objects -- native language of
the Jet database engine) or ADO (ActiveX Data Objects -- a different access
method, already superceded in 'the rest of Microsoft's software') to
retrieve the records. If they are retrieved in the same order you show
them, then you can write a little additional code to save and apply the
Account Number to each record before writing it to a new recordset/table.
It's not very difficult code, but, sad to say, time comes at a premium for
me just now, so perhaps someone else will give you the basics of the actual
code.

Larry Linson
Microsoft Office Access MVP
 
Is this a linked Excel spreadsheet?
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
No, it is a text file using specs to import, for info my table has a ID key
field
Thanks
 
This is an external text file, i dont have any control with the info in this
file is for that I have requested your help.

Thanks so much
 
An external text file, in Access terms, is NOT a "table", and, no surprise,
my cautions do not apply to external text (aka sequential) files. Access
has a good complement of File I/O instructions in its Visual Basic for
Applications (VBA) language. Open, Close, Input #, Print #, Get, and Put
are pertinent instructions. I fear the current Help on these functions may
not be as clear as Help was for some previous versions, but there should be
enough to get you started. You can, simply, read the records sequentially
and populate records in a table from the data, changing Account Numbers
whenever there is a new one in the external text file.

On the other hand, springing "surprises" such as "not a table as I said, but
an external text file" isn't going to make people happy who are trying to
assist you.

Larry Linson
Microsoft Office Access MVP
 
Ok,

1. First off, what is the table structure of the table you are inserting
into (field names and data types).

2. I'm going to assume that because the date information in your table is
not in the mm/dd/yyyy format that you are importing that into a text field.
I created a text file with the data in the format you described, and
imported it into a table with 5 fields (AcctNum, StrTransDate, Amount, ID,
ImportDate, and TransDate) where the field types are (text, text, currency,
autonumber, date/time with default value = Now(), date/time).

It appears that the items from the text file were imported correctly, and in
the same order I entered them in the text file. The down side, is, as you
say, you don't have an Account number for each of these values, and the
dates are formatted as strings. While you could write an update query to
update the values where the account number is empty, it would be
complicated. Instead, I think I would write a function to do the updates
for me; something like the following (I took the liberty of adding the
TransDate field because Access does not translate non-US formatted dates
very well, and added code to the function to convert these as well). Then,
I would put a loop in the code immediately following your import operation
that calls this function repetitively until it returns a False value
(meaning it could not find any empty account numbers).

While fnUpdateAcctNumber() = True : Wend

*******************************************************
Public Function fnUpdateAcctNumber() As Boolean

Dim dtLastImport As Variant
Dim strSQL As String
Dim strAcctNum As String, strDate As String
Dim rs As DAO.Recordset

dtLastImport = DMax("ImportDate", "tbl_Account_Import", "Len([AcctNum] &
'') = 0")
If IsNull(dtLastImport) Then
fnUpdateAcctNumber = False
Exit Function
End If

strSQL = "SELECT AcctNum, TransDate2, TransDate FROM tbl_Account_Import
" _
& "WHERE [ImportDate] = #" & dtLastImport & "# " _
& "ORDER BY [ID]"
Set rs = CurrentDb.OpenRecordset(strSQL)
While Not rs.EOF
rs.Edit
If Not IsNull(rs("AcctNum")) Then
strAcctNum = rs("AcctNum")
Else
rs("AccountNumber") = strAcctNum
End If
strDate = rs("TransDate2")
rs("TransDate") = DateSerial(Mid(strDate, 7), Mid(strDate, 4, 2),
Left(strDate, 2))
rs.Update
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
fnUpdateAcctNumber = True

End Function

HTH
Dale
 
Back
Top