I need to calculate totals between dates...

[andrewhanna]

I have a single table with information for a call tracking system. Most of the information is yes/no. I need to be able to total the amount of yes' for each column within a date range. Right now, I have about 100 queries to try and accomplish this. However, I am almost certain that this can be done in a single query.

I put the CallNumber total in the query with the date specifications. Then a query for each field combined with this query to give the amount of yes' in that time range. I am only able to do one Where after the CallNumber query...is there a way to calculate all the yes' inside the same query (for between those dates of course)?

 

[edtab]

Try using the Dcount() function.

If you are trying the count the number of "Yes" records, or "No" records, this function should do it.

You'll have to have two of them, one for each condition.

edtab

 

[RV]

Just a question of interest, how many columns does your table consist of?

RV

 

[raskew]

Say, as an example, you have table4 with this structure:

myDate - date
Item1 - Yes/No
Item2 - Yes/No
Item3 - Yes/No

- and Items 1 - 3 are populated either with Yes (-1) or No (0),
in any manner you choose

- then you need only to sum each of the Item fields and apply the ABS() function to determine how many to arrive at the number of Yes (-1) answers in each field.

- Here's sample code which you can adapt to meet your needs:

SELECT Abs(Sum(Table4.item1)) AS SumOfitem1, Abs(Sum(Table4.item2)) AS SumOfitem2, Abs(Sum(Table4.item3)) AS SumOfitem3
FROM Table4
WHERE (((Table4.myDate)>=[enter start date] And (Table4.myDate)<=[enter end date]));