Relationship Help Needed

[justgeig]

accidental double post

[justgeig]

Wouldn't the same info be communicated by
-BrochureCustomer3 wants NonLodging
-BrochureCustomer5 wants Lodging

That works just fine. What I'm running up against now is...what do I do about BrochureCustomer1,2,4,6 that all want to be both Lodging and NonLodging? I just don't know how to "tell" that to the database.

[justgeig]

Since I took out the multi-value fields, I could save it to an mdb file.

Brochure Database - Test 2-1 - Copy.zip

[orange]

I'll look at your mdb and the excel file you posted earlier. I'm on a different machine at the moment.
I think your issue is "the little difference in LocationType and DistributionType".


The issue is here:

Choices the BrochureCustomer has/can make-
-Lodging
-NonLodging
-Lodging and NonLodging

LocationTypes for the RackLocations
-Lodging
-NonLodging


These are separate things and should be identified so. Their usage should be clear.

It would be set up such that, based on our recent posts,

Originally Posted by orange
Wouldn't the same info be communicated by
-BrochureCustomer3 wants NonLodging
-BrochureCustomer5 wants Lodging

and
BrochureCustomer1 wants Lodging and NonLodging
BrochureCustomer2 wants Lodging and NonLodging
BrochureCustomer4 wants Lodging and NonLodging
BrochureCustomer6 wants Lodging and NonLodging

[orange]

This is what I have gleaned from your posts and database. Fleshing this out will lead to a proper database design.

The "business" concerns Brochure Distribution Service for Customers in that Customers Contract with us to distribute, display, and maintain the supply of Brochures concerning their Events and Attractions in Racks in various Locations. Brochures are placed in Racks in designated locations according to Customer selections. A Customer may choose to have his/her Brochures displayed in Lodging, nonLodging or both Lodging and nonLodging locations. We have Routes that include various RackLocations.


A BrochureCustomer may have 0 or 1 active Contracts
A Contract is for a specific Attraction_Event and has a Start and End Date
Each Contract has 1 identified Contact.
Each Contact has 1 set of coordinates(address, phone..)
RackLocations.are identified by Address,City,State,Zip
Each RackLocation has an Owner.
A RackLocation has a RackType.
A RackType may be ??????????????????????????
A RackLocation has a LocationType.
A LocationType may be Lodging or nonLodging.
A RackLocation is associated with 1 RouteArea.

* Your immediate issue--
Do you have data for LocationType for each RackLocation?
Do you have a list of Routes and a list of which RackLocations are on which Route?

If you know:
Each RackLocation.LocationType, and Each BrochureCustomer.DistributionType, then you should have the info you need
to identify(make a list) for each Customer, the RackLocations to be Supplied/Serviced.

And if you have identified RackLocations by RouteArea, you have the info to create a list for each Customer, the RackLocations to be Supplied/Serviced by RouteArea.

* For database design you will need a list of Owners of the RackLocations. And then assign to RackLocations.
A list of RackTypes, assign RackTypes to RackLocations.

Just a few thoughts for your consideration.

For clarification, a BrochureCustomer selects the type of RackLocation from all of the existing RackLocations, or does the Customer have only some of the RackLocations -of the Type he selected??


There are duplicate locations in your RackLocations Table

Id	LocationName
19	Auction
29	Auction
38	Twin
39	Laura's
40	Super 8
41	Holiday
42	Regency
43	Twin
44	Laura's
45	Super 8
46	Holiday
47	Regency

The duplicates will have to be removed, or if these are really distinct locations, then the LocationName needs adjustment to remove any uncertainty.

For testing, I have assigned some DistributionTypes to BrochureCustomers and Locations to Routes, and also some LocationTypes to RackLocations.

Here is a sample of BrochureCustomers and their selected DistributionTypes.

ID	Attraction_Event	DistributionType
1	1st Dibs Quilt Auctions	Lodging
3	A & L Storage Barns	All locations(lodging and NonLodging)
4	Amish Acres	Non-lodging
5	Annie Oakley	Lodging
6	Apple Creek Alley	All locations(lodging and NonLodging)
8	B&L Woodcrafts	Lodging

Here are samples of LocationNames By LocationType By Route

RouteArea	ID	LocationName	RackLocations.LocationType	LocationType.LocationType
1	11	KOA	1	Lodging
1	16	4-H	2	NonLodging
1	22	Ruhe	1	Lodging
1	27	Red Barn	2	NonLodging
1	32	Davis	1	Lodging
1	37	Campgrd.	2	NonLodging
1	42	Regency	1	Lodging
1	47	Regency	2	NonLodging
2	12	Eby's	2	NonLodging
2	17	Fashion	1	Lodging
2	23	Splash	2	NonLodging

Please review and see if these are suitable, at least as a first attempt at what you're trying to do.

[justgeig]

Thank you very much for the detailed response! There is a lot of detail in there so I will take some time and review it. At first glance it looks like it's pretty much spot on. Also, the RackType is NOT the right word to use but the employee is insisting on it. RackType would more accurately be PhysicalDescription of the rack.

Side note: All of the data in there is completely made up so I'm not surprised I duplicated some. I just did come copy n' pasting ;-)

[orange]

You're welcome. As I said before "getting the tables and relationships right is critical to database" - so spend some time up front on design to simplify things.

Post back if you have questions.