Make a field based on combining records?

[Katherine R]

My database (Access 2003) is for Brands Registration. Each brand can have
many labels. Each label can be registered to many wholesalers. I need to be
able to enter a date that a label is registered to a particular wholesaler.
Number 14, Bud Dry would have a different date for wholesaler 469 than it
would have for wholesaler 963. My database is complete and working fine,
with the exception of the date issue. Can anyone tell me how to do this?

BrandID tblBrand LabelID tblLabel WholesalerID tblWholesaler
1 Budweiser 14 Bud Dry 469 ABC Beverage
1 Budweiser 14 Bud Dry 963 AAA Distributors

Basically, I need 1-14-469 to have a registration date of 01/01/2006 and
1-14-963 a date of 03/01/2006.

 

[Klatuu]

My database (Access 2003) is for Brands Registration. Each brand can have
many labels. Each label can be registered to many wholesalers. I need to be
able to enter a date that a label is registered to a particular wholesaler.
Number 14, Bud Dry would have a different date for wholesaler 469 than it
would have for wholesaler 963. My database is complete and working fine,
with the exception of the date issue. Can anyone tell me how to do this?

BrandID tblBrand LabelID tblLabel WholesalerID tblWholesaler
1 Budweiser 14 Bud Dry 469 ABC Beverage
1 Budweiser 14 Bud Dry 963 AAA Distributors

Basically, I need 1-14-469 to have a registration date of 01/01/2006 and
1-14-963 a date of 03/01/2006.

 

[mnature]

Since you have given no information about your table structure, it is really
difficult to give you a specific answer. Generally, though, I would think
that you have a table that keeps track of your stock coming in to your
business. Seems like you could add a date field to wherever you keep track
of your incoming stock, which would tie a date to a label and wholesaler.

 

[Katherine R]

Hi. I may be giving you more information than you need, but here goes. I've
posted the below previously, but didn't get a lot of feed back. It was
suggested that I create a cross reference table and I have done so. The
cross reference table is as follows:

tblLabelDates
LabelID (PK)
WholesalerID (PK)
ApprovedDate
EndDate

I have a one-to-many relationship from tblLabel to tblLabelDates and from
tblWholesaler to tblLabelDates.

I have a query that brings the Brand, Label, & Wholesaler together and a
form based on this query. A command button opens a form that is based on a
LabelDates query which is based on the LabelDates table. The command button
links the two forms together by LabelID. I think it needs to link them
together by the combination of LabelID AND WholesalerID. Can that be done?
I think this may solve my problem if someone can tell me how to do it.

I apologize for such a long posting. My previous posting listing my table
structure is below. Thanks!

Previous Posting:

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