Am I doing this right??? (1 Viewer)

MSherfey · Apr 10, 2009

I am curious on whether I am doing this rather simple query correctly. I need to find out how many customers have NOT made any purchases since a specific year. Sounds simple, right?

Queries 1&2 are simply a list of EndUser OCN's based on FY (Fiscal Year 07 and 08) InvoiceData.

Code:

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:];

Query 3 has the OCNs from FY06 left-joined to each of the above queries and finds those NOT in either list.....I think. I want to make sure it is finding IDs not in EITHER list, and not finding in one OR the other.

Code:

SELECT [FY06 NCC].[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:]
GROUP BY [FY06 NCC].[EndUser OCN:], [FY07 TCC].[EndUser OCN:], [FY08 TCC].[EndUser OCN:]
HAVING ((([FY07 TCC].[EndUser OCN:]) Is Null) AND (([FY08 TCC].[EndUser OCN:]) Is Null))
ORDER BY [FY06 NCC].[EndUser OCN:];

My next step is to get this down to one query. Baby steps, people

pbaldy · Apr 10, 2009

Maybe I'm misunderstanding, but based on your description, I see a query that groups on customer and returns the max sale date. With a HAVING clause on that max date, you can return the customers whose most recent sale was earlier than your desired date.

MSherfey · Apr 10, 2009

Jeez! That's a typo. It should read WHERE. Sorry!

Code:

SELECT [FY06 NCC].[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 NCC].[EndUser OCN:]
ORDER BY [FY06 NCC].[EndUser OCN:];

pbaldy · Apr 10, 2009

Did my solution work? Yours wasn't really a typo, but a WHERE clause and a HAVING clause operate a little differently. A WHERE clause is applied before the summing and grouping, the HAVING after.

MSherfey · Apr 10, 2009

I counted it as a typo. My original query contained the WHERE clause. I was doing some experimenting on the query and forgot to change it back when I posted.

The question still stands though. Is the query, as posted with the WHERE clause, returning the proper results? I want to make sure it returns all the accounts in 2006 which do NOT have any activity in 2007 or 2008 (per their individual queries).

Brianwarnock · Apr 10, 2009

I like Paul's approach, why not give it a try, then you can compare results from both approaches.

Brian

MSherfey · Apr 10, 2009

But which one returns the correct answer? I never thought there could be two different answers and they both could be correct. In this case, is that possible?

In my head I am thinking there is a definite number of customers who made a purchase in 2006 and DID NOT in 2007 nor in 2008.

Brianwarnock · Apr 10, 2009

So you have tried both and got different results?

Time for a tie breaker.

Find all who purchased in 2006.
join back to see who also purchased in 2007 or 2008.

the difference is those who did not purchase in 7 and 8.

Brian

MSherfey · Apr 10, 2009

So, to be clear, my first example works (ignore the HAVING, it should read WHERE). It does compare against both 2007 and 2008 and only returns the records which do not exist in either year. Correct?

MSAccessRookie · Apr 10, 2009

Would something like this work?

Create a Query to locate all Distinct [EndUser OCN:] from 2007 and 2008. These records can be ignored since they have purchase recently.
Second Join the 2006 Table to the result of the previous step, and take records that have a record in the 2006 Table, but not one in the 2007-2008 Combined Query.

qryFY0708: (2007-2008 Combined Query)

SELECT DISTINCT [FY07 TCC].[EndUser OCN:] FROM [FY07 TCC]
UNION
SELECT DISTINCT [FY08 TCC].[EndUser OCN:] FROM [FY08 TCC];

Main Query:

SELECT [FY06 NCC].[EndUser OCN:], [qryFY0708].[EndUser OCN]
FROM ([FY06 NCC] LEFT JOIN qryFY0708 ON [FY06 NCC].[EndUser OCN:] = [qryFY0708].[EndUser OCN:])
WHERE [qryFY0708].[EndUser OCN:] IS NULL;
GROUP BY [FY06 NCC].[EndUser OCN:]
ORDER BY [FY06 NCC].[EndUser OCN:];

Brianwarnock · Apr 10, 2009

I would say that your design concept is incorrect, you are sayimg that if somebody purchased in 2006 and has not purchased in 2007 or 2008 then that is a customer who has not purchased since 2006 - True, but what about a customer who last purchased in 2005, I know your data doesn't go back that far, but next year we will have moved on, and what about when your boss asks about who hasn't purchased since 2007. No look at Paul's solution I am sure that it is the correct approach.

Brian

boblarson · Apr 10, 2009

Brianwarnock said:
I would say that your design concept is incorrect, you are sayimg that if somebody purchased in 2006 and has not purchased in 2007 or 2008 then that is a customer who has not purchased since 2006 - True, but what about a customer who last purchased in 2005, I know your data doesn't go back that far, but next year we will have moved on, and what about when your boss asks about who hasn't purchased since 2007. No look at Paul's solution I am sure that it is the correct approach.

Brian

If it helps, I'll add my "affirmative" to what Brian (and Paul) has said.

boblarson · Apr 10, 2009

boblarson said:
If it helps, ...

And if not, oh well, as my wife would say, "Whatever."

gemma-the-husky · Apr 10, 2009

