How to pull all transactions into 1 record

[i_wanted_another_name]

Hi there - I'm a relative rookie to this...

I have (inherited) a table with multiple records for each ID detailing
dates and transactions.

ID Date Amount
123 1/15/04 $1500.00
123 3/25/04 $1600.00

In order to complete an analysis of the transactions over time, I need
to take the date and $ info and add to the first record in new fields,
ideally like this:

ID Date Amount Date2 Amount2 etc.
123 1/15/04 $1500.00 3/25/04 $1600.00

Haven't figured out if this is possible using a query in design view,
or whether this requires some SQL code (of which I have very limited
knowledge). I'm assuming there has to be some criteria to examine the
ID to check if it's the same as the previous record, keep track of
which iteration of the record it is and then populate a new field with
the data from the record - I just have no idea how...
Any help would be appreciated. Thanks -

Rob

[Bas Cost Budde]

i_*******************@yahoo.ca wrote:

Hi there - I'm a relative rookie to this...

I have (inherited) a table with multiple records for each ID detailing
dates and transactions.

ID Date Amount
123 1/15/04 $1500.00
123 3/25/04 $1600.00

In order to complete an analysis of the transactions over time, I need
to take the date and $ info and add to the first record in new fields,
ideally like this:

ID Date Amount Date2 Amount2 etc.
123 1/15/04 $1500.00 3/25/04 $1600.00

Looks like a Crosstab query to me. Try the wizard.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[i_wanted_another_name]

Thanks - have tried that, however there are too many different dates
for too many different id's (data goes back 15 years for 1,000,000+
records) so I get a 'too many columns' error after the query runs.
I'm using MS Access 2k if that makes a difference in the method to
resolve.
Rob

[Bas Cost Budde]

i_*******************@yahoo.ca wrote:

Thanks - have tried that, however there are too many different dates
for too many different id's (data goes back 15 years for 1,000,000+
records) so I get a 'too many columns' error after the query runs.
I'm using MS Access 2k if that makes a difference in the method to
resolve.
Rob

Grlzzt--15 years? And how is the output readable to you?

What would you like to see? I think I can devise something that writes a
text file in the format you want, but you'd have to have a
looooooooooooooooooooooooooooooooooooooooooooooooo ooooooooooooooong
printer carriage ;-)
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[i_wanted_another_name]

Sorry, should have been more specific.
The data goes back 15 years but there's no more than 8 transactions per
ID (just checked), so the maximum length of a record I would like to
create would be:

ID, DATE, AMOUNT, DATE2, AMOUNT2, ..., DATE7, AMOUNT7 (15 fields)

based on a how they now are in the table:

ID DATE AMOUNT
ID DATE(2) AMOUNT(2)
..
..
..
ID DATE(7) AMOUNT(7)

If there's a way to output as a text file that I could then re-import,
that would be great. Appreciate the help !
Rob

[Bas Cost Budde]

i_*******************@yahoo.ca wrote:

Sorry, should have been more specific.
The data goes back 15 years but there's no more than 8 transactions per
ID (just checked), so the maximum length of a record I would like to
create would be:

ID, DATE, AMOUNT, DATE2, AMOUNT2, ..., DATE7, AMOUNT7 (15 fields)

based on a how they now are in the table:

ID DATE AMOUNT
ID DATE(2) AMOUNT(2)
.
.
.
ID DATE(7) AMOUNT(7)

If there's a way to output as a text file that I could then re-import,
that would be great. Appreciate the help !

It's easy to me to create such a routine. It's a wheel that has been
invented several times; see if you can find more products with the
keyword 'concatenation' or 'enumeration'.

But, what are you going to do with the resulting "spreadsheet"? I cannot
envision any operation.

You could try to have a report with 8 columns, group on transactionID,
and display the 'subrecords' horizontally. I think that can be done.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Andrew]

Hi,
take a look at the documentation on Cross-Tab Query. This may do
what you need. What are you trying to populate/calculate?

Andrew
i_*******************@yahoo.ca wrote in message news:<11**********************@f14g2000cwb.googleg roups.com>...

Hi there - I'm a relative rookie to this...

I have (inherited) a table with multiple records for each ID detailing
dates and transactions.

ID Date Amount
123 1/15/04 $1500.00
123 3/25/04 $1600.00

In order to complete an analysis of the transactions over time, I need
to take the date and $ info and add to the first record in new fields,
ideally like this:

