subquery expression

[jdraw]

I think the issue is you know exactly what you want to do, and you know the table structure. I'm going by exactly what you're writing.

ClientId 660 has Treatments Order from between 3 and 6 months ago; and has future Orders for Traetments -- that's why you're not showing records.
But I was going on your statement

the email that would be sent to client 660 is none. the client has not had a treatment in the period between 3 and 6 months ago.

If you can find examples of a clientId who
a) has ordered treatments in last 3-6months and has no future order for the treatments condition, and
b) has ordered treatments in last 3-6months and has a future order for the treatments condition, then I have some sample to work with.

I feel we are just not connecting with the words. I'm trying to help but feel I only have some of the info.

 

[shutzy]

i totally understand where you are coming from, at least we both know the same thing now. i have found a client ClientDetailsID = 721. for this to work you must delete the items 778-774-123 from date 02 june 12. also i have just generated a query to get these results. the following sql will make things easier to explain.

SELECT tblOrders.ClientDetailsID, tblOrders.OrderDate, tblOrdersItems.ItemsID
FROM tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID
GROUP BY tblOrders.ClientDetailsID, tblOrders.OrderDate, tblOrdersItems.ItemsID
HAVING (((tblOrders.ClientDetailsID)=721) AND ((tblOrders.OrderDate)>DateAdd('m',-6,Date())));

the date range should be between -3 months to -6 months.

so the results for this date range will be(working backwards)
ItemID 15 - 10 - 8 - 775 - 774 - 123 - 49 - 15 - 10 - 8 - 777

the future treatments are ItemID 1 - 8 - 10 - 15 date 06 oct 12

now i asked you to delete those items so the results would be as above

the last treatment in the date range is ItemID 15. but this item is in the future so the query should move to the next last which is ItemID 10. but this item is in the future also so the query should move to the next last which is ItemID 8. but this item is in the future also so the query should move to the next last which is ItemID 775.

so the query would result in ItemID 775.

i really do hope that this is clear to you now as im begining to lose hope that i will be able to do this. i know it can be done its just getting our 2 heads to sync.

thanks for your patients and perseverance

 

[jdraw]

Just an update to status-I am looking at your database -
I used a query to find out which OrderId related to clientId 721 for 02 June 2012.
It was OrderId 2841.
I then used this query to delete the Items where ItemsId >122
(removing items 778-774-123 ).

DELETE *
FROM tblOrdersItems
WHERE (((tblOrdersItems.OrderID)=2841) AND
((tblOrdersItems.ItemsID)>122));

I'm not sure what is giving you the order of these items. I find the OrderDate and Time can give any of these items???
see attached jpg

 

[shutzy]

when the query ran i just used the sort by accending on the date column. i didnt realise that it would come out different.

 

[shutzy]

im sorry. i just re did the query and got a different result. the principal should still be the same. the last 3 itemID in the date range should be ItemID 15, 10, 8. no particular order. which would mean that the only ItemID's remaining would be 774, 123, 49, 775, 777. i have already excluded the ItemID's 15, 8 ,10 date 21 apr 12 from the list in my head. these items should definately not appear in the final list of results.

at least we have until the 6 oct 12 to work on this using the same client. because as the 6 oct 12 comes round it will be no longer >date()

 

[shutzy]

i think i have just thought of another way of explaining this. my last post kind of says it.

i would like to compile a list of treatments that a client has had in a given date range excluding any that appear in date range of a given date.

let me know if this is any clearer.

if it is then it will be a case of just selecting 1 of the compiled results!

 

[jdraw]

I have a query (at this point for a specific ClientId 721) that looks at
a)the Treatments OrdersItems for the last 3 to Months, and ignores/removes
b)those future orders for the same Treatments, and
c)lists those Treatments OrderItems in descending order of OrderDate and OrdersItems

query SQL code

SELECT  [tblOrders.OrderDate] AS Expr1, tblItems.ItemsID, tblOrders.OrderTime, tblOrdersItems.OrdersItemsID, tblOrders.ClientDetailsID
FROM tblOrderItemsType INNER JOIN (tblItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblItems.ItemsID = tblOrdersItems.ItemsID) ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID
WHERE ((([tblOrders.OrderDate]) Between DateAdd("m",-3,Date()) And DateAdd("m",-6,Date())) AND ((tblItems.ItemsID) Not In (SELECT DISTINCT tblOrdersItems.ItemsID FROM tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID WHERE (((tblOrders.ClientDetailsID)=721) AND ((tblOrders.OrderDate) Between DateAdd("m",-3,Date()) And DateAdd('m',-6,Date())) AND ((Exists (select "x" from tblorders as Sec Inner Join tblOrdersItems as subOrderItems ON  sec.OrderID = subOrderItems.OrderID   where sec.OrderDate >Date()  and  sec.ClientDetailsId = tblOrders.ClientDetailsID AND    subOrderItems.ItemsId = tblOrdersItems.ItemsID))<>False)))) AND ((tblOrderItemsType.Type)="Treatments"))
ORDER BY [tblOrders.OrderDate] DESC , tblItems.ItemsID DESC;

