Obtain total without dups

[Stockwell43]

Hello,

I have a report that works off a query where the information is pulled from
a main form and a sub form. The report works well except I get dups in one
field which is not a problem because I change the Hide Duplicates property to
yes and they are going. However, the total at the bottom still includes the
duplicates I am hiding. Is there a way to keep the dups hidden and also their
numbers so the total at the bottom only adds the visible numbers?

Thanks!!

 

[Dan @BCBS]

I cannot add a date parameter to the totals query then add that information
to the record source, since the original query that the report is build from,
already has a date parameter.

Could you answer me this:
My report is grouped by Insurance type which gives me a count in the
insurance type footer with =Count(1).

Can it also count on a second group Member?

I've tried adding both group headers and footers and performing the counts
but I can only get one or the other, not both.

Thanks

You need to figure out how to add the date parameters to your query. If you
can't get your head around this you will need to provide us with significant
information such as SQL views, table structures, etc.

--
Duane Hookom
Microsoft Access MVP

Thank you for your timly reply:
What you say below will give me all the members for that Insurance Type.
But, of course I have a date peramiters in my qorginal query.

I created the totals query and added it to the reports record source (by
adding it to the original query that created the report, joined by Insurance
type).

The count reflects all the members for that insurance type in the database
with no regard to the date peramiters. I tried adding date peramiters to the
totals query and received the error "that could refer to more than one table".

So Close, any suggestions?

Did you try to create a totals query that Groups by InsuranceType and counts
Members? Then add this query to your report's record source and join the
InsuranceType fields. You should then be able to add the CountOfMembers field
to your report.

--
Duane Hookom
Microsoft Access MVP


:

I see what your talking about and it helped - but that's not really the issue.
My issue is within the report and I don't need to eliminate dups.
I did try your suggestion with a seperate query Grouped and Counted, then I
linked that to the query that the report was made from, I get an error when I
try to Sum or Count the Members.

There must be a simple way to perform this in the report?
I have two veriables "Member" and "InsuranceType".
The report groups by InsuranceType (InsuranceType Header) but I need to
count the members per Insurance type grouping.

I tried sort/group by Insurancetype with a "=Count(1) in Insurance Type Footer
I tried "=Count([Member])"
and about 1000 other ways.

I cannot see what I'm missing...




:

There are a couple methods. I suggest you create a totals query that groups
by some field and totals exactly the amount you want to display in the
footer. Add this query to your report's record source and join the
appropriate fields if necessary. Then add your total field to the query grid
so that it is available in the report.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have a report that works off a query where the information is pulled from
a main form and a sub form. The report works well except I get dups in one
field which is not a problem because I change the Hide Duplicates property to
yes and they are going. However, the total at the bottom still includes the
duplicates I am hiding. Is there a way to keep the dups hidden and also their
numbers so the total at the bottom only adds the visible numbers?

Thanks!!

 

[Duane Hookom]

You need to figure out how to add the date parameters to your query. If you
can't get your head around this you will need to provide us with significant
information such as SQL views, table structures, etc.

--
Duane Hookom
Microsoft Access MVP

Thank you for your timly reply:
What you say below will give me all the members for that Insurance Type.
But, of course I have a date peramiters in my qorginal query.

I created the totals query and added it to the reports record source (by
adding it to the original query that created the report, joined by Insurance
type).

The count reflects all the members for that insurance type in the database
with no regard to the date peramiters. I tried adding date peramiters to the
totals query and received the error "that could refer to more than one table".

So Close, any suggestions?

Did you try to create a totals query that Groups by InsuranceType and counts
Members? Then add this query to your report's record source and join the
InsuranceType fields. You should then be able to add the CountOfMembers field
to your report.

--
Duane Hookom
Microsoft Access MVP

I see what your talking about and it helped - but that's not really the issue.
My issue is within the report and I don't need to eliminate dups.
I did try your suggestion with a seperate query Grouped and Counted, then I
linked that to the query that the report was made from, I get an error when I
try to Sum or Count the Members.

There must be a simple way to perform this in the report?
I have two veriables "Member" and "InsuranceType".
The report groups by InsuranceType (InsuranceType Header) but I need to
count the members per Insurance type grouping.

I tried sort/group by Insurancetype with a "=Count(1) in Insurance Type Footer
I tried "=Count([Member])"
and about 1000 other ways.

I cannot see what I'm missing...




:

There are a couple methods. I suggest you create a totals query that groups
by some field and totals exactly the amount you want to display in the
footer. Add this query to your report's record source and join the
appropriate fields if necessary. Then add your total field to the query grid
so that it is available in the report.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have a report that works off a query where the information is pulled from
a main form and a sub form. The report works well except I get dups in one
field which is not a problem because I change the Hide Duplicates property to
yes and they are going. However, the total at the bottom still includes the
duplicates I am hiding. Is there a way to keep the dups hidden and also their
numbers so the total at the bottom only adds the visible numbers?

Thanks!!

 

[Duane Hookom]

There are a couple methods. I suggest you create a totals query that groups
by some field and totals exactly the amount you want to display in the
footer. Add this query to your report's record source and join the
appropriate fields if necessary. Then add your total field to the query grid
so that it is available in the report.

 

[Duane Hookom]

Why can't you add a date parameter. First off, consider never using parameter
prompts for your criteria. IMHO this is not an acceptable user interface. You
should use controls on forms for all user interaction with your
queries/reports. You can use the same date controls on your form for criteria
in a dozen queries.

--
Duane Hookom
Microsoft Access MVP

