A query to join data from two tables based on a date range

[frankt68]

I've a table tDeliveries and a table tOrders. tDeliveries has the fields DeliveryID, Delivery_Date and ItemID, and tOrders has the fields OrderID, Order_Date and ItemID.
I want to create a query that displays, for each ItemID, a list of all deliveries from the tDeliveries table and their associated orders created within a few days (for example, five days) before the delivery, where the order date is closest to the delivery date.

I've attached a test database with the mentioned tables and two queries that I've tried, but none of them return the desired results.
I want the query to display the results as they appear in the table "Example Query Results" in the attached test database.

Any suggestion on how I can create a query that will provide the desired display?

I'd also like to know how to complete the query if I would like it to also compare ordered and delivered quantities of the same product (ItemID).

 

[arnelgp]

can you check Query1 if this is what you want.

 

[ebs17]

Question 1:

SELECT
   D.ItemID,
   D.DeliveryID,
   D.Delivery_date,
   O.OrderID,
   O.Order_Date
FROM
   tDeliveries AS D
      INNER JOIN tOrders AS O
      ON D.ItemID = O.ItemID
WHERE
   O.Order_Date BETWEEN DateAdd("d", - 5, D.Delivery_date)
      AND
   D.Delivery_date

Question 2:

SELECT
   D.ItemID,
   D.DeliveryID,
   D.Delivery_date,
   D.Quantity,
   COUNT(*) AS NumberOrders,
   SUM(O.Quantity) AS SumQuantity
FROM
   tDeliveries AS D
      INNER JOIN tOrders AS O
      ON D.ItemID = O.ItemID
WHERE
   O.Order_Date BETWEEN DateAdd("d", - 5, D.Delivery_date)
      AND
   D.Delivery_date
GROUP BY
   D.ItemID,
   D.DeliveryID,
   D.Delivery_date,
   D.Quantity

 

[frankt68]

can you check Query1 if this is what you want.

Thanks for your answer. Unfortunately, this isn't what I'm looking for. I'd like to have a query that returns data for all deliveries for a particular product and the associated purchase orders based on the delivery date. For example, for all deliveries for ItemID 72370, I'd like to see the DeliveryID and Delivery_Date data and the associated purchase order (OrderID, Order_Date) if the purchase order meets the date criteria - as shown in the "Example Query Results" table. So I'd like the query results look like this:

 

[frankt68]

Question 1:

SELECT
   D.ItemID,
   D.DeliveryID,
   D.Delivery_date,
   O.OrderID,
   O.Order_Date
FROM
   tDeliveries AS D
      INNER JOIN tOrders AS O
      ON D.ItemID = O.ItemID
WHERE
   O.Order_Date BETWEEN DateAdd("d", - 5, D.Delivery_date)
      AND
   D.Delivery_date

[/CODE]

Thank you for your answer, Eberhard. Unfortunately, the result of your query is the same as of mine query q1 from the test database (although your solution is more elegant...). But this isn't what I'm looking for,
I'd like the query results look like this:

Regarding the second question, I probably wasn't clear enough. Namely, I would also like to compare the ordered quantity of the product and the delivered quantity of the same product (ItemID). So if the Order_date for ItemID matches the date criteria (within 5 days before delivery), I'd also like to check if the delivered quantity (Q_delivered) of the ItemID matches the quantity ordered (Q_ordered).

 

[ebs17]

If you want to see all deliveries, change INNER JOIN to LEFT JOIN.

Second: Change field name Quantity to Q_delivered / Q_ordered.
You can use an additional field that shows the difference between the two numbers.

 

[arnelgp]

i created a "temporary" table, tOutput.
this table got it's record when you open dsfrmOutput (datasheet form).
there is a Code when you open this forrm (that fills the record of tOutput).

open dsfrmOutput.

 

[frankt68]

i created a "temporary" table, tOutput.
this table got it's record when you open dsfrmOutput (datasheet form).
there is a Code when you open this forrm (that fills the record of tOutput).

open dsfrmOutput.

