Re: Help with counting only one time

Ah: so you don't just want to know how many of each row there are.
You want to exclude all but the first row for each order?

You will need a primary key in your table to achieve this. In the following example, I will assume there is a primary key field named ID, and the table is called Table1.

SELECT Table1.*
FROM Table1
WHERE ID = (SELECT Min(ID) AS MinOfID
FROM Table1 AS Dupe
WHERE Dupe.[Order] = Table1.[Order]);

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cam" <Cam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:07894683-D7CE-453B-999C-A05215FBC31E@xxxxxxxxxxxxxxxx
Allen,

Thanks for the suggestion, but I am clueless on what to do in the query even
after looking at the link you suggested. I thought maybe if I put some sample
data, then you might help me better with what I am trying to achieve.

Table with the following data:
Part Order Oper WC Date
AAA 123 1000 8951 1/2/08
AAA 123 2000 8951 1/3/08
AAA 123 3000 8951 1/7/08
AAA 123 4000 8951 1/3/08
ABC 205 5000 8951 1/4/08
ABC 205 6000 8951 1/8/08
ABZ 314 1000 8951 1/3/08
AAH 380 2000 8951 1/3/08

result of query data I like to achieve:
Part Order Oper WC Date
AAA 123 1000 8951 1/2/08
ABC 205 5000 8951 1/4/08
ABZ 314 1000 8951 1/3/08
AAH 380 2000 8951 1/3/08

Records that are excluded:
AAA 123 2000 8951 1/3/08
AAA 123 3000 8951 1/7/08
AAA 123 4000 8951 1/3/08
ABC 205 6000 8951 1/8/08

For order with multiple oper, it only returns one order with the lowest oper
number. Thank again.



"Allen Browne" wrote:

Use a subquery to get the unique records, and then count the number of
records in the subquery.

This example works in the Northwind sample database:
SELECT Count("*") AS HowMany
FROM (SELECT DISTINCT OrderID FROM [Order Details]) AS MySource;

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

If you want a function to return unique counts, see ECount() here:
Extended DCount()
at:
http://allenbrowne.com/ser-66.html

"Cam" <Cam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B076454D-719D-416C-AF91-0ED58A592169@xxxxxxxxxxxxxxxx
> Hi,
>
> I am trying to run a query to count number of order received in the > week
> (date range). The problem is some order has more than 1 record with
> different
> operation, so if the order 1100 has 10 operations, then it is counting > 10
> instead of 1.
>
> Is there a way in query design to specify that the order is a unique > value
> only? Or how should I go about solving this? Thanks

.

Relevant Pages

  • Re: Displaying row no/Record Counting
    ... EnteredOn Date/Time when the record was added. ... You create a query that contains Table1. ... On each row of your query, you need to count the number of records in Table1 ... In> order to generate an alphabetical listing of these people,> I have to analyze the report in Excel and sort the> spreadsheet based on the client's name. ...
    (microsoft.public.access.queries)
  • Re: How to add to a new field based on information in existing field?
    ... a query to join the two tables. ... For example, Table1 includes fields TestID ... Table2 includes fields Description and DescriptionFr. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Non-updateable query issue
    ... You have an unnormalized table (of course, as it came from a flat spreadsheet instead of a relational source), and is it has 100 columns. ... joined to the master, showing all the records, the query is not updateable. ... > Say Fred deletes record 79 from Table1, and then Betty changes the> phone ... > Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: Non-updateable query issue
    ... Microsoft Access MVP ... When putting together that query, ... "Duane Hookom" wrote: ... Say Fred deletes record 79 from Table1, ...
    (microsoft.public.access.queries)
  • Re: Non-updateable query issue
    ... When putting together that query, ... spreadsheet instead of a relational source), and is it has 100 columns. ... Allen Browne - Microsoft MVP. ... Say Fred deletes record 79 from Table1, ...
    (microsoft.public.access.queries)