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

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

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

On Oct 10, 7:13 am, "myemail...@googlemail.com"
wrote:
> 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.

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

> 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!

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

On Oct 10, 10:47 am, "myemail...@googlemail.com"
wrote:
> > 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... ;-)

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

> 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!