Access/ Not sure where to insert Null and parameters

[nover11]

Would you be able to help me out with this error? I’m trying to get the tblOutOfOffice (“Subject”) to pull (if subject,subject,0). It’s not doing it when I add in the parameters for that date. It’s only pulling the people out of office and not showing those who should be “0”

PARAMETERS [Start Date] DateTime;
SELECT tblPairings.NamePairingsList, tblPairings.Territory, tblPairings.Title, Sum(qryTouches.[CountOfCreated By]) AS Touches, Sum(qryHVTouches.[CountOfCreated By]) AS HVTouches, Round(Sum([Talk Time]),2) AS [Daily Talk Time], Round(Sum([Inbound Time]),2) AS [Daily Inbound Time], Round(Sum([Outbound Time]),2) AS [Daily Outbound Time], Round(Sum([Queue Time]),2) AS [Daily Queue Time], Sum(qryAvaya.[ACD Calls]) AS [Queue Calls], Nz(Sum([tblOutOfOffice]![Subject]),0) AS Subject
FROM (((tblPairings LEFT JOIN qryHVTouches ON tblPairings.NamePairingsList = qryHVTouches.[Created By]) LEFT JOIN qryTouches ON tblPairings.NamePairingsList = qryTouches.[Created By]) LEFT JOIN qryAvaya ON tblPairings.NamePairingsList = qryAvaya.NamePairingsList) LEFT JOIN tblOutOfOffice ON tblPairings.NamePairingsList = tblOutOfOffice.Assigned
WHERE (((qryHVTouches.Date)=[Start Date]) AND ((qryTouches.Date)=[Start Date]) AND ((qryAvaya.Date)=[Start Date]) AND ((tblOutOfOffice.Date)=[Start Date]))
GROUP BY tblPairings.NamePairingsList, tblPairings.Territory, tblPairings.Title;

Thank you!

 

[dhookom]

A couple suggestions:
[ul]
[li]Use TGML to make your posts more readable. The better your posts, the better your chances of getting prompt assistance.[/li]
[li]IMO it is never ever appropriate to use parameter prompts in queries. It's poor user interface. Use controls on forms.[/li]
[li]Try the following SQL which might or might not work.[/li]
[/ul]

PARAMETERS [Start Date] DateTime;
SELECT tblPairings.NamePairingsList, tblPairings.Territory, tblPairings.Title, 
  Sum(qryTouches.[CountOfCreated By]) AS Touches, Sum(qryHVTouches.[CountOfCreated By]) AS HVTouches, 
  Round(Sum([Talk Time]),2) AS [Daily Talk Time], Round(Sum([Inbound Time]),2) AS [Daily Inbound Time], 
  Round(Sum([Outbound Time]),2) AS [Daily Outbound Time], Round(Sum([Queue Time]),2) AS [Daily Queue Time], 
  Sum(qryAvaya.[ACD Calls]) AS [Queue Calls], Nz(Sum([tblOutOfOffice]![Subject]),0) AS Subject
FROM (((tblPairings 
   LEFT JOIN qryHVTouches ON tblPairings.NamePairingsList = qryHVTouches.[Created By]) 
   LEFT JOIN qryTouches ON tblPairings.NamePairingsList = qryTouches.[Created By]) 
   LEFT JOIN qryAvaya ON tblPairings.NamePairingsList = qryAvaya.NamePairingsList) 
   LEFT JOIN tblOutOfOffice ON tblPairings.NamePairingsList = tblOutOfOffice.Assigned
WHERE qryHVTouches.Date=[Start Date]
  AND qryTouches.Date=[Start Date]
  AND qryAvaya.Date=[Start Date]
  AND Nz(tblOutOfOffice.Date,[Start Date])=[Start Date]
GROUP BY tblPairings.NamePairingsList, tblPairings.Territory, tblPairings.Title;

Duane
Hook'D on Access
MS Access MVP

 

[nover11]

Hi Duane,

Thank you for looking at this and your tips. Not sure what you mean by TGML. Do you mean the formatting of the the SQL, indents and such? I don't understand how to create a form without setting up parameters in a query to test it. Would it be better to add the data to a query then create a form with the parameters and test that way?

I tried running the SQL but it didn't work. It keeps giving me only half of the employees that were out of the office. The actual total of employees out for the day is 45, but it's giving 19. In addition, it's not showing the null figures for the remaining employees, who were in the office. I should have a result around 283 employees.

I would like it to look like. (two tables; original data and what i wish it was doing)

Original table
Name DateOut OutOfOffice Total
Ted 4/1/2015 0.25
Ted 4/2/2015 1
Ted 4/3/2015 1
Ted 4/6/2015 0.25
Suzy 4/6/2015 0.5
Suzy 4/15/2015 1


What table should look like
What table should look like (sorry for the formatting couldn't figure out to get a table)
Name 4/1/2015
Bob 1
Ted 0.25
Suzy 0

thanks
~Nova

 

[dhookom]

It's impossible to tell from your SQL view, which table contains "all of the employees". You may need to create a new query with your all query outer joined to a table of all employees.

Duane
Hook'D on Access
MS Access MVP