one way of finding a negative is

a) find customers who HAVE purchased since whenever

join customers to sales, salesdate >= checkdate
select customers, distinct values only

b) then do an unmatched query using this query, and the customers table to find those with no sales,

----------
not sure if this is already an answer, as I only skimmed thep ostings.

MSherfey · Apr 10, 2009

Brianwarnock said:
I would say that your design concept is incorrect, you are sayimg that if somebody purchased in 2006 and has not purchased in 2007 or 2008 then that is a customer who has not purchased since 2006 - True, but what about a customer who last purchased in 2005, I know your data doesn't go back that far, but next year we will have moved on, and what about when your boss asks about who hasn't purchased since 2007. No look at Paul's solution I am sure that it is the correct approach.

Brian

This is correct, however there is more which I haven't shared. I wasn't being secretive, I just thought it would cause the thread to go off topic.

I run this query for each year for the two following years. For example, I have a query for each year which lists the customers which have bought for that specific year. I label the query by the year (FY06 Customers, FY07 Customers, etc). Then I try to find all the customers from a specific year which have not purchased in subsequent years, and I do this for each year. "FY00 not in FY01-02", "FY01 not in FY02-03", etc....

I was just confirming the data returned in one of those queries was correct. If the logic and SQL was accurate, then I would apply it to my other queries and all is well. I think it is, but I'm not sure.

Once I get those queries accurate, I use them to pull additional data. I'll join the "FY06 not in FY07-08" to the "Main Invoice Data" table and pull out their specifics like customer size, geo, industry, etc. This way I can see the types of customers who make a purchase one year, but not for the next two years.

Does this help or am I going about it all wrong?

MSherfey · Apr 11, 2009

MSAccessRookie said:
Would something like this work?

qryFY0708: (2007-2008 Combined Query)

SELECT DISTINCT [FY07 TCC].[EndUser OCN:] FROM [FY07 TCC]
UNION
SELECT DISTINCT [FY08 TCC].[EndUser OCN:] FROM [FY08 TCC];

Are there duplicates in a UNION query? I just ran this, and it doesn't look like there are any dups. What happens if there are customer hits in both parts of the UNION? Does the UNION remove the duplicate? Also, what if I wanted to show what FY year they came from?

I would have never thought of this query. I made one, using your code above, and it doesn't let you go into the Design View. I know that's basic, but I use it to help visualize what is going on.

This is pretty neat! Thanks!

Rabbie · Apr 11, 2009

Union queries don't let you go into the design grid - just the SQL view.

Read up about them in Access help

dallr · Apr 11, 2009

Find All Customers who didn't Purchase the Following Yr

There is no need to break up your customers into separate years and then try and pull them back together. You can simply get your results based upon the tables in question using one sub query.

If I understand your requirements correctly you want a listing of all customers who have purchased in one year but have not purchased the following year. So they can purchase more than one times in the one year but if they have not purchased the following year then they should not be included in your query.

Using this simplified structure just for this purpose (Customers, Orders) we can create our query. Note that this query returns an additional field which shows the date that the customer made the purchase the following year. If there is on date that means that the customer did not make a purchase the following year. As such, you can filter this field by "Is Null" or "Is Not Null" to show customers who purchased the following year and customers who did not purchase the following year.

Code:

SELECT Customers.CustName
   , Customers.CustomerID
   , Orders.SalesTotal
   , Orders.DatePurchased
   , (SELECT min(O.DatePurchased) 
      FROM [Orders] O 
      WHERE O.CustomerID = Orders.CustomerID 
         AND O.OrdID > Orders.OrdID 
         AND Year(O.DatePurchased) 
             = Year(DateAdd("yyyy",1,Orders.[datepurchased])) ) AS FollYearPurDate
    , Orders.OrdID
FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID;

I have also uploaded a sample db if you are interested.

PS: I change the title of your post just to make it more descriptive for future readers.

Dallr

MSherfey · Apr 11, 2009

This is exactly what I was needing to get done. In fact, I just posted that exact question here: http://www.access-programmers.co.uk/forums/showthread.php?t=169897

I'm getting errors on the query and couldn't figure out how to fix it. I'll take a look at your query and sample database to try and figure it out.

THANKS!!

MSherfey · Apr 12, 2009

Dallr,

What is 'O' in your select statement:

(SELECT min(O.DatePurchased)
FROM [Orders] O
WHERE O.CustomerID = Orders.CustomerID
AND O.OrdID > Orders.OrdID...

Is that the temp holder referencing back to the main query? That is the part that loses me in the text I've been finding online. If so, I don't understand "...FROM [Orders] O". The other Os seem to point back to specific fields in the Orders table. That O is by itself. In the examples I've been finding online it looks like it is assuming the "AS" command (...FROM [Orders] AS O). Am I reading that right?

I think I'm grasping the concept, but it's tenuous at best right now

Am I doing this right??? (1 Viewer)

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Retired

Registered User.

Retired

Registered User.

AWF VIP

Retired

Smeghead

Smeghead

Super Moderator

Registered User.

Registered User.

Super Moderator

AWF VIP

Attachments

Registered User.

Registered User.

Similar threads

Users who are viewing this thread