How do I link tables..(or whatever it is called that I need to do)?
How do I link tables..(or whatever it is called that I need to do)?
(OP)
I've worked with simple databases before. They were glorified spreadsheets. Now I'm working on something more complicated. I may be over thinking this, but can't figure out what to do. Here is my goal:
Create a database of junk cars by make, model, and year. (I can do this part)
Create an inventory of parts available in each car. (This is where I strugle)
When I query for a car, I want to see all parts available in the car. When a part is sold, I want to mark it as unavailable so that the next query will not show it.
I will be using VB.net to query the Access database. I can convert over to MySql if you think it is necessary. I will gladly read a book or another website if you have a good recommendation. I just haven't been able to find anything yet.
Create a database of junk cars by make, model, and year. (I can do this part)
Create an inventory of parts available in each car. (This is where I strugle)
When I query for a car, I want to see all parts available in the car. When a part is sold, I want to mark it as unavailable so that the next query will not show it.
I will be using VB.net to query the Access database. I can convert over to MySql if you think it is necessary. I will gladly read a book or another website if you have a good recommendation. I just haven't been able to find anything yet.
Talk To Other Members
RE: How do I link tables..(or whatever it is called that I need to do)?
RE: How do I link tables..(or whatever it is called that I need to do)?
tblCarParts
======================
CarPartID (autonumber primary key)
CarID (relates to CarID in your car table)
PartTypeID (relates to a lookup table of part types such as Transmission, Glass, Body, Lights, or whatever)
Description
Quantity
This is only a guess since I don't have a clue what you really want to store for each part
Duane
Hook'D on Access
MS Access MVP
RE: How do I link tables..(or whatever it is called that I need to do)?
dhookom, I'm having trouble grasping how the two are linked. Table cars will have year, make and model info. Table carparts will have a list of car parts and if the part is available (maybe pricing info etc).
So, lets say I want to
select * from tblCars where Model = '300ZX'
And I want it to return a list of parts available in the 300ZX. How would I do that (or where can I look to learn how)? Below are examples of the table setups.
tblCars
==========
CarID (autonumber primary key)
Model_Year (year of the car)
Make (make of the car)
Model {model of the car)
tblCarParts
======================
CarPartID (autonumber primary key)
CarID (relates to CarID in your car table)
PartTypeID (relates to a lookup table of part types such as Transmission, Glass, Body, Lights, or whatever)
Description
Available (is the part still in the car)
RE: How do I link tables..(or whatever it is called that I need to do)?
CODE
SELECT C.CarID, C.Model_Year, C.Make, C.Model, P.PartID, P.PartTypeID, P.Description, P.Available FROM tblCars As C INNER JOIN tblCarParts As P ON C.CarID = P.CarIDWHERE C.Model = '300ZX' --> A particular Model
WHERE C.Model = '300ZX'
AND P.PartTypeID = 'Starter' --> A specific part for a model
WHERE C.Model = '300ZX'
AND P.Available = TRUE --> Only parts in stock
RE: How do I link tables..(or whatever it is called that I need to do)?
I entered the data straight into the tables--a no-no. You will need to add a form (or add combos to the existing form) to add vehicles and mark parts as available or sold. This isn't completely normalized, because each separate part should have a distinct ID, but (I guess) a front fender on a Toyota Corolla is a Toyota Corolla front fender. And I suspect you want to keep it simple.
I'm currently using Access 2013. The first attachment is in that format. The second attachment is in Access 2002-2003 format.
Look at the relationships table to understand what's happening.
Yes. I misspelt Cor(r)olla.
Peter