Unique Values Count still counting duplicates

[Miskondukt]

Lo All o/



I am trying to get an accurate count of unique values (in a single field, such as "Order Numbers" & "Won") and am not getting the right results. I have attached five screen shots:

1. The inaccurate results of the intended distinct count query
2. The query design view for 1.
3. A validation query I ran showing the correct number of total order numbers
4. The query design view for 3
5. A pivot report I created in Excel as a redundant validation

1 should match the result of 3 & 5, but it isn't and in 2's query I have tried both unique values & unique records. What might I be doing wrong?

Thank you kindly

[aytee111]

Leave out distinct and unique values - this is a simple totals query. Decide which fields should be unique and group by them. It looks like you are trying to get a count by agent and period, group by those and count the orders. What is wrong with your first two screen prints?

[Miskondukt]

The first two screenshots count the duplicate order numbers, most particularly quotes since there are multiple quote versions, thus why I am trying to grab the distinct counts (there are also, quite seldom in comparison, more than one "order") of order numbers.

[aytee111]

Create a first query to select the distinct values. Still struggling to get exactly what you want to count. Would C34780 be counted as a 1? Are you trying to ignore quotes (that would be in the criteria)?

[orange]

What field(s) should be counted as unique---simple, plain English?

[Miskondukt]

What field(s) should be counted as unique---simple, plain English?

Or.der.Num.bers ... per period & agent.

[Miskondukt]

Create a first query to select the distinct values. Still struggling to get exactly what you want to count. Would C34780 be counted as a 1? Are you trying to ignore quotes (that would be in the criteria)?

I am trying to count unique order numbers per agent & period (quarter) because (a) the raw data lists multiple versions of quotes so multiple records and (b) NOT every order was originated from a quote thus why I need to include both quotes and orders, but the unique count of said order numbers.