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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
Design Questions
|
#4
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|