I have attached the result as a jpg. Now this is only for 1 client 721. So more work is required.


EDIT: I didn't see your last 2 posts before sending this. Just saw them now when I went back to the forum.

 

[shutzy]

ha haaaaa. there is a glimmour of light. there is no way i could ever have wrote the sql.

i dont know if i am putting you of by saying this. but is it possible to calculate the ClientDetailsID + ',' + ItemsID and have a statement that if equal then ignore. i have tried to calculate the 2 field fromt the 2 different tables but had no success but obviously you are much better at this.

i thought that this would be a fool proof way of the query looking for 2 records that appear the same?

 

[jdraw]

I have adjusted the query named CheckClientTreatmentsInLast3_6MonthsLessFutureOrderItems to identify By Ascending ClientId the Treatment OrderDate Descending and OrdersItems Descending for Treatments in the past 3 to 6 months less future OrderItems.

SELECT tblOrders.ClientDetailsID, [tblOrders.OrderDate] AS OrderDate3_6monthsAgo, tblItems.ItemsID, tblOrders.OrderTime, tblOrdersItems.OrdersItemsID
FROM tblOrderItemsType INNER JOIN (tblItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblItems.ItemsID = tblOrdersItems.ItemsID) ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID
WHERE ((([tblOrders.OrderDate]) Between DateAdd("m",-3,Date()) And DateAdd("m",-6,Date())) AND ((tblItems.ItemsID) Not In (SELECT DISTINCT tblOrdersItems.ItemsID FROM tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID WHERE (((tblOrders.ClientDetailsID)=721) AND ((tblOrders.OrderDate) Between DateAdd("m",-3,Date()) And DateAdd('m',-6,Date())) AND ((Exists (select "x" from tblorders as Sec Inner Join tblOrdersItems as subOrderItems ON  sec.OrderID = subOrderItems.OrderID   where sec.OrderDate >Date()  and  sec.ClientDetailsId = tblOrders.ClientDetailsID AND    subOrderItems.ItemsId = tblOrdersItems.ItemsID))<>False)))) AND ((tblOrderItemsType.Type)="Treatments"))
ORDER BY tblOrders.ClientDetailsID, [tblOrders.OrderDate] DESC , tblItems.ItemsID DESC;

Attached is a sample screen dump showing various clientIds including 721.

Do you do any vba coding? I ask because at this point if you had this query output as a recordset, you could go through the recordset and get the first item and base your email on that OrderItems, and you could get the Client Name and email from the ClientDetailsId.

 

[shutzy]

in a word, no. in my original query i had the tblClientDetails in cluded so that i can have the Firstname and EmailAddress Fields. then i would just use word with mail merge and merge fields. that is how i am doing things like something we have called loyalty points. quite self explanetary. i use word as it works well with outlook.

thanks

 

[shutzy]

thanks for all your help jd. could you tell me at which point our heads synced. im very curious about that 1.

i have attached a print screen of the final result. i have added the field for emailing purposes and i plan on using 'make table' so i can run an update query from the results to check the Email Purchase Reminder check box so they will only get the email once.

once again fantastic work. the next(which will be the first) time im in canada i will buy you a beer.

 

[jdraw]

Ok. Also, I just noticed an error in the SQL -- I still limit one of the subqueries to Client 721. I'm working to remove that oversight on my part. The intent is to have 2 queries.
One to get a list of OrderItems from 3-6 months ago with any future order for those OrdersItems removed.
The other to get the List of ordersItems from 3-6 months ago where there are no future OrdersItems.

I believe this query handles list One.

SELECT tblOrders.ClientDetailsID, [tblOrders.OrderDate] AS OrderDate3_6monthsAgo
, tblItems.ItemsID, tblOrders.OrderTime, tblOrdersItems.OrdersItemsID
FROM tblOrderItemsType INNER JOIN (tblItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblItems.ItemsID = tblOrdersItems.ItemsID) ON 
 tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID
WHERE
((([tblOrders.OrderDate]) Between DateAdd("m",-3,Date()) And DateAdd("m",-6,Date()))  AND
 ((tblOrderItemsType.Type)="Treatments" AND 
((tblItems.ItemsID) Not In
 (SELECT DISTINCT tblOrdersItems.ItemsID FROM tblOrders as  AUX INNER JOIN tblOrdersItems ON aux.OrderID = tblOrdersItems.OrderID WHERE (((aux.ClientDetailsID)=tblOrders.ClientDetailsId) AND ((aux.OrderDate) Between DateAdd("m",-3,Date()) And DateAdd('m',-6,Date())) AND
((Exists 
(select "x" from tblorders as Sec Inner Join tblOrdersItems as subOrderItems ON  sec.OrderID = subOrderItems.OrderID   where 
 sec.OrderDate >Date()  and 
 sec.ClientDetailsId = tblOrders.ClientDetailsID AND
   subOrderItems.ItemsId = tblOrdersItems.ItemsID))<>False))))) )
