Count Distinct in Access

[georgerudy]

Hi,



I am relatively new to access and was trying to do a select count distinct values for a table. Reading a few sites on google, I know that access does not support this. This is for a school project and I am starting to pull my hair out. The database is for a library inventory system. I have 2 tables: Book_Copy and Inventory. Book_Copy has the columns (Copy_ID, Book_ID Checkout Date, Check In Date and Due Date, Status). The inventory table has columns: Inventory ID, Book _ID and Available Copies. I am trying to update the Inventory table, Available copies column based on the status (checked in) in the Book_Copy table. I have tried to write an update query trying different ways but all I end up getting is syntax errors. Any help would be appreciated. This is what I have tried:
UPDATE Inventory
SET Inventory.Available_Copies = (SELECT COUNT (*) AS Count from (Select DISTINCT BOOK_ID from BOOK_COPY where Book_Copy.Status_abbrv = 'CI');

[pkstormy]

You can't utilize an update query when you're doing a count. You'll need to do this in separate queries.

MSAccess does support using Distinct. Just not using the Distinct in the same query as the count. I will typically design a query which first shows Distinct or unique records and then design another query based upon this query which does a count (in the queries properties, you can select Unique Records or Unique Values to get distinction). That way I'm sure my initial query is showing only the unique type records which I want to show to do the count off of. For example, if you do a count type query off of a table and that table has duplicate records, you would then scew your counts. That's why you would want to design a query to first show only the distinct type records and then another query based off of this one to do the count (ie. a query based on another query instead of a table.)