Create a report based on a multi field comparison query 1

[jimc01]

Hi,
Challenge - check table for conflict between one field and several other fields.
I am looking for a little assistance : I have a table which has a key field of JOB_ID and other general fields called CCT; RC1; RC2; RC3; RC4. I am trying to write a query that will create a table or report of records where the value of CCT is found in either RC1 RC2 RC3 or RC4 ( and if I can crack that problem, the next challenge would be to write a similar query which reports any records where the values in either RC1 RC2 RC3 or RC4 exist in other RC1 RC2 RC3 RC4.)
I originally thought a simple query like...
Select table.JOB_ID from table
where table.CCT = table.RC1
or table.CCT = table.RC2
or table.CCT = table.RC3
or table.CCT = table.RC4;
would do what I wanted, but this query seems only to pick up my required conflicting records based on RC1

Thanks in anticipation
JimC

 

[PHV]

What something like this ?
SELECT JOB_ID
FROM yourTable
WHERE CCT IN (RC1, RC2, RC3, RC4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jimc01]

Thanks PHV, I tried
SELECT dry_ehv_outage.JOB_ID, dry_ehv_outage.JOB_NUMBER, dry_ehv_outage.JOB_NAME, dry_ehv_outage.CCT, dry_ehv_outage.[RC1], dry_ehv_outage.[RC3], dry_ehv_outage.[RC5], dry_ehv_outage.[RC6]
FROM dry_ehv_outage
WHERE dry_ehv_outage.CCT in (dry_ehv_outage.[RC1],dry_ehv_outage.[RC2],dry_ehv_outage.[RC3],dry_ehv_outage.[RC4],dry_ehv_outage.[RC5], dry_ehv_outage.[RC6]);

but this returned no records - if I use
WHERE
dry_ehv_outage.CCT in (dry_ehv_outage.[CCT])

the query does find records .... can you see where I am going wrong?

JimC

 

[Golom]

I assume that you are aware that the comparisons that you're doing are on a within-the-current-record basis. That is, this will not look for values of "CCT" that match (RC1...RC6) on other records. It looks for records where the value of CCT on a record matches one of (RC1...RC6) on the same record.

One possible thing to look at is the data types of the fields in question. CCT and RC1...RC6 should all be of the same data type. There may also be problems if the data types are float (i.e. Double or Single) because floats are approximations and comparisons of values that appear equal may fail because of that.

dry_ehv_outage.CCT in (dry_ehv_outage.[CCT]) works because its equivalent to CCT = CCT and it always is equal to itself (except when it's NULL.)

 

[jimc01]

Thanks Golom
Unfortunately I am an engineer trying to create a tool to check circuit outage conflicts and not a programmer. Any advice on how I can achieve my goal would be very welcome.
In my head I know what result I want .... but as can be seen my lack of knowledge on sql is holding me back - i.e. all I want to do is compare a field called circuit (CCT) with all other records in the same table and if the same numeric value exists in any other reserved circuit (RC1 RC2 etc...) select the record id (JOB_ID), job name (JOB_NAME) and planned date (PLAN_START) and planned end date (PLAN_END).... it seems so simple, but I just can't figure this one out.

JimC

 

[Golom]

OK. I'll walk through what I think you're trying to do and develop some SQL along the way. Part of the problem that you're having is that your table isn't normalized. Specifically the fields RC1, RC2, ... RC6 are called repeating fields and good database design would have a separate table of reserved circuits for each JobID. Something Like
[tt]
JobID
CurcuitNum
CircuitVal
[/tt]

Here I'm assuming that JobID is the primary key for your table. If it isn't then add the additional primary key fields to the above table and the following SQL.

Soooo ... lets create a query that puts your information in that form

(Select JobID, 1 As CircuitNum, RC1 As CircuitVal From dry_ehv_outage Where RC1 IS NOT NULL)
UNION ALL
(Select JobID, 2 As CircuitNum, RC2 As CircuitVal From dry_ehv_outage Where RC2 IS NOT NULL)
UNION ALL
(Select JobID, 3 As CircuitNum, RC3 As CircuitVal From dry_ehv_outage Where RC3 IS NOT NULL)
UNION ALL
(Select JobID, 4 As CircuitNum, RC4 As CircuitVal From dry_ehv_outage Where RC4 IS NOT NULL)
UNION ALL
(Select JobID, 5 As CircuitNum, RC5 As CircuitVal From dry_ehv_outage Where RC5 IS NOT NULL)
UNION ALL
(Select JobID, 6 As CircuitNum, RC6 As CircuitVal From dry_ehv_outage Where RC6 IS NOT NULL)

and we'll call that qryRC

Now, assuming that you want to return the record where a RC value matched CCT (i.e. not the record containing the CCT value.)

SELECT D2.JOB_ID, D2.JOB_NUMBER, D2.JOB_NAME, R.CCT,
       R.CircuitNum, R.CircuitVal

FROM (dry_ehv_outage D1 INNER JOIN qryRC As R
      ON D1.CCT = R.CircuitVal)
     INNER JOIN dry_ehv_outage D2 
     ON R.JobID = D2.JobID

 

[jimc01]

Brilliant Golom...

Excellent result, I am very grateful.

JimC

 

[Golom]

Always glad to help a fellow engineer.