Query to Match fill Date corresponding to item arrival date (1 Viewer)

Gladis

New member
Local time
Today, 19:02
Joined
Mar 17, 2022
Messages
11
Hi, Posting here for the first time. I wasn't able to locate what I was looking for.

I have 2 tables
Inbound table: Inbound Item from Manufacture
Outbound table: Orders from customer.

I need to be able to provide customers with expected fill dates based on their order type and order date, corresponding to our receipt dates.

I was able to do this in excel. using running sum and Xlookup. it is too slow to do it in excel. also Excel data I am working with is snap shot in time. (day behind)
with access I can connect to live data base and obtain the information I need.

I tried to use running sum in Access with Dsum() Function I keep getting an error.

I have attached my Sample DB and Sample Excel results I am looking for.

I would really appreciate if anyone can provide me the necessary queries or point me in the right directions.

Thanks
 

Attachments

  • ETA Look Up.accdb
    928 KB · Views: 164
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 16:02
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Can't download your file at the moment, but I will try to take a look at it later.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:02
Joined
May 7, 2009
Messages
19,169
the output is "as-is" as you state it in your db.
imo, query alone is not enough to output your table.
see module1 for the code.

open form1, it calls the Sub on the Load event.
 

Attachments

  • ETA Look Up.accdb
    756 KB · Views: 153

Gladis

New member
Local time
Today, 19:02
Joined
Mar 17, 2022
Messages
11
Hi arnelgp,

Thanks for the prompted reply. still not working as I expected. maybe I didn't explain it properly my bad sorry.
I need running total separate for each item.
for example
1570C945 15 15
1570C945 10 25

1570C941 1 1
1570C941 1 2
1570C941 1 3

match each Item separately to the outbound.

I will be using criteria field to search by Item ID : "1570C945". it should only show the Eta for that item.

I tried to change to your running total on Query 2 column: total rcpt: CLng(DSum("[Exp Qty]","Inbound","[ID] <= " & [ID]))
to
total rcpt: CLng(DSum("[Exp Qty]","Inbound","[Item ID] <= " & [Item ID]))

I am getting #Error


this is how I am doing it in Excel. image Bellow
Using =filter() function to bring in Column A,B,C from inbound table and Columns F,G,H,I from out table.
Calculating running totals on Column D and E
and using formula =XLOOKUP(E2,D:D,C:C,,1) to calculate ETA on Column J

Excel formula.png




Thanks
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:02
Joined
May 7, 2009
Messages
19,169
Did you open form1?
 

Gladis

New member
Local time
Today, 19:02
Joined
Mar 17, 2022
Messages
11
sorry for the Late reply arnelgp
Yes, I did, this is what I am seeing.
Different Items numbers are added on running total.
also how would i go about looking at ETA for just 1 Item number ? Ex: 1570C945

thanks
form1.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:02
Joined
May 7, 2009
Messages
19,169
o, i see what you mean.
i added allocation table and some new queries.
modified the code too.
run form1.
 

Attachments

  • ETA Look Up.accdb
    832 KB · Views: 168

Gladis

New member
Local time
Today, 19:02
Joined
Mar 17, 2022
Messages
11
Thanks arnelgp really appreciate it.

it working perfectly.
I tried to do some tweaks and ran into a wall.
I want to use a control form to look up by Item id or Order Date, or Order ID.

see frm_Eta. running Query on Output2 table, but worried when I try to process more data this way I might run into a problem.


why do i keep getting error on:
DSum("Exp Qty","Inbound","Item ID=" & [Item ID] & "")

Thankls
 

Attachments

  • ETA Look Up (1).accdb
    3.4 MB · Views: 165

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:02
Joined
May 7, 2009
Messages
19,169
your criteria is Missing one Single quote:

..., "Item ID='" & [Item ID] & "")
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:02
Joined
May 7, 2009
Messages
19,169
why don't you use combobox instead.
 

Attachments

  • ETA Look Up (1).accdb
    3.4 MB · Views: 146

