INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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.




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

The table of cars should have an autonumber primary key like [CarID]. Your parts table might have a structure like:

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)?

(OP)
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)

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

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

WHERE 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)?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close