By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,948 Members | 1,958 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,948 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Oct 10 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Oct 10, 7:13 am, "myemail...@googlemail.com"
<myemail...@googlemail.comwrote:
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.

Oct 10 '07 #2

P: n/a
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!

Oct 10 '07 #3

P: n/a
On Oct 10, 10:47 am, "myemail...@googlemail.com"
<myemail...@googlemail.comwrote:
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... ;-)

Oct 11 '07 #4

P: n/a
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!

Oct 12 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.