A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Design Questions



 
 
Thread Tools Display Modes
  #1  
Old December 4th, 2009, 01:01 AM posted to microsoft.public.access.tablesdbdesign
Colin Penman
external usenet poster
 
Posts: 1
Default Design Questions

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
  #2  
Old December 4th, 2009, 01:30 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Design Questions

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

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.
--

John W. Vinson [MVP]
  #3  
Old December 4th, 2009, 04:28 AM posted to microsoft.public.access.tablesdbdesign
Colin Penman[_2_]
external usenet poster
 
Posts: 2
Default Design Questions

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" wrote:

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

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.
--

John W. Vinson [MVP]
.

  #4  
Old December 4th, 2009, 05:29 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Design Questions

On Thu, 3 Dec 2009 19:28:01 -0800, Colin Penman
wrote:

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.

--

John W. Vinson [MVP]
  #5  
Old December 4th, 2009, 06:52 AM posted to microsoft.public.access.tablesdbdesign
Colin Penman[_2_]
external usenet poster
 
Posts: 2
Default Design Questions

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

Have a great weekend!



"John W. Vinson" wrote:

On Thu, 3 Dec 2009 19:28:01 -0800, Colin Penman
wrote:

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.

--

John W. Vinson [MVP]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:29 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.