Thank you, arnelgp, the temporary table look Ok, but when I open the form, I get a Run-time error 3075: Syntax error in date in query expression "ItemID=72370 And [Order_date]>=#12.30.2018# And [Order_date]<=#01.04.2018# pointin at this part of code:

                Set rst3 = dbs.OpenRecordset( _
                        "Select * From tOrders Where ItemID=" & !ItemID & " And " & _
                        "[Order_Date] >= #" & Format$(rst2!Delivery_date - 5, "mm/dd/yyyy") & "# And " & _
                        "[Order_Date] <= #" & Format$(rst2!Delivery_date, "mm/dd/yyyy") & "#", _
                        dbOpenSnapshot, dbReadOnly)

I can't figure out what is wrong with the code.

I've noticed you used "mm/dd/yyyy" date format. I've changed it to "dd.mm.yyyy", because that is the format I'm using.

 

[arnelgp]

Delivery_Date and Order_Date should be Date/Time field type (not Short Text).

 

[ebs17]

Obviously, the filter on date must also be included in the join condition of the JOIN:

SELECT
   D.ItemID,
   D.DeliveryID,
   D.Delivery_date,
   O.OrderID,
   O.Order_Date
FROM
   tDeliveries AS D
      LEFT JOIN tOrders AS O
      ON
         (D.ItemID = O.ItemID)
            AND
         (O.Order_Date >= DateAdd("d", - 5, D.Delivery_date))
            AND
         (O.Order_Date < D.Delivery_date)

I find a formulated query better than recordset loops and an additional table with redundant data.

 

[cheekybuddha]

I've noticed you used "mm/dd/yyyy" date format. I've changed it to "dd.mm.yyyy", because that is the format I'm using.

Don't do that. Try the format as suggested.

SQL requires an unambiguous date format which essentially means either US date format (mm/dd/yyyy) or ISO date format (yyyy-mm-dd).

You can always select the dates in the format that suits you (though if dd.mm.yyyy is your local regional setting they will be output in that format by default)

 

[frankt68]

Delivery_Date and Order_Date should be Date/Time field type (not Short Text).

I checked all the date fields and found that Delivery_date in tDeliveries has the wrong field type. I changed it to the date/time (short date) type. But when I open the form, I still get the same error (Run-time error 3075).

 

[arnelgp]

is it possible for you to upload those 2 two tables to finally conclude this?

 

[frankt68]

Obviously, the filter on date must also be included in the join condition of the JOIN:

SELECT
   D.ItemID,
   D.DeliveryID,
   D.Delivery_date,
   O.OrderID,
   O.Order_Date
FROM
   tDeliveries AS D
      LEFT JOIN tOrders AS O
      ON
         (D.ItemID = O.ItemID)
            AND
         (O.Order_Date >= DateAdd("d", - 5, D.Delivery_date))
            AND
         (O.Order_Date < D.Delivery_date)

I find a formulated query better than recordset loops and an additional table with redundant data.

Thank you, that works.
As for the table with redundant data - I don't like tables with redundant data either, but I was instructed by my boss to prepare the data in just such a query (or table) as I requested.

 

[frankt68]

is it possible for you to upload those 2 two tables to finally conclude this?

All this time I'm talking about the data in the file you posted on Saturday (test.accdb). When I open this test file and try to open the form, I get the said error message. This also deletes all the data in the tOutput table.

 

[frankt68]

Don't do that. Try the format as suggested.

SQL requires an unambiguous date format which essentially means either US date format (mm/dd/yyyy) or ISO date format (yyyy-mm-dd).

You can always select the dates in the format that suits you (though if dd.mm.yyyy is your local regional setting they will be output in that format by default)

Thank you for the advice.

 

[ebs17]

A (selection) query returns a virtual table as a result.
Thus, you have a "table" that is usable in most cases like a real table.
However, no redundancies are created because the query result is temporary.
The query result shows the current data from the table data at the moment of generation. This is not automatically the case with a detour via an additional table, which by the way will promote the expansion of the database. With stored intermediate results, nothing else is such a temporary table, there is always potentially the risk of data anomalies, if in the original tables data are added, changed or deleted. If one wants to avoid such problems secured, one needs extra measures in the context of thought out expirations.

Conclusion: As long as you can functionally achieve a solution by query and thus with respective recalculation from original table data and do not run into performance problems, you will rely on the query.

 

[arnelgp]

Conclusion: As long as you can functionally achieve a solution by query and thus with respective recalculation from original table data and do not run into performance problems, you will rely on the query.

of course it can be achieved, just overlooked it.
your query is same in the vba query i posted.