Regrouping data on a table (from a table of all transactions to a summary by customer)

[myemail.an]

I have a database with information on customers and their
transactions.
The table with transactions has the following fields:
Customer ID , Transaction ID, Transaction Type, Transaction Amount,
Transaction Date

Each customer may have up to 6 transactions.
I'd need to create a table where each record is a customer and which
has these fields:
Customer ID , 1st Transaction - Transaction Type, 1st Transaction -
Transaction Date, 1st Transaction - Transaction Amount, 2nd
Transaction - transaction type and so on

I can create a summary of the amounts with a crosstab, but it would
not be sufficient as I need the specific details of each and every
transaction. Plus, "Transaction type" is a text field.

Would you have any suggestions?

Thanks!

PS I use Access 2007

[OldPro]

On Oct 10, 7:13 am, "[e-mail]"
<[e-mail]:

I have a database with information on customers and their
transactions.
The table with transactions has the following fields:
Customer ID , Transaction ID, Transaction Type, Transaction Amount,
Transaction Date

Each customer may have up to 6 transactions.
I'd need to create a table where each record is a customer and which
has these fields:
Customer ID , 1st Transaction - Transaction Type, 1st Transaction -
Transaction Date, 1st Transaction - Transaction Amount, 2nd
Transaction - transaction type and so on

I can create a summary of the amounts with a crosstab, but it would
not be sufficient as I need the specific details of each and every
transaction. Plus, "Transaction type" is a text field.

Would you have any suggestions?

Thanks!

PS I use Access 2007

You have made several declarative statements and one question, "Would
you have any suggestions?. What is it that you want? To build the
table, or to create a report off of it? I can't say that I am happy
with your choice of mixing customer data with transaction data. This
seems like a really bad idea. They should be in separate tables.

[myemail.an]

You have made several declarative statements and one question, "Would

you have any suggestions?. What is it that you want? To build the
table, or to create a report off of it?

I need to combine the information as I described. Being a novice to
access, I have little clue as to what the best method might be (query,
report or else).

I can't say that I am happy
with your choice of mixing customer data with transaction data. This
seems like a really bad idea. They should be in separate tables.- Hide quoted text

They are separate tables and will stay as such. I do not wish to
change the structure of the whole database; I just need to combine the
information as described (customers and transactions) to run a very
specific analysis on a very specific subset of customers.

Sorry if the questions might seem very banal: I literally just got
started with Access! Thanks in advance!

[OldPro]

On Oct 10, 10:47 am, "[e-mail]"
<[e-mail]:

You have made several declarative statements and one question, "Would
you have any suggestions?. What is it that you want? To build the
table, or to create a report off of it?

I need to combine the information as I described. Being a novice to
access, I have little clue as to what the best method might be (query,
report or else).

I can't say that I am happy
with your choice of mixing customer data with transaction data. This
seems like a really bad idea. They should be in separate tables.- Hide quoted text

They are separate tables and will stay as such. I do not wish to
change the structure of the whole database; I just need to combine the
information as described (customers and transactions) to run a very
specific analysis on a very specific subset of customers.

Sorry if the questions might seem very banal: I literally just got
started with Access! Thanks in advance!

It all depends on what your output will be; if you want a printed
report, then you will need to make a report. If you only want it to
display the combined data on the screen, then a query will do. A
report can also be made off of a query. If this is a one time thing
and you don't need to print it, then make a query in the query
builder. If you are going to use it more than once, then build some
code into a command button. Does this database have existing forms?
Do you want to add a button to one of them for this report? Do you
know any programming? If only I were psychic... ;-)

[myemail.an]

It all depends on what your output will be; if you want a printed
report, then you will need to make a report. If you only want it to
display the combined data on the screen, then a query will do.

The I guess a query will do, in my case.

A
report can also be made off of a query. If this is a one time thing
and you don't need to print it, then make a query in the query
builder. If you are going to use it more than once, then build some
code into a command button. Does this database have existing forms?

No, it doesn't at the moment

Do you want to add a button to one of them for this report? Do you
know any programming? If only I were psychic... ;-)-

I know VBA and Matlab programming, but I can't say I know any SQL

Thanks!