Counting UNIQUE or DISTINCT numbers in Access 1

[SDRichardC]

I am trying to count distinct or unique numbers from a field named Order. I have build the queries within queries but not sure how to make it work. I put the SQL code into the first query and it gave me an error such Circular Referece caused by OMOrder. What is wrong? Can someone help, please. Thanks in advance.

Here is the SQL from the three queries:

Main Query:
---------------------
SELECT VLDataTable.Date, VLDataTable.Shift, VLDataTable.Order, VLDataTable.[Engine Name]
FROM VLDataTable
WHERE (((VLDataTable.Date) Between [Forms]![frmPhoenixDetailQuery]![DateFrom] And [Forms]![frmPhoenixDetailQuery]![DateTo]) AND ((VLDataTable.Order)<>"Sahara" And (VLDataTable.Order)<>"IQ" And (VLDataTable.Order)<>"OTC" And (VLDataTable.Order)<>"N/A"))
ORDER BY VLDataTable.[Engine Name];
---------------------
Second Query:
SELECT OMOrder.Shift, OMOrder.Order, OMOrder.[Engine Name]
FROM OMOrder;
---------------------
Third Query:
---------------------
SELECT OMOrderQuery.Order
FROM OMOrderQuery
GROUP BY OMOrderQuery.Order;
---------------------
What do I need to do to count DISTINCT Order?

 

[pjm]

Not recommended to use reserved words as column names: "Date", "Order".

You could try enclosing them in square brackets [Date], [Order], but best to change the underlying table definition.

Access (I only have 2003 so don't know about newer versions) doesn't support "count distinct" so you have to do:

select count (*) from (select distinct [order] from OMOrderQuery)

HTH
pjm