Gladis

New member
Local time
Today, 19:02
Joined
Mar 17, 2022
Messages
11
why don't you use combo box instead.

how would I go about passing the combo box parameters to recordset in your code?
Tried this code "where a.[item.id] = '" & [Forms]![frm_Eta]![tbx_itemid] & "' " & _ and I am getting error.


Code:
    Set r_in = db.OpenRecordset( _
                "select a.*, b.id from outbound as a " & _
                "inner join [item number] as b " & _
                "on a.[item id] = b.[item id] " & _
                "where a.[item.id] = '" & [Forms]![frm_Eta]![tbx_itemid] & "' " & _
                "order by " & _
                "b.id, a.[order type], a.[request date];", _
                dbOpenSnapshot, dbReadOnly)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:02
Joined
May 7, 2009
Messages
19,169
you don't need to do that, after you select from a combobox the output2 will be filtered by your query.
 

Gladis

New member
Local time
Today, 19:02
Joined
Mar 17, 2022
Messages
11
you don't need to do that, after you select from a combobox the output2 will be filtered by your query.
Correct, I am trying to reduce the number of data from the Output2 table.
I have close to 15000 Item numbers and 10,000 orders to be able to lookup eta. that table with all the item number getting too big.

trying to see if I can filter beforehand.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:02
Joined
May 7, 2009
Messages
19,169
you don't need to Run ProcessOrder2 every time you change Criteria.
You only run it "ahead" once, then you can filter it using your query.

i added a button "Requery Data". you only need to click this once (if there is additional update on your table).
also, if you have large amount of data to output in your listbox, it will fail (there is limit up to how many list items you can put).
better use a datasheet subform.
 

Attachments

  • ETA Look Up (1).accdb
    3.4 MB · Views: 155

Gladis

New member
Local time
Today, 19:02
Joined
Mar 17, 2022
Messages
11
hi arnelgp,

attached my current data base. Eta look up is working fine.
running ProcessOrder2 takes a bit of time. I need to run it quite a bit to get live data.

Example: Customer places an order and calling us with in like 30min for eta. it happens a lot and I can't see the order without refreshing it.

I am not an expert. wouldn't filtering the Item before ProcessOrder2 makes it go faster?
I really appreciate all the help

thanks in advanced.
 

Attachments

  • ETA Look Up.zip
    3.4 MB · Views: 157

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:02
Joined
May 7, 2009
Messages
19,169
it will now process only the item no you type.
type the Item No and press Update Data button.
 

Attachments

  • ETA Look Up.zip
    497.9 KB · Views: 158

Gladis

New member
Local time
Today, 19:02
Joined
Mar 17, 2022
Messages
11
it will now process only the item no you type.
type the Item No and press Update Data button.
Hi arnelgp,

sorry for reopening an old post.

I ran into a problem today. didn't make any changes to your script.

everything was working fine until. I added more Item no to the Item Number table.

when I don't enter Item no, Processorder 2 script is getting stuck on code bellow.

funny thing is if I use the Old table "Item Number1" everything is running fine, but Eta is not allocating properly.


Code:
Do Until r_in.EOF
            subt = subt + Nz(r_in![exp qty], 0)
            .Edit
            ![item no] = r_in![item id]
            ![expected qty] = r_in![exp qty]
            ![eta] = r_in![EXP ETA]
            ![total rcpt] = subt
            .Update
            .MoveNext
            r_in.MoveNext
            If Not r_in.EOF Then
                If r_in!id <> lngID Then
                    subt = 0
                    lngID = r_in!id
                End If
            End If
        Loop

Here is the error message

Screenshot 2022-03-31 173611.png
 

Attachments

  • ETA Look Up.zip
    2.4 MB · Views: 133

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:02
Joined
May 7, 2009
Messages
19,169
here is the modification.
 

Attachments

  • ETA Look Up (1).zip
    720.8 KB · Views: 127

Users who are viewing this thread

Top Bottom