Search results

Thanks Plog. That makes sense. I'll give that a go.

Hi MajP The only record I am storing is the initial closing stock, the rest is calculated. Unfortunately I can't send you the DB as the data is sitting in SQL tables. I am wondering whether I stick them all in the same field, I just clicked what plog meant now when he mentioned putting them in...

Update. Managed to get the 23rds closing stock with this SELECT f1.Year, f1.Week, f1.Date, f1.[Combined Dept], (SELECT TOP 1 f2.ClosingStock FROM qryRollingForecastWorking f2 WHERE f2.Date = (f1.Date-1) and f2.[Combined Dept] = f1.[Combined Dept]) AS OpeningStock, f1.Arrivals, f1.Orders...

I am really struggling with this and am starting to pull my hair out, I can't get my head around it. 23rd March is the start of my data. We will be closing on 5945. I need the 24th March Opening Stock to be the 23rds closing stock. Then I need the 24ths Closing Stock to add on the arrivals and...

Thanks MajP I've just managed to do something similar, nearly got it working

Thanks plog, I'll revisit them. When you say 'Arrivals and Orders should be in the same field' what do you mean by that? I need to see them in my output. How can I see them if they are in the same field? How do I sum the fields of a previous day?

This is just example data, ignore the fact that the closing stock doesn't tally up to the opening, arrivals and orders lol Just need to know how to get the closing stock from the previous row

Hi I need to get the closing stock from the previous day for the same department. See below example. So for department 111 I need the closing stock for the previous due date. This then becomes the opening stock for that day, hope that makes sense At the moment I have manually done it, but I...

Got it! It was to do with the date formatting. So I had to amend the query to this DAvg("Orders","Sheet1","[DueDate] Between #" & Format([DueDate]+1,"yyyy/mm/dd") & "# and #" & Format([DueDate]+10,"yyyy/mm/dd") & "#")

Could it be to do with the date formatting? i.e. 01/04/2021 (1st April in UK formatting) becomes 04/01/2021 (4th Jan in USA formatting) If so how can I get around this?

Its not quite perfect. Not sure what its doing. See below This is what Excel says I should get, in yellow. The orange ones are what match to Access And this is what Access is giving me This is the Access query I can't figure out why its so different. Anyone able to help at all?

Thanks plog, this looks like it has worked perfectly. Will try it more during the day. Thank you

Thanks both. Have tried that, but its coming out blank. See below

Thanks Bob. The result will be used in a query. The closing stock will then divide by this average to give the approx number of days stock DAvg() looks good, but how can I tell it that it needs to be 10 days after the date on the current row?

How can I get Access to get an average of the next 10 days orders. See example picture. So todays date is the 20-Mar as highlighted in orange. I need to get the average of the next 10 days orders (21st to 30th March), the ones highlighted in Green. To give me the figure in Blue. But I also need...

Ignore that, I figured it out, I was point to the wrong table. Thanks for your help

jdraw, thanks for this. I have tried it but I am getting error Run-Time Error '3219' Invalid Operation And its point to rs2.AddNew when you debug

This question has been asked before so apologies for duplication, mine is slightly different though. I have a table with a few columns in, one column has codes in it, split by commas. I need to split this column into separate rows, with the rest of the data. Example below, just a few columns...

In the first column there are numbers, 4049929726987 and 4049925626325, I want to get the latest entry for those numbers and the corresponding, A, B, C, etc columns SQL in my Access Query is SELECT Number, Supplier, Country, Max(ExpiryDate), A, B, C, D, E, F, G FROM tbl_SupplierNumber GROUP BY...

How can I get the latest date and the relevant columns in an access query. My Data is in a similar format to this. Number Supplier Country ExpiryDate A B C D E F G 4049929726987 W RSA SA 19-Jul-19 No No No No No No No 4049929726987 W RSA SA 19-Mar-19 Yes Yes Yes Yes Yes No Yes 4049929726987 W...