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

[chris86t]

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.

 

[Golom]

See my reply in thread702-1694042

 

[dhookom]

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

 

[chris86t]

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)

 

[Golom]

Using the table structure that Duane suggested, your basic query would look something like

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]

 

[PeterMAS]

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