subquery expression

[shutzy]

can someone please tell me where i have gone wrong with this. it worked until i added the LAST(tblOrdersItems.ItemsID)AS LastOfItemsID.

i was Expr1: (SELECT tblOrders.ClientDetailsID, tblOrdersItems.ItemsID
FROM tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID)

like this until i added the extra LAST. the reason for this was that it was telling me that

you have written a subquery that can return more than one field without using the EXISTS reserved word in the main queries FROM clause. revise the SELECT statement of the subquery to request only one field.

any help?

 

[jdraw]

Review allen Browne's subquery info/examples at
http://allenbrowne.com/subquery-01.html and
http://allenbrowne.com/subquery-02.html

 

[shutzy]

hi jd. ive give it a go and tried everything i can think of. i really think that this out of my depth. ive learnt

Not In (SELECT tblPoItems.ItemID FROM tblPoItems WHERE (((tblPoItems.Po)=[Forms]![frmManagePOs]![lstPos]));)

how this works. but i just cant get this to work in my query

SELECT tblOrders.ClientDetailsID, tblClientDetails.FirstName, tblClientDetails.Surname, Last(tblOrdersItems.OrdersItemsID) AS LastOfOrdersItemsID, Last(tblItems.Items) AS LastOfItems, Last(tblOrders.OrderDate) AS LastOfOrderDate, Last(tblCategories.Catergorie) AS LastOfCatergorie, tblOrderItemsType.Type, Last(tblItems.available) AS LastOfavailable, Last(tblItems.ItemsID) AS LastOfItemsID, tblClientDetails.EmailAddress, tblOrdersItems.EmailPurchaseReminder, Last(tblEmployeeList.FirstName) AS LastOfFirstName
FROM tblOrderItemsType INNER JOIN (tblClientDetails INNER JOIN (tblCategories INNER JOIN (tblEmployeeList INNER JOIN (tblItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblItems.ItemsID = tblOrdersItems.[ItemsID]) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) ON tblCategories.CategoriesID = tblItems.CategoriesID) ON tblClientDetails.ClientDetailsID = tblOrders.ClientDetailsID) ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID
GROUP BY tblOrders.ClientDetailsID, tblClientDetails.FirstName, tblClientDetails.Surname, tblOrderItemsType.Type, tblClientDetails.EmailAddress, tblOrdersItems.EmailPurchaseReminder
HAVING (((Last(tblOrders.OrderDate)) Between DateAdd("m",-3,Date()) And DateAdd('m',-6,Date())) AND ((tblOrderItemsType.Type)="Treatments") AND ((Last(tblItems.available))=Yes) AND ((tblClientDetails.EmailAddress) Is Not Null) AND ((tblOrdersItems.EmailPurchaseReminder)=No))
WHERE NOT EXISTS (Select tblOrdersItems.ItemID FROM tblOrdersItems INNER JOIN WHERE tblOrders.OrderDate FROM tblOrders=>date());

any chance of a little further guidance?

 

[jdraw]

In plain English WHAT is it you're trying to do exactly?

Please show your tables and fields with their datatypes.

You have

tblOrdersItems.EmailPurchaseReminder)=No

Is this field text or Boolean?
Also

(Last(tblItems.available))=Yes

Text or Boolean?

What version of Access are you using? I have 2003 so can not use an accdb format database.

This is invalid syntax - you have an INNER JOIN without a second table

(Select tblOrdersItems.ItemID FROM tblOrdersItems INNER JOIN WHERE tblOrders.OrderDate FROM tblOrders=>date())

 

[shutzy]

what i am trying to do is compile a list of all the items a customer has bought between the date of 3 months ago and 6 months ago. i would like to deduct from this list if the client has made a future order of an item that appears in the list. and thus move to the next last item.

tblOrdersItems.EmailPurchaseReminder and tblItems.Available is a tick box / yes - no box / 1 - 0 box

ive attached the relevant tables below

 

[shutzy]

i have attached the database with the relevant tables and the query i am trying to get the correct results in.

 

[jdraw]

I was working with your data. I don't know if you can do it all by query.
I looked at clientId 660. I noticed orders with dates in October.

So I went looking for orders between 3 and 6 months ago for clientId 660; then looked to see if ClientId had Orders with Orderdate between 3 and 6 months ago and also had an Order with Orderdate > Today

Then generalized to get those ClientIds and OrderItems, that were on Orders with Orderdates between 3 and 6 months ago and had future order for these items using Orderdate >Date()

This is that query ClientsWithOrdersInLast3To6MonthsAndOrdersForSameItemsInFuture

