How do I best normalize this data?

[M Skabialka]

In trying to set up a new normalized table I need some help with this set of
data.
There are six fields to consider.
Item, description, and four dates that can occur with this item. There is
always an L date and sometimes an F date, an H date and/or an M date. i.e.
two or three things out of L H F and M happen to each item.
e.g.
Item Descr LDate FDate HDate MDate
abc widget Jan07 Feb07 Mar07
abd widget Feb07 Mar07 Apr07
abe widg2 Mar07 Apr07

Should I have rows as shown or should there be a table of items and
description, and another table:
Item DateType Date
abc L Jan07
abc F Feb07
abc M Mar07
abd L Feb07
abd F Mar07
abd M Apr07

Reports will show a table of months at the top, items at the left, and an
indication on the graph (kind of like in Excel or Project) of the item
occuring.

Jan07 Feb07 Mar07 Apr07
abc L F M
abd L F H
abe L H

What is the best way to set up the tables in order to create the output most
efficiently?
Thanks,
Mich

 

[John W. Vinson]

In trying to set up a new normalized table I need some help with this set of
data.
There are six fields to consider.
Item, description, and four dates that can occur with this item. There is
always an L date and sometimes an F date, an H date and/or an M date. i.e.
two or three things out of L H F and M happen to each item.
e.g.
Item Descr LDate FDate HDate MDate
abc widget Jan07 Feb07 Mar07
abd widget Feb07 Mar07 Apr07
abe widg2 Mar07 Apr07

Should I have rows as shown or should there be a table of items and
description, and another table:
Item DateType Date
abc L Jan07
abc F Feb07
abc M Mar07
abd L Feb07
abd F Mar07
abd M Apr07

Absolutely this latter design.

Reports will show a table of months at the top, items at the left, and an
indication on the graph (kind of like in Excel or Project) of the item
occuring.

Jan07 Feb07 Mar07 Apr07
abc L F M
abd L F H
abe L H

A Crosstab query based on the tall-thin design will work for you.


John W. Vinson [MVP]

 

[M Skabialka]

I hadn't considered the cross-tab query for my report but this table design
makes more sense.
Thanks for your help.
Mich

 

[M Skabialka]

Close - but what I am getting is a line per entry:

Jan Feb Mar Apr
abc L
abc F
abc M
abd L
abd F
abd H
abe L
abe H

and the Cross-tab query created "Jan" - "Dec" as my headings so I can't show
more than a year, though the data covers several years. Can it be made to
create column headings as needed?
The query tried to Count rather than show the L, M, H, F so I used First,
but will than catch them all?
What is the "tall-thin design" design?

Thanks,
Mich