Looking for help with an SQL Statement

[Old_EE]

I have a database form that has two lookup tables. The entire DB has roughly
ten colums and six tables in it, but one requires a complex query.
My database deals with antiques. I have one table dedicated to types of
antiques, and another table dedicated to specific antiques. My complex query
is give a customeer a list of specifics based on a previous entry he has
made, specifically the type of antique he is looking for. Thus the output of
the query is a list of specific items within the subcatagory of the type of
antique.

I have the book "Access 2007 INSIDE/OUT", and have written numerous lines of
SQL, but the output.... You get the picture, it's not right

I would appreciate any assistance anyone couls offer.

Thank You
GB

 

[Kashaan]

Hi,

Can you please explain yourself more specifically.

What are you trying to do?

What's the query you're trying to build?

How's you're form made up?

What are the structure of the two tables you're trying to run the query
from?

Can you please give us above info, so we can better assist you?

Kashaan.

 

[Old_EE]

Hello and thank you for responding,

My database is going to give my customers the ability to choose a
specific item once he or she chooses from a general catagory. The SQL help I
am looking for is to populate a drop down list based on a previous drop down
list response.
For example the general types of antiques would be {toys, collectibles,
automotive, equipment, furnature}, once the customer chooses from that drop
down list, the next drop down only shows items relevant to that catagory.
For example the customer chose "Collectibles", the next drop down would
contain a new list {Jewlery, coins, trading cards, bottles},

Once I get my brain wrapped around the correct syntax of this type of
complex query, I can further refine my customer's search.

My question is the correct syntax of a nested or complex query.

Thank You
GB

 

[Kashaan]

Ok let me see if I got this right.

You have two tables: Antiques and Collectibles.

Antiques is build up the following fields:
- Antique_id (This is the primary key)
- Antique_description

Collectibles has the following fields:
- Collectible_id (Primary key)
- Collectible_description
- Collectible_Antique_id (Foreign key with the Antique_id of the Antique
table)

I hope this is your structure, if not, please post yours.

Ok let's say you have a form with two comboboxes on it named: cboAntiques
and cboCollectibles.

cboAntiques.RowSource should be something like this: "SELECT Antique_id,
Antique_description FROM Antiques"

In the OnClick Event of the cboAntiques you can try the following code:

'begin code
Dim strSql As String
'build sql string based on selection in cboAntiques
strSql = "SELECT Collectible_id, Collectible_description FROM Collectibles
WHERE ((Collectible_Antique_id = " & Me.cboAntiques & "))"
'change list items
Me.cboCollectibles.RowSource = strSql
'refresh list items
Me.cboCollectibles.Requery
'end code

Alternatively you can put the same code in the AfterUpdate event of the
cboAntiques combobox.

Please see which one suits you best.

I hope I have understood your problem and was able to give you a workable
solution.

Have a nice day,

Kashaan.