Unanswered: Looking for help with an unmatched record query
Essentially I am linking two tables, although others will be joined in. The main tables look like this:
Table 1 (c=column)
c1 | c2 | c3 | c4
c1 | cA | cy | cc
c2 | cA | cn | ch
c3 | cB | cy | cc
c4 | cC | cy | ch
c5 | cD | cn | cc
c6 | cE | cy | cc
c1 | c2 | c3
c1 | c1 | cA
c2 | c1 | cB
c3 | c1 | cE
c4 | c2 | cA
c5 | c2 | cE
c6 | c3 | cB
T1 has records that, if c4 = "Y" and c4 = "c" I want to include in the result. The records colored green meet those conditions.
T1.c2 and t2.c3 are the field that links the two tables.
I want to generate a list of the T1.c2 values that are missing from T2.c3.
This is fairly straightforward except that T2.c3 can contain more than one occurance of value found in T1.c2. I have colored the occurances above.
Each occurance is distinct based upon the value in T2.c2.
So I need to create a query that asks: if I looking at the records in T2 and filter those records based on the value in T2.c2, which records from T1.c2, when they meet the conditions noted above, do not appear in T2.c3.
The results of the query would look like this:
T2.c2 filtered on 1: empty list
T2.c2 filtered on 2: B
T2.c2 filtered on 3: A E
I have made several attempts at getting somewhere but whenever I attempt to apply a filter t2 I end up with an empty dataset. I know the answer... I just can't figure out how to ask the question.
Hope I had better luck explaining than doing. Any help would be greatly appreciated!
PS, if it is a greater help for me to explain what it is i am exactly up to... table one contains a list of sites that need to be scheduled in a route. Table two is the schedule... and the twist is, it contains various scenarios... so in scenario 1, site 1,2,3 are done in this order, in scenario 2, sites 1,2,3 are done in that order. In need to pull a list that tells me that, for instance, scenario 2 is missing site 3.
Thanks a heap in advance!
Last edited by spm; 01-13-10 at 21:25.
Thanks for the reply r937! Wish the query results were so quick!
The column 5 was a typo... I meant c4. My apologies for seeming to be looking to do two different things with the query as well. I will go back and try to re-phrase what I am after...which is ... The query will show me which values in T1.c2 (based on the conditions mentioned) are not in T2.c3 when T2 is filtered on T2.c2.
Hi Rudy. Gave the code a whirl. Came back with the error "invalid filename". My guess is that the error arises because the query hasn't returned any columns from T2 other than c2.
Would it be possible at this point to add in a subquery that would retrieve the field so a filter can then be applied?
I set up these tables in Access and ran the following code, which is based on the code sample you wrote:
ON Table2.siteno = Table1.siteno
AND Table2.sceno = 1
WHERE (((Table1.sign)=Yes) AND ((Table1.age)="cur") AND ((Table2.sceno) Is Null))
All the bracketing in the WHERE clause is based on what the Access query wizard came up with.
Access comes back with the error message "Join expression not supported". As I mentioned, BDE (Borland Database Engine) returned the error "invalid filename" when I ran the query last night on Paradox Tables.
I am pretty sure that what I've coded above is the same as what you coded. I could try this out with in MYSQL tonite.