Darn Access Relationships management/creation

[Stephanie1977]

Hi, I really need explanations here :banghead:

I'm trying to establish the relationships in my database but I am missing something, really basic I doubt, in the relationships rules since I can only create one to one Relationship but can't find the reason why. I've change properties of my tables rows and since then, it's a mess

TABLES I HAVE:

CHASSISOEM
IdChassis (pk, autonum)
ShortVIN (text, indexed, no dupe allowed - this is a serial vehicle identification #)

DEALER
IdDealer (pk, autonum)
DealerNickName (txt, indexed, no dupe allowed - I want to be able to refer to my dealers as short nickname ex: Whatever Bus Sales inc would become Whatever as nickname)

CUSTOMER
IdCustomer (pk, autonum)
CustNickName (txt, indexed, no dupe)
DealerNickName (txt, indexed, no dupe, null allowed)

OEMVehicleSale
IdOEMVehSale (pk, autonum)
ShortVin (text, indexed, no dupe allowed )
CustNickName (txt, indexed, no dupe)
DealerNickName (txt, indexed, no dupe, null allowed)

Preowned1 (2, 3)
IdPreowned1 (pk, autonum)
ShortVin (text, indexed, no dupe allowed )
CustNickName (txt, indexed, no dupe)
DealerNickName (txt, indexed, no dupe, null allowed)

RELATIONSHIPS TO BE CREATED:
OEM CHASSIS - ONE TO ONE - OEMVEHICLESALE
OEM CHASSIS - ONE TO MANY - PREOWNED
DEALER - ONE TO MANY - CUSTOMER
CUSTOMER - ONE TO MANY - OEMVEHICLESALE
DEALER - ONE TO MANY - OEMVEHICLESALE
DEALER - ONE TO MANY - PREOWNED
CUSTOMER - ONE TO MANY - PREOWNED

To resume, with thoses tables and Relationships established, it will tell me which vehicle was saled to which Customer (end-user) and if this Customer has a dealer.
I want to test this base before going too much further in my database creation to avoid needing to modify too many tables/forms already created.

I doubt the problem is that my primary keys are autonum and foreign keys txt ... but how can I put numeric properties to a field that needs to be txt in data entry (for my fk)?!?! This isn't logical to me so if someone could explain it would be so appreciated!!!

Thanks in advance !!

p.s. it would have been so much easier if I would have been allowed to post an image ... but first post so it wasn't possible !!! Sorry for the visual inconvenience

 

[sneuberg]

The related table on the many side needs to have a foreign key of the same type as the primary key of the one side. Just one example. This

DEALER
IdDealer (pk, autonum)
DealerNickName (txt, indexed, no dupe allowed - I want to be able to refer to my dealers as short nickname ex: Whatever Bus Sales inc would become Whatever as nickname)

CUSTOMER
IdCustomer (pk, autonum)
CustNickName (txt, indexed, no dupe)
DealerNickName (txt, indexed, no dupe, null allowed)


Should be like


DEALER
IdDealer (pk, autonum)
DealerNickName (txt, indexed, no dupe allowed - I want to be able to refer to my dealers as short nickname ex: Whatever Bus Sales inc would become Whatever as nickname)

CUSTOMER
IdCustomer (pk, autonum)
IdDealer (fk, number(long), indexed dupes allowed)
CustNickName (txt, indexed, no dupe)


When you drag the IdDealer in DEALER to the IdDealer in CUSTOMER in Relationships you will see that this works. You don't have to give the foreign key the same name. That just my personal preference.

Also you don't want or need the DealerNickName in the CUSTOMER table. You can get that when you join the two tables.

 

[jdraw]

Further to sneuberg's advice, please tell us a little about the "Business involved" in plain English.
Please expand upon

...will tell me which vehicle was saled to which Customer (end-user) and if this Customer has a dealer.
I want to test this base before going too much further in my database creation to avoid needing to modify too many tables/forms already created.

Did you build a data model, before building the database?

You can post an image if you zip it and post the zip file.

 

[Stephanie1977]

I'm trying to figure out your explanations sneuberg and probably I just stick too much on the user side view instead of Creator view..
Just in case it clarifies or change your proposition, the way I wish to ultimately manage the data entry and forms creation, is that all ID (pk) will be unknown to the general users, it will remain an invisible software background unique reference if you prefer... not sure i'm clear...

For example, I will never search, call or type an id# and will never show that reference in any of my forms, query or state. ShortVin and CustomerNickname will be the main known reference when referring to a vehicle in the field and a customer in their specific table for example.

If I do as you wrote, the main reference for the Customer or Vehicle would be their ID# no? Geez my nose is bleeding. I definitely miss the basics i guess...

jdraw : All my "system" is right now in excel with heavy codes behind. I have multiple databases that autofeed my recovery/claim logs. This is ok to produce my non-compliance reports for example but a total hell for the follow up ... I have like 2 km of columns to look at in my log resume. The update of the background data is also a nightmare and time consuming. That's why I wanted to try to build the database into access ; it would also ease the data entry.

I have done multimedia classes in the past.. but it's way back as you can tell... I remember more about design and commands than database part...

As for the database, I know it well since I have built it with the tools I had on hand (excel) ; for sure I had to review my actual excel kind-of-database and divide a lot the tables I originally had in excel for access ; and will probably have to do some more in the process.

My company build bus (commercial and school bus) and the database I am trying to create is somewhat the product aftersales service quality management. The ShortVIN# is the main foreign key to be followed cause it's where everything begins.

 

[jdraw]

Access/database is quite different than Excel/spreadsheet.

I am going to point you to a couple of tutorials from RogersAccessLibrary.
You have to work through them, but you'll learn the basics of database and table design --including relationships. It will take about 30-60 minutes to work through a tutorial, but you will learn.

They do have solutions included.

Class Information system
Consolidated Widgets


Access tables are typically narrow and deep, whereas spreadsheets tend to be wide and shallow.
Many people who have an Excel background have difficulty with database concepts -- they have to unlearn some familiar techniques.

95% of a good, useful database is getting the tables and relationships designed to meet the business requirements.

Good luck.

 

[Stephanie1977]

here is the screenshot of the tables that will be needed in providing with the basic information such as which vehicle was sold to which dealer and/or customer

(there's more than what I have explained earlier cause the preowned vehicles will be handle in a similar way to the OEM vehicles- first end-user)

thanks for the zip hint!!

 

[Stephanie1977]

ok thanks for the links I will definitely dive into it !

 

[jdraw]

Just saw your last post and jpg.
I encourage you to work through a tutorial as suggested in my last post.

Where are you located I noticed the french object names...

If you have time you might want to watch these free youtube videos by Dr. Daniel Soper.
He has an 8 part database series. I recommend you watch #1,2 and 4 as soon as possible to clarify concepts of database.
The video series starts here.

Bonne chance.

 

[Stephanie1977]

yes.. the international French is my second major problem with the access version that has been installed ... I haven't been consulted about my preferences lol I'm from Montreal

Will definitely read the documentation you suggested, I want to finally see the light and since faith isn't enough...

 

[jdraw]

I just added some video links in my last post. They would be a good introduction, even before tackling the tutorials from RogersAccessLibrary.

Good luck. It's worth getting your database set up to match your needs.

 

[Stephanie1977]

Great! Thanks a lot for your time and help!

 

[sneuberg]

I'm trying to figure out your explanations sneuberg and probably I just stick too much on the user side view instead of Creator view..
Just in case it clarifies or change your proposition, the way I wish to ultimately manage the data entry and forms creation, is that all ID (pk) will be unknown to the general users, it will remain an invisible software background unique reference if you prefer... not sure i'm clear...

For example, I will never search, call or type an id# and will never show that reference in any of my forms, query or state. ShortVin and CustomerNickname will be the main known reference when referring to a vehicle in the field and a customer in their specific table for example.

If I do as you wrote, the main reference for the Customer or Vehicle would be their ID# no? Geez my nose is bleeding. I definitely miss the basics i guess...

No, as you said they wouldn't be visible .

When you use an Autonumber for the primary keys of your table you are using what's called a surrogate key. If you google "debate on surrogate keys" you will see there are pros and cons. I think one of the cons are they confuse people as they have done to you. Anyway surrogate keys are usually just used to connect the tables in the relationship and joins and the user never sees or interacts with them. If you made a query by joining these table you would join on the IdDealer but would leave that out of the result the query would only display DealerNickName and CustNickName. Also if you made a form and subform for these tables the IdDealer would not be displayed. There are Link Master Fields and Link Child Fields in the subform control that maintain the keys for you.

One thing that the advocates of surrogate keys always seem to leave out is that you still need some sort of visible data that will act as a natural primary key. If you don't you can end up with duplicate data. In your case I noticed that you specified that the nick names were unique (no dupes) so this will be good for that.

 

[Stephanie1977]

now that's clear ! thanks for the explanation!