Counting Help Pretty Please!

[Mel]

Hello,

I am trying to count the # of each different wine shown in my report so I
can use it in a formula to show the % of each wine in the report. I used the
regular count funtion on one that I only had 2 values I was looking for and
it worked great; however, it will not work of the wines for me. Below is
what I did for the 2 values one that worked and I wished it would work for
the wines which is about 20 different values. Can some one help me please?
I really appreciate your help. Thanks!

=Count([VARIETAL])

=IIf([DIRECT IMPORT]="YES",Count([DIRECT IMPORT]),0)

=IIf(([COUNT DI YES]/[COUNT VARIETAL TOTAL])<>0,[COUNT DI YES]/[COUNT
VARIETAL TOTAL],0)

 

[Access_Rookie]

Hello,

Set up your report with a group header and footer. In the header, place the
field decribing the wine and in the footer use the count function. This with
count the number of times that wine appears in it's group. If I understood
your question, this should work.

Hope this helps!!

 

[Mel]

Thanks; however, that is what I have done. If the text value I was looking
for was only 2 different values (example Yes or No), then I do get a correct
count value.

If the text value I am looking for is more than 2 (example the 20 different
wine types), then I only receive a 0 for the count value.

I need to get a count for each of the 20 different wine types.

Is there another option?

I really appreciate your help. Thanks!

Mel

Hello,

Set up your report with a group header and footer. In the header, place the
field decribing the wine and in the footer use the count function. This with
count the number of times that wine appears in it's group. If I understood
your question, this should work.

Hope this helps!!

Hello,

I am trying to count the # of each different wine shown in my report so I
can use it in a formula to show the % of each wine in the report. I used the
regular count funtion on one that I only had 2 values I was looking for and
it worked great; however, it will not work of the wines for me. Below is
what I did for the 2 values one that worked and I wished it would work for
the wines which is about 20 different values. Can some one help me please?
I really appreciate your help. Thanks!

=Count([VARIETAL])

=IIf([DIRECT IMPORT]="YES",Count([DIRECT IMPORT]),0)

=IIf(([COUNT DI YES]/[COUNT VARIETAL TOTAL])<>0,[COUNT DI YES]/[COUNT
VARIETAL TOTAL],0)

 

[Access_Rookie]

Hello,

If you have everything set up correctly, at the end of each section of each
wine you should have a total. Could be my mistake instead of Count use Sum.
So in the footer (group footer not report footer) make a copy of the text box
that you are obtaining your value from and paste it in the group footer. Then
click on the properties of the textbox and in the Control Source type
=Sum([Name of your field]) and this will give you your totals for each wine
listed in the different groups.

Thanks; however, that is what I have done. If the text value I was looking
for was only 2 different values (example Yes or No), then I do get a correct
count value.

If the text value I am looking for is more than 2 (example the 20 different
wine types), then I only receive a 0 for the count value.

I need to get a count for each of the 20 different wine types.

Is there another option?

I really appreciate your help. Thanks!

Mel

Hello,

Set up your report with a group header and footer. In the header, place the
field decribing the wine and in the footer use the count function. This with
count the number of times that wine appears in it's group. If I understood
your question, this should work.

Hope this helps!!

Hello,

I am trying to count the # of each different wine shown in my report so I
can use it in a formula to show the % of each wine in the report. I used the
regular count funtion on one that I only had 2 values I was looking for and
it worked great; however, it will not work of the wines for me. Below is
what I did for the 2 values one that worked and I wished it would work for
the wines which is about 20 different values. Can some one help me please?
I really appreciate your help. Thanks!

=Count([VARIETAL])

=IIf([DIRECT IMPORT]="YES",Count([DIRECT IMPORT]),0)

=IIf(([COUNT DI YES]/[COUNT VARIETAL TOTAL])<>0,[COUNT DI YES]/[COUNT
VARIETAL TOTAL],0)

 

[Mel]

Hello,

I do really appreciate your help; however, if I do it that way, I cannot use
it in another formula I have (example below) for each wine type. I did try
it and I got an error-Data type mismatch in criteria expression.

Maybe I am approaching this from the wrong angle. Is there another way to
achieve the individual counts to use in the below formula? Or is there
another way to achieve the results of the below formula?

Again, thank you so much for your help!

=IIf(([COUNT DI YES]/[COUNT VARIETAL TOTAL])<>0,[COUNT DI YES]/[COUNT
VARIETAL TOTAL],0)

