Most Efficient Way to Accomplish Task

[cc143most]

I have a very basic MasterTbl that is imported from Excel. Each line item is a task that was not completed on-time. The fields are: Employee, Customer, Date (This is a daily Long date with time that I have formatted to Short Date). I need to count this data different ways and have created several Crosstab Queries:
1) Number of shifts worked by Employee.
2) Number of customers helped by employee and shift.


3) Total number of customers helped.
4) Total of overdues with breakdown by date (count of Date)

These all work great and give me exactly what I need; and, with PBaldy's help, I have created a form that runs all at the same time with one button click. I created a query that pulls the totals from each of the above crosstabs and creates a column called "Index" that is Overdues / Customer Total / Shift Total. When I have tried to create a chart (graph) from this query, my calculated field didn't show up as an available field. So, for now, I export the info to Excel to graph it.

I am self-taught (with lots of help from you guys) and know that I have missed some of the basics. I am curious if I really had to create these multiple crosstabs to accomplish my task. Could I have used Count Distinct to count the info so many different ways in the same query? While I have this working, I want to learn the most efficient way to have my database perform.

Thank you in advance for any suggestions or comments!

[June7]

I don't think could be done in one query because each has different GROUP BY criteria (by employee, by employee and shift, by date). Is 3 a count of customers by employee? Don't understand why calculated field not showing up.

Want to provide db for analysis? Follow instructions at bottom of my post.

[cc143most]

June,

Thank you for the feedback. I felt I would have to do multiple crosstabs due to the different sorts, but wanted to make sure.

I don't know why that calculated field wouldn't show as an available field; but, I ended up having the query create a new table and the field is available when building the graph from the table. The info I have in the db is confidential, so I would have to do alot of work on it to be able to post it. I will play with it this weekend; and, if I cannot get the graph the way I need it, I'll definitely post!

Thanks again for your response. It was a confidence booster (which is badly needed right now as I struggle through this).

Have a great weekend!