Would this be a Crosstab or pivot or what?

[DaveMac]

I already know this is bad DB design however I did not design it. Here is the issue. I need a query that results in:

Item, Description, UPC, QTY, Tag_Num
55, Bike, 12345, 21, 77
71, cart, 55552, 39, 901
88, bike, 12345, 5, 901
Etc…

From a table that is laid out as:

Tag_Num, Description1, UPC1, Qty1, Item1 etc…

This DAMN table goes to 25 so I have Description 1 though Description25 in rows but I need this data as columns.

Sorry but I have not run into this before because I normally don’t deal with type of setup. Any help is appreciated.

Dave

 

[PHV]

Use a normalisation UNION query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveMac]

Thanks for the response.

I did a quick search on what you recommended however I only found 4 results and only 1 looks relavent.

Based on that one here is what I have. Of course it does not work because I still do not understand what is needed. So the UNION statement allows a different fields data to be selected and placed in the same results field?

Here is what I have

SELECT pallet_tag_num and
Pallet_Details.DESC01 from Pallet_Details
Union
Pallet_Details.DESC02 from Pallet_Details
Union
Pallet_Details.DESC03 from Pallet_Details
Union
Pallet_Details.DESC04 from Pallet_Details
Union
Pallet_Details.DESC05 from Pallet_Details
Union
Pallet_Details.DESC06 from Pallet_Details

etc...

 

[dhookom]

You weren't too close ;-). What fields/columns do you need to see in the result? Making a guess based on your postings:

SELECT  Tag_Num, DESC01 As Description, UPC01 as UPC, Qty01 AS Quantity, Item01 As Item
FROM Pallet_Details
UNION ALL
SELECT  Tag_Num, DESC02, UPC02, Qty02, Item02
FROM Pallet_Details
UNION ALL
SELECT  Tag_Num, DESC03, UPC03, Qty03, Item03
FROM Pallet_Details
UNION ALL
SELECT  Tag_Num, DESC04, UPC04, Qty04, Item04
FROM Pallet_Details
UNION ALL
-- etc --
SELECT  Tag_Num, DESC25, UPC25, Qty25, Item25
FROM Pallet_Details;

You might want to eliminate records where the Qty is 0 or some field is null. This might also be too complex to run with 25 UNION ALLs in it.

Duane
Hook'D on Access
MS Access MVP

 

[DaveMac]

Thanks to All I am good now!

One not to future readers:
With some filtering the query runs much faster. After 8 items I was very slow however by adding a where qty > 0 after each select the query ran very fast. So if your hign fields can be empty but you need to check them try that to get better speed.

Here is the code that worked the best and again thanks!



SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC01 as Description , Pallet_Details.QTY01 as Qty, Pallet_Details.UPCA01
FROM Pallet_Details ;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC02 , Pallet_Details.QTY02, Pallet_Details.UPCA03
FROM Pallet_Details where QTY02 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC03 , Pallet_Details.QTY03, Pallet_Details.UPCA03
FROM Pallet_Details where QTY03 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC04 , Pallet_Details.QTY04, Pallet_Details.UPCA04
FROM Pallet_Details where QTY04 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC05 , Pallet_Details.QTY05, Pallet_Details.UPCA05
FROM Pallet_Details where QTY05 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC06 , Pallet_Details.QTY06, Pallet_Details.UPCA06
FROM Pallet_Details where QTY06 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC07 , Pallet_Details.QTY07, Pallet_Details.UPCA07
FROM Pallet_Details where QTY07 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC08 , Pallet_Details.QTY08, Pallet_Details.UPCA08
FROM Pallet_Details where QTY08 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC09 , Pallet_Details.QTY09, Pallet_Details.UPCA09
FROM Pallet_Details where QTY09 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC10 , Pallet_Details.QTY10, Pallet_Details.UPCA10
FROM Pallet_Details where QTY10 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC11 , Pallet_Details.QTY11, Pallet_Details.UPCA11
FROM Pallet_Details where QTY11 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC12 , Pallet_Details.QTY12, Pallet_Details.UPCA12
FROM Pallet_Details where QTY12 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC13 , Pallet_Details.QTY13, Pallet_Details.UPCA13
FROM Pallet_Details where QTY13 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC14 , Pallet_Details.QTY14, Pallet_Details.UPCA14
FROM Pallet_Details where QTY14 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC15 , Pallet_Details.QTY15, Pallet_Details.UPCA15
FROM Pallet_Details where QTY15 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC16 , Pallet_Details.QTY16, Pallet_Details.UPCA16
FROM Pallet_Details where QTY16 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC17 , Pallet_Details.QTY17, Pallet_Details.UPCA17
FROM Pallet_Details where QTY17 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC18 , Pallet_Details.QTY18, Pallet_Details.UPCA18
FROM Pallet_Details where QTY18 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC19 , Pallet_Details.QTY19, Pallet_Details.UPCA19
FROM Pallet_Details where QTY19 > 0;

UNION ALL
SELECT Pallet_Details.PTAGNO, Pallet_Details.STATUS, Pallet_Details.OrderNbr, Pallet_Details.DESC20 , Pallet_Details.QTY20, Pallet_Details.UPCA20
FROM Pallet_Details where QTY20 > 0;

 

[dhookom]

I wouldn't expect the SQL to work with about 19 extra semi-colons:

...
FROM Pallet_Details where  QTY18 > 0[b][red];[/red][/b]
...

Duane
Hook'D on Access
MS Access MVP