Mel

Hello,

If you have everything set up correctly, at the end of each section of each
wine you should have a total. Could be my mistake instead of Count use Sum.
So in the footer (group footer not report footer) make a copy of the text box
that you are obtaining your value from and paste it in the group footer. Then
click on the properties of the textbox and in the Control Source type
=Sum([Name of your field]) and this will give you your totals for each wine
listed in the different groups.

Thanks; however, that is what I have done. If the text value I was looking
for was only 2 different values (example Yes or No), then I do get a correct
count value.

If the text value I am looking for is more than 2 (example the 20 different
wine types), then I only receive a 0 for the count value.

I need to get a count for each of the 20 different wine types.

Is there another option?

I really appreciate your help. Thanks!

Mel

Hello,

Set up your report with a group header and footer. In the header, place the
field decribing the wine and in the footer use the count function. This with
count the number of times that wine appears in it's group. If I understood
your question, this should work.

Hope this helps!!

:

Hello,

I am trying to count the # of each different wine shown in my report so I
can use it in a formula to show the % of each wine in the report. I used the
regular count funtion on one that I only had 2 values I was looking for and
it worked great; however, it will not work of the wines for me. Below is
what I did for the 2 values one that worked and I wished it would work for
the wines which is about 20 different values. Can some one help me please?
I really appreciate your help. Thanks!

=Count([VARIETAL])

=IIf([DIRECT IMPORT]="YES",Count([DIRECT IMPORT]),0)

=IIf(([COUNT DI YES]/[COUNT VARIETAL TOTAL])<>0,[COUNT DI YES]/[COUNT
VARIETAL TOTAL],0)

 

[Access_Rookie]

How are you accumulating the information? Meaning, on your form how are you
collecting the data for each type of wine are you using checkboxes, CBO's,
Option Groups. Depending on how your form is set up will determine how your
query will pull from the table which at the end will determine what comes out

Hello,

I do really appreciate your help; however, if I do it that way, I cannot use
it in another formula I have (example below) for each wine type. I did try
it and I got an error-Data type mismatch in criteria expression.

Maybe I am approaching this from the wrong angle. Is there another way to
achieve the individual counts to use in the below formula? Or is there
another way to achieve the results of the below formula?

Again, thank you so much for your help!

=IIf(([COUNT DI YES]/[COUNT VARIETAL TOTAL])<>0,[COUNT DI YES]/[COUNT
VARIETAL TOTAL],0)

Mel

Hello,

If you have everything set up correctly, at the end of each section of each
wine you should have a total. Could be my mistake instead of Count use Sum.
So in the footer (group footer not report footer) make a copy of the text box
that you are obtaining your value from and paste it in the group footer. Then
click on the properties of the textbox and in the Control Source type
=Sum([Name of your field]) and this will give you your totals for each wine
listed in the different groups.

Thanks; however, that is what I have done. If the text value I was looking
for was only 2 different values (example Yes or No), then I do get a correct
count value.

If the text value I am looking for is more than 2 (example the 20 different
wine types), then I only receive a 0 for the count value.

I need to get a count for each of the 20 different wine types.

Is there another option?

I really appreciate your help. Thanks!

Mel

:

Hello,

Set up your report with a group header and footer. In the header, place the
field decribing the wine and in the footer use the count function. This with
count the number of times that wine appears in it's group. If I understood
your question, this should work.

Hope this helps!!

:

Hello,

I am trying to count the # of each different wine shown in my report so I
can use it in a formula to show the % of each wine in the report. I used the
regular count funtion on one that I only had 2 values I was looking for and
it worked great; however, it will not work of the wines for me. Below is
what I did for the 2 values one that worked and I wished it would work for
the wines which is about 20 different values. Can some one help me please?
I really appreciate your help. Thanks!

=Count([VARIETAL])

=IIf([DIRECT IMPORT]="YES",Count([DIRECT IMPORT]),0)

=IIf(([COUNT DI YES]/[COUNT VARIETAL TOTAL])<>0,[COUNT DI YES]/[COUNT
VARIETAL TOTAL],0)

 

[Mel]

Yes it is a complex data base.

I do not have a form for data entry at this point. I am entering the data
straight into a table. I enter the UPC and then I have a query pull in the
information from other tables based off of what is entered so the user cannot
provide incorrect information and the data will all be consistant. The types
of wine or "Varietal" is one of those fields.

Does this effect it?

Thanks!

Mel