ORDER BY tblOrders.ClientDetailsID, [tblOrders.OrderDate] DESC , tblItems.ItemsID DESC;

You could run this query and see if it matches your understanding of the data.
I still have to work on the first item for each clientId.

But I have to go out for a couple of hours at the moment.


EDIT/UPDATE. I just saw your last post. Seems you're ahead of me on getting your info for emails.

Have you got it sorted out now, or is there more to do?

 

[shutzy]

dont worry about it. take your time.

by the way. how that patio looking?

 

[jdraw]

It isn't so much a patio, but 30 patio stones (3 x 10) that serve as front walk. They had settled and become quite uneven over the years. So it was lifting, leveling, replacing and washing that took the time.

So where exactly do we stand on this subquery/emailing thingy at the moment.

I have reworked the sql (and trying to check it) to get the Last item from orders in the last 3-6 months removing any items identified in future orders for each clientId. But I saw you jpg and am now wondering if you have everything you need???????

Here is that query:

select  ClientDetailsID,Last(OrderDate3_6monthsAgo) as LastOrderDate,Last(ItemsID) as LastItemID , Last(Items) as LastItem
from 
(
SELECT tblOrders.ClientDetailsID, [tblOrders.OrderDate] AS OrderDate3_6monthsAgo, tblItems.ItemsID, tblOrders.OrderTime, tblItems.Items
FROM tblOrderItemsType INNER JOIN (tblItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblItems.ItemsID = tblOrdersItems.ItemsID) ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID
WHERE ((([tblOrders.OrderDate]) Between DateAdd("m",-3,Date()) And DateAdd("m",-6,Date())) AND ((tblItems.ItemsID) Not In (SELECT DISTINCT tblOrdersItems.ItemsID FROM tblOrders as  AUX INNER JOIN tblOrdersItems ON aux.OrderID = tblOrdersItems.OrderID WHERE (((aux.ClientDetailsID)=tblOrders.ClientDetailsId) AND ((aux.OrderDate) Between DateAdd("m",-3,Date()) And DateAdd('m',-6,Date())) AND
((Exists 
(select "x" from tblorders as Sec Inner Join tblOrdersItems as subOrderItems ON  sec.OrderID = subOrderItems.OrderID   where sec.OrderDate >Date()  and  sec.ClientDetailsId = tblOrders.ClientDetailsID AND    subOrderItems.ItemsId = tblOrdersItems.ItemsID))<>False)))) AND ((tblOrderItemsType.Type)="Treatments"))

)

Group by ClientDetailsId;

Could you please run that query and do some spot checking to see if it agrees with your expectations?

This is for those Clients who had orderItems in the last 3-6 months and a future orders. Any Items in the future orders that were also in the 3-6 months orders have been removed from consideration.

Please advise on status of your project.

 

[shutzy]

im not sure if i have already done it. i used the most recent sql before your last post. added the FirstName, ItemName,EmailAddress and chose the Sum option. i changed some of the columns to 'last' and i have yet to spot check it but i think its right. i better get some coffee and paper. i only have 59 records to check. i have narrowed it down to EmailAddress Is Not Null. so i have fewer records. if this is ok ill let you know.

 

[jdraw]

Ok, and do you have it working for those clients with no future records, as well as those with future records?

 

[shutzy]

is the image before or after you levelled it! i cant say anything a i have yet to try this. i prefer a lovely grean striped lawn.

i have gone through most or the records that were returned. i did change the subquery date to not include any items where >DateAdd('m',-3,Date()) as i dont really want to email them something they just had yesterday for example. there is one particular client i know it has worked for. ClientDetailsID 415. this client has had the same 2 treatments many times since god knows when with one exception on the 16 may 2012. so it has definately worked on that one.

thanks for all your help with this. how well do you know vba. i have had someone supply me a sample diary to be able to view all the appointments. the only thing is it needs integrating into my database and i have tried but dont really have the skills. the person that supplied it said he will check it in a few days as he is very busy at work. but that was end of aug and it was kind of a glimmer for my holy grail. i have been trying to get a diary for multiple emploees for over a year now and demo like the one supplied are a little thin on the ground.

however i really do think i have taken quite a lot of you time up recently so ill keep it in mind and poosibly check back in a month or so if thats ok.

 

[jdraw]

That's fine by me. So you're good to go at the moment (subject to the adjustments - diary related things)- with the help of your friend.