Summing Multiple Queries & Summing Time

[WillJ]

Hi There,

I've come up against a bit of a problem.
I have a sales form. The sales form has many Call, Meetings and Emails linked to each sales record. I want to total all the number of calls, meeting and email records related to the sales record to give a total- Touches.
I've created 3 queries;
1 - Counts Calls
2 - Counts Emails
3 - Counts Meetings

These all work fine however when I combine them to attempt to count the results it doesn't produce the correct results.

I have a second query as well (no pun intended).

I am trying to sum together a column that has values in Time. The results displays as a decimal. How can I have the result display as a Time i.e. 1:20 (1 hour 20 minutes).

Thanks for your help!

 

[plog]

We need more information about your table structure. Can you post either a screen shot of your relationships or list all the relevant fields from the 4 tables you mentioned?

Without that, this is my general advice. Your 3 summation queries should have a SalesID field in which you can group sales together. You would then combine those 3 queries using a UNION query (http://www.techonthenet.com/sql/union.php). Then on that UNION query you would Sum the counts and group by the SalesID to get total touches.

As for summing time, you should create a field which determines the time in the next smallest unit that you want to end up with (i.e. if you want total hours, determine time in minutes) . Then, you would Sum those values together and convert the end value to the units you want (i.e. 720 minutes = 12 hours).

Again, post table structures for specific advice.