conditional query?

[romanticode]

i have a query from 2 table. let's say table a & b. i have one form to show the query. the query show data based on what enter on the form.

when someone enter a value, on form, the query use it as filter for displaying data. my problem is one field of my query show data from the second table.
if input is AA, the data will be shorted by AA plus showing first colum of table 2.
if input is AB, the data will be shorted by AB plus showing second colum of table 2. etc.

so far, i make several query for each kode to display the corect column.
can i use 1 query to do that? if not, what should i use? macro/module? please the example aslo.

thanks

 

[liddlem]

Hi Romanticode
Lets try an answer here. I find it much easier to make more than 1 query as I find I can trace problems at each stage much quicker.


If TABLE A has fields like
MyID(PK), FirstName, LastName, BirthDay, FavouriteColour

TABLE B has
ID(Pk), MyID (Relation into Table A), StreetAddress, Town, PostalCode

Lets say that we want to find all people who's FirstName is "Fred" and their FavouriteColour is "Red" (This is Query_1 and the code looks something like this. (NOTE: I have not checked this code, so there may be an error in it.)

Select *
FROM Table_A
WHERE ((FirstName=[Forms]![FormName]![FirstnameComboBox]) and (FavouriteColour=[Forms]![FormName]![FavouriteColourComboBox]))

Then we want to see how many of these people live in a town called "Happyville"
This is query number 2....BUT this query gets its results from Query_1 and the field(s) that you need from Table_B

SELECT *
FROM Query_1 INNER JOIN Table_B ON MyID
WHERE (Town=[Forms]![FormName]![TownNameComboBox])

Hope this helps you