I'm stuck!!

[MSherfey]

Please help....if it's even possible

I need to know what customers had maintenance but no longer do.

I have my query working correct but I don't know the next step. Currently I have a query which searches for a specific product, tells me the purchase date as well as when their maintenance ends. I also have an expression which looks at the maintenance end date and tells me whether they are current on their maintenance or not. Pretty simple. Here's the rub.

There are many many customers who have made purchases over the years. The query may return 5 or more lines for each customer with a 'No' in the 'Current' field and then one 'Yes' for the current year. Some customers have all 'No' for their purchases since they decided not to renew their maintenance. I can't just look at the current purchase year since some customers purchased maintenance many years in advance and may still be current (or not).

Customer     PurchaseDate          MaintenanceDate          Current
ABC                2005                        2006                      No
ABC                 2006                        2009                     Yes
123                 2007                         2008                     No
123                 2008                        2009                      No
WXY               2008                          2009                     Yes
456                2006                          2007                      No

I only want the second 123 customer. The other customers are either too old (expired) or have maintenance (current).

What is the easiest or best way to find out who had maintenance but currently does not? Or another way, how do I remove the old purchases (expired) and only look at the current ones to see if they have maintenance. The problem is I need to know if they had maintenance earlier to compare if it still 'current'.

Manually I would remove all the expired records (those over 1 year old) then look to see if their maintenance expired this year. If it did, did they renew? If not, put it in a table. If so, go to the next records and repeat.

Does that make sense?

 

[georgedwilkinson]

I'm getting conflicting information. Can you just post the SQL for the query along with the results and tell us which rows aren't supposed to be there (or are)?

I'm imagining something like:

select m.allmystuff
from mytable m inner join mytable o
on m.Customer = o.Customer
and m.PurchaseDate > o.PurchaseDate
where
o.Current = 'Yes'
and
m.Current = 'No'

There may be additional complexity because of customers becoming current/uncurrent but this is the general gist, I think.

Additionally, bad idea putting the results into another table...but that's for a different discussion. Let's figure out your query problem first.

 

[MSherfey]

I'll try this right away. Thank you.

I haven't put the results in a table yet. I only listed the results of the query. I don't like using query results to build a table in order to use them in another query. But sometimes I'm forced to since I don't have a lot of experience in Access yet.

Thanks again for the tips. I'll let you know how it works.

 

[MSherfey]

Here's the query:

SELECT IIf(DatePart('m',[Invoice Data].[Invoice Date:])>10,DatePart('yyyy',[Invoice Data].[Invoice Date:])+1,DatePart('yyyy',[Invoice Data].[Invoice Date:])) AS FY, [Invoice Data].[Geo1:], [Invoice Data].[Invoice Quarter:], [Invoice Data].[Maint End:], IIf([Invoice Data]![Maint End:]>Date(),"Yes","No") AS [Current], [Invoice Data].[Customer Size Segment:], [Invoice Data].[EndUser OCN:], [Invoice Data].[EndUser Customer Name:], [Item Number Info].BLUE_DESC_4, [Invoice Data].Quantity, Sum([Invoice Data].[Invoice Amt USD]) AS Invoice
FROM [Item Number Info] INNER JOIN [Invoice Data] ON [Item Number Info].ITEM_NUM = [Invoice Data].[Order Item Num:]
WHERE ((([Invoice Data].[Product Level 4:]) Like "*Monitor*"))
GROUP BY IIf(DatePart('m',[Invoice Data].[Invoice Date:])>10,DatePart('yyyy',[Invoice Data].[Invoice Date:])+1,DatePart('yyyy',[Invoice Data].[Invoice Date:])), [Invoice Data].[Geo1:], [Invoice Data].[Invoice Quarter:], [Invoice Data].[Maint End:], [Invoice Data].[Customer Size Segment:], [Invoice Data].[EndUser OCN:], [Invoice Data].[EndUser Customer Name:], [Item Number Info].BLUE_DESC_4, [SRM Invoice Data].Quantity
HAVING (((Sum([Invoice Data].[Invoice Amt USD]))>0))
ORDER BY [Invoice Data].[EndUser OCN:];

And I've attached a sample of the data. In this example I only care about customer8. The other customers are either current in their maintenance or they expired many years ago and are 'lost'.

Any help would be great.

 

[georgedwilkinson]

You'll need to explain this further. The Customer "Customer8" has NEVER had maintenance before, according to your spread sheet. I'm not sure how that meets the criteria of your scenario. Why do you believe Customer8 should or should not be in your result set and the other customers should or should not?

 

[MSherfey]

All the customers on this sheet had maintenance at the beginning of their purchase life cycle. The 'Current' field only reports if their maintenance is current. The customer will renew their maintenance every year (perfect conditions). Last year's purchase will change the status to 'No' and this year's purchase will have a status of 'Yes'.

