MS Access Count Question

[jeremiah32333]

The values showed below are not duplicate. What I'm trying to see if possible is counting the first instance of a value in a report. For example, my query returns seven lines, so of the values are the same. Is there a way where to create an additional column can be added to shown if multiple instances that same value exists?

Trailer Ladder Month PO Vendor YesNO
04050 SEP 293530 STUDIO RAY Yes
18196 OCT 315902 KELLWOOD CO. Yes
279852 SEP 300456 VAN HEUSEN Yes
300159 SEP 342079 CASTLE HILL Yes
300159 SEP 342087 DANNY & NICOLE No
300159 SEP 348699 KATHERINE BISHOP No


In Excel the same can be accomplished by using formula =IF(COUNTIF(A$1:A1,A1)>1,"No","Yes")

Example

123 Yes
123 No
123 No

 

[jrbarnett]

From your data above, where are you pulling the value "123" from?

You can use a GROUP BY and HAVING statement to filter down to sets with more than one row. For example:

SELECT trailer, YesNo, Count (*) As Total
FROM YourTable
GROUP BY trailer, YesNo
HAVING COUNT (*) >1

From your data this will show:
300159 No 2

All other rows have been excluded because:
Trailers 04050, 18196 and 279852 only have one row each.
There is only one row of data for 300159 with a "Yes" value in the YesNo field, so it is excluded by the Count statement in the Having clause.

John

 

[dhookom]

It looks like the records are in order of Trailer and then PO. You should be able to determine if the PO is the Minimum for the trailer and return either Yes or No.

I would group first by Trailer with a header. Add a text box to the Trailer Header section:
Name: txtMinPO
Control Source: =Min(PO)
Then set the control source of your YesNo text box to:
=IIf([PO]=[txtMinPO],"Yes","No")


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeremiah32333]

dhookom you're suggestion worked great! thank you

 

[jeremiah32333]

In my detail section, I get Yes and No value to return for each PO. If the PO matches the value in my Trailer header, it returns a yes, if not then no. The problem I'm having is how do I count the yes in my report header. I tried creating an unbound text box with the formula: Count(IIf([Text142]="Yes",[Text142])). When I go to print preview, I receive a prompt for Text142?

 

[dhookom]

As you have discovered, you can't "aggregate" a control from one report section to another. I would create a query similar to your report's record source that groups by Trailer. Then create another query based on the group by that counts the records in the grouped trailers. This should result in a single record query that can be added to your report's record source query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeremiah32333]

Thanks, I did what you said, it worked.