Data-Sorting Report Question - Version: 2000 (9.0)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a report that sorts the data from a table. The table is
set up as follows:

Quantity0, Description0, Color0, Size0, Quantity1, Description1, Color1,
Size1, etc...

I'm looking to create a report that sorts the data as follows:

Description
Color
Size - Quantity
Size - Quantity
Color
Size - Quantity

Basically, I need to coalesce all of the description fields according to the
data entered in them, then do the same for the colors, sizes and quantities
the apply to each description. I hope that makes sense! If not, I can send
the actual table to anyone who's interested.

Thanks in advance,
Jessica
 
If you have any say in how the table(s) are set up, I would not go any
further until the table(s) were normalized. Repeating groups of columns is
an ugly way to set up data for reporting and querying.

If you can't normalize then consider creating a union query
SELECT 0 as Item, Quantity0 as Quantity, Description0 as Description, Color0
As Color, Size0 as Size
FROM tblSpreadsheet
UNION ALL
SELECT 1, Quantity1, Description1 , Color1, Size1
FROM tblSpreadsheet
WHERE Quantity1 is not null
UNION ALL
SELECT 2, Quantity2, Description2, Color2, Size2
FROM tblSpreadsheet
WHERE Quantity2 is not null
UNION ALL
--etc--
You can then create a report that groups first by Description then Color,
then Size.
 
Unfortunately there's not a lot i can do about the table set up. The Union
query sounds like just the thing! However, I'm having a little trouble with
the code. Here's what I have in my Union query:

SELECT 0 as Item, Quantity0 as Quantity, Description0 as Description, Color0
As Color, Size0 as Size
FROM DataImportSpr05
UNION ALL
SELECT 1, Quantity1, Description1 , Color1, Size1
FROM DataImportSpr05
WHERE Quantity1 is not null
UNION ALL
SELECT 2, Quantity2, Description2, Color2, Size2
FROM DataImportSpr05
WHERE Quantity2 is not null
UNION ALL
SELECT 3, Quantity3, Description3, Color3, Size3
FROM DataImportSpr05
WHERE Quantity3 is not null
UNION ALL
SELECT 4, Quantity4, Description4, Color4, Size4
FROM DataImportSpr05
WHERE Quantity4 is not null
UNION ALL
SELECT 5, Quantity5, Description5, Color5, Size5
FROM DataImportSpr05
WHERE Quantity5 is not null
UNION ALL
SELECT 6, Quantity6, Description6, Color6, Size6
FROM DataImportSpr05
WHERE Quantity6 is not null
UNION ALL
SELECT 7, Quantity7, Description7, Color7, Size7
FROM DataImportSpr05
WHERE Quantity7 is not null
UNION ALL
SELECT 8, Quantity8, Description8, Color8, Size8
FROM DataImportSpr05
WHERE Quantity8 is not null
UNION ALL

When I go to view the query, I get this notice: "Syntax error in query.
Incomplete query clause." I've never really worked with SQL before, so I'm
sure I'm missing something small. Any help would be appreciated!

Thanks again,
Jessica
 
Unfortunately there's not a lot i can do about the table set up. The Union
query sounds like just the thing! However, I'm having a little trouble with
the code. Here's what I have in my Union query:

SELECT 0 as Item, Quantity0 as Quantity, Description0 as Description, Color0
As Color, Size0 as Size
FROM DataImportSpr05
UNION ALL
SELECT 1, Quantity1, Description1 , Color1, Size1
FROM DataImportSpr05
WHERE Quantity1 is not null
UNION ALL
SELECT 2, Quantity2, Description2, Color2, Size2
FROM DataImportSpr05
WHERE Quantity2 is not null
UNION ALL
SELECT 3, Quantity3, Description3, Color3, Size3
FROM DataImportSpr05
WHERE Quantity3 is not null
UNION ALL
SELECT 4, Quantity4, Description4, Color4, Size4
FROM DataImportSpr05
WHERE Quantity4 is not null
UNION ALL
SELECT 5, Quantity5, Description5, Color5, Size5
FROM DataImportSpr05
WHERE Quantity5 is not null
UNION ALL
SELECT 6, Quantity6, Description6, Color6, Size6
FROM DataImportSpr05
WHERE Quantity6 is not null
UNION ALL
SELECT 7, Quantity7, Description7, Color7, Size7
FROM DataImportSpr05
WHERE Quantity7 is not null
UNION ALL
SELECT 8, Quantity8, Description8, Color8, Size8
FROM DataImportSpr05
WHERE Quantity8 is not null
UNION ALL

When I go to view the query, I get this notice: "Syntax error in query.
Incomplete query clause." I've never really worked with SQL before, so I'm
sure I'm missing something small. Any help would be appreciated!

Thanks again,
Jessica
 
Try remove the last UNION ALL.
....
SELECT 8, Quantity8, Description8, Color8, Size8
FROM DataImportSpr05
WHERE Quantity8 is not null;
 
That fixed it, thanks! The query is now running properly. I'm still having
trouble getting the items to sort properly in the report, unfortunately.
Here's the output format I'm looking for:

Description: Men's Windowpane Polo
Color: Charcoal
Size/Quantity: S - 2
M - 4
XL - 3
Color: True Blue
Size/Quantity: M - 1
L - 3
2XL - 2

Does that make sense? I'm basically looking to sort total quantities for
each size & color for 6 different apparel items.

Thanks again for all the help,
Jessica
 
This shouldn't be too difficult using the sorting and grouping dialog in the
report design view.
 
Back
Top