grouping

[Joel Allen]

Hello,

I have a table with fields like this:

Store location, quanity of red apples, quantity of green apples.

e.g.

Redmond,26,40

I want to make a query that brings the data into multiple lines like this:

e.g.
Redmond,26,Red
Redmond,40,Green

I'm not sure exactly how to do this. Can somebody give me a pointer so I
can start on the right foot?

Thanks,
Joel

 

[John Spencer]

Use a union query.

SELECT StoreLocation
,[QuantityRedApples]
,"Red" as TheColor
FROM TheTable
UNION ALL
SELECT StoreLocation
,[QuantityGreenApples]
,"Green"
FROM TheTable
ORDER BY StoreLocation, TheColor Desc

 

[Guest]

Try this ---
SELECT Joel_Allen.[Store location], Joel_Allen.[red apples] AS QTY, "Red" AS
Variety
FROM Joel_Allen
UNION ALL SELECT Joel_Allen.[Store location], Joel_Allen.[green apples] AS
QTY, "green" AS Variety
FROM Joel_Allen;

 

[Jeff Boyce]

Joel

The right foot would be to not use a spreadsheet as your model for your
Access table.

As soon as you have repeating fields ("qty of x", "qty of y", ...), you have
a spreadsheet.

This makes using Access' features and functions much more difficult.

First look into normalizing your data structure, perhaps along the lines of:

tlkpStore
StoreID (Primary Key)
StoreName
StoreStreetAddress
....

tlkpFruit
FruitID (PK)
FruitName (e.g., apple, mango, ...

tlkpColor
ColorID (PK)
Color (e.g., red, green, ...)

trelInventory
InventoryID (PK)
StoreID (foreign key, pointing back to tlkpStore)
FruitID (FK...)
ColorID (FK...)
Quantity

This design has one record per valid store/fruit/color combination. Now
your query is a simple lookup and you don't need to deal with multiple
columns of potential locations.

Or you could look at building a normalizing union query (check Access HELP).

Regards

Jeff Boyce
Microsoft Office/Access MVP