Access Query Help

[farazmeghani]

I have inherited an Access Database when I started working with Ericsson a few months back. I'm currently working on an invoicing report for the invoicing department. Below is a brief example of the kind of data I have:

Sales Order Number / Item Ordered / Status
908111 Coaxial Cable C
908111 Adapter C
908111 Transreceiver
908112 Coaxial Cable C
908112 Adapter C
908112 Transreceiver C
908113 Coaxial Cable
908113 Transreceiver
908113 Adapter

The above query demonstrates that Sales Order Number 908111 is not fully closed (or Partially delivered) since the 3rd item does not have a 'C' in the status column. Sales Order Number 908112 is fully closed (or 100% delivered) since all items have a status of 'C'. Sales Order Number 908113 has not started yet (or Not Delivered) since none of the items show a status of 'C'.

What I'm trying to do from here is that I'm trying to separate those sales order number that are 100% delivered and place them in a different Table. For sales order numbers that are partially delivered, place them in a different Table. For sales order numbers that are not delivered at all, place them in a different Table.

I've tried using different Filters but can't seem to do what I've explained above. Any help would be greatly appreciated.

[MMcCarthy]

SELECT TableName.[Sales Order Number], Count(TableName.[Item Ordered]) AS [CountOfItem Ordered], Count(TableName.Status) AS CountOfStatus
FROM TableName
GROUP BY TableName.[Sales Order Number];

This will give you a query showing the number of items ordered and the number of items complete. You should be able to use it in a join with the main table to create your different tables.