How are you accumulating the information? Meaning, on your form how are you
collecting the data for each type of wine are you using checkboxes, CBO's,
Option Groups. Depending on how your form is set up will determine how your
query will pull from the table which at the end will determine what comes out

Hello,

I do really appreciate your help; however, if I do it that way, I cannot use
it in another formula I have (example below) for each wine type. I did try
it and I got an error-Data type mismatch in criteria expression.

Maybe I am approaching this from the wrong angle. Is there another way to
achieve the individual counts to use in the below formula? Or is there
another way to achieve the results of the below formula?

Again, thank you so much for your help!

=IIf(([COUNT DI YES]/[COUNT VARIETAL TOTAL])<>0,[COUNT DI YES]/[COUNT
VARIETAL TOTAL],0)

Mel

Hello,

If you have everything set up correctly, at the end of each section of each
wine you should have a total. Could be my mistake instead of Count use Sum.
So in the footer (group footer not report footer) make a copy of the text box
that you are obtaining your value from and paste it in the group footer. Then
click on the properties of the textbox and in the Control Source type
=Sum([Name of your field]) and this will give you your totals for each wine
listed in the different groups.

:

Thanks; however, that is what I have done. If the text value I was looking
for was only 2 different values (example Yes or No), then I do get a correct
count value.

If the text value I am looking for is more than 2 (example the 20 different
wine types), then I only receive a 0 for the count value.

I need to get a count for each of the 20 different wine types.

Is there another option?

I really appreciate your help. Thanks!

Mel

:

Hello,

Set up your report with a group header and footer. In the header, place the
field decribing the wine and in the footer use the count function. This with
count the number of times that wine appears in it's group. If I understood
your question, this should work.

Hope this helps!!

:

Hello,

I am trying to count the # of each different wine shown in my report so I
can use it in a formula to show the % of each wine in the report. I used the
regular count funtion on one that I only had 2 values I was looking for and
it worked great; however, it will not work of the wines for me. Below is
what I did for the 2 values one that worked and I wished it would work for
the wines which is about 20 different values. Can some one help me please?
I really appreciate your help. Thanks!

=Count([VARIETAL])

=IIf([DIRECT IMPORT]="YES",Count([DIRECT IMPORT]),0)

=IIf(([COUNT DI YES]/[COUNT VARIETAL TOTAL])<>0,[COUNT DI YES]/[COUNT
VARIETAL TOTAL],0)

 

[Access_Rookie]

Well, if you have multiple tables I am going to guess that all your
information from those tables are not pulling into the query and if it is
pulling in the query correctly, then something is set up wrong in the report.
Without seeing what your doing this would be beyond me and probably need one
of the more experienced access members to help. I am very sorry I was not
able to help. However, depend on the amount of information and fields used,
sometimes a flatsheet database works well and is much less complicated to
structure and modify. You only have one table to hold all your information
which makes it easier to pull your query and create reports. However, this
will only suite some folks not others who need to have separated tables.

One piece of advice though; unless you are the only one entering information
in the table I would strongly recommend you create a form for the users to
enter data otherwise accidents could happen and someone might delete or
accidentally type over current information. Trust me, I seen it happen and
have always used a form since.

Yes it is a complex data base.

I do not have a form for data entry at this point. I am entering the data
straight into a table. I enter the UPC and then I have a query pull in the
information from other tables based off of what is entered so the user cannot
provide incorrect information and the data will all be consistant. The types
of wine or "Varietal" is one of those fields.

Does this effect it?

Thanks!

Mel

How are you accumulating the information? Meaning, on your form how are you
collecting the data for each type of wine are you using checkboxes, CBO's,
Option Groups. Depending on how your form is set up will determine how your
query will pull from the table which at the end will determine what comes out

Hello,

I do really appreciate your help; however, if I do it that way, I cannot use
it in another formula I have (example below) for each wine type. I did try
it and I got an error-Data type mismatch in criteria expression.

Maybe I am approaching this from the wrong angle. Is there another way to
achieve the individual counts to use in the below formula? Or is there
another way to achieve the results of the below formula?

Again, thank you so much for your help!

=IIf(([COUNT DI YES]/[COUNT VARIETAL TOTAL])<>0,[COUNT DI YES]/[COUNT
VARIETAL TOTAL],0)

Mel

:

Hello,

