Tables and Relationships (Re Posted)

[Katherine R]

I posted a question yesterday and followed the advice at the end of this
post, but I'm still having problems. I formed a one-to-many relationship
from tblLabel to the new table (tblLabelDetails) and a one-to-many
relationship from tblWholesaler to tblLabelDetails. Is this correct? My
queries and forms based on the new table are still returning an approval date
(and/or end date) based on the label. If the label Bud... has an approval
date of 01/01/01 for wholesaler ABC Beverage, label Bud for wholesaler AAA
Distributors gets the same date. I need the dates to be different. Can
someone expand on the answer below? Thanks.

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

"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

"Katherine R wrote":

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.

 

[Douglas J Steele]

Are you storing the Approval Date in the new tblLabelDetails, or are you
still storing it in tblLabel?

 

[Katherine R]

I'm storing it in the new tblsLabelDetails

Are you storing the Approval Date in the new tblLabelDetails, or are you
still storing it in tblLabel?

 

[Douglas J Steele]

Are you querying the correct field? What does the SQL for your queries look
like?

 

[Katherine R]

I'm not sure - probably not. I've tried many combinations. This is what I
have currently - - I've just created a query from tblLabelDetails with four
fields - LabelID, WholesalerID, ApprovedDate, and EndDate. I've created a
form from the query (frmLabelDates). I've placed a button on frmLabel to
open frmLabelDates. They are linked by the LabelID number. Form
frmLabelDates opens to a blank record. I then have to enter in the LabelID,
WholesalerID, and the dates. I guess what I expected was for the LabelID and
WholesalerID to already be there when the Label Dates form pops up, and I
could just enter the dates.