Counting unique datasets with multiple test fields

[datas_brother]

HI

although I've been working with MS Access for years, I've only just found this forum. Lots of great info in here.

This is my first post and yes I have used the search function but couldn't find an answer that worked. What I need to do is count unique sets of data. I have a table with about 15 columns and the definition of a unique data set is when 3 fields in one row (date, item, and Inspector) are the same as in other rows or exist only once. If that's not complicated enough, the "Item" is an item number with multiple formats : 6-digit number followed by either nothing or an alpha-numeric code i.e. "123456" or "234567 x1x y2y" For the query, only the first 6 digits should be considered i. e. "123456 xxx yyy" is the same as "123456 aaa bbb" and should be counted only once. Here's an example of what I'm after:

Record . Date . . . . . Item . . . . . . . . .Inspector . Quantity
1. . . . . . 01/01/10 . 123456 xxx yyy . John Smith . 100
2. . . . . . 01/01/10 . 234567. . . . . . . Jane Doe . . 200
3. . . . . . 02/01/10 . 234567. . . . . . . Jane Doe . . 300
4. . . . . . 02/01/10 . 123456 xxx yyy . John Smith . 200
5. . . . . . 02/01/10 . 123456 xxx zzz . .John Smith . 100
6. . . . . . 02/01/10 . 345678 . . . . . . . Frank Zappa 200
7. . . . . . 02/01/10 . 123456 xxx yyy . .Jane Doe . . 300

rows 1, 2, 3, 4, 6 and 7 would each be counted row 5 would not be counted because the first 6 digits of the item number, date and inspector are all the same as in row 4. In row 7 the item no. and date are the same as rows 4 and 5 but the inspector is different.

I've tried the following but it still counts row 5 with the others:

SELECT DISTINCT InspectionTbl.Date, Count(InspectionTbl.Item) AS Numer_of_items, InspectionTbl.Inspector AS [item count]
FROM InspectionTbl
GROUP BY InspectionTbl.Date, InspectionTbl.Inspector;

Any ideas?

Thanks
DB

 

[namliam]

Do this in a 2 step approach,

1)
Make a query to make the entries unique/distinct.
Select distinctrow ... from yourtable
Save it as qryYourTableDistinct (or some other name you prefer)

2)
Make another query, with qryYourTableDistinct as the source and count all you need to count.

Good luck

 

[datas_brother]

Do this in a 2 step approach,

1)
Make a query to make the entries unique/distinct.
Select distinctrow ... from yourtable
Save it as qryYourTableDistinct (or some other name you prefer)

...snip

Thanks for the suggestion but this is exactly the problem I'm having. I can't get the query to distinguish unique rows because I can't get the query to compare multiple fields.

Cheers
DB

 

[namliam]

I can't get the query to distinguish unique rows because I can't get the query to compare multiple fields.

If you do as I suggested:
Select DISTINCTROW ... from yourTable

The distinct row will make the complete row be unique, guarantee...

 

[datas_brother]

If you do as I suggested:
Select DISTINCTROW ... from yourTable

The distinct row will make the complete row be unique, guarantee...

Not sure what I did wrong the first time but after completely re-writing the code it works perfectly. Thanks for your help.

Cheers
DB