Customer8 shows purchases for both Q3 and Q4 of 2008. Had they renewed this year it would also show a purchase for Q3 and Q4 of 2009 and the status on those new purchases would read 'Yes'. Since there isn't a 'Yes' in the 'Current' column AND the purchases are within a year I need to know about them. There's still a chance to save them

Does that make sense?

 

[georgedwilkinson]

So, you're only interested in the most recent year(s)?

 

[MSherfey]

Also, I found a work around for the problem but it is VERY VERY sloppy.

I created a second query and had it find all the customer numbers where there was 'Yes' in the Maintenance field. I then created a third query which showed all the accounts from query1 which did not match the customer id from query2; in short, all the 'No' accounts which do not have a 'yes' associated as well.

This gives me ALL the customers who have not renewed their maintenance. While it covers all the years, it is a simple filter at this point to remove anything over 1 yr old.

It works but it takes three separate queries to work and I have to believe there is a more elegant solution.

 

[MSherfey]

So, you're only interested in the most recent year(s)?

Yes. If the customer has remained out of maintenance for over a year it is very difficult to get them back. Only the recent customers have a chance I think.

 

[georgedwilkinson]

Will something like this work?
(Consider this pseudocode...you test & troubleshoot)

select allthestuffyouwant
from yourtable t
where
t.Maintenance = 'No'
and
t.[Maint End:] > Date() - 365
and
t.Customer not in
(select Customer from yourtable where [Maint End:] > Date() - 365 and Maintenance = 'Yes')

 

[MSherfey]

I'm troubleshooting the code. It looks like it makes sense but I have one question.


I didn't know 'not in' was valid sql (I know soooo little). Did you mean 'not in' literally or is there a function/command I need instead?

SELECT [Monitor Account Status].FY, [Monitor Account Status].[Geo1:], [Monitor Account Status].[Invoice Quarter:], [Monitor Account Status].[Invoice Date:], [Monitor Account Status].[Maint End:], [Monitor  Account Status].[Maint Length], [Monitor Account Status].Current, [Monitor  Account Status].[Customer Size Segment:], [Monitor Account Status].[Industry:], [Monitor Account Status].[EndUser OCN:], [Monitor  Account Status].[EndUser Customer Name:], [Monitor Account Status].BLUE_DESC_4, [Monitor Account Status].Quantity, [Monitor Account Status].Invoice
FROM [Monitor Account Status]
where
[Monitor Account Status].Current = 'No'
and
[Monitor Account Status].[EndUser OCN:] not in
(SELECT [Monitor Account Status].[EndUser OCN:]
FROM [Monitor Account Status]
WHERE ((([Monitor Account Status].Current)="Yes")));

 

[georgedwilkinson]

"NOT IN" literally is valid SQL. Also, "IN" is valid SQL. It expects a result set next as its "argument" (either a select statement or a list). You cannot do this with the visual editor (QBE).

 

[MSherfey]

I'm getting a "Query is too complex" error on the above code. I think I need to do the second part with a sub-query, but that is still pretty confusing to me. I'm going to try a few things to troubleshoot.

 

[georgedwilkinson]

It doesn't look complex. Check your syntax, maybe.

Also, I wanted to point out that "not in" is moderately inefficient from an execution time standpoint. See if it works for you before looking for an alternative, though. I've found that it works just fine for me in most circumstances.

 

[MSherfey]

Thanks. It was a syntax error on my part even though I can't remember what it was right now. I went ahead and tried a different route. Using your idea of "Not In" I tried "NOT EXISTS" in the sub-query. It almost worked accept it would take FOREVER returning any results. I then decided to do a different sub-query. Since the others were already built it was a matter of cut/pasting them into one.

In the end I got completely away from a base query all together. The one query goes against the main data table for everything from various sub-queries. And it is about the same execution time as the original three queries; not bad.

I haven't gone through and formatted the code to make it easier to read yet since Access always seems to lose my formatting anyway. But here it is in case you're interested. Please let me know if you see a more efficient way to do this. I was thinking, instead of running the core query multiple times, maybe I can create a temp table which disappears after the query is finished. It would process a little quicker and be easier to decode/change the SQL if needed. Yes?

