DCount problem

[Tom]

I have a table of customers. (We have about 15,000 customers. This
table has approx 186,000 records):

tblElec_Cust_Consump_4_Andy

LocID
CustID
Year
Month
Flag
Consump

There is usually only one customer per location, but there MAY be
more, as in the case of apartment houses. There will be up to 12
records per customer per location, as I'm only interested in a one
year snapshot. (Any location won't have more than 12 records as only
one customer can be at a given location at any given time.)

I need to produce a monthly average of consumption for the year, for
each customer. Since not every customer will necessarily have 12
months of consumption, I need to count how many months a given
customer has. Next I need to add all the consumptions for those
months and divide by the number of months I came up with first.

I've created a separate table for the averages. This table has only
one record per customer, since it covers the entire one year period.
(This table has approx 22,000 records. The apartment complexes run
thru about 600 customers per month (15,000 + (600*12)=22200)):

tblElec_Cust_Consump_4_Andy_AVG

LocID
CustID
Year
Month
Nmbr of Mos
Consump Avg

I've tried:

UPDATE
tblElec_Cust_Consump_4_Andy_AVG, tblElec_Cust_Consump_4_Andy
SET
tblElec_Cust_Consump_4_Andy_AVG.[KW Months Billed] =
DCount("[tblElec_Cust_Consump_4_Andy].[KW
CMP]","tblElec_Cust_Consump_4_Andy","[KW CMP]='KW'");

Actually, I have to calc a monthly average twice. This first time is
not actually for consumption, but rather for an average "demand" (KW).
(Hence the (Flag) [KW CMP]='KW'.) Only about 600 customers have
demand. And ALL customers have consumption ((Flag) [KWH CMP]='KWH'.).

My current update query has both tables. I've tried:

Inner join - You are about to update 186928 row(s).
Left join - You are about to update 186928 row(s).
Right join - You are about to update 186928 row(s).

The left join is correct though, isn't it? This is my first time
using DCount, and apparently I just don't get it...yet. Any help you
can provide is very much appreciated...thanks in advance.

Tom

 

[John Spencer (MVP)]

You can use the Average aggregate function to do this. Unless, you have records
where Consump is ZERO and want to exclude them.


SELECT T.LocID, T.CustID, Avg(T.Consump) as Avg
FROM tblElec_Cust_Consump_4_Andy as T
GROUP BY T.LocID, T.CustID

In the query grid, add in the fields you want in your query. Now select Totals
from the View menu.

You will have a new row available,
Select Group By for LocId and CustID
Select Average for Consump

Run the query.

If you have records with zero in them you can exclude them using a where clause
Add ANOTHER Consump clause
Select WHERE as the TOTAL setting
Set the Criteria to > 0

That will exclude the zero records. You have to add the extra Consump column
for the where statement to exclude the records before the average is calculated.
If you try to do this under the average column you will be excluding customers
where the AVERAGE of the records is less than zero instead of the individual
month records where Consump is less than zero.

I have a table of customers. (We have about 15,000 customers. This
table has approx 186,000 records):

tblElec_Cust_Consump_4_Andy

LocID
CustID
Year
Month
Flag
Consump

There is usually only one customer per location, but there MAY be
more, as in the case of apartment houses. There will be up to 12
records per customer per location, as I'm only interested in a one
year snapshot. (Any location won't have more than 12 records as only
one customer can be at a given location at any given time.)

I need to produce a monthly average of consumption for the year, for
each customer. Since not every customer will necessarily have 12
months of consumption, I need to count how many months a given
customer has. Next I need to add all the consumptions for those
months and divide by the number of months I came up with first.

I've created a separate table for the averages. This table has only
one record per customer, since it covers the entire one year period.
(This table has approx 22,000 records. The apartment complexes run
thru about 600 customers per month (15,000 + (600*12)=22200)):

tblElec_Cust_Consump_4_Andy_AVG

