Relationship Help Needed

[justgeig]

I'm just certain that I'm making this more complicated than it needs to be because I've been hitting my head against this problem for a few days and haven't come up with how to make it work. Thanks in advance for your help!

I am creating a database for a brochure distribution service. The relevant information that needs to be tracked is the BrochureCustomer, RackLocations, RouteAreas, DistributionType, LocationType, StartDate, EndDate. RackLocations are just the locations that we have a brochure rack that we keep filled. RouteAreas are the different cities that these rack locations are in. DistributionType is the RackLocations that the customer can choose that their brochures go in (Lodging, non-lodging, or all locations) and LocationType states what option the RackLocation is (Lodging or non-lodging.) StartDate and EndDate signify when the contract with the BrochureCustomer is up.



The goal right now is to generate a print-out that will list all the BrochureCustomers as the rows and the RackLocations as columns. An "X" or some other symbol will go in the cells that are a RackLocation that each specific BrochureCustomer does not want their brochures placed in (the DistributionType and the LocationType determine that.) I will use the StartDate and EndDate to filter the results to make sure that no BrochureCustomer is included in the printout that hasn't started their contract or whose contract is finished.

How do I set up my relationships? I've set up join tables in the past but do I really need one (or more) this time? I've also attached one of the many Excel files that is currently being used so you can have an example of what is currently being done. Thanks!

Brochure Checkoff sheet (Sturgis).zip

[orange]

Access and Excel are quite different, as you probably know.
Getting your table structures and relationships correct is key to database.
Your tables and relationships must"accurately" model your business.
Here are some free video tutorials that describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project.

[justgeig]

Thank you for those resources, I'll be sure to check them out.

Any suggestions for my immediate question?

[orange]

I suggest Normalization and ER_R Diagramming. At least get a first cut at a database model.

[justgeig]

I've got it to where the only problem now is taking the raw data and presenting it in a similar fashion to how the excel files are. Any thoughts? I've tried a crosstab query and a report and neither are quite right. Here is what I've got so far.

Brochure Database - Test 2-1.zip

[orange]

Please post your relationships as a jpg.

[justgeig]

relationships

[orange]

I think there is one or more tables missing - but I don't know your data or business as well as you.
It seems there is a Customer table missing. You show Contacts but not Customers.
Also, the is mention of ContractNumber, that perhaps should be linked to a Table of Contracts --just a thought.
I'm not sure what the plus sign in the box means in BrochureCustomers, but if its a multivalued field or a Lookup at the table level - i recommend removing it and adjust the design accordingly.
see if this applies http://access.mvps.org/access/lookupfields.ht

[justgeig]

This database is actually more of a stop-gap for one of our employees until we get our comprehensive database up and running. Right now, everything is VERY segmented and so the BrochureCustomers table is all that is needed for this specific database. She has very much outgrown her many excel files so I wanted to quickly put something together for her.

As for the contract number, that is just a page number/contract number for an InDesign file that holds all of the contracts within one document. Like I said, everything is very segmented and I wasn't around when all the current ways of storing information were concocted. It's worked up until now but the business has experienced very rapid growth and so now I'm playing catch-up on our data.

The plus sign does indeed signify a multi-value field. I don't like using those (never have before) but wasn't sure of how else to do it. Each brochure rack location can either be a "lodging" location or a "non-lodging" location. However, each brochure has the option of being on a lodging only route, a non-lodging only route, or both routes (multiple values?) I read through that link...not sure how to adjust the design to avoid using it.

EDIT: for the benefit of anyone else that reads this thread, the link above is broken. The "m" is missing on the end of it. Should be http://access.mvps.org/access/lookupfields.htm

[justgeig]

I'm second-guessing my relationships now (again.) The real-world relationship between RackLocations and BrochureCustomers is ONLY LocationType and DistributionType. I may be repeating myself but for clarity's sake ... each brochure customer chooses what distribution type they want (lodging, non-lodging, or both.) Each rack location is either a lodging location or a non-lodging location. With that in mind, I don't need a join table do I? At least not like it stands now. Each brochure customer isn't related to the rack locations other than the what type of location it is and what kind of distribution the customer has asked for.

[orange]

Sorry about dropping the trailing 'm' in the link.
I don't have Acc2007 so can not read your accdb file.

Do you have a mock up of the output you'd like from the query?
Can you post the SQL of the query you have?

The link I gave was really for lookups at the table level, so doesn't apply to the multivalued field.
To get rid of the multivalued field, you can create a table (DistributionTypes or whatever you call it), preferably with an ID and a brief description.
Then link on the Id field.

Are you saying LocationType = DistributionType??

Looks like we were both typing at same time.

Seems that LocationType and DistributionTYpe are similar but not the same.
RackLocation can have only 1 of 2 values. Lodging/NonLodging, but Distribution type can have 3 (Lodging,NonLodging/Both).
Does that mean you could physically have 2 Racks for that Customer?

I would remove the link on LocationTYpe and DistributionType.

[justgeig]

I've removed the link and here is where I'm at.

[orange]

Do you have a mock up of the output you'd like?
It isn't uncommon too use codes that are similar but not exactly the same and end up in difficulty -- quite common.

If you try and write a 1-2 line description of these, you often find the issue/overlap/uncertainty. It usually highlights the need for a different element or code.

[justgeig]

Here is a mockup of what is currently being used. It's used as a checklist for the person doing the filling of the racks. The large "X" is to signify that the person filling the rack at that location should not put any brochures from that customer in the rack. That could be signified in any way, it wouldn't have to be an "X".

In this example, BrochureCustomer3 does not want to be in at lodging locations and BrochureCustomer5 does not want to have brochures in non-lodging locations. The rest have chosen to be in all locations.



Forgot to add that this will all be filtered on different cities (RouteArea)

[orange]

You know that 2,3 and 4 are "lodging locations" , and 1 and 5 are "non Lodging locations".

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

Why the NOT logic and then specifying location?