How to - Qry question

[AJ]

I have a table that has 7 different fields where a product name can be
entered (Product1, Product2, etc)... this is because up to 7 different
products can be affected by the one incident. Some records may have 1
product other all 7, it will vary.

I am trying to write a qry for a report where the report will show:

Record# Product .....(other fields) in columns.

On this report I don't care if the record # is repeated, I just want to be
able to list each product without the "awkwardness" of the spacing that will
be created if I leave all 7 fields and the report sorts by the record #.

I hope that makes sense.
any help is greatly appreciated.

Thank you.

 

[Duane Hookom]

Your table structure is not normalized. Each Product should create a unique
record in a related table.

The solution depends on the problem which I am having trouble identifying
(other than the table structure). I think you will need to first normalize
your table with a union query like:
SELECT RecordNum, Product1 as Product
FROM tblMySpreadsheet
WHERE Product1 Is Not Null
UNION ALL
SELECT RecordNum, Product2
FROM tblMySpreadsheet
WHERE Product2 Is Not Null
UNION ALL
SELECT RecordNum, Product3
FROM tblMySpreadsheet
WHERE Product3 Is Not Null
UNION ALL
SELECT RecordNum, Product4
FROM tblMySpreadsheet
WHERE Product4 Is Not Null
UNION ALL
SELECT RecordNum, Product5
FROM tblMySpreadsheet
WHERE Product5 Is Not Null
UNION ALL
SELECT RecordNum, Product6
FROM tblMySpreadsheet
WHERE Product6 Is Not Null
UNION ALL
SELECT RecordNum, Product7
FROM tblMySpreadsheet
WHERE Product7 Is Not Null;

Use the union query in the Record Source of your report.

 

[AJ]

Thank you very much!!

Your table structure is not normalized. Each Product should create a unique
record in a related table.

The solution depends on the problem which I am having trouble identifying
(other than the table structure). I think you will need to first normalize
your table with a union query like:
SELECT RecordNum, Product1 as Product
FROM tblMySpreadsheet
WHERE Product1 Is Not Null
UNION ALL
SELECT RecordNum, Product2
FROM tblMySpreadsheet
WHERE Product2 Is Not Null
UNION ALL
SELECT RecordNum, Product3
FROM tblMySpreadsheet
WHERE Product3 Is Not Null
UNION ALL
SELECT RecordNum, Product4
FROM tblMySpreadsheet
WHERE Product4 Is Not Null
UNION ALL
SELECT RecordNum, Product5
FROM tblMySpreadsheet
WHERE Product5 Is Not Null
UNION ALL
SELECT RecordNum, Product6
FROM tblMySpreadsheet
WHERE Product6 Is Not Null
UNION ALL
SELECT RecordNum, Product7
FROM tblMySpreadsheet
WHERE Product7 Is Not Null;

Use the union query in the Record Source of your report.