Advance Search, compare and display records

[Joelo]

I want to search for records in Table1 base on month and facility criteria and use the search results to compare records in Table2 .... and display non-matching records

Please anyone with any IDEAS on how I do it

Thanks in Advance

 

[Tarwn]

I will assume that Table2 has some field that links it to Table1, so here is the easiest way off the top of my head:
"SELECT * FROM Table2 WHERE table1_field NOT IN (SELECT table1_field FROM Table1 WHERE facility = '" & whatever & "' AND month = " & whatever & &quot"

So basically it is saying select everything from table 2 that isn't in your query for table1. Course you should probably check my syntax on that statement, it was on the fly and I don't know which db your working with.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[Joelo]

Thanks for quick response....but I do have alittle problem... The all month field in Table2 is null(empty) and all the month field in Table have month values.. Please how do I get around that.

Please help me out

 

[Joelo]

Right now I am working with Access DB

 

[Joelo]

I tried the SQL statement but got the below Error...
I don't what I am mising....

strsql = "SELECT * FROM ["& server.mappath("db\Database.mdb&quot & "].devicecomponentdatabase WHERE FACILITYCOMPONENTID NOT IN (SELECT * FROM devicecomponentdatabase WHERE facility = '" & facility & "' AND month = " & monthyear & &quot"


Error Message.....

SELECT * FROM [C:\Inetpub\wwwroot\compliance\db\Database.mdb].devicecomponentdatabase WHERE FACILITYCOMPONENTID NOT IN (SELECT * FROM devicecomponentdatabase) WHERE (([MONTHYEAR] = 'December-2003' AND [FACILITY] = 'Dehydration' ))
Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'FACILITYCOMPONENTID NOT IN (SELECT * FROM devicecomponentdatabase) WHERE (([MONTHYEAR] = 'December-2003' AND [FACILITY] = 'Dehydration' ))'.

/compliance/devicecomponentdatabasehnottestedlist.asp, line 193

 

[foxbox]

i think it is because of the * in de sub-query.
change it:


FACILITYCOMPONENTID NOT IN (SELECT FACILITYCOMPONENTID FROM devicecomponentdatabase WHERE facility = '" & facility & "' AND month = " & monthyear & &quot"


hth,
Foxbox