SELECT DISTINCT tblOrders.ClientDetailsID, tblOrdersItems.ItemsID
FROM tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID=tblOrdersItems.OrderID
WHERE ((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);

So are these the ones that you want to change to second last order in the 6 months to 3 months ago????

I'm using your data to build subquery but I still am unclear of the goal.

I'm busy with patio stones will check back later.

 

[shutzy]

the end goal it to email client. i have started to put together a form for quick emails. each button on a form open a different word document. each word document is tailored to send bulk email to clients. i have and email already set up for sending out reminder/feedback for client that purchased item that we suspect should be used by now. so we are kind of prompting them to purchase another and asking for feedback on our products. on the tblOrderItems there is a check box for has this client been reminded of this already. if yes then i want to remind them about a different item next time rather than bombarding them with the same old email.

the query that i have already created for the product purchases creates a table and then i run an update query from that table to check the reminder box in tblOrderItems. the table is overwritten each time i want to send a fresh batch.

i plan on having a whole host of different emails that can be done with just one click of a button.

hopefully this will increase sales.

thanks for your help so far.

 

[jdraw]

In the sample database you posted, what email would be sent to client 660?

Can you show with your data which product exactly the email will address?
If you have another example, someone without future orders, can you show which product(s) the email would target?

Also, was the subquery helpful?

 

[shutzy]

im sorry but i havnt tried the sub query yet. 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. that is according to my query. the query i put in the sample gives a result of the last treatment the client had in the period between 3 and 6 months ago.

 

[MSAccessRookie]

hi jd. ive give it a go and tried everything i can think of. i really think that this out of my depth. ive learnt

Not In (SELECT tblPoItems.ItemID FROM tblPoItems WHERE (((tblPoItems.Po)=[Forms]![frmManagePOs]![lstPos]));)

how this works. but i just cant get this to work in my query

SELECT tblOrders.ClientDetailsID, tblClientDetails.FirstName, tblClientDetails.Surname, Last(tblOrdersItems.OrdersItemsID) AS LastOfOrdersItemsID, Last(tblItems.Items) AS LastOfItems, Last(tblOrders.OrderDate) AS LastOfOrderDate, Last(tblCategories.Catergorie) AS LastOfCatergorie, tblOrderItemsType.Type, Last(tblItems.available) AS LastOfavailable, Last(tblItems.ItemsID) AS LastOfItemsID, tblClientDetails.EmailAddress, tblOrdersItems.EmailPurchaseReminder, Last(tblEmployeeList.FirstName) AS LastOfFirstName
FROM tblOrderItemsType INNER JOIN (tblClientDetails INNER JOIN (tblCategories INNER JOIN (tblEmployeeList INNER JOIN (tblItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblItems.ItemsID = tblOrdersItems.[ItemsID]) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) ON tblCategories.CategoriesID = tblItems.CategoriesID) ON tblClientDetails.ClientDetailsID = tblOrders.ClientDetailsID) ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID
GROUP BY tblOrders.ClientDetailsID, tblClientDetails.FirstName, tblClientDetails.Surname, tblOrderItemsType.Type, tblClientDetails.EmailAddress, tblOrdersItems.EmailPurchaseReminder
HAVING (((Last(tblOrders.OrderDate)) Between DateAdd("m",-3,Date()) And DateAdd('m',-6,Date())) AND ((tblOrderItemsType.Type)="Treatments") AND ((Last(tblItems.available))=Yes) AND ((tblClientDetails.EmailAddress) Is Not Null) AND ((tblOrdersItems.EmailPurchaseReminder)=No))
WHERE NOT EXISTS (Select tblOrdersItems.ItemID FROM tblOrdersItems INNER JOIN WHERE tblOrders.OrderDate FROM tblOrders=>date());

any chance of a little further guidance?

I reformatted the Query to make it more readable for me. If I did it correctly, then the Format (and content?) of the Sub-Query is incorrect below. Please indicate which in order for a better understanding.

[COLOR=#0000bb][FONT=Courier New][SIZE=3][COLOR=#000000][FONT=Times New Roman]SELECT tblOrders.ClientDetailsID,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   tblClientDetails.FirstName,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   tblClientDetails.Surname,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   Last(tblOrdersItems.OrdersItemsID) AS LastOfOrdersItemsID,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   Last(tblItems.Items) AS LastOfItems,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   Last(tblOrders.OrderDate) AS LastOfOrderDate,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   Last(tblCategories.Catergorie) AS LastOfCatergorie,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   tblOrderItemsType.Type,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   Last(tblItems.available) AS LastOfavailable,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   Last(tblItems.ItemsID) AS LastOfItemsID,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   tblClientDetails.EmailAddress,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   tblOrdersItems.EmailPurchaseReminder,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   Last(tblEmployeeList.FirstName) AS LastOfFirstName[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]FROM tblOrderItemsType INNER JOIN (tblClientDetails INNER JOIN (tblCategories INNER JOIN (tblEmployeeList INNER JOIN (tblItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblItems.ItemsID = tblOrdersItems.[ItemsID]) ON tblEmployeeList.EmployeeListID = tblOrdersItems.Employee) ON tblCategories.CategoriesID = tblItems.CategoriesID) ON tblClientDetails.ClientDetailsID = tblOrders.ClientDetailsID) ON tblOrderItemsType.OrderItemsTypeID = tblItems.OrdersItemsTypeID[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]GROUP BY tblOrders.ClientDetailsID,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   tblClientDetails.FirstName,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   tblClientDetails.Surname,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   tblOrderItemsType.Type,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   tblClientDetails.EmailAddress,[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   tblOrdersItems.EmailPurchaseReminder[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]HAVING (((Last(tblOrders.OrderDate)) [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   Between DateAdd("m",-3,Date()) And DateAdd('m', 6,Date())) AND [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   ((tblOrderItemsType.Type)="Treatments") AND [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   ((Last(tblItems.available))=Yes) AND [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   ((tblClientDetails.EmailAddress) Is Not Null) AND [/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman]   ((tblOrdersItems.EmailPurchaseReminder)=No))[/FONT][/COLOR][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red][B]WHERE NOT EXISTS [/B][/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red][B]   (Select tblOrdersItems.ItemID[/B][/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red][B]   FROM tblOrdersItems INNER JOIN [/B][/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red][B]   WHERE tblOrders.OrderDate [/B][/COLOR][/FONT][/SIZE]
[SIZE=3][COLOR=#000000][FONT=Times New Roman][B][COLOR=red]   FROM tblOrders=>date())[/COLOR][/B];  [/FONT][/COLOR][/SIZE]
[/FONT][/COLOR]

 

[shutzy]

hi rookie, i dont really know what you are asking. the WERE NOT EXISTS if from the link that jd gave. on that page it explains. i tried to do the subquery as it explains on that page but had no success.

 

[MSAccessRookie]

hi rookie, i dont really know what you are asking. the WERE NOT EXISTS if from the link that jd gave. on that page it explains. i tried to do the subquery as it explains on that page but had no success.

The SELECT Statement in the WHERE NOT EXISTS Clause is incorrectly formatted (SEE Below).

[B][COLOR=#ff0000]WHERE NOT EXISTS [/COLOR][/B]
[COLOR=red][B]   (SELECT [/B][/COLOR][COLOR=red][B]tblOrdersItems.ItemID[/B][/COLOR]
[B][COLOR=red]   FROM tblOrdersItems INNER JOIN [/COLOR]{}[COLOR=red]  [COLOR=purple]{Missing Table Name and [/COLOR][/COLOR][COLOR=#800080]Missing ON Statement}[/COLOR][/B]
[COLOR=red][B]   WHERE tblOrders.OrderDate [/B][/COLOR]
[COLOR=#000000][B][COLOR=red]   [/COLOR][COLOR=purple]FROM tblOrders=>date()) {FROM Statement Invalid in this position}[/COLOR][/B][/COLOR]
[COLOR=#000000];  [/COLOR]

 

[jdraw]

In post #4 I mentioned the invalid syntax in the where clause.

In post #7 I showed how to create an Exists with a subquery.

I'd like to see a plain English statement of what the underlying question/issue is.

It seemed to be orders in the past 3 to 6 months originally, but as I read more of the posts, I see it was a specific type of Order "Treatments".

 

[shutzy]

Hi jd, I don't really know how to explain it any better. Yes you are correct in saying that the particular type of order is treatments but this is for me just a fail safe. In actual fact a client cannot book in a order type retail. If you look at tblOrdersItems you will see that all the ItemID are of treatment type.

I'll try and explain it again

The results I require are:
I would like the last treatment item a client purchased between 3 and 6 months ago that does not appear in the tblOrdersItems with a date in the future. So if ClientId + ItemID is equal to ClientID + ItemID with a date in the future should be removed and the move to the second last and so on until ClientID + ItemID arenit equal.

I thought you had known what I meant in post#7.

I would like the last treatment that a client had in 3 - 6 months ago that they do not have re-booked.

 

[jdraw]

Perhaps we could take this one step at a time since I'm just trying to understand your database and query request.
In post 10 you said

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.

But if I try this query on the database1 you posted

SELECT tblOrders.ClientDetailsID
, tblOrders.OrderDate
, tblOrderItemsType.Type
, tblItems.ItemsID
, tblItems.available
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.ClientDetailsID)=660) AND
 ((tblOrders.OrderDate) Between DateAdd("m",-3,Date()) And DateAdd("m",-6,Date())) AND
 ((tblOrderItemsType.Type)="Treatments"));

I see the following "Treatments" (see attached screen capture)


I'm seeing treatments for this Client in your data and you are not????

What part of this am I missing???

 

[shutzy]

i havnt got a clue what has happened. i have just downloaded the database that i uploaded and the sql must be different. i have looked at the same query and there is no clientid 660 in the list. i have included a screen shot. whats going on?

 

[shutzy]

screen shot

 

[jdraw]

Here is a jpg of records from client 660 I downloaded

You are showing the results of a query,

Show me what records there are for clientId 660 in your original table.

 

[shutzy]

hi the only way i can do it is from a query as the ClientDetailsID are in a different table to the OrdersItems. i have attached a screen shot of a fresh query that just brings the results of the date criteria. between DateAdd('m',-3,Date()) And DateAdd('m',-6,Date())