Need help selecting in queries

[Stuartp]

Hoping someone can help me with a query I'm finding really difficult...

I am storing some data on customers and how much they are spending each month, and the data for each month goes in a seperate table labelled 1, 2, 3 and so on.

I now need to select all the customer names and how much they have spent in the current month, and then if this customer spent anything in any of the previous 5 months, list this as well, so for example I might end up with:

Customer Month 1 Month 2 Month 3
XXXXXXX 400 364 845
YYYYYYY 213 328

etc etc

but I am not sure how to go about doing this, in particular how to compare the different values, select them and put them in the right place etc.

Thanks in advance to anyone who can help.

Stuart

 

[braindead2]

The first three options that pop in my mind are
1. Create a union query that brings in all the data from the tables into 1 then do a crosstab based on the query
or
2. Use VBA and open 6 recordsets then maniuplate the data that way.
or
3. Lastly normalize your database and quit creating needless tables. Keep the data in one table then run a crosstab query based on the table.

Of the above I vote for number 3.