My first subquery......Help???

[MSherfey]

I'm trying to save some time and effort by combining some frequent queries into one. Basically, I need to see the customers which have purchased in FYxx and have not purchased in FYxx+1 AND FYxx+2. To do this I need to create 4 queries:

[FY06 TCC] gives me the customers from FYxx

SELECT [Main Invoice Data].[EndUser OCN:]
FROM [Main Invoice Data]
WHERE ((([Main Invoice Data].[Invoice Fiscal Year:])=2006))
GROUP BY [Main Invoice Data].[EndUser OCN:]
ORDER BY [Main Invoice Data].[EndUser OCN:];

[FY07 TCC] gives me the customers from FYxx+1

SELECT [Main Invoice Data].[EndUser OCN:]
FROM [Main Invoice Data]
WHERE ((([Main Invoice Data].[Invoice Fiscal Year:])=2007))
GROUP BY [Main Invoice Data].[EndUser OCN:]
ORDER BY [Main Invoice Data].[EndUser OCN:];

[FY08 TCC] gives me the customers from FYxx+2

SELECT [Main Invoice Data].[EndUser OCN:]
FROM [Main Invoice Data]
WHERE ((([Main Invoice Data].[Invoice Fiscal Year:])=2008))
GROUP BY [Main Invoice Data].[EndUser OCN:]
ORDER BY [Main Invoice Data].[EndUser OCN:];

The real work is done in Query 4 which finds all the customers in [FY06 TCC] that DO NOT exist in [FY07 TCC] AND [FY08 TCC]

SELECT [FY06 TCC].[EndUser OCN:]
FROM ([FY06 NCC] LEFT JOIN [FY07 TCC] ON [FY06 NCC].[EndUser OCN:] = [FY07 TCC].[EndUser OCN:]) LEFT JOIN [FY08 TCC] ON [FY06 NCC].[EndUser OCN:] = [FY08 TCC].[EndUser OCN:]
WHERE ((([FY07 TCC].[EndUser OCN:]) Is Null) AND (([FY08 TCC].[EndUser OCN:]) Is Null))
GROUP BY [FY06 TCC].[EndUser OCN:]
ORDER BY [FY06 TCC].[EndUser OCN:];

The problem I'm having is every example I find about subqueries is either an uncorrelated subquery, where I need to compare against a single result, or a correlated subquery, where I compare a single result against each line in the main query. I know the correlated sunquery is the way to go, but I can't get past the rrror messages telling me "At most one record can be returned by this subquery". I have even tried to keep it simple and just compare against [FY07 TCC].

SELECT [FY06 NCC].[EndUser OCN:]
FROM [FY06 NCC]
WHERE ((([FY06 NCC].[EndUser OCN:])<>(SELECT [Main Invoice Data].[EndUser OCN:]
FROM [Main Invoice Data]
WHERE ((([Main Invoice Data].[Invoice Fiscal Year:])=2007)))))
ORDER BY [FY06 NCC].[EndUser OCN:];

Any help would be greatly appreciated.

 

[dallr]

I am not sure what is this sites policy but u should not be double posting the same question. This can cause confusion and have. Different people helping u in different threads. It might be best to close off one and keep one active. I have replied in your first post. http://www.access-programmers.co.uk/forums/showthread.php?p=831713#post831713

 

[MSherfey]

It was never my intention to double-post. The first thread started as a simple question about the accuracy of my query and evolved into a discussion about it's efficiency. Also, this thread was posted before your very helpful reply about subqueries.

Since the first thread has changed from it's intent, I don't think it's a double-post. I would never selfishly or knowingly waste the forum's time like that.

 

[dallr]

No problem I trust that the solution works for you.

Dane