If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
grouping
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 |
#2
|
|||
|
|||
grouping
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 --- John Spencer Access MVP 2001-2005, 2007 Joel Allen wrote: 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 |
#3
|
|||
|
|||
grouping
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; "Joel Allen" wrote: 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 |
#4
|
|||
|
|||
grouping
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 "Joel Allen" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|