removing multiples of an order number

[mindbender]

I have data that looks like this


What I am trying to do is not count any record that has multiple orders.



The main portion of the order number is the first 6 characters ex: WC6939
If there is only one order in the group the number would be WC6939001
If there are multiple orders it would show like order number WC6969 above WC6969001, WC6969002, WC6969003

What I want to do is not return any records if they have multiples so basically using the data above WC6969001, 002 and 003 would not return

I tried using IIf(Count([MAIN_ORDER]=1),1,0) but that didn't work.

Any help would be appreciated

[June7]

The following will return only the 001 records:

SELECT * FROM table where Val(Mid([fieldname], 7))=1;

If you don't even want the 001 records for orders that have multiples:

SELECT * FROM table WHERE NOT Order IN (SELECT Order FROM table WHERE Val(Mid([fieldname],7)) > 1);

[mindbender]

Is there a way to do this using the builder from a column within a query?

[June7]

Don't really understand the question. Enter expressions in the Criteria row of query designer.

[mindbender]

Maybe I am asking it wrong. I tried entering it in the criteria for the field but it throws a subquery error.

This is what my query currently looks like

[June7]

My suggestions are not aggregating - there is no grouping.

[mindbender]

I figured it out this way

SELECT *
FROM (SELECT ORDER_DETAIL.REGION, Left([ORDER_DETAIL]![ORDER NUMBER],6) AS MAIN_ORDER, Count([MAIN_ORDER]) AS UNIQUE_ORDERS, ORDER_DETAIL.VOSS, (Day([ORDER DATE])-1)\7+1 AS Week
FROM ORDER_DETAIL INNER JOIN x_Employee_list ON ORDER_DETAIL.VOSS = x_Employee_list.VOSS_Name
GROUP BY ORDER_DETAIL.REGION, Left([ORDER_DETAIL]![ORDER NUMBER],6), ORDER_DETAIL.VOSS, (Day([ORDER DATE])-1)\7+1, ORDER_DETAIL.[NEW BUS TYPE], Year([ORDER DATE]), Month([ORDER DATE])
HAVING (((ORDER_DETAIL.REGION)="CENTRAL") AND ((ORDER_DETAIL.[NEW BUS TYPE])="XTI") AND ((Year([ORDER DATE]))=Year(Now())) AND ((Month([ORDER DATE]))=Month(Now())-1)))  AS [%$##@_Alias]
WHERE [UNIQUE_ORDERS]=1;