Still struggling with relating tables

[djclntn]

I don’t know if I ought to give up & quit, which I don’t easily do. I’m still struggling with my make-shift car dealership dbs. I try to follow the rules of Normalization, yet I can’t get these Tales to relate for me no matter what I try

I have the following 5-Tables:
1. Customers Employer- (customers employment info – address, contact, etc)
2. Customers Employment- (Where the customer works/position, DateHired, & MonthlyIncome)
3. Customers Finance- (how much is the vehicle, DownPayment, & MonthlyPayments)
4. Customers Info- (Customers’ contact info)
5. Sales Rep- (Contact info, commission, DateHired, etc.)
6. Vehicle Inventory- Vehicles info)

There are a few things I would like to do, but I don’t know how to relate these Tables to achieve my goals as followed:
1. I want to see both the ‘CustomerID’ (related to my Customers Table) & the ‘SalesRepID’ in my Inventory Table.
2. I want to know how I can entered a ‘CustomerID’ just once & have it reflect (I don’t know what word to use here) into ALL of my other Tables. Ie. If I enter a ‘CustomerID’ in my Inventory Table (when a customer purchases a vechicle) then I would like to have that “CustomerID’ be in my Customers Info Table, Customers Finance Table, as well as in the Customer Employment Table.

I ought to only have to input the ‘CustomerID once, but I’ve spent a week trying to figure out how it can be done. Note, I’ve had help from many people here on this forum regarding this post, but I’m starting ALL over from scratch & just REALLY REALLY struggling!!!!!!

I’m not asking anyone to do my work; just some re-direction starting from scratch.

[aytee111]

One table per customer. There is no need to have separate tables unless there is a one-to-many relationship and it seems to me that all the customer tables have a one-to-one relationship. That is making it more complex than it needs to be. Customer anem and address info (only one), employment details (only one), employer info (only one) etc.

One table per sales rep with all their static info, one table per vehicle in inventory. Then the information as to the vehicle being sold - customer, sales rep, vehicle id, cost, monthly payments, etc can go in one transaction details table.

Well done for trying to do the normalization thing. Note, however, that key fields are what holds the relationships together so these fields are duplicated wherever needed. Such as the detail table above will need to carry the key id from all three other tables.

[djclntn]

You really have me confused. What do you mean, "One table per customer?" & "One Table per sales Rep" "One table per vehicle inventory?

I have no idea what your suggestion; I'm going to have 1,000 tables in my dbs

[orange]

You will NOT have a table per Customer, a table per Vehicle etc.

But you can't start by saying I have 5 tables. Now, you may have created 5 tables, but there's nothing sacred about that.

Do you have a write up/description of what your application is about?
A paragraph or two that can be parsed and scrutinized to make sure what that everyone who reads it will understand the application.

Did you watch the videos? Did you follow how he broke down the Orders and LineItems, and built an Item table? And how he developed the Customer table? And how the Price of an Item may change with time, so he had to adjust how he recorded Price and quantity?

[djclntn]

I'm going to watch them again, Orange

[June7]

Covering same ground from https://www.accessforums.net/databas...les-21700.html

Regarding the Vehicle and Customer tables. If you put vehicleID in CustomerInfo table don't put customerID in VehicleInventory table or vice versa. Since customer could buy more than one vehicle what you want is the customer ID in VehicleInventory table. Link the tables on CtmerInfoID. Remove the CustomerID text field from VehicleInventory. Remove VCHLID from CustomerInfo.

Limit customer to only one employer and put employment info in the CustomerInfo table. Link CustomerInfo to Employers on EmployerID. The junction CustomersEmployment table not needed. This assumes every customer has an employer so no blank fields. Even if customer self-employed could require these fields have a value.

Have you decided not to maintain details of arrangement with financial institution?

Imagine this.

Person walks into dealership, decides to buy car. Salesrep opens vehicle record and tries to select customer from a combobox on form. Oh, wait, this is a new customer, never did business here. Another form opens for entry of customer details. Close form. Now customer ID is available for selection in the combobox of vehicle record.

