run report using three criterias

[rcollins]

I have a database where I keep track of services and service types. Also i have to keep track of the time that three people spend at each of these services. The accountant would like it to read like this:

          person1          person2         person3         total
          :45                 :30                :00                 1:15
          :15                1:00               :15                 1:30
total   1:00               1:30               :15                 2:45

Any help would be greatful

[MMcCarthy]

I have a database where I keep track of services and service types. Also i have to keep track of the time that three people spend at each of these services. The accountant would like it to read like this:
person1 person2 person3 total
:45 :30 :00 1:15
:15 1:00 :15 1:30
total 1:00 1:30 :15 2:45
Any help would be greatful

Can you post a reply to this thread on Wednesday/Thursday to bump it up the list. It may not get much attention over Christmas.

Happy Holidays.

Mary

[Killer42]

I'm just re-posting the question with [c o d e] tags to preserve the formatting, as it doesn't show up in the original...

I have a database where I keep track of services and service types. Also i have to keep track of the time that three people spend at each of these services. The accountant would like it to read like this:

          person1          person2         person3         total
              :45              :30             :00          1:15
              :15             1:00             :15          1:30
total        1:00             1:30             :15          2:45

(Actually, I had to shuffle things around a bit to line up in the non-proportional font.)

[Killer42]

Now that I actually come to read the question, can you be more specific as to what part(s) you need hep with? I think most of us will find the request too vague, so far.

[rcollins]

Person1, person2 and person3 are all from the same dropdown list.As you can see, the accountant wants it to look more like a spreadsheet rather than a report. How do I get the report to put all three on the same line rather than under each other? Also, I have to calulate the times across and down and am not sure how to do that. If you would rather email me, rcollins at mds dot acsol dot net. Thanks

[acoder]

Also, I have to calulate the times across and down and am not sure how to do that. If you would rather email me, [e-mail]. Thanks

Calculating the totals could be done via a mod function or similar so that 60 mins. equals 1 hour, so, e.g. 75 mins would equal 1:15 (75 mod 60 : 75 rem 60). Not exactly sure how that's done in Access, but if you can find modulus and remainder functions, you should be on the right track.

Don't ask to be emailed because that's a complete no-no on a forum.

[Killer42]

Mary, where are you when we need you? Personally, I think this sounds like a job for a crosstab query, but I'm not familiar with them.

Don't ask to be emailed because that's a complete no-no on a forum.

Also, placing your e-mail address on a forum (any forum) like this allows spammers to pick it up and flood you with spam. Not a good idea. If you must post it, try to obscure it in some way so that a person can tell what you mean, but software will have a hard time with it.

[MMcCarthy]

Mary, where are you when we need you? Personally, I think this sounds like a job for a crosstab query, but I'm not familiar with them.

Also, placing your e-mail address on a forum (any forum) like this allows spammers to pick it up and flood you with spam. Not a good idea. If you must post it, try to obscure it in some way so that a person can tell what you mean, but software will have a hard time with it.

Sorry guys, I've been sick all week.

OK the crosstab would give you the person1, person2, person3 but not the total. First you will need to query it. To design the query I need to know the structure of the table. What is the name of the field with the person drop down list and what is the name of the field which stores the time? What is the name of the table? Also can a person have more than one time record (i.e.) does the time need to be sumed for each person?

Happy New Year!

Mary

[rcollins]

Mary, sorry to here you were sick. And through the holidays.
Here is some info on the table.
table name = tblMain
fields:
ID-primary key
ClientID-client id number
Date
Title (3 titles to be split by, Line Staaf, Lead Therapist, Senior Therapist)-therapists
Service-category of service
ServiceType-detail ofservice
Time
We will run a report at the end of the month to get total time spent whith each and the client can have time with all three people. What they would like to see is the total of time for the month that each client spends with each person, and a total for each person and the time spent with all clients. I do know a little about crosstabs, but not enough to pull this one off. Let me know what you think. Thanks so much

[MMcCarthy]

Mary, sorry to here you were sick. And through the holidays.
Here is some info on the table.
table name = tblMain
fields:
ID-primary key
ClientID-client id number
Date
Title (3 titles to be split by, Line Staaf, Lead Therapist, Senior Therapist)-therapists
Service-category of service
ServiceType-detail ofservice
Time
We will run a report at the end of the month to get total time spent whith each and the client can have time with all three people. What they would like to see is the total of time for the month that each client spends with each person, and a total for each person and the time spent with all clients. I do know a little about crosstabs, but not enough to pull this one off. Let me know what you think. Thanks so much

Try creating a report based on the following and see where that leaves you.

TRANSFORM Sum ([Time]) As TimeSpent 
 SELECT ClientID,  MonthName(Month([Date])) As MthName
 FROM tblMain 
 GROUP BY    ClientID,  MonthName(Month([Date])) As MthName
 PIVOT Title;
 

Mary

[rcollins]

So far so good. You have some skills. I only had to modify it a little bit. Lets see about this one:
For example, on the report, the first client spent 2 hrs with linestaff, 4 hrs with lead therapist and 5 hrs with seior therapist. I have totals on the bottom of the report, but now would like to have totals for each client. So this one should be 11.

[MMcCarthy]

So far so good. You have some skills. I only had to modify it a little bit. Lets see about this one:
For example, on the report, the first client spent 2 hrs with linestaff, 4 hrs with lead therapist and 5 hrs with seior therapist. I have totals on the bottom of the report, but now would like to have totals for each client. So this one should be 11.

Put an unbound textbox in the details section and set the control source to

 =[textbox1] + [textbox2] + [textbox3]

where textbox1 is linestaff total etc.

Mary