Design Questions

[Colin Penman]

Hi folks.

I am working on a db that will allow us to track the visits of our reps to
various retailers. Currently we have a total of 13 different product lines
that may or may not be at different retailers. I fear that I have committed
spreadsheet when I setup the Visit Table so that:

Visit ID is PK
Retailer (when entering in visit is a drop down from Retailer table)
Store Number (drop down from Stores table)
Date of Visit
Rep Name (drop down from Rep table)
then 13 fields, one for each of the 13 product lines where time (in hours)
is entered for that call. (ie 1.0 for one hour, 0.25 for 15 minutes)

Everything seems to be working OK except when I get to reporting.... I have
created an unbound form that allows me to pick startdate, enddate, repname
etc. and just run one report based on the data "picked" I need now to have
a report by vendor that shows only their product lines. Need a way to have
them "pick" the fields to include. The other option is to have a specific
query/report combo that uses only the data for that vendor, but I would like
it cleaner to have one report like the rep one.

Any suggestions on how to make the report work, or on the overall structure??

Any help at all would be greatly appreciated - my head hurts!

Colin

 

[John W. Vinson]

On Thu, 3 Dec 2009 16:01:01 -0800, Colin Penman <Colin

Hi folks.

I am working on a db that will allow us to track the visits of our reps to
various retailers. Currently we have a total of 13 different product lines
that may or may not be at different retailers. I fear that I have committed
spreadsheet when I setup the Visit Table so that:

Visit ID is PK
Retailer (when entering in visit is a drop down from Retailer table)
Store Number (drop down from Stores table)
Date of Visit
Rep Name (drop down from Rep table)
then 13 fields, one for each of the 13 product lines where time (in hours)
is entered for that call. (ie 1.0 for one hour, 0.25 for 15 minutes)

Yep. That's a severe case of spreadsheetitis you have there.

A normalized design would have a table for ProductLines and a table for
VisitLines, with multiple RECORDS per visit.

Everything seems to be working OK except when I get to reporting.... I have
created an unbound form that allows me to pick startdate, enddate, repname
etc. and just run one report based on the data "picked" I need now to have
a report by vendor that shows only their product lines. Need a way to have
them "pick" the fields to include. The other option is to have a specific
query/report combo that uses only the data for that vendor, but I would like
it cleaner to have one report like the rep one.

Any suggestions on how to make the report work, or on the overall structure??

Redesign your tables. If you ever change a product line, or add a new one, or
delete one, you'll have to do a major redesign given your current structure.

 

[Colin Penman]

John, thank you. That is what I suspected. I was wanting to keep the data
entry to a minimum - is there any way to make it easier to enter?
Information like date of visit, rep, store # etc would be the same for many
records. It is time consuming to re-enter the same thing for say 4 product
lines serviced on the same visit (assuming that I have understood your line
of thought - each PL serviced per visit would need one record).

Any more thoughts?

Colin

 

[John W. Vinson]

John, thank you. That is what I suspected. I was wanting to keep the data
entry to a minimum - is there any way to make it easier to enter?
Information like date of visit, rep, store # etc would be the same for many
records. It is time consuming to re-enter the same thing for say 4 product
lines serviced on the same visit (assuming that I have understood your line
of thought - each PL serviced per visit would need one record).

Any more thoughts?

Um?

The date of visit, rep, store etc. would be entered once, and once only, on a
mainform.

The lines data would be entered on a subform, and the only information you
would need would be to pick a product line from a combo box.

Perhaps I'm misunderstanding the nature of the business rules. My idea was
that you'ld have a one to many relationship from Visits to VisitLines.

 

[Colin Penman]

Great! Thank you - that flash of insight was exactly what I needed!

Have a great weekend!