complex SQL help needed

[hermanko]

Hi,

I have a query that is built using VB which takes user-selected items
from a Value List listbox. The SQL doesn't quite do what I want, but
close. below is a sample of the underlying table for this SQL (note
that duplication exists):

Table: Main Database
ID Fund Name Business Unit
1 Fund1 A
2 Fund2 A
3 Fund2 B
4 Fund3 A
5 Fund4 A
6 Fund4 C
7 Fund5 B
8 Fund6 B
9 Fund7 C
10 Fund8 C

In this example the listbox will contain "A;B;C" only for user to
select. Say A and B are selected, my query should extract the records
for business units A and B only, as well as ONLY the duplicate Funds
between A and B. So, the query should extract the following records
only:
ID2, ID3

However, the way i have my SQL written, it also selects ID5, because
ID5 and ID6 have the same fund, but leaves ID6 because it is Business
Unit "C". I do not want ID5 selected.

Here is my SQL:

SELECT [Main Database].*
FROM [Main Database]
WHERE ((([Main Database].[Fund Name]) In (SELECT [Fund Name] FROM [Main
Database] As Tmp GROUP BY [Fund Name] HAVING Count(*)>1 )) AND (([Main
Database].[Business Unit])="A" Or ([Main Database].[Business
Unit])="B"))
ORDER BY [Main Database].[Fund Name];

If anyone can please assist with any suggestions that would be GREATLY
appreciated.. thanks!!
Herman

 

[Tom Ellison]

Dear Herman:

First, I post your query altered for my reading preferences:

SELECT *
FROM [Main Database]
WHERE [Fund Name] In (
SELECT [Fund Name]
FROM [Main Database] As Tmp
GROUP BY [Fund Name] HAVING Count(*)>1)
AND ([Business Unit] = "A"
Or [Business Unit] = "B")
ORDER BY [Fund Name];

When you generate this from the listbox, it would be good to use:

SELECT *
FROM [Main Database]
WHERE [Fund Name] In (
SELECT [Fund Name]
FROM [Main Database] As Tmp
GROUP BY [Fund Name]
HAVING Count(*) > 1)
AND [Business Unit] = IN("A", "B")
ORDER BY [Fund Name];

This keeps things shorter and simpler. OK?

The subquery will return Fund2 and Fund4. You then also filter to Business
Units A and B. That would surely select Fund2/A, Fund2/B, and Fund4/A. You
don't want Fund4/A because there is really only one row for Fund4 within
Business Units A and B. Is that the core problem?

When you COUNT() the rows in the subquery, then you really only want to
COUNT() those within your selected set of Business Units, which you failed
to do. I thing that would be:

SELECT *
FROM [Main Database]
WHERE [Fund Name] In (
SELECT [Fund Name]
FROM [Main Database] As Tmp
WHERE [Business Unit] IN ("A", "B")
GROUP BY [Fund Name]
HAVING Count(*) > 1)
AND [Business Unit] = IN("A", "B")
ORDER BY [Fund Name];

As seen above, you must filter twice for this to work. Make sense now?

Finally, you have created an alias (Tmp) but not used it. I've never read a
specification, but it seems that, within a subquery with only a single table
in it, that table is the default table within the subquery. I think you're
alright on this, but I would add it for clarity anyway:

SELECT *
FROM [Main Database]
WHERE [Fund Name] In (
SELECT Tmp.[Fund Name]
FROM [Main Database] As Tmp
WHERE Tmp.[Business Unit] IN ("A", "B")
GROUP BY Tmp.[Fund Name]
HAVING Count(*) > 1)
AND [Business Unit] = IN("A", "B")
ORDER BY [Fund Name];

This is the form I would have expected. Please let me know if this works
out for you.

Tom Ellison

Hi,

I have a query that is built using VB which takes user-selected items
from a Value List listbox. The SQL doesn't quite do what I want, but
close. below is a sample of the underlying table for this SQL (note
that duplication exists):

Table: Main Database
ID Fund Name Business Unit
1 Fund1 A
2 Fund2 A
3 Fund2 B
4 Fund3 A
5 Fund4 A
6 Fund4 C
7 Fund5 B
8 Fund6 B
9 Fund7 C
10 Fund8 C

In this example the listbox will contain "A;B;C" only for user to
select. Say A and B are selected, my query should extract the records
for business units A and B only, as well as ONLY the duplicate Funds
between A and B. So, the query should extract the following records
only:
ID2, ID3

However, the way i have my SQL written, it also selects ID5, because
ID5 and ID6 have the same fund, but leaves ID6 because it is Business
Unit "C". I do not want ID5 selected.

Here is my SQL:

SELECT [Main Database].*
FROM [Main Database]
WHERE ((([Main Database].[Fund Name]) In (SELECT [Fund Name] FROM [Main
Database] As Tmp GROUP BY [Fund Name] HAVING Count(*)>1 )) AND (([Main
Database].[Business Unit])="A" Or ([Main Database].[Business
Unit])="B"))
ORDER BY [Main Database].[Fund Name];

If anyone can please assist with any suggestions that would be GREATLY
appreciated.. thanks!!
Herman

 

[hermanko]

Hi Tom,

That was a fantastic reply to my question. I learned quite a bit about
SQL language just from your post alone (i'm still a rookie with
Access). I was able to generate the correct results using your last SQL
statment (with the Tmp in the subquery). Now it seems to be working
perfectly.

Thanks a bunch!!
Herman