datas_brother
New member
- Local time
- Today, 08:01
- Joined
- Jul 30, 2010
- Messages
- 8
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:
Any ideas?
Thanks
DB
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:
Code:
SELECT DISTINCT InspectionTbl.Date, Count(InspectionTbl.Item) AS Numer_of_items, InspectionTbl.Inspector AS [item count]
FROM InspectionTbl
GROUP BY InspectionTbl.Date, InspectionTbl.Inspector;
Thanks
DB