WHERE statement in table

[pyromaniac511]

TBL_Brands
.ID
.Brand
TBL_Models
.ID
.Brand (Look up from TBL_Brand)
.Model
TBL_Inventory
.ID
.Brand (Look up from TBL_Brand)
.Model (Look up from TBL_Models)
.Other things to be added like Serial Number, Description, ETC.

***SQL SO FAR ***
SELECT TBL_Models.ID, TBL_Models.Model
FROM TBL_Models
WHERE (((TBL_Models.Brand)=[TBL_Brands].[ID]))
ORDER BY TBL_Models.Model;

The goal was to select the brand first and get a list of models that are part of that brand (IE Cisco - 2800, 7600 ...) but when I click the drop down a pop up asks for an entry. If i put a number it works fine but the result effects all entries.



All of the research I have looked at have people filtering in the form and not the table, is this where I made my mistake?

[ssanfu]

Tables are for storing data.
Forms are for viewing/adding/editing data in tables.

Should never allow direct access to tables by users.

Would advise NOT using look up FIELDS in tables (different than look up tables). (see http://access.mvps.org/access/lookupfields.htm)

3 tables with "ID" as a PK field is a poor naming convention.

[CJ_London]

the problem with your query is you are only referencing one table but your criteria references two.

your query should look more like

SELECT TBL_Models.ID, TBL_Models.Model
FROM TBL_Models INNER JOIN TBL_Brands ON TBL_Models.Brand=[TBL_Brands].[ID]
ORDER BY TBL_Models.Model;

[pyromaniac511]

Thanks guys.

As of now, I am the user but I did plan on having a form front end. I was just trying to get the fields set up prior, It seems odd to do the filtering on the front end but I will play ball if that is standard for ms access.

What is wrong with look up fields in tables? Not arguing, I would like to know where they fail.

you're right, I am terrible with naming my fields.

Thanks for reminding me of the INNER JOIN I forget how to use this every time I touch it.

[orange]

Here's a tutorial from RogersAccessLibrary that will help you with table design, relationships and sound database structure. Getting your tables and relationships designed to support your requirements is the key to a good database design. You have to work through the tutorial (about 30-45 minutes) but you will learn, and what you learn can be used with any database.

[CJ_London]

What is wrong with look up fields in tables?

they are misleading - read the link provided by ssanfu