Monthly Totals with a Subquery

[Tirelle]

I am still working on an application where currently I need to Calculate
charge totals for each type of charge per month. Here are the details.

tblCharges with applicable fields QBRMonth as Date, QBR as Text,
ChargeType as string, and HoursOfCharge as Double.
QBRMonth represents the first day of the month of which charge was
performed. There will be several charges with this date.
QBR is an Identifier for which period Charge is a part of.
ChargeType represents type of charge performed. 4 Options = (Normal,
Equalizing, Partial and Test Discharge )
HoursOfCharge represents time to perform a given charge.

I need to Count the number of each type of charge per month and sum the
totals of each charge for that same period.

Here is SQL statement I am currently working with to count Equalizing charges.

SELECT qryCharges.QBRMonth, qryCharges.Type, qryCharges.QBR
(SELECT Count(qryCharges.Type) FROM qryCharges AS M2
Where M2.QBRMonth = qryCharges.QBRMonth And qryCharges.Type =
"EQUALIZING" ) AS Equalizing
FROM qryCharges;

It seems to work to count the number of charges per month, but I have
duplicates in results. I believe its a grouping issue. Please Help

Thank You

 

[Robert]

You are nesting the table to itself. Have you tried joining the table to
itself?

I would just use a simple query and make the totals in a report. Reports
have a sorting and grouping function..

Robert

 

[Tirelle]

How would I count the number of each type of charge in the report?

 

[Robert]

The Count function should do it (as the control source of a textbox in your
group footer. i.e. =Count([fieldname]). Double check to make sure it resets
the count for a new group.

 

[Tirelle]

Thanks, I'll look into that too but I want to be able to view this data on a
form too. Also, ChargeType is one field with 3 options . How do I count each
type for a given month?

The Count function should do it (as the control source of a textbox in your
group footer. i.e. =Count([fieldname]). Double check to make sure it resets
the count for a new group.

How would I count the number of each type of charge in the report?

 

[Robert]

There's no sorting and grouping on continuous forms.

You can create a group for chargetype. Or use iif functions in your totals
textboxes.

Thanks, I'll look into that too but I want to be able to view this data on
a
form too. Also, ChargeType is one field with 3 options . How do I count
each
type for a given month?

The Count function should do it (as the control source of a textbox in
your
group footer. i.e. =Count([fieldname]). Double check to make sure it
resets
the count for a new group.

How would I count the number of each type of charge in the report?

:

You are nesting the table to itself. Have you tried joining the table
to
itself?

I would just use a simple query and make the totals in a report.
Reports
have a sorting and grouping function..

Robert

I am still working on an application where currently I need to
Calculate
charge totals for each type of charge per month. Here are the
details.

tblCharges with applicable fields QBRMonth as Date, QBR as Text,
ChargeType as string, and HoursOfCharge as Double.
QBRMonth represents the first day of the month of which charge was
performed. There will be several charges with this date.
QBR is an Identifier for which period Charge is a part of.
ChargeType represents type of charge performed. 4 Options = (Normal,
Equalizing, Partial and Test Discharge )
HoursOfCharge represents time to perform a given charge.

I need to Count the number of each type of charge per month and sum
the
totals of each charge for that same period.

Here is SQL statement I am currently working with to count
Equalizing
charges.

SELECT qryCharges.QBRMonth, qryCharges.Type, qryCharges.QBR
(SELECT Count(qryCharges.Type) FROM qryCharges AS M2
Where M2.QBRMonth = qryCharges.QBRMonth And qryCharges.Type =
"EQUALIZING" ) AS Equalizing
FROM qryCharges;

It seems to work to count the number of charges per month, but I
have
duplicates in results. I believe its a grouping issue. Please Help

Thank You

 

[Tirelle]

If I get all transformations and analysis performed with query, I can show it

There's no sorting and grouping on continuous forms.

You can create a group for chargetype. Or use iif functions in your totals
textboxes.

Thanks, I'll look into that too but I want to be able to view this data on
a
form too. Also, ChargeType is one field with 3 options . How do I count
each
type for a given month?

The Count function should do it (as the control source of a textbox in
your
group footer. i.e. =Count([fieldname]). Double check to make sure it
resets
the count for a new group.

How would I count the number of each type of charge in the report?

:

You are nesting the table to itself. Have you tried joining the table
to
itself?

I would just use a simple query and make the totals in a report.
Reports
have a sorting and grouping function..

Robert

I am still working on an application where currently I need to
Calculate
charge totals for each type of charge per month. Here are the
details.

tblCharges with applicable fields QBRMonth as Date, QBR as Text,
ChargeType as string, and HoursOfCharge as Double.
QBRMonth represents the first day of the month of which charge was
performed. There will be several charges with this date.
QBR is an Identifier for which period Charge is a part of.
ChargeType represents type of charge performed. 4 Options = (Normal,
Equalizing, Partial and Test Discharge )
HoursOfCharge represents time to perform a given charge.

I need to Count the number of each type of charge per month and sum
the
totals of each charge for that same period.

Here is SQL statement I am currently working with to count
Equalizing
charges.

SELECT qryCharges.QBRMonth, qryCharges.Type, qryCharges.QBR
(SELECT Count(qryCharges.Type) FROM qryCharges AS M2
Where M2.QBRMonth = qryCharges.QBRMonth And qryCharges.Type =
"EQUALIZING" ) AS Equalizing
FROM qryCharges;

It seems to work to count the number of charges per month, but I
have
duplicates in results. I believe its a grouping issue. Please Help

Thank You

 

[Robert]

Since the result of a query always consists of uniform columns (I've never
seen one that didn't), then, yes, you could always show it on a continuous
form.

If I get all transformations and analysis performed with query, I can show
it

There's no sorting and grouping on continuous forms.

You can create a group for chargetype. Or use iif functions in your
totals
textboxes.

Thanks, I'll look into that too but I want to be able to view this data
on
a
form too. Also, ChargeType is one field with 3 options . How do I
count
each
type for a given month?

:

The Count function should do it (as the control source of a textbox in
your
group footer. i.e. =Count([fieldname]). Double check to make sure it
resets
the count for a new group.

How would I count the number of each type of charge in the report?

:

You are nesting the table to itself. Have you tried joining the
table
to
itself?

I would just use a simple query and make the totals in a report.
Reports
have a sorting and grouping function..

Robert

I am still working on an application where currently I need to
Calculate
charge totals for each type of charge per month. Here are the
details.

tblCharges with applicable fields QBRMonth as Date, QBR as
Text,
ChargeType as string, and HoursOfCharge as Double.
QBRMonth represents the first day of the month of which charge
was
performed. There will be several charges with this date.
QBR is an Identifier for which period Charge is a part of.
ChargeType represents type of charge performed. 4 Options =
(Normal,
Equalizing, Partial and Test Discharge )
HoursOfCharge represents time to perform a given charge.

I need to Count the number of each type of charge per month and
sum
the
totals of each charge for that same period.

Here is SQL statement I am currently working with to count
Equalizing
charges.

SELECT qryCharges.QBRMonth, qryCharges.Type, qryCharges.QBR
(SELECT Count(qryCharges.Type) FROM qryCharges AS M2
Where M2.QBRMonth = qryCharges.QBRMonth And qryCharges.Type =
"EQUALIZING" ) AS Equalizing
FROM qryCharges;

It seems to work to count the number of charges per month, but I
have
duplicates in results. I believe its a grouping issue. Please
Help

Thank You