Recent content by SiGill

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...