Question

[JoeSmoe55]

Ok the particulars are MS ACCESS 2003.

Table 1
ID pk
Text

Table 2
FID (Table 1) pk
FID2(Table 3) pk
miscfield pk

Ok - I am given text with one or more strings matching that of the Text field in Table 1. I want the rows in Table 2 where the FID2 field is the same where the Text in Table 1 equals what I have been given. I hope this makes sense. I know how to join Table1 and two based upon the text I am given but what I don't know how to to is state in SQL that the FID2 must be identical.

For example with the following data

Table 1
ID TEXT
1 asfd
2 fghj
3 mnbv
4 jhgf


Table 2
FID FID2 MISC
1 91 1
2 90 2
1 90 1
3 91 1
4 90 2


The only piece of info I am given is 'asdf' and 'mnbv'. Therefore if I want to get the results where the rows where the Text equals 'asdf' and 'mnbv' where the FID2 is equal, how would I do that?

The results that I would want to get back are :

1 91 1
3 91 1

Thanks in advance.

Joe Smoe

 

[Golom]

For your specific example

Select B.ID, B.FID2, B.MISC

From Table1 A INNER JOIN Table2 B
     ON A.ID = B.FID

Where A.Text IN ('asfd', 'mnbv')

If however, Table2 was
[tt]
FID FID2 MISC
1 91 1
2 90 2
1 90 1
3 91 1
4 90 2
3 88 5
[/tt]
Then the above would produce
[tt]
1 91 1
3 91 1
3 88 1 <-- FID2 is different
[/tt]
How do you want to decide which value of FID2 should appear?

 

[JoeSmoe55]

Golom,

Thank you. I must not have explained well enough. I want the FIDs returned where Text is equal to 'mnbv' AND 'asdf'. The example you gave would return the FIDs where Text is equal to 'mnbv' OR 'asdf'. Do you understand? Sorry for my bad explanation. A simpe join won't do the trick it returns a recordset where the Text is either or but what I need is to take that recordset and group it by FID and filter out the records where 'asdf' AND 'mnbv' exist for the same FID. I hope that helps. Thank you for your help.

Joe