I cannot add a date parameter to the totals query then add that information
to the record source, since the original query that the report is build from,
already has a date parameter.

Could you answer me this:
My report is grouped by Insurance type which gives me a count in the
insurance type footer with =Count(1).

Can it also count on a second group Member?

I've tried adding both group headers and footers and performing the counts
but I can only get one or the other, not both.

Thanks

You need to figure out how to add the date parameters to your query. If you
can't get your head around this you will need to provide us with significant
information such as SQL views, table structures, etc.

--
Duane Hookom
Microsoft Access MVP

Thank you for your timly reply:
What you say below will give me all the members for that Insurance Type.
But, of course I have a date peramiters in my qorginal query.

I created the totals query and added it to the reports record source (by
adding it to the original query that created the report, joined by Insurance
type).

The count reflects all the members for that insurance type in the database
with no regard to the date peramiters. I tried adding date peramiters to the
totals query and received the error "that could refer to more than one table".

So Close, any suggestions?


:

Did you try to create a totals query that Groups by InsuranceType and counts
Members? Then add this query to your report's record source and join the
InsuranceType fields. You should then be able to add the CountOfMembers field
to your report.

--
Duane Hookom
Microsoft Access MVP


:

I see what your talking about and it helped - but that's not really the issue.
My issue is within the report and I don't need to eliminate dups.
I did try your suggestion with a seperate query Grouped and Counted, then I
linked that to the query that the report was made from, I get an error when I
try to Sum or Count the Members.

There must be a simple way to perform this in the report?
I have two veriables "Member" and "InsuranceType".
The report groups by InsuranceType (InsuranceType Header) but I need to
count the members per Insurance type grouping.

I tried sort/group by Insurancetype with a "=Count(1) in Insurance Type Footer
I tried "=Count([Member])"
and about 1000 other ways.

I cannot see what I'm missing...




:

There are a couple methods. I suggest you create a totals query that groups
by some field and totals exactly the amount you want to display in the
footer. Add this query to your report's record source and join the
appropriate fields if necessary. Then add your total field to the query grid
so that it is available in the report.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have a report that works off a query where the information is pulled from
a main form and a sub form. The report works well except I get dups in one
field which is not a problem because I change the Hide Duplicates property to
yes and they are going. However, the total at the bottom still includes the
duplicates I am hiding. Is there a way to keep the dups hidden and also their
numbers so the total at the bottom only adds the visible numbers?

Thanks!!

 

[Dan @BCBS]

My date parameter is a control, the user enters the date on the form, the
query just looks at that date: Between [forms]![f_KeyIndicators].[txtstart]
And [forms]![f_KeyIndicators].[txtend]

There for, I cannot add another date in the count query, then add those
results, that would be two date parameters.

I believe this thread has gone down a rabbit trail, I've reposted my
question under subject "Add a Second count" in hopes to simplify and start
over...

Why can't you add a date parameter. First off, consider never using parameter
prompts for your criteria. IMHO this is not an acceptable user interface. You
should use controls on forms for all user interaction with your
queries/reports. You can use the same date controls on your form for criteria
in a dozen queries.

--
Duane Hookom
Microsoft Access MVP

I cannot add a date parameter to the totals query then add that information
to the record source, since the original query that the report is build from,
already has a date parameter.

Could you answer me this:
My report is grouped by Insurance type which gives me a count in the
insurance type footer with =Count(1).

Can it also count on a second group Member?

I've tried adding both group headers and footers and performing the counts
but I can only get one or the other, not both.

Thanks

You need to figure out how to add the date parameters to your query. If you
can't get your head around this you will need to provide us with significant
information such as SQL views, table structures, etc.

--
Duane Hookom
Microsoft Access MVP


:

Thank you for your timly reply:
What you say below will give me all the members for that Insurance Type.
But, of course I have a date peramiters in my qorginal query.

I created the totals query and added it to the reports record source (by
adding it to the original query that created the report, joined by Insurance
type).

The count reflects all the members for that insurance type in the database
with no regard to the date peramiters. I tried adding date peramiters to the
totals query and received the error "that could refer to more than one table".

So Close, any suggestions?


:

Did you try to create a totals query that Groups by InsuranceType and counts
Members? Then add this query to your report's record source and join the
InsuranceType fields. You should then be able to add the CountOfMembers field
to your report.

--
Duane Hookom
Microsoft Access MVP


:

I see what your talking about and it helped - but that's not really the issue.
My issue is within the report and I don't need to eliminate dups.
I did try your suggestion with a seperate query Grouped and Counted, then I
linked that to the query that the report was made from, I get an error when I
try to Sum or Count the Members.

There must be a simple way to perform this in the report?
I have two veriables "Member" and "InsuranceType".
The report groups by InsuranceType (InsuranceType Header) but I need to
count the members per Insurance type grouping.

I tried sort/group by Insurancetype with a "=Count(1) in Insurance Type Footer
I tried "=Count([Member])"
and about 1000 other ways.

I cannot see what I'm missing...




:

There are a couple methods. I suggest you create a totals query that groups
by some field and totals exactly the amount you want to display in the
footer. Add this query to your report's record source and join the
appropriate fields if necessary. Then add your total field to the query grid
so that it is available in the report.

--
Duane Hookom
Microsoft Access MVP


:

Hello,

I have a report that works off a query where the information is pulled from
a main form and a sub form. The report works well except I get dups in one
field which is not a problem because I change the Hide Duplicates property to
yes and they are going. However, the total at the bottom still includes the
duplicates I am hiding. Is there a way to keep the dups hidden and also their
numbers so the total at the bottom only adds the visible numbers?

Thanks!!