INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access/ Not sure where to insert Null and parameters

Access/ Not sure where to insert Null and parameters

(OP)
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!

RE: Access/ Not sure where to insert Null and parameters

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

CODE --> SQL

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

RE: Access/ Not sure where to insert Null and parameters

(OP)
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

RE: Access/ Not sure where to insert Null and parameters

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close