[SOLVED] Count and filter with Query

[frankt68]

Hello!

I need help building a query that will count and filter my records.

I have a table like this, with several thousend records:

I would like to build a query that will count and filter out all records with same Item and more than one Group and will give result like this:

How should I do that?

 

[theDBguy]

Hi. Based on the small sample data, I'm not sure if there's enough info if you need more than this:

SELECT [Item], Count(*) As Total
FROM TableName
GROUP BY [Item]
HAVING Count(*)>1

Hope that helps...

 

[frankt68]

Hi. Based on the small sample data, I'm not sure if there's enough info if you need more than this:

SELECT [Item], Count(*) As Total
FROM TableName
GROUP BY [Item]
HAVING Count(*)>1

Hope that helps...

Thank you for your answer. Unfortunately, it does not provide the solution I wanted, because for each item it returns a total occurrence in a table greater than 1. I agree that the sample data provided was small, so I added some more.

All items that appear only once are not highlighted.
All items that appear more than once, and have the same group (eg. item 1A.02.AD.04.001) are highlighted with green.
All items that appear more than once, and do not have the same group are highlighted in yellow (eg. item 1A.02.AH.xx.001).

I would like the query to show only items that appear multiple times and do not have the same group (items highlighted in yellow) and the number of different groups for that item. E.g. for the table above, I would like the query to show a result only for item 1A.02.AH.xx.001, like this:

Although the item appears three times in the above table, the number of different groups is 2.

I hope this is more clear and understandable.

 

[namliam]

Not exactly sure if access supports this syntax, but something like so:

SELECT [Item], Count(distinct Group) As Total
FROM TableName
GROUP BY [Item]
HAVING Count(distinct Group)>1

Note base query stolen from @theDBguy

 

[vba_php]

namliam,

I tried what you posted but i got a syntax error. Honestly, that looks like a query that would be supported in PHP and .NET. But hey frank, what about 3 stacked queries?

QUERY 1

SELECT DISTINCT t.item, t.group FROM t;

QUERY 2

SELECT q.item, Count([item]) AS Group_Count FROM q GROUP BY q.item;

QUERY 3

SELECT q2.item, q2.Group_Count FROM q2 WHERE (((q2.Group_Count)>1));obviously you would change the table names and field names if they are different.

also frank, I don't think I've personally ever seen a question like this on the AWF forum. You might be making things a bit difficult on yourself by using un-normalized data (oh no, did I just use an expert-term!?). Everybody preaches "normalization". It might benefit you to look into it? Just a thought.....

 

[namliam]

@vba_php
Can actually combine Q2 and Q3 into:

SELECT q.item, Count([item]) AS Group_Count FROM q GROUP BY q.item having count([item]) > 1;

"my" syntax actually works in more bigger databases like Oracle, DB2 and SQL Server, and simular big crap databases that are too bloody huge to deal with.

 

[vba_php]

"my" syntax actually works in more bigger databases like Oracle, DB2 and SQL Server, and simular big crap databases that are too bloody huge to deal with.

which ones are exactly "crap"? I actually like Oracle and DB2 (I used AS400 from IBM many years ago when I did a small job for YellowBook), however like I've always said, I despise Microsoft. But whatever works better for Frank. I don't really care what he uses, as long *something* works. =)

 

[namliam]

Most databases upon themselves are fine and do their job dispite their individual idiosynchronies.

It is mostly the people that abuse the database structure and completely destroy the usablity of the data in a database. This goes for access databases designed like spreadsheets as much as the big brothers. Nice part of access is it will punish you hard at some point, Oracle or the big brothers actually have the power to "work around" such nasty solutions with brute force.

Solutions designed such ways make me cry for having chosen my line of work. On the other hand I recently designed a new DWH structure for some reports that the supplier had reports on as well. Their reports run 2 hour at minimum to retrieve the data, *CRY*.
I run a 5 minute proces to extract and reorder, restructure their data in our own database and run reports in 10 seconds
Which is what I love about my work

 

[vba_php]

Oracle or the big brothers actually have the power to "work around" such nasty solutions with brute force.

speaking of "brute force", I would assume that black hats have a heck of time nowadays cracking passwords that are already hashed and salted that are dumped from server DBs. Considering the hash functions nowadays spit out 64-bit length or longer garbage, I would assume the concept of a rainbow table won't work anymore when it comes to "bruteness". Hopefully this off-topic post is short enough that Colin won't notice.

I run a 5 minute proces to extract and reorder, restructure their data in our own database and run reports in 10 seconds
Which is what I love about my work

well good for you! and hey, if you have a follow up to this, send me a PM. I don't wanna hijack this thread like Richard and I always seem to do.

 

[frankt68]

But hey frank, what about 3 stacked queries?

QUERY 1

SELECT DISTINCT t.item, t.group FROM t;

QUERY 2

SELECT q.item, Count([item]) AS Group_Count FROM q GROUP BY q.item;

QUERY 3

SELECT q2.item, q2.Group_Count FROM q2 WHERE (((q2.Group_Count)>1));obviously you would change the table names and field names if they are different.

Thanks Adam, this is working.

 

[frankt68]

@vba_php
Can actually combine Q2 and Q3 into:

SELECT q.item, Count([item]) AS Group_Count FROM q GROUP BY q.item having count([item]) > 1;

Thanx namliam, your improvement also works.

 

[vba_php]

Thanks Adam, this is working.

..........................

 

[riz4829]

Actually, look at the syntax error that you got in namliam's sample. Do you have spaces or other characters that might not read as a full field name? It might be that the field name you replace for 'distinct group' needs to be put in brackets. It just worked for me.
compare syntax:

SELECT [Item], Count(distinct Group) As Total
FROM TableName
GROUP BY [Item]
HAVING Count(distinct Group)>1

SELECT [Item], Count([distinct Group]) As Total
FROM TableName
GROUP BY [Item]
HAVING Count([distinct Group])>1

 

[jdraw]

Access does not have a Count(Distinct fld)

Oracle/others

select count(distinct ReportsTo) as num_of_managers
from Employees

Access equivalent

select count(ReportsTo) as num_of_managers
from
(
    select distinct ReportsTo from Employees
)

 

[jdraw]

How did Frank mark this SOLVED? I've seen others ask this also.

 

[frankt68]

How did Frank mark this SOLVED? I've seen others ask this also.

It's easy. Go to "More Options" at the beginning of the thread (simply find the three "..." and click on them). Then click on "Edit thread", edit the thread title and then save it.

 

[jdraw]

Perfect. Thanks.