Need some help with MS Access... SQL Question

[her209]

I'm trying to query a table and its not working exactly the way I want it to.

Here is the generic setup.

Table 1 contains:
ID
Field1
Field2

The relationship between Field1 and Field2 is n-to-n, ie something can appear in Field1 more than once or Field2 more than once, but you won't ever have duplicate copies Field1 and Field2.

Table 2 contains:
Field3
Field4

Field3 is a listbox, source is a group by query on Field1.
Based on what was chosen as Field3, it will build its source based on a query on Field2 where Field3 = Field1.

All good right, but MS Access is fudgin' it up by not doing a query with the current Field3, but on all the values for Field3. I don't want this. :|

SELECT [Table1].[Field2] FROM [Table1] WHERE ([Table1].[Field1]=[Table2].[Field3]) ORDER BY [Table1].[Field2];

Your thoughts?

 

[SQL]

I'm having a little difficulty in following what you are trying to do, so bear with me...

So you have a listbox on a form, and when you choose a value you want the query to be based on what you chose in the listbox?

If so, you need to know the name of the listbox and make sure the bound column matches what value you want for it.

Then in the query instead of
WHERE ([Table1].[Field1]=[Table2].[Field3])
you'd want something like
WHERE ([Table1].[Field1]=forms!whateverformname!listboxname)

 

[her209]

Actually this is within the table, not a form.

If you go to the design, then click on a field, then click the Lookup tab at the bottom, you can select the Display Control option to Listbox, thnen choose then input an SQL statement or build one using the SQL Builder thing that comes with Access in the Row Source option.