Help creating query 1

[EdRev]

I have a table with the following data table

Store Item Jan Feb Mar

Gap #1 Shirt 150 175 50
Gap #1 Pants 75 85 90
Gap #2 Shirt 100 100 100
Gap #2 Pants 100 100 100
Old Navy #1 Shirt 80 65 75
Old Navy #1 Pants 25 35 40
Old Navy #2 Shirt 100 100 100
Old Navy #2 Pants 100 100 100
Banana Rep #1 Shirt 60 40 45
Banana Rep #1 Pants 45 40 35
Banana Rep #2 Shirt 100 100 100
Banana Rep #2 Pants 100 100 100


I need to create a query that will look like this:

For Jan:

Gap Old Navy Banana Rep
Shirts 250 180 160
Pants 175 125 145


Any help will be greatly appreciated

 

[lynchg]

It would be best (if possible), to add a field like Store_Type and have just one value for Gap stores (like 'Gap'), 'Old Navy' for Old Navy, etc.

Then try these:

TRANSFORM Sum(aaSales.Jan) AS January
SELECT aaSales.Item
FROM aaSales
GROUP BY aaSales.Item
ORDER BY aaSales.Item DESC
PIVOT aaSales.Store_Type;

TRANSFORM Sum(aaSales.Feb) AS February
SELECT aaSales.Item
FROM aaSales
GROUP BY aaSales.Item
ORDER BY aaSales.Item DESC
PIVOT aaSales.Store_Type;

The fields in my table (aaSales), are Store, Item, Jan, Feb, Mar, etc. and Store_Type

Good Luck.

 

[EdRev]

It works perfectly.

 

[EdRev]

It was working perfectly in Access, but I just realized that the file I will be manipulating is a flat text file.
Any idea on how I can go about doing this?

 

[lespaul]

import the text file to an access table?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design