Am I doing this right??? (1 Viewer)
- Thread starter MSherfey
- Start date
Got it. Thanks!
Logically, could I change the 'min' to a 'count'? It looks like it would make a table with their IDs and the count of purchases in that time frame. I would then look for counts greater than 1.
I'm starting to get a small understanding of your query so I thought I'd try it a few different ways to make sure I got it.
Thanks again, this is very simple query is really helping me grasp what I think is a pretty advanced topic.
Logically, could I change the 'min' to a 'count'? It looks like it would make a table with their IDs and the count of purchases in that time frame. I would then look for counts greater than 1.
I'm starting to get a small understanding of your query so I thought I'd try it a few different ways to make sure I got it.
Thanks again, this is very simple query is really helping me grasp what I think is a pretty advanced topic.
I thought I had a grasp of what was happening in the subquery, but I can't seem to get this to work. When I run the query, it doesn't return any errors but instead it just runs forever. I let it run for about 5 minutes before I closed it. Traditionally, the two separate queries and the third un-matched query would take about 30 seconds to complete.
Any ideas?
Any ideas?
Code:
SELECT DISTINCT [Invoice Data].[EndUser OCN:], [Invoice Data].[Invoice Quarter:], (SELECT DISTINCT [P2].[Invoice Quarter:]
FROM [Invoice Data] AS P2
WHERE [P2].[EndUser OCN:]=[Invoice Data].[EndUser OCN:] AND [P2].[Invoice quarter:]="2009 Q2") AS 2009_Cust
FROM [Invoice Data]
WHERE ((([Invoice Data].[Invoice Quarter:])="2008 Q2"));
MSAccessRookie
AWF VIP
- Local time
- Today, 10:52
- Joined
- May 2, 2008
- Messages
- 3,428
It does not look like you have joined the table [Invoice Data] to the Table [P2] anywhere. This will result in what is referred to as a Cartesian Join, which requires comparisons between every row of the table [Invoice Data] to every other row of the table [Invoice Data]. You can imagine that the more rows that you have in the table [Invoice Data], the longer it will take. You will need to create a Join between them on a unique column to prevent this from happening.
Last edited:
You are right. Thank you.
I had to add the subquery to the LEFT JOIN section. Man, that took a bit of figuring out
All in all, this is working. Thank you to everyone who helped. (I clicked on the scales next to your names.)
This thread started out with an accuracy question and you helped me answer that as well as streamline the process. Now I have one query and not three. Imagine that over 5 years of data and you saved me a TON of work!
Thanks again!
I had to add the subquery to the LEFT JOIN section. Man, that took a bit of figuring out
All in all, this is working. Thank you to everyone who helped. (I clicked on the scales next to your names.)
Code:
SELECT DISTINCT [Invoice Data].[EndUser OCN:]
FROM [Invoice Data] LEFT JOIN (SELECT DISTINCT [Invoice Data].[EndUser OCN:] FROM [Invoice Data] WHERE ((([Invoice Data].[Invoice Quarter:])="2009 Q2") )) AS P2 ON [Invoice Data].[EndUser OCN:] = P2.[EndUser OCN:]
WHERE ((([Invoice Data].[Invoice Quarter:])="2008 Q2") AND ((P2.[EndUser OCN:]) Is Null));Thanks again!
