Concatenate work order #'s

  • Thread starter Thread starter Annette
  • Start date Start date
A

Annette

I have a query that returns the DateWorked, WorkerID, ServiceID,
TotalHours. I would like to get a report that shows on a particular
day, all the ServiceID's a Worker did and for how many hours.

So if the data looked like this (DateWorked, Worker ID, ServiceID and
TotalHours)
01/01/2011 1 99 2
01/01/2011 1 97 4
01/01/2011 1 80 2
01/01/2011 2 99 1
01/01/2011 2 91 3
01/01/2011 2 70 5

The results would be that on 01/01/2011 worker 1 worked 8 hours on
99,97,80 and on 01/01/02011 worker 2 worked 9 hours on 99,91,70.

How can I do this?
 
Annette said:
I have a query that returns the DateWorked, WorkerID, ServiceID,
TotalHours. I would like to get a report that shows on a particular
day, all the ServiceID's a Worker did and for how many hours.

So if the data looked like this (DateWorked, Worker ID, ServiceID and
TotalHours)
01/01/2011 1 99 2
01/01/2011 1 97 4
01/01/2011 1 80 2
01/01/2011 2 99 1
01/01/2011 2 91 3
01/01/2011 2 70 5

The results would be that on 01/01/2011 worker 1 worked 8 hours on
99,97,80 and on 01/01/02011 worker 2 worked 9 hours on 99,91,70.

How can I do this?
Click to expand...

Use a Totals Query, with Date, Worker, and Service ID as Group By, Total
Hours as Sum.

Just FYI, "concatenate" in the Access world is normally used to indicate
appending one string to the end of another. You in fact want to Sum (or
Total) the hours each worker worked by Service ID, by Date.
 
Yes, I know to use a sum to get the total hours. That is not what I am
seeking help on. I want to know how I can concatenate the Service
ID's. As in my example above, I want to see that on 01/01/2011, worker
1 worked on service id 99, 97 and 80 -- which is a concatenation. So
in a sense the query I have that lists the 6 detail records, I want
to summarize as two entries on the reports -- showing the date, the
employee, total number of hours and the concatenation of the service
id's they worked on that day.
 
Yes, I know to use a sum to get the total hours. That is not what I am
seeking help on. I want to know how I can concatenate the Service
ID's. As in my example above, I want to see that on 01/01/2011, worker
1 worked on service id 99, 97 and 80 -- which is a concatenation. So
in a sense the query I have that lists the 6 detail records, I want
to summarize as two entries on the reports -- showing the date, the
employee, total number of hours and the concatenation of the service
id's they worked on that day.
Click to expand...

You'll need to use a bit of VBA to do this. One sample code is
http://www.mvps.org/access/modules/mdl0004.htm
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Back
Top