SELECT Main.FY, Main.[Geo1:], Main.[Invoice Quarter:], Main.[Invoice Date:], Main.[Maint End:], Main.[Maint Length], Main.Current, Main.[Customer Size Segment:], Main.[Industry:], Main.[EndUser OCN:], Main.[EndUser Customer Name:], Main.BLUE_DESC_4, Sum(Main.Quantity) AS SumOfQuantity, Sum(Main.Invoice) AS SumOfInvoiceFROM (SELECT IIf(DatePart('m',[Invoice Data].[Invoice Date:])>10,DatePart('yyyy',[Invoice Data].[Invoice Date:])+1,DatePart('yyyy',[Invoice Data].[Invoice Date:])) AS FY, [Invoice Data].[Geo1:], [Invoice Data].[Invoice Quarter:], [Invoice Data].[Invoice Date:], [Invoice Data].[Maint End:], DateDiff("d",[Invoice Date:],[Maint End:]) AS [Maint Length], IIf([Invoice Data]![Maint End:]>Date(),"Yes","No") AS [Current], [Invoice Data].[Customer Size Segment:], [Invoice Data].[Industry:], [Invoice Data].[EndUser OCN:], [Invoice Data].[EndUser Customer Name:], [Item Number Info].BLUE_DESC_4, [Invoice Data].Quantity, Sum([Invoice Data].[Invoice Amt USD]) AS Invoice FROM [Item Number Info] INNER JOIN [Invoice Data] ON [Item Number Info].ITEM_NUM=[Invoice Data].[Order Item Num:] WHERE ((([Invoice Data].[Product Level 4:]) Like "*Monitor*")) GROUP BY IIf(DatePart('m',[Invoice Data].[Invoice Date:])>10,DatePart('yyyy',[Invoice Data].[Invoice Date:])+1,DatePart('yyyy',[Invoice Data].[Invoice Date:])), [Invoice Data].[Geo1:], [Invoice Data].[Invoice Quarter:], [Invoice Data].[Invoice Date:], [Invoice Data].[Maint End:], [Invoice Data].[Customer Size Segment:], [Invoice Data].[Industry:], [Invoice Data].[EndUser OCN:], [Invoice Data].[EndUser Customer Name:], [Item Number Info].BLUE_DESC_4, [Invoice Data].Quantity HAVING (((Sum([Invoice Data].[Invoice Amt USD]))>0)))  AS Main LEFT JOIN (SELECT Covered.[EndUser OCN:] FROM (SELECT IIf(DatePart('m',[Invoice Data].[Invoice Date:])>10,DatePart('yyyy',[Invoice Data].[Invoice Date:])+1,DatePart('yyyy',[Invoice Data].[Invoice Date:])) AS FY, [Invoice Data].[Geo1:], [Invoice Data].[Invoice Quarter:], [Invoice Data].[Invoice Date:], [Invoice Data].[Maint End:], DateDiff("d",[Invoice Date:],[Maint End:]) AS [Maint Length], IIf([Invoice Data]![Maint End:]>Date(),"Yes","No") AS [Current], [Invoice Data].[Customer Size Segment:], [Invoice Data].[Industry:], [Invoice Data].[EndUser OCN:], [Invoice Data].[EndUser Customer Name:], [Item Number Info].BLUE_DESC_4, [Invoice Data].Quantity, Sum([Invoice Data].[Invoice Amt USD]) AS Invoice FROM [Item Number Info] INNER JOIN [Invoice Data] ON [Item Number Info].ITEM_NUM=[Invoice Data].[Order Item Num:] WHERE ((([Invoice Data].[Product Level 4:]) Like "*Monitor*")) GROUP BY IIf(DatePart('m',[Invoice Data].[Invoice Date:])>10,DatePart('yyyy',[Invoice Data].[Invoice Date:])+1,DatePart('yyyy',[Invoice Data].[Invoice Date:])), [Invoice Data].[Geo1:], [Invoice Data].[Invoice Quarter:], [Invoice Data].[Invoice Date:], [Invoice Data].[Maint End:], [Invoice Data].[Customer Size Segment:], [Invoice Data].[Industry:], [Invoice Data].[EndUser OCN:], [Invoice Data].[EndUser Customer Name:], [Item Number Info].BLUE_DESC_4, [Invoice Data].Quantity HAVING (((Sum([Invoice Data].[Invoice Amt USD]))>0)))  AS Covered WHERE Covered.Current="Yes")  AS Temp ON Main.[EndUser OCN:]=Temp.[EndUser OCN:]GROUP BY Main.FY, Main.[Geo1:], Main.[Invoice Quarter:], Main.[Invoice Date:], Main.[Maint End:], Main.[Maint Length], Main.Current, Main.[Customer Size Segment:], Main.[Industry:], Main.[EndUser OCN:], Main.[EndUser Customer Name:], Main.BLUE_DESC_4, Temp.[EndUser OCN:]HAVING (((Temp.[EndUser OCN:]) Is Null));

Thanks again for all your help!!!!

 

[MSherfey]

I think the code was too long for the message. Here it is in .doc format.

Thank you again!!
​