No Match Query

tt1611

Registered User.
Local time
Today, 00:38
Joined
Jul 17, 2009
Messages
132
Good Morning All
Before you ask, yes I have searched through the forum for this particular query but allow me to shed some light.I have a number of devices (printers, monitors, ID Scanners etc)

  • that may or may not be associated with a particular PC
  • If associated with a PC may or may not be signed for as received.
I want my query to return the PC name with all the associated devices, (LEFT JOIN). If the PC name is found as matching in the device table, check to see the device has been signed for.

If name exists, display receipt number
If name doesnt exist display not applicable
if name exists but receipt number not available display not signed for.

I know this sounds tedious but these are the results i am needing from my query.

So far I have

Code:
SELECT DISTINCT PCs.Device_Name, Monitors.HR_Number, NZ(RPrinters.HR_Number,"Not Found") AS [Rectrac Printer HR], CDisplay.HR_Number, CashDraws.HR_Number, CPS.HR_Number, IDScans.HR_Number
FROM (((((PCs LEFT JOIN Monitors ON PCs.Device_Name = Monitors.PC_Name) LEFT JOIN RPrinters ON PCs.Device_Name = RPrinters.Device_Name) LEFT JOIN CDisplay ON PCs.Device_Name = CDisplay.Device_Name) LEFT JOIN CashDraws ON PCs.Device_Name = CashDraws.Device_Name) LEFT JOIN CPS ON PCs.Device_Name = CPS.PC_Name) LEFT JOIN IDScans ON PCs.Device_Name = IDScans.Device_Name
WHERE (((PCs.Device_Name) Like 'ARTI%' Or (PCs.Device_Name) Like 'HOHE%' Or (PCs.Device_Name) Like 'GRAF%' Or (PCs.Device_Name) Like 'VILS%'))
ORDER BY PCs.Device_Name;

I tested with the NZ function on one of the devices ie RPrinter and it works great except it shows me only one part of my 2 criteria ie where no match for the PC is found in the RPrinter table.

I also need to check to see if there is a matchm does it have a receipt number and thats where I'm stuck

Can anyone help with this?
 
You can nest iif statements in a query to get the results you're looking for.
 
Thank you James
Yes I could use the iif function, the question is how? I tried using that at

Code:
SELECT DISTINCT PCs.Device_Name, Monitors.HR_Number, NZ(RPrinters.HR_Number,"Not Found") AS [Rectrac Printer HR], CDisplay.HR_Number, CashDraws.HR_Number, CPS.HR_Number, IDScans.HR_Number
FROM (((((PCs LEFT JOIN Monitors ON PCs.Device_Name = Monitors.PC_Name) LEFT JOIN RPrinters ON PCs.Device_Name = RPrinters.Device_Name) LEFT JOIN CDisplay ON PCs.Device_Name = CDisplay.Device_Name) LEFT JOIN CashDraws ON PCs.Device_Name = CashDraws.Device_Name) LEFT JOIN CPS ON PCs.Device_Name = CPS.PC_Name) LEFT JOIN IDScans ON PCs.Device_Name = IDScans.Device_Name
WHERE (((PCs.Device_Name) Like 'ARTI%' Or (PCs.Device_Name) Like 'HOHE%' Or (PCs.Device_Name) Like 'GRAF%' Or (PCs.Device_Name) Like 'VILS%')) AND iif(pcs.device_name in (SELECT device_name FROM RPrinters), RPrinters.HR_Number, "No applicable device")
ORDER BY PCs.Device_Name;

Again, thie errored out on incorrect syntax. Do you have an example on how this will work?
 
So James I got to thinking and I was able to resolve using nested iif statements to return different values based on differing scenarios. It was a hell of a SQL Statement but it worked.

Thanks

For the rest of us

SELECT iif(table1.column1 is null, "Output 1", iif(table1.column2 is null, "Output 2", table1.column2))
 

Users who are viewing this thread

Back
Top Bottom