UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To?, Access 2016    
 
   
faca
post Jan 21 2020, 06:36 AM
Post#1



Posts: 54
Joined: 21-May 18



Hello!

I have database for car mechanic...


I have two tables:

Customer:
CustomerID
FirstName
LastName
Address

Vehicle:
VehicleID
CustomerID
Make
Model
HorsePower


Example:
VehicleID -> 10
Current customer ID -> 20

car is sold than i would have

VehicleID -> 10
New customer ID -> 35

How can i have 1 car and 2 customers?

Thanks
This post has been edited by faca: Jan 21 2020, 06:37 AM
Go to the top of the page
 
June7
post Jan 21 2020, 06:55 AM
Post#2



Posts: 1,222
Joined: 25-January 16
From: The Great Land


3 tables

Customers

Vehicles

CustomerVehicles

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
projecttoday
post Jan 21 2020, 07:01 AM
Post#3


UtterAccess VIP
Posts: 11,598
Joined: 10-February 04
From: South Charleston, WV


Further to that:

tblCustomerVehicles

CustomerVehicleID
CustomerID
VehicleID
EntryDate
DeleteDate

Remove customerID from the vehicles table.

When ownership of a vehcle is transferred, a delete date is entered into the current record in tblCustomerVehicles and a new record is entered. You can't delete the old record because you will need to see past data.

--------------------
Robert Crouser
Go to the top of the page
 
orange999
post Jan 21 2020, 07:30 AM
Post#4



Posts: 2,050
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


faca,

Further to the advice received so far, you mention car mechanic which could involve several repair services and/or parts sales. But you mention only the sale of a car. Some clarification of the requirement and scope would be helpful.
Here is a link to a generic vehicle maintenance data model that be useful to you.

--------------------
Good luck with your project!
Go to the top of the page
 
GroverParkGeorge
post Jan 21 2020, 09:56 AM
Post#5


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


This blog post discusses the question of many-to-many relationships, which you have. It is, coincidentally, about Cars and Employees of a company, but if you substitute "Owner" for "Employee" it mostly fits fairly well.

One car can have many owners (sequentially or concurrently)

One owner can have many cars (sequentially or concurrently)

I say that because two people can pool their money and buy a car together in both of their names, can they not?

At the same time, people can own multiple cars.

That makes for a bit more complicated indexing in the junction table required to handle the possibility of both sequential ownership and concurrent ownership.

Perhaps I'm over-complicating this a bit, but you do need to think through all of the implications to your business.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st February 2020 - 11:25 AM