Stuck on a query!

[arkman]

Everyone,
I'm really stuck on this one. i feel like I should be able to figure it our, but I can't. Here's the deal:

1. I have table1.y - "y" is a lookup list with three possible values in it. The user sets "y" through a form right at the start of the program.

2. In another table I have a lot of records each with a setting that corresponds to the setting in table1.y Let's call this other table/field table2.y2 {I know this isn't the best design, but it is what I'm working with).

3. I have another form that I want to show all the records from table2 that are equal to the setting the user put in table1.y at the start.

4. One more wrinkle - let's say the three possible values for table.y are "apples", "oranges", or "both", and when the value for table1.y is "both" I want it to show all the records from table2.

Does that make any sense at all? Like I said I feel like I should be able to figure this out, but I'm completely stuck. Any help is greatly appreciated!!!

 

[arkman]

In hindsight that post might have been a little vague. Here is a little more detail:

[table1]
id
name
y <= lookup list with three values 1. Orange 2. Apple
3. Both

[table2]
id
different name
more data...
y2 <= a different lookup list with the same values as above

[Form1]
- I'm trying to get this form to show all the records from [table2] when table2.y2=table1.y
- Further...if table1.y='both' then [form1] should show all records from [table2]

This is driving me nuts!

 

[Darkshade]

hi I've been dealing with crosstable before, here is something that might help:

switch($y)

Case &quot;orange&quot;

$query = &quot;SELECT * FROM table2 WHERE table1.y1=orange
AND table2.y2=table1.y1&quot;
Break;

Case &quot;apple&quot;

$query = &quot;SELECT * FROM table2 WHERE table1.y1=apple
AND table2.y2=table1.y1&quot;
Break;

Case &quot;Both&quot;

$query = &quot;SELECT * FROM table2 WHERE table1.y1=apple OR table1.y1=orange AND table2.y2=table1.y1&quot;

Break;

_________________

there is anotherway to do it where you use a var from your program in your query:

$y= &quot;the choice you want&quot;

$query = &quot;SELECT * FROM table2 WHERE table1.y1=$y
AND table2.y2=table1.y1&quot;


Hope it helped

Darkshade
the language that all programmer know best is blaspheme.

 

[jhowley58]

dim strQuery as string

strQuery = &quot;SELECT table2.ID, table2.data From table1 inside join table2 on table1.y = table2.y&quot;

Set the form recordset to the value of strquery (ie the select string) and that should work.

John