Magic VBA to update a field in a table

[jane30]

Hi, I have a table in my Access97 database. It was created by the make-table query doing the one-to-many table join. The records are like this:

Acct# StartDate EndDate Days ActivityDate Clinic
111111 11/24/99 11/30/99 7 11/24/99 xxxxxx
111111 11/24/99 11/30/99 7 11/25/99 yyyyyy
111111 11/24/99 11/30/99 7 11/26/99 zzzzzz
111111 11/24/99 11/30/99 7 11/27/99 aaaaaa
111111 11/24/99 11/30/99 7 11/28/99 bbbbbb
111111 11/24/99 11/30/99 7 11/29/99 cccccc
111111 11/24/99 11/30/99 7 11/30/99 dddddd
222222 01/02/00 01/05/00 4 01/02/00 xxxxxx
222222 01/02/00 01/05/00 4 01/03/00 nnnnnn
222222 01/02/00 01/05/00 4 01/04/00 mmmmmm
222222 01/02/00 01/05/00 4 01/05/00 cccccc

There are total over 70,000 records in this table.
When I create a pivot table to get the total days, the sum value got overstated alot. What should I do to get the correct calculation? Any ideas? Thanks a bunch in advance

 

[BillPower]

The following will give you the total days in the table:

SELECT Sum(YourTableName.Days) AS Days
FROM YourTableName;

This will give you the total days in the table grouped by clinic:

SELECT YourTableName.Clinic, Sum(YourTableName.Days) AS Days
FROM YourTableName
GROUP BY YourTableName.Clinic;

This will give you the total days by clinic between a certain StartDate and EndDate:

SELECT YourTableName.Clinic, Sum(YourTableName.Days) AS Days, YourTableName.StartDate, YourTableName.EndDate
FROM YourTableName
GROUP BY YourTableName.Clinic, YourTableName.StartDate, YourTableName.EndDate
HAVING (((YourTableName.StartDate)>=#1/1/2003#) AND ((YourTableName.EndDate)<=#1/5/2003#));

I did experiment with a crosstab (pivot), but you would end up with loads of columns.

Next time you have a question, try giving the table/query/form name etc. Replace YourTableName with your table name in the queries above. Hope this helps.