LocID
CustID
Year
Month
Nmbr of Mos
Consump Avg

I've tried:

UPDATE
tblElec_Cust_Consump_4_Andy_AVG, tblElec_Cust_Consump_4_Andy
SET
tblElec_Cust_Consump_4_Andy_AVG.[KW Months Billed] =
DCount("[tblElec_Cust_Consump_4_Andy].[KW
CMP]","tblElec_Cust_Consump_4_Andy","[KW CMP]='KW'");

Actually, I have to calc a monthly average twice. This first time is
not actually for consumption, but rather for an average "demand" (KW).
(Hence the (Flag) [KW CMP]='KW'.) Only about 600 customers have
demand. And ALL customers have consumption ((Flag) [KWH CMP]='KWH'.).

My current update query has both tables. I've tried:

Inner join - You are about to update 186928 row(s).
Left join - You are about to update 186928 row(s).
Right join - You are about to update 186928 row(s).

The left join is correct though, isn't it? This is my first time
using DCount, and apparently I just don't get it...yet. Any help you
can provide is very much appreciated...thanks in advance.

Tom

 

[Tom]

That works Great! Thanks. But now I've run into another snag.

I found out that I can't use an update query to get my counts &
averages, instead I must use a select query. So then I figured that I
would use the select query with my count/average as input to my update
query with my 'AVG' table. When I do this however, I get that nasty
msg:

"Operation must use an updateable query."

In the past I've been able to get around this. This time I think I'm
stumped. Access HELP tells me:

"This error occurs when the current query's Update To row includes a
field from either a crosstab query or select query in which an
aggregate (total) was calculated for the field (using either the
Totals row or a domain function in the Field row). To update a field
using the aggregate of another field, calculate the aggregate in the
update query itself, not a different query."

But when I try to accomplish this in my update query (which I believe
I started out with trying), I get teh same msg.

Could I impose for a little more help?

You can use the Average aggregate function to do this. Unless, you have records
where Consump is ZERO and want to exclude them.


SELECT T.LocID, T.CustID, Avg(T.Consump) as Avg
FROM tblElec_Cust_Consump_4_Andy as T
GROUP BY T.LocID, T.CustID

In the query grid, add in the fields you want in your query. Now select Totals
from the View menu.

You will have a new row available,
Select Group By for LocId and CustID
Select Average for Consump

Run the query.

If you have records with zero in them you can exclude them using a where clause
Add ANOTHER Consump clause
Select WHERE as the TOTAL setting
Set the Criteria to > 0

That will exclude the zero records. You have to add the extra Consump column
for the where statement to exclude the records before the average is calculated.
If you try to do this under the average column you will be excluding customers
where the AVERAGE of the records is less than zero instead of the individual
month records where Consump is less than zero.

I have a table of customers. (We have about 15,000 customers. This
table has approx 186,000 records):

tblElec_Cust_Consump_4_Andy

LocID
CustID
Year
Month
Flag
Consump

There is usually only one customer per location, but there MAY be
more, as in the case of apartment houses. There will be up to 12
records per customer per location, as I'm only interested in a one
year snapshot. (Any location won't have more than 12 records as only
one customer can be at a given location at any given time.)

I need to produce a monthly average of consumption for the year, for
each customer. Since not every customer will necessarily have 12
months of consumption, I need to count how many months a given
customer has. Next I need to add all the consumptions for those
months and divide by the number of months I came up with first.

I've created a separate table for the averages. This table has only
one record per customer, since it covers the entire one year period.
(This table has approx 22,000 records. The apartment complexes run
thru about 600 customers per month (15,000 + (600*12)=22200)):

tblElec_Cust_Consump_4_Andy_AVG

LocID
CustID
Year
Month
Nmbr of Mos
Consump Avg

I've tried:

