Still struggling with relating tables

[djclntn]

Orange,



I took notes, care to see?

[orange]

No not really. But I am glad that you got something from the videos.

Did you follow his sample and see why he has the tables and relationships that he does?

[June7]

The attached is what makes sense to me. EDIT: Purpose served, file removed.

Because you are constructing custom unique ID for vehicles, customers, employers, sales reps, the Autonumber fields are not needed. I removed them and set relationships on the constructed ID fields. Had to correct a couple of the CustomerID values in the Vehicles table before the join between Vehicles and Customers could be set.

Some people would recommend to still use the Autonumber fields as PK/FK. If you do, would also want to set the constructed ID fields as Index Yes no duplicates. And the constructed CustomerID field would NOT be in Vehicles, only the FK field related to the customer Autonumber. And NONE of the constructed IDs would be in related tables. You could even have the VIN as the PK in Vehicles. At least it should be set as Index Yes no duplicates.

I simplified table names. Good to see no spaces, special characters, punctuation (underscore can be exception) in field names. This convention recommended for all names.

The images in the attachment field have at least doubled the file size. Was just working with images yesterday. Discovered can be an issue to display images from attachment field (however, yours are working just fine). I prefer to leave the images in a file outside the db and then save the folder path in a text field. Then use image control with ControlSource set to that field. A major reason against Attachment field is file bloat. Access has a file size limit of 2gb. Attachments, especially images, use up that allotment. Although you probably will not have to worry about hitting 2gb even with attached images.

Zip should be a text field because it is an identifier (like SSN and phone numbers), not a number value. As a number field zip like 00045 would drop the leading zeros. And could not have hyphenated zips because this would be a text string.

[orange]

June7 can you post a jpg or pdf of the relationships from that database? I Have Acc2003 so can use the accdb.

[June7]

Here is mdb version - without images of course because no attachment field in 2003.

EDIT: Purpose served, file removed.

[orange]

Thanks for the mdb


Edit: I have attached a jpg of the Relationships for those following this thread.

[djclntn]

Thank you sooooooooooo much June; I know that you went above & beyond what you may do for most people. I gratefully & sincerely appreciate what you've done for me

To both Orange & You I'm going to take a long look at the Relationships between the Tables & try to comprehend their Relationships. I may have question if that's ok

Thank again June

[djclntn]

June or Orange,

In the Relationship window, under the "1" is an arrow. How did you do that & more importantly whatt des it mean?

[June7]

It means that table is the 'one' side of a relationship. It is set by clicking Enforce Referential Integrity. Double click the line joining the fields to open a dialog.
http://office.microsoft.com/en-us/ac...010120534.aspx

[RayMilhon]

Here's a sample, take a look at it and ask questions.

[June7]

Ray, what is it you want djlntn to understand from your example db? You built duplicate tables but did not establish all relationships.

[orange]

June or Orange,

In the Relationship window, under the "1" is an arrow. How did you do that & more importantly whatt des it mean?

He goes through the process of establishing Cardinality in this video

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

[RayMilhon]

Wanted to show 1 possible design and get him to ask some questions on relating them.

[orange]

Ray, I have the same question as June7 - What is the point exactly?

Not everyone can use accdb, that is why I posted June7's response to djcIntn as a jpg.
If you have the relationships defined differently, why not post a jpg and some text to tell us what and why you have adjusted/ developed the model you have?

Putting an answer back inside an accdb isn't really helping-- in my view.

OOPs I was writing while Ray was posting.

[RayMilhon]

For me anyway, I learn more by getting pointed in the right direction and trying to figure it out then coming back with specific questions. It just sticks better for me. Having someone give me too much detail to start with let's me finish quicker but 3 months later I have to ask the same questions again.