Solved Query is not working :( (1 Viewer)

[Number11]

Hi, So i have a query that is looking at two tables one table is customers orders with a field called Order Date and then i have a dispatch table that has a field Dispatch Date. I am wanting the query to only show records with a dispatch date greater than the Order Date

I have this in the Field Dispatch Date

>=[Order Date] OR is NULL

so i want the query to show only records where the order date is newer than the dispatch date and then also if not dispatch date. it not showing records when there has been a previous order that was dispatched and the new once is not showing up? as the order date is newer than the dispatch date

 

[plog]

it not showing records when there has been a previous order that was dispatched and the new once is not showing up?

I don't know what that means. I would post your full SQL. However, before you do that do some debugging 101 on it--divide and conquer. You listed 2 criteria--find out which one is the offender. Remove one of the criteron and see what results it gives, then remove that one and add the other one back in and see what it gives. Narrow this problem down and then investigate.

 

[Number11]

So i have a customer with 3 orders two have been dispatched and the 3rd was ordered post the dispatch date, but query does not show this it sjows the newest order line with the older disptach dates

So what i need to work out is how to show records

Show all records where the Order Date is Greater than the Dispatch Date or if the Dispatch Date is NULL

 

[plog]

Show all records where the Order Date is Greater than the Dispatch Date or if the Dispatch Date is NULL

The criteria you initially posted should achieve that. What you can do is use 2 criteria lines in the Query Designer to ensure it. Put >[OrderDate] on one line then Is Null on the one below it.

So i have a customer with 3 orders two have been dispatched and the 3rd was ordered post the dispatch date, but query does not show this it sjows the newest order line with the older disptach dates

Can you demonstrate this with data? I don't fully grasp it.

 

[Number11]

so here is an example

SELECT Requests.ID, Requests.[Account Number], Requests.[Requested Item], Requests.[Date Request Added], Dispatch.[Dispatched On], Dispatch.[Delivery Date], Dispatch.[Delivery Tine Window]
FROM Requests LEFT JOIN Dispatch ON Requests.[Account Number] = Dispatch.[Account Number]
WHERE (((Dispatch.[Dispatched On])>[Date Request Added]));

The results this gives is zero?

i have attached a sample database it should be showing i record for requested date 03.11.2020 as not yet dispatched??? its just showing the last order??

 

[plog]

...WHERE (((Dispatch.[Dispatched On])>[Date Request Added]));

You have 2 records in Dispatch, A -> January 6, 2020; B -> November 25, 2019
You have 2 records in Requests, X -> November 3, 2020; Y -> January 3, 2020

Because all records have the same account number the 2 records in both tables will match the 2 records in the other table yielding a possibility of 4 matches in the query. Your criteria knocks that down to 1:

AX -> Dispatch before Request -> fails criteria
BX -> Dispatch before Request -> fails criteria
AY -> Dispatch after Request -> passes criteria
BY -> Dispatch before Request -> fails criteria

Its returning exactly what it should. Which of those 4 records (AX, BX, AY, BY) do you want returned?

I just reviewed your prior posts and you've incorrectly stated what you want at least once:

1st post --I am wanting the query to only show records with a dispatch date greater than the Order Date

2nd post--Show all records where the Order Date is Greater than the Dispatch Date or if the Dispatch Date is NULL

I'm confused and I think the only way out of it is for you to tell me which of those 4 records (AX, AY, BX, BY) in the sample database should be showing in your query.

 

[Number11]

...WHERE (((Dispatch.[Dispatched On])>[Date Request Added]));

You have 2 records in Dispatch, A -> January 6, 2020; B -> November 25, 2019
You have 2 records in Requests, X -> November 3, 2020; Y -> January 3, 2020

Because all records have the same account number the 2 records in both tables will match the 2 records in the other table yielding a possibility of 4 matches in the query. Your criteria knocks that down to 1:

AX -> Dispatch before Request -> fails criteria
BX -> Dispatch before Request -> fails criteria
AY -> Dispatch after Request -> passes criteria
BY -> Dispatch before Request -> fails criteria

Its returning exactly what it should. Which of those 4 records (AX, BX, AY, BY) do you want returned?

I just reviewed your prior posts and you've incorrectly stated what you want at least once:



I'm confused and I think the only way out of it is for you to tell me which of those 4 records (AX, AY, BX, BY) in the sample database should be showing in your query.

So i just want to show the Request record from the 03/11/2020 as this has not been dispatched the 2 other records refer to the same order for 03/01/2020, that request has two dispatch dates 06/01/2020 & 25/11/2019 both are before the last request of the 03/11/2020

​

 

[Number11]

example its not even showing the request 03/11/2020
Query1 Query1

ID	Account Number	Reqested Item	Date Request Added	Dispatched On	Delivery Date	Delivery Tine Window
2​	47000001	Sheets	03/01/2020​	06/01/2020​	06/03/2020​	AM
3​	47000001	blabla	01/11/2019​	25/11/2019​	13/12/2019​	AM
3​	47000001	blabla	01/11/2019​	06/01/2020​	06/03/2020​	AM

 

[arnelgp]

see your Dispatch table, you only have account number?
to be specific add a column "which item" was dispatched.

 

[Number11]

see your Dispatch table, you only have account number?
to be specific add a column "which item" was dispatched.

yeah thats not possible as the item names are not called the same in both tables

 

[arnelgp]

why is

yeah thats not possible as the item names are not called the same in both tables

why is that? for you know, an account number can request more than 1 item for a single day.
and a delivery on a day on same account can be multiple items.
not only that, it can be partial delivery of item/items.

 

[plog]

Using your last database (the one with 3 records in Requests and 2 in Dispatch) show me what data you expect the query to return. Don't explain it to me, show me the data you expect your query to return.

 

[Number11]

Using your last database (the one with 3 records in Requests and 2 in Dispatch) show me what data you expect the query to return. Don't explain it to me, show me the data you expect your query to return.

D	Account Number	Reqested Item	Date Request Added	Dispatched On	Delivery Date	Delivery Tine Window
3	47000001	Sheets	03/11/2020

 

[plog]

That can't be right. The requested item for ID=3 is blabla and its Date Request Added is 11/1/2019.

Please check the data in the latest database you posted and show me what data you expect returned.

 

[Number11]

That can't be right. The requested item for ID=3 is blabla and its Date Request Added is 11/1/2019.

Please check the data in the latest database you posted and show me what data you expect returned.

just an example of what i want to see the request with no dispatch date

 

[arnelgp]

see Query2