If you have everything set up correctly, at the end of each section of each
wine you should have a total. Could be my mistake instead of Count use Sum.
So in the footer (group footer not report footer) make a copy of the text box
that you are obtaining your value from and paste it in the group footer. Then
click on the properties of the textbox and in the Control Source type
=Sum([Name of your field]) and this will give you your totals for each wine
listed in the different groups.

:

Thanks; however, that is what I have done. If the text value I was looking
for was only 2 different values (example Yes or No), then I do get a correct
count value.

If the text value I am looking for is more than 2 (example the 20 different
wine types), then I only receive a 0 for the count value.

I need to get a count for each of the 20 different wine types.

Is there another option?

I really appreciate your help. Thanks!

Mel

:

Hello,

Set up your report with a group header and footer. In the header, place the
field decribing the wine and in the footer use the count function. This with
count the number of times that wine appears in it's group. If I understood
your question, this should work.

Hope this helps!!

:

Hello,

I am trying to count the # of each different wine shown in my report so I
can use it in a formula to show the % of each wine in the report. I used the
regular count funtion on one that I only had 2 values I was looking for and
it worked great; however, it will not work of the wines for me. Below is
what I did for the 2 values one that worked and I wished it would work for
the wines which is about 20 different values. Can some one help me please?
I really appreciate your help. Thanks!

=Count([VARIETAL])

=IIf([DIRECT IMPORT]="YES",Count([DIRECT IMPORT]),0)

=IIf(([COUNT DI YES]/[COUNT VARIETAL TOTAL])<>0,[COUNT DI YES]/[COUNT
VARIETAL TOTAL],0)

 

[Kc-Mass]

Hi Mel

You need three things to get your report:
1. a Table or Tables that holds the data you want to report on, 2. a query
that pulls the desired data from the table and 3. a report to present that
data.

Just to simplify things let me describe them in a very basic way.

Create a table that has two text fields -"Varietal" and "WineName".

Enter in three records with this data:

VARIETAL WINENAME
Cabernet Big Red
Chardonnay Big White
Cabernet Very Red

Create a query that selects the Varietal and and Winename fields for all the
records. This will pull the three records.

Create a report that has two text boxes representing the two fields (use
the Wizard for simplicity). When the Wizard asks if you want any grouping
answer Yes and specify "Varietal" as the field to group on.

When the Wizard finished switch to design view and right click on the
topmost frame of the report and select "Sorting and Grouping". Click on the
first line of the Sorting and Grouping pane (which will be Varietal). Make
sure it is sorting on Varietal) Down below select the Group footer property
and change the value to Yes. Access will add a group footer. Place a text
box control in the footer. The control will initially show the data source
as Unbound. Change that to "=Count(WineName)".

Run the report. You will see something like:

WineName Varietal

Big Red Cabernet

Very Red Cabernet

Count of this Varietal 2

Big White Chardonnay

Count of this Varietal 1



This is the simplest example but it shows you the flow you are looking for.
Your data and query will be more complex but the process will be the same.

If you understand this but are still getting "0" counts you probably need to
look at your query. If it pulls Varietals for which there is no
corresponding WineName in your data then a count of "0" would be correct.
If you don't want them in the report then screen out Null values in
WineNames.

If I've missed the point comment back.



Regards



Kevin

Thanks; however, that is what I have done. If the text value I was
looking
for was only 2 different values (example Yes or No), then I do get a
correct
count value.

If the text value I am looking for is more than 2 (example the 20
different
wine types), then I only receive a 0 for the count value.

I need to get a count for each of the 20 different wine types.

Is there another option?

I really appreciate your help. Thanks!

Mel

Hello,

Set up your report with a group header and footer. In the header, place
the
field decribing the wine and in the footer use the count function. This
with
count the number of times that wine appears in it's group. If I
understood
your question, this should work.

Hope this helps!!

Hello,

I am trying to count the # of each different wine shown in my report so
I
can use it in a formula to show the % of each wine in the report. I
used the
regular count funtion on one that I only had 2 values I was looking for
and
it worked great; however, it will not work of the wines for me. Below
is
what I did for the 2 values one that worked and I wished it would work
for
the wines which is about 20 different values. Can some one help me
please?
I really appreciate your help. Thanks!

=Count([VARIETAL])

=IIf([DIRECT IMPORT]="YES",Count([DIRECT IMPORT]),0)

=IIf(([COUNT DI YES]/[COUNT VARIETAL TOTAL])<>0,[COUNT DI YES]/[COUNT
VARIETAL TOTAL],0)