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)
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
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?
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.
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?