Query to combine multiple queries

[jortizz]

I have 3 queries with sales information. Their format is as follows:

Query 1:
[Item #] [Acct Name] [Week #] [2011 Sales $]
542..............ABC........Jan Wk 1....$9,000
625............. XYZ....... Feb Wk 2.... $350

Query 2:
[Item #] [Acct Name] [Week #] [2010 Sales $]
625............ XYZ........Feb Wk 2.... $200
725............ DEF........ Jan Wk 3.... $528

Query 3:
[Item #] [Acct Name] [Week #] [2009 Sales $]
542............GHI.........Jan Wk 4.......$112
625............ XYZ........Feb Wk 2.......$775



I would like to join them into 1 query that looks something like this. Every query I have tried usually duplicates data and I'm not sure why. Note that new columns have been added:

New Query:
[Item #] [Acct Name] [Week #] [2011 $] [2010 $] [2009 $]
542.............ABC.......Jan Wk 1...$9,000.......0........$112


625.............XYZ.......Feb Wk 2...$350......$200.......$775
725.............DEF.......Jan Wk 3......0.........$528........0

I'm not an Access expert so any help with details is appreciated. Thank you.

[alansidman]

What does your table look like. I think that this can be accomplished with a cross tab query if all the information is in one table. Look at this video for a tutorial.

http://www.datapigtechnologies.com/f.../crosstab.html

Alan

[jortizz]

Alan, I have gotten this to work with the crosstab query you suggested. Thank you so much. However, your link does not work for me. I would like to see this video. Do you have another link?

[alansidman]

Try this and then scroll to the appropriate tutorial

http://www.datapigtechnologies.com/AccessMain.htm

Alan

[jortizz]

Try this and then scroll to the appropriate tutorial

http://www.datapigtechnologies.com/AccessMain.htm

Alan

Thanks Alan -This video gave me some new ideas that I will be trying.