ID Date Amount Date2 Amount2 etc.
123 1/15/04 $1500.00 3/25/04 $1600.00

Haven't figured out if this is possible using a query in design view,
or whether this requires some SQL code (of which I have very limited
knowledge). I'm assuming there has to be some criteria to examine the
ID to check if it's the same as the previous record, keep track of
which iteration of the record it is and then populate a new field with
the data from the record - I just have no idea how...
Any help would be appreciated. Thanks -

Rob

[i_wanted_another_name]

Thanks Bas,

I need to perform some calibrations on the transaction amounts over
time to come up with an estimation routine (factoring in a number of
other parameters) to predict future transaction values for specific IDs
and then infill the predictions across other records for which I have
no historical data and derive confidence statements etc.

I will try searching using the keywords you mention as well as the
report approach (can I export the report to a table?). Hopefully I can
find a solution...

[i_wanted_another_name]

Andrew - thanks, see above re cross-tab query issue (too many different
dates requried for column headings).

[Bas Cost Budde]

i_*******************@yahoo.ca wrote:

Thanks Bas,

I need to perform some calibrations on the transaction amounts over
time to come up with an estimation routine (factoring in a number of
other parameters) to predict future transaction values for specific IDs
and then infill the predictions across other records for which I have
no historical data and derive confidence statements etc.

I will try searching using the keywords you mention as well as the
report approach (can I export the report to a table?). Hopefully I can
find a solution...

What about feeding the estimation routine the table? It can filter on ID
if necessary, it can look up any value.

Anyway, a code fragment should
* open a file
* walk through the table, ordered by ID and then date
* for every new ID, start a new line with the ID of the record
* output the date/value pair of the record
* end walk
* close the file

will come up with an actual code fragment later
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Bas Cost Budde]

Sub makeSheet(cTable As String, cFile As String)
'will assume cFile is a correct path/file name
Dim nFile As Long
Dim rs As Recordset
Dim cID As String
nFile = FreeFile
Open cFile For Output As nFile
Set rs = CurrentDb.OpenRecordset("select * from " & cTable & " ORDER
BY ID, date", dbOpenDynaset)
cID = ""
Do Until rs.EOF
If rs!ID <> cID Then
If cID <> "" Then Print #nFile, ""
Print #nFile, rs!ID & vbTab;
End If
Print #nFile, rs!Date & vbTab & rs!amount & vbTab;
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Close nFile
End Sub
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[brett valjalo]

Ya know, Access, like all RDB's, is designed to STORE data, keeping it
in a relational (which has a number of advantages, primarily space
saving and integrity-related) format. Depending on how it is set up,
it enables a designer to enforce data integrity in terms of how each
piece of data gets inserted,updated, and deleted. It allows for the
logging of changes to data (although, lacking triggers or a
transaction log, access is not too great in this arena, but you can
still 'roll your own' tracking system with it if you really care to
work at it). Perhaps it's greatest advantage over your average data
analysis tool, RDB's also allow multiple simultaneous users to view
and/or make changes to the same set of data (within certain limits, of
course).

Basically what I'm getting at is this: Access, nor any RDB, is not
really the best tool to use when it comes to (even remotely) complex
calculations, or other data analysis (save simple grouping and
summing) such as you're describing. I would tend to suggest you
export to, or Link from, Excel, then use that program to analyze the
data, my friend. It is a far more suitable tool for what it sounds
like you're doing (from where I sit, anyway)...

i_*******************@yahoo.ca wrote in message news:<11********************@c13g2000cwb.googlegro ups.com>...

Andrew - thanks, see above re cross-tab query issue (too many different
dates requried for column headings).

[Rich P]

if by pivoting your data like this

ID Date Amount Date2 Amount2 etc.
123 1/15/04 $1500.00 3/25/04 $1600.00

from the vertical --- all you want to accomplish is a horizontal view
you can use a crosstab query, which is one of the query selections in
Access. The crosstab query is a little bit tricky, but there is a
wizard that helps you select the fields you need. In your case you
would use the ID field as a RowHeader (one of the selections in the
wizard), you would use the date field as a column header, and the Amount
field is your value field (select Mas as the operation for Amount).
That should give you what you are looking for. And you may want to
uncheck the checkbox for "Yes Include Row Sums" which is located on the
left side of the wizard when you get to the last selection page.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!