Summary Query help

  • Thread starter Thread starter Daveo
  • Start date Start date
D

Daveo

Hi there,

I have a table with the following fields:

ID (autonumber)
employee (text)
obTravelDay (date/time)
ibTravelDay (date/time)

What I need to do is create a query that totals, for each employee, the
number of Weekday travel days and the number of Weekend travel days in
a given period.

I know about the Weekday() function but am completely confused as to
how to tie everything together.

Thanks in advance - David
 
I can help you with a little more information. Is obTravelDay the first day
of travel for a record and ibTravelDay the last day.
 
Hi James,

obTravelDay is the date of outbound travel and ibTravelDay is inbound.
I need count how many weekdays travel and how many weekend days travel
an employee has done. The inbound and outbound part is not important
and the totals for each can be added together. I dont need to work out
the difference between the dates, as an employee never travels for more
than one day. I just need a total of the count of each.

Thanks for your help - David
 
Perhaps, something like the following.

SELECT ID, Employee,
Abs(Sum(Weekday(ObTravelDay)=1 or WeekDay(ObTravelDay)=7)) +
Abs(Sum(Weekday(IbTravelDay)=1 or WeekDay(IbTravelDay)=7)) as WkEndDays,
Abs(Sum(Weekday(ObTravelDay)>1 and WeekDay(ObTravelDay)<7)) +
Abs(Sum(Weekday(IbTravelDay)>1 or WeekDay(IbTravelDay)<7)) as WkDays
FROM TheTable
WHERE ObTravelDay Between #1/1/05# and #1/31/05# OR
IBTravelDay Between #1/1/05# and #1/31/05#
GROUP BY ID, Employee

Abs takes care of the sign of the data since the test will return 0 or -1 in Access.
Sum adds up the number of -1 and Zeroes returned by the test.
 
Hi John,

That worked a treat, except I had to change the OR to AND in the fourth
Abs(... line.

Many thanks for your help - David
 
Well, now that I look at it a bit more closely, you could still have problems
getting an accurate count - depending on your data entry rules.

Example:
Employee: A
obTravelDay: Feb 2 2005
ibTravelDay: Jan 30 2005

With AND If you were looking for travel days in February this record would not
get picked up and if you were looking for travel days in January it would not
get picked up. On the other hand if you were looking for travel in the first
quarter of the year it would get picked up.

Using the original query (with or) the record would get picked up, but you would
have extra days counted in the first two cases above.

The problem lies with the structure. For this problem, your table should be
more like
ID
Employee
TravelDate
InboundOrOutbound

That makes it simple. If you have to use the current structure, I would revise
the process and use a union query to normalize the data and then use that in
your query

SELECT ID, Employee, obTravelDay
FROM TheTable
UNION ALL
SELECT ID, Employee, ibTravelDay
FROM TheTable

Save that as qryTravelDays

Now your query becomes

SELECT ID, Employee,
Abs(Sum(Weekday(ObTravelDay)=1 or WeekDay(ObTravelDay)=7)) as WkEndDays,
Abs(Sum(Weekday(ObTravelDay)>1 and WeekDay(ObTravelDay)<7)) as WkDays
FROM qryTravelDays as Q
WHERE ObTravelDay Between #1/1/05# and #1/31/05#
GROUP BY ID, Employee

You may be able to do this all in one fell swoop by embedding your query.

SELECT Q.ID, Q.Employee,
Abs(Sum(Weekday(Q.ObTravelDay)=1 or WeekDay(Q.ObTravelDay)=7)) as WkEndDays,
Abs(Sum(Weekday(Q.ObTravelDay)>1 and WeekDay(Q.ObTravelDay)<7)) as WkDays
FROM [SELECT ID, Employee, obTravelDay
FROM TheTable
ObTravelDay Between #1/1/05# and #1/31/05#
UNION ALL
SELECT ID, Employee, ibTravelDay
FROM TheTable
ibTravelDay Between #1/1/05# and #1/31/05#]. as Q
WHERE ObTravelDay Between #1/1/05# and #1/31/05#
GROUP BY ID, Employee
 
Thanks John,

It works great, but I cant nest the query inside the other one. Doesn't
matter though because I'm getting the results I need!

Thanks again - David
 
Back
Top