Is that what you want to achieve?

Alternative. Salesrep opens customer info form, searches for customer. No record found. Enter new record. Or record found and data verified. Close form. Open form for vehicle record. Select customer from combobox.

[djclntn]

Hey June,

Update, I implemented the use of LookUp Fields as followed:
1. In Vehicle Inventory Table I have a Lookup field for ‘Customer’ (referencing back to the Customers Table) -&- a Lookup Field for ‘SalesRep’ (referencing back to the Sales Rep Table).
2. In the Customer Info Table I created a Lookup Field for ‘Employer’ (referencing back to the Customers Employer Table).

One trick I realized which maybe I was missing ALL along is, if in the Sales Rep Table I have the ‘SalesRepID’field and in the Customers Table I name the ‘SalesRepID’ to just ‘SalesRep’ field. Likewise with ‘CustomerID’ in the Customer Table -&- just ‘Customer’ in the Vehicle Inventory Table. Am I sort of on the right track here? If so, I surely owe it ALL to you guys- Orange, Ray, & you! I find the one advantage using the Lookup feature is that it relates the Tables for you – in a sense.

Now June, in your last post in inquired about the financial institution arrangements. Well, yes I do except I’m not going to use outside financial institution, but the deal ship will offer its own finance options. Therefore, I have this rough idea for a Table.
- Customer: I’ll try to use the LookupField to the Vehicle Inventory Table
- SalePrice: I may need to input that manually unless I can figure out a way to pull it from the Vehicle Inventory Table – Any ideas???
- DownPayment:
- AmountBorrowed: Which I have set as a calculated Field, ([SalePrice]-[DownPament])
- NumberfMonths:
- AnnualRate: Percentage
- MonthlyPayments: This I get from an online auto calculator site where I punch in the Amount Borrowed, Number of Months, & the Annual interest rate & I get the monthly payment.

Now I need to work on creating Forms...wish me luck. That’s where I’m at right now – any comments?

[June7]

I NEVER set lookups in tables. As developer, when I view tables I want to see real values. I don't let users work with tables and queries. Use forms and reports as data interface.

Use whatever naming convention helps you recognize which field is referenced.

SalesRep should NOT be in Customers table. This limits customer to one sales rep for all purchases. The sales rep is info about the sale of a vehicle. Sales Rep goes in Vehicles (or Sales table).

As I pointed out before, financial arrangements will be unique for each sale. This is a 1-to-1 relationship. Info could be in the Vehicles table.

The customer lookup should be to the Customers table, not Vehicles.

[djclntn]

June,

Why vwhen I use the LookUp Wzard & try to pull 'Customer' from my Vehicle Inventory Table the vaule are numberss instead of text?

[djclntn]

Can you explain the difference between LookUp & Combo Box?

[June7]

Because the customer field in vehicle inventory is a number (fk) with its own lookup to the Customers table. The alias will not pass through. Do all customer lookups to the Customers table.

If you want to show the sale and finance info in separate table, do not put customer ID in Vehicles. The sale/finance table will be a junction table with Customer and Vehicle IDs as FKs. The combination of customer and vehicle IDs will be unique in this table. Do not duplicate the sale amount into two tables. The sales amount in Vehicles could be MSRP (else don't have it at all) but the sale/finance table would have the actual negotiated sale amount.

A Lookup can only be set up with a combobox or listbox. Lookup is an operation, combo and list boxes are the tools to do the lookup.

[djclntn]

Is thereany chance i could send you my dbs & you can relate my tbls? I know, I'm probably asking for too muchhere. Otherwise, I'm going to chuck this project all together.

[June7]

Attach to post and will look at.

[djclntn]

I know that I'm probably asking a lot from you, but thanks!

BTW, I put the finance field in with the inventory table

[orange]

I have acc2003 so can not access the accdb.
I'm sure June7 will help you get it resolved.

My hope is that you will get a data model and understand how and why the relationships get defined, rather than just a physical data base.

How did the second trip through the videos go?