Report based on multiple queries...or not 2

[Jima330]

All Right. I have had it with this problem and hope someone can help me. I stumble through Access as best I can and now I have a problem I can't seem to get my head around.

I have a Form that collects data from 3 other tables (employee info tables) as well as data entered by the user. I figured out, finally, how to get this form to populate a new table with the info gathered and entered on the form. here is what I am basically working with:

ExcTable

with these fields:
ID (key)
AgentCode
Activity
Starttime
Endtime
Duration
Date

Ok, the purpose is to track the persons activity throughout the day by way of coded activities that are input under "Activity". Each activity has a start time, endtime and the duration is calculated by the form and written to the record as a hard number.

Here is where it gets iffy, I need to compare Scheduled Work Time to Actual work time. Scheduled work time is a constant and Actual work time is a time that we get from our phone system that tells us how long someone was on the phone. These two numbers are no problem to compare with Actual time being a percentage of the Scheduled time. The issue arises from changes during the day. If someone has a meeting I need to add time to there Actual time so that it can match Scheduled time. No problem, "Activity" is "MEET" with a start, end and duration. No problem. The problem comes from the fact that there are any number of "Activity" codes that do one of 4 things: add to "Actual time", Subtract from "Actual time", Add to "Scheduled time" or Subtract from "Scheduled time". What I need to do is to seperate "Activity" into these 4 catgories so that I can do the calcs to get a camparison. It would seem easy to do 4 queries with "OR" statements to select only the codes for each category. And it was. But when I try to combine these in a report, Access locks up and won't do the report on 4 queries, and when I just do 3 it gives me no info just headings. I tried crossatb queries but I don't think that is exactly what I am looking for either. I just want to seperate out the codes into the categories they go in, add those together to get a total for the category and then either add or subtract that total from the Actual time or the Scheduled time. Sorry this is so long but I am at my wits end just trying to figure out which direction I should go from this point. Just point me in the right direction and I will limp along from there. But nothing I have tried so far is working. Thanks for any help you can provide.
Also: I related the 4 Queries through the AgentCode field.

Jim

 

[randysmid]

Hi,
You may want to consider using subreports. Each subreport will be based on a different query that gathers the info you need for each activity type. There are probably other ways to go about this, but this technique seems the simplest to me.

HTH,
Randy Smith
California Teachers Association

 

[Jima330]

Randy,

This seems to be the way to go. I have embedded the subreports and it all seems to go together quite nicely. The report header of the original report seems to stop half way down the page no matter what I do but I will get that figured out. I created a report out of the first query and then embedded the other 3. Do you think this is will cause any problems in the end? Should I (for that matter..could I) open a new report in design mode and embed all 4 as subreports? I only ask as I seem to have a couple of formatting problems. Thanks for your time and thoughts on this. I read some of the discourse involving Carl something or other and saw you responding to their obvious egocentric attitude. I don't want to appear that way. When I get a free app from Tucows I don't expect it to work for more than 30 days, but the advice I get on this site works forever. The best free value in America.

 

[randysmid]

Hi,
I think your current setup will work just fine. I have learned tons of stuff from this site, especially posts by CosmoKramer, KenReay, scriverb, PaulBricker, etc. Because of how much I learned here, I decided to become a cash contributor (for me, $10 a month isn't too much to donate for such wonderful assistance and learning opportunities). I think of it this way:

Where would I be if there wasn't a Tek-Tips?????

By ranking the posters, we can see who the top experts are, and when they respond to our requests, I know that I am getting the absolute best advice in the world. Because none of them get any compensation for it, I definitely reward good answers with a star (easy to do - just click on "Mark this post as a helpful/expert post!").

HTH,
Randy Smith
California Teachers Association

 

[Jima330]

Randy,

Then consider it done for this post!

Thanks

Jim

 

[randysmid]

Hi,
I wasn't asking for a star. I just wanted to make a point about the fact there are some REALLY OUTSTANDING tipsters who really deserve them. I am still learning alot from them. But, thank you for the star.
By the way, if you look in the FAQ's section, you will see several of my FAQ's. You may want to print, and review them. You will find my email address there, and if you want, I can send you a copy of my coredatabase.mdb. There is no cost for this, and shows some really interesting techniques.

HTH,
Randy Smith
California Teachers Association