Compare Query?~

[Wiz0fBaud]

Hey guys, really simple, I think, but I'm a tech, not a programmer.

I have two queries that spit out a list. Basically there are two queries are electrical Inputs/Outputs. The InputID of one query matches the OutputID of the second query. We have a problem though, there are like over 2 thousand signals, and there are more electrical inputs than outputs. How do I write a query that compares those IDs and says hey, I found the culprit inputs that aren't in the output table, and here's a list?

Anyone follow?

 

[glalsop]

I think that this should give you a list of the culprits:

SELECT InputTable.InputID
FROM InputTable LEFT JOIN OutputTable ON InputTable.InputID = OutputTable.OutputID
WHERE (((OutputTable.OutputID) Is Null));


-Gary

 

[Wiz0fBaud]

how about if I only want to compare say the first 10 characters in that field? any ideas?

 

[hneal98]

That is kind of vague. Do you mean you want to compare if the 1st 10 characters are in the field or not or do you mean you want to do a comparison to determine if the record is in the table?

 

[Wiz0fBaud]

The thing is the InputID, has IID added on, and the Output has OID added on. So For example, I have a signal say marked by ABCD, well the Input Id is ABCDIID and the output is ABCDOID. How would I tell this compare query to only find the ABC instead of the entire field?

 

[hneal98]

I see. Try this:

SELECT InputTable.InputID
FROM InputTable LEFT JOIN OutputTable ON left(InputTable.InputID,10) = left(OutputTable.OutputID,10)
WHERE (((OutputTable.OutputID) Is Null));

 

[glalsop]

Ultimately, you will probably be better served by splitting this field into two fields in your base table.

-Gary

 

[hneal98]

I would agree.