SQL help

[hermanko]

Hi, I need help setting up a query.

I have a table that shows the following data, for example (only showing
the critical fields):

Table: All Funds
ID Fund Name Proxy
1 Fund1 A
2 Fund1 A
3 Fund2 B
4 Fund2 B
5 Fund3 C
6 Fund3 D
etc....

The nature of my database is such that we WILL have duplicate Fund
Names...tho they are uniquely identified with the ID field. The queries
i need to set up will extract two sets from this table, one that will
extract the duplicate Fund Names that ALSO have the same Proxy, i.e.
1,2,3,4. The other query should extract the duplicate funds with
different Proxys, i.e. 5 and 6.

I have the first query set up like so (there are several other fields
involved but only the above mentioned are critical to me):

Query: SameProxy
SELECT [All Funds].ID, [All Funds].[Fund Name], [All Funds].Proxy, [All
Funds].[Business Unit], [All Funds].[Historical Data Range], [All
Funds].Beta, [All Funds].[R-Squared], [All Funds].[Mean Tracking
Error], [All Funds].Comments, [All Funds].Date, [All Funds].[Yes/No]
FROM [All Funds]
WHERE ((([All Funds].[Fund Name]) In (SELECT [Fund Name] FROM [All
Funds] As Tmp GROUP BY [Fund Name],[Proxy] HAVING Count(*)>1 And
[Proxy] = [All Funds].[Proxy])))
ORDER BY [All Funds].[Fund Name], [All Funds].Date;

Query: DiffProxy
???

Is there a simple way to get my 2nd required query? Like taking all
remaining records from All Funds that were NOT selected by SameProxy
query? Also, i tried doing this with a totals query but found later
that i needed to update it in a form with checkboxes and it wouldn't
allow me to. So my thoughts are that I need SQL that isn't a totals
query?

Any help would be greatly appreciated!
Herman

 

[Jerry Whittle]

If the query is working like you wish, you should be able to get the opposite
results by changing the IN to NOT IN just as you suggested.

 

[hermanko]

Well..that worked pretty easily lol.

Thanks!