I have a database with columns allowing for six different items to be assigned to one event.
Then you have an incorrectly normalized database. "Fields are
expensive, records are cheap" - if you have a one (event) to many
(item) relationship, the correct structure would be to have TWO tables
related one to many; the second table would have fields for Event
Number and Item.
I would like to combine these columns in a report instead of running a
report for each item. My database fields are event number, staff,
location,date, time, item1, item2, item3, item4, item5,item6. Any ideas.
Please reply in simple english as I am a relatively new user.
It's really easy with the properly normalized structure; with your
current "wide-flat" table you can use a query with a criterion under
each Item field, or you can use a UNION query. Go into the SQL window
of a new Query and type
SELECT [Event Number], [Staff], [Location], [Date], [Time], [Item1]
FROM yourtable
WHERE [Item1] = [Enter item:]
UNION
SELECT [Event Number], [Staff], [Location], [Date], [Time], [Item2]
FROM yourtable
WHERE [Item2] = [Enter item:]
UNION
SELECT [Event Number], [Staff], [Location], [Date], [Time], [Item3]
FROM yourtable
WHERE [Item3] = [Enter item:]
UNION
SELECT [Event Number], [Staff], [Location], [Date], [Time], [Item4]
FROM yourtable
WHERE [Item4] = [Enter item:]
UNION
SELECT [Event Number], [Staff], [Location], [Date], [Time], [Item5]
FROM yourtable
WHERE [Item5] = [Enter item:]
UNION
SELECT [Event Number], [Staff], [Location], [Date], [Time], [Item6]
FROM yourtable
WHERE [Item6] = [Enter item:]
This query can also be used (without the WHERE lines) to migrate your
data into a properly normalized table, which I'd heartily recommend!
