Tables and Relationships Design Question

[Guest]

I'm using Access 2003: I need to add two fields to my database, ApprovedDate
and EndDate. I don’t know where to put them or how to relate them. The Date
Approved is the date that a Label is assigned to/approved for a specific
wholesaler (not every wholesaler). Using the following scenario, Bud Dry
could have an approved date of 01/01/01 for ABC Beverage, but could have an
approved date of 07/15/02 for AAA Distributors. Do a need a separate table
for Approved and End dates? How would I form the relationships? Everything
I've tried ends up with a label having the same approved/end dates for every
wholesaler.

My tables and relationships are set up as shown below and I’m using forms
and subforms. (frmLabel - formatted as a continuous form, is a subform of
frmBrand; frmBrand – single form, is a subform of frmShipper; frmWholesaler –
continuous, is a subform of frmShipper; A command button on frmWholesaler
opens frmCounties – continuous, which is used to assign the counties to a
wholesaler. (And frmShipper is formatted as a single form.)

If you could picture the following data on my form with subforms:

Shipper is Anheuser Busch
Brand “Budweiser†has Labels Bud Dry, Bud Light, and Bud Ice AND
Brand “Budweiser†can ship to Wholesalers “ABC Beverageâ€, and “AAA
Distributorsâ€, AND
Wholesaler “ABC Beverage†can service the counties White, Brown, and
Scarlett, AND
Wholesaler “AAA Distributors†can service the counties of Finch, Robin, and
Eagle

Tables:

tblShipper
ShipperID
ShipperName

tblBrand
BrandID
BrandName
ShipperID

tblBrandDetails
BrandID
WholesalerID

tblLabel
LabelID
LabelName
BrandID
DateApproved
EndDate

tblWholesaler
WholesalerID
BrandID
WholesalerName

tblWholesalerDetails
WholesalerID
CountyCode

tblCounty
CountyCode
County

Relationships:

One-to-Many from tblShipper to tblBrand
One-to-Many from tblBrand to tblBrandDetails
One-to-Many from tblBrand to tblLabel
One-to-Many from tblWholesaler to tblBrandDetails
One-to-Many from tblWholesaler to tblWholesalerDetails
One-to-Many from tblWholesalerDetails to tblCounty

 

[Guest]

"The Date
Approved is the date that a Label is assigned to/approved for a specific
wholesaler (not every wholesaler). "
therefore you need a cross-reference table like:
LabelID - PK
WholesalerID - PK
Approved Date

You don't define what an 'end date' is so I can't help you there.
If it's an end date for the approval, put it in the same table.
Then you would have to think about what if an approval ends and then
restarts and would you need to keep the whole history or just the current
approval.

-Dorian

 

[Guest]

Thanks. I've created the cross-reference table. And I created a one-to-many
relationship from tblsLabel to the new table (tblLabelDetails) and a
one-to-many from tblWholesaler to tblLabelDetails. Is this correct? I've
tried to create a query and a form from this, but I'm not getting the
expected results.