Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I link tables..(or whatever it is called that I need to do)?

Status
Not open for further replies.

chris86t

Technical User
Jun 13, 2008
41
US
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.




 
The table of cars should have an autonumber primary key like [CarID]. Your parts table might have a structure like:
[tt]
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
[/tt]
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
 
Golom, I've looked at several templates/samples. I can't find any that are setup similar to my needs. I will read the site you refrenced to see if it helps.

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)
 
Using the table structure that Duane suggested, your basic query would look something like
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.CarID
And you can then restrict what appears with various WHERE clauses, for example
[tt]
[blue]WHERE C.Model = '300ZX'[/blue] --> A particular Model

[blue]WHERE C.Model = '300ZX'[/blue]
[blue] AND P.PartTypeID = 'Starter'[/blue] --> A specific part for a model

[blue]WHERE C.Model = '300ZX'[/blue]
[blue] AND P.Available = TRUE[/blue] --> Only parts in stock
[/tt]
 
Here's a REAL simple db that does what you want. Click on the two vehicles to see the parts.

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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top