H
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
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