UPDATE
tblElec_Cust_Consump_4_Andy_AVG, tblElec_Cust_Consump_4_Andy
SET
tblElec_Cust_Consump_4_Andy_AVG.[KW Months Billed] =
DCount("[tblElec_Cust_Consump_4_Andy].[KW
CMP]","tblElec_Cust_Consump_4_Andy","[KW CMP]='KW'");

Actually, I have to calc a monthly average twice. This first time is
not actually for consumption, but rather for an average "demand" (KW).
(Hence the (Flag) [KW CMP]='KW'.) Only about 600 customers have
demand. And ALL customers have consumption ((Flag) [KWH CMP]='KWH'.).

My current update query has both tables. I've tried:

Inner join - You are about to update 186928 row(s).
Left join - You are about to update 186928 row(s).
Right join - You are about to update 186928 row(s).

The left join is correct though, isn't it? This is my first time
using DCount, and apparently I just don't get it...yet. Any help you
can provide is very much appreciated...thanks in advance.

Tom

 

[Tom]

Not to worry. I got past it. Had to create yet ANOTHER table to
store the results..temporarily. Kludgy, but it works.

Thanks again for the help.

You can use the Average aggregate function to do this. Unless, you have records
where Consump is ZERO and want to exclude them.


SELECT T.LocID, T.CustID, Avg(T.Consump) as Avg
FROM tblElec_Cust_Consump_4_Andy as T
GROUP BY T.LocID, T.CustID

In the query grid, add in the fields you want in your query. Now select Totals
from the View menu.

You will have a new row available,
Select Group By for LocId and CustID
Select Average for Consump

Run the query.

If you have records with zero in them you can exclude them using a where clause
Add ANOTHER Consump clause
Select WHERE as the TOTAL setting
Set the Criteria to > 0

That will exclude the zero records. You have to add the extra Consump column
for the where statement to exclude the records before the average is calculated.
If you try to do this under the average column you will be excluding customers
where the AVERAGE of the records is less than zero instead of the individual
month records where Consump is less than zero.

I have a table of customers. (We have about 15,000 customers. This
table has approx 186,000 records):

tblElec_Cust_Consump_4_Andy

LocID
CustID
Year
Month
Flag
Consump

There is usually only one customer per location, but there MAY be
more, as in the case of apartment houses. There will be up to 12
records per customer per location, as I'm only interested in a one
year snapshot. (Any location won't have more than 12 records as only
one customer can be at a given location at any given time.)

I need to produce a monthly average of consumption for the year, for
each customer. Since not every customer will necessarily have 12
months of consumption, I need to count how many months a given
customer has. Next I need to add all the consumptions for those
months and divide by the number of months I came up with first.

I've created a separate table for the averages. This table has only
one record per customer, since it covers the entire one year period.
(This table has approx 22,000 records. The apartment complexes run
thru about 600 customers per month (15,000 + (600*12)=22200)):

tblElec_Cust_Consump_4_Andy_AVG

LocID
CustID
Year
Month
Nmbr of Mos
Consump Avg

I've tried:

UPDATE
tblElec_Cust_Consump_4_Andy_AVG, tblElec_Cust_Consump_4_Andy
SET
tblElec_Cust_Consump_4_Andy_AVG.[KW Months Billed] =
DCount("[tblElec_Cust_Consump_4_Andy].[KW
CMP]","tblElec_Cust_Consump_4_Andy","[KW CMP]='KW'");

Actually, I have to calc a monthly average twice. This first time is
not actually for consumption, but rather for an average "demand" (KW).
(Hence the (Flag) [KW CMP]='KW'.) Only about 600 customers have
demand. And ALL customers have consumption ((Flag) [KWH CMP]='KWH'.).

My current update query has both tables. I've tried:

Inner join - You are about to update 186928 row(s).
Left join - You are about to update 186928 row(s).
Right join - You are about to update 186928 row(s).

The left join is correct though, isn't it? This is my first time
using DCount, and apparently I just don't get it...yet. Any help you
can provide is very much appreciated...thanks in advance.

Tom