Using previous month's "closing" value for this month's "opening"?

[Catherine B]

Using previous month's "closing" value for this month's "opening"?

Hello,

I've been looking through the archives and learnt a fair amount about date manipulation, but am still a bit stuck. I'm sure there is a way to do this and would appreciate any ideas:

Basically I am monitoring changes in Fuel level over months. For each month, I have a variety of readings, each on a different table, including sales from that tank, delivery in to it, and most importantly a closing dip reading taken at the end of the month.

In order to figure out the change in Fuel level over each month, you need an "opening" figure : this is taken to be the closing dip value of the previous month. And this is where I'm getting stuck!

I am writing a query to generate new table which combines all the relevent values. I have managed to link them through the date field, even though the specific date is not identical what matters is that it is the same month. Effectively I am trying to add a new 'column' showing the opening value, where opening value = previous months closing value for this tank.

I noticed there is a post talking about previous month data <a href="http://www.access-programmers.co.uk/forums/showthread.php?t=39708&highlight=previous+month">here</a>, which gives me some ideas but I'm not sure how to go about putting this into the SQL query. I can't use Date() because I'm interested in more than just previous to THIS month - the data spans back over a year!

I'm still learning Access so this might be a simple question - if so please point me to the relevent parts of the help file!

many thanks
Catherine

 

[gromit]

Hi Catherine -

I might step back and look how you are structuring the data for this task. Although creating a table for each month may provide you some of the answers that you seek, it can make it very difficult to extract other information (for example, what is the total sales over the course of a year or even over the life of the tank).

Is the closing dip value any different from a reading at any other particular time? In my mind, it may make sense to just look at all the dips as sequence of readings, each having a date and an amount. If this works, then you could just look at the reading having the Max(DateTaken) as the starting point for the next calculation. (Which is what I would suggest anyway as the starting point for your Make Table approach).

- g

 

[Catherine B]

Hello, thanks for your response! Sorry I think I've been a bit confusing. The different tables I have are not one for each month, but rather one for each type of information. So, one table shows each tank with the closing dips for all the months I have, one table shows the total sales for each tank, one table has delivery.. This is dues to how I receive the data eg:

[tank] [date] [closingdip]

[tank] [date] [total sales]

[tank] [date] [delivery]

So in this case it is effectively a sequence of readings like you say
....

What I'm trying to make is a new table combining the information but also adding some new columns (in caps):

[tank][date] [OPEN] [close] [delivery] [sales] [BOOKSTOCK]

Where [OPEN] = [close] for [date]-1month (ignoring the day value)

This last bit is where I'm having problems, I'm not sure where or how to incorporate it into the SQL query. Especially when it comes to going from Jan back to December..

 

[Catherine B]

Make table query?

Hi again.. I just had a look at the 'make-table' query in Access Help - this sounds like it is what I want to do, the problem now is when I try and do from "SQL view" of the query back to "design view" I get an error message

"Microsoft Access can't represent the join expression month(closing.DATE)=month(delivery.DATE) in design view."

The odd thing is the query WORKS when I run it, so I'm not sure what is up with Access! How would I do a 'make-table' query using just SQL?

 

[neileg]

If you create a totals query based on the closing dip table and set the function to Max on the date field, this will give you the latest dip. Select queries will give you the transactions from the sales and delivery tables. You can then join these on the tank field into one query.

You shouldn't be trying to put this data in a new table, but rather on a form based on the new query.

The structure would be easier if your transactions were all in the same table. Since your tables have all the same fields you could combine them with just one extra field for the transaction type. If you made the deliveries a +ve value and sales a -ve value, a simple totals query woul give you the overall position. I think I would keep the dip records in a separate table, since these are 'nsapshots' of the quantity on hand rather than transactions, but you could combine them too.

 

[neileg]

Hi again.. I just had a look at the 'make-table' query in Access Help - this sounds like it is what I want to do, the problem now is when I try and do from "SQL view" of the query back to "design view" I get an error message

"Microsoft Access can't represent the join expression month(closing.DATE)=month(delivery.DATE) in design view."

The odd thing is the query WORKS when I run it, so I'm not sure what is up with Access! How would I do a 'make-table' query using just SQL?

Access doesn't like the fullstop in your field names.

See my other post about not creating a new table - you are needlessly duplicating data.

 

[Catherine B]

Access doesn't like the fullstop in your field names.

See my other post about not creating a new table - you are needlessly duplicating data.

aah : those are not field names as such, closing = table name, which has a field called DATE.

I'm trying to get my head round how the SQL query part works in Access - I've noticed sometimes it puts things in square brackets eg "SELECT [closing].[DATE]" or "SELECT closing.CLOSE AS [OPEN]". What is the difference between putting something in [] or not? Apologies if this is basic question, but once again the Help file isn't being very helpful

Thanks for clarifying about the new table - I see what you mean.

hmm

What I'm trying to end up with here are two values for each tank for each month. One is the [bookstock] - ie what we expect to find in the tank, this is [opening reading](=previous months closing reading) + [delivery] - [sales]. I then compare this to the ACTUAL [closing] value for that tank for that month to get the [variance]. I don't need to do any totalling because the values I have for sales, delivery are already one totalled value for each month.

[tank][month][closing][bookstock][variance][sales]

It's a straightforward calculation, my problem is just how to get a handle on the [opening] value (get the closing value from previous month) to use in the bookstock calculation.

I get the feeling it is something obvious I just can't see!

 

[neileg]

OK.

Date is a reserved word in Access and you should not use it or any reserved words as object names. This may not affect your current problem, but sooner or later it will drive you mad when things start to go wrong!

Do you record daily dips or do you only record the month end dip? If you only record monthly dips, then using Max in a query or DMax() will give you the last recorded value. If you want the routine to be universal so you can rework previous variances, then this won't do. What date is recorded against the month end dip? If, say, you only work weekdays and the month end dip is really the nearest working day to the month end, this will affect any answer.

 

[Catherine B]

Hi Neil, thanks for this

OK.

Date is a reserved word in Access and you should not use it or any reserved words as object names. This may not affect your current problem, but sooner or later it will drive you mad when things start to go wrong!

right, that may well be the problem. Thanks for pointing this out! I will go and change them to something more unique

Do you record daily dips or do you only record the month end dip? If you only record monthly dips, then using Max in a query or DMax() will give you the last recorded value. If you want the routine to be universal so you can rework previous variances, then this won't do. What date is recorded against the month end dip? If, say, you only work weekdays and the month end dip is really the nearest working day to the month end, this will affect any answer.

* I only record the month end dip; on the last day of the month. Other values eg total delivery value might have a different day connected to it - the last day of the month there was a delivery - but there will still only be one value per month for each tank.
* I do want a universal routine rather than the last value, or just the value for this month. This is so I can look at monthly variance values for each tank over the last X months.

Currently the only way I can think of getting to the 'opening' value is making a new table which is a copy of the closing one but with one month taken of the date. Then just joining them together as usual and doing the calculation. I don't like the idea of duplicating data though, especially since i have so much of it!

 

[neileg]

Right, so if you record the dip on the last day of the month, you can get this date by going back from the first day of the month you are interested in. Say you are working on April 2005. Then you need the dip from 31 March 2005. I assume you will be providing a user form to capture the month of interest. You can 'build' the date 1 April 2005 from this selection and then use DateAdd() to subtract 1 day from this. Use this as the parameter to return the dip value from your table.

You seem to be making a mistake in thinking that all your calculations need to be done in a query or table. They don't. I suggest you use a form to 'assemble' all the data and then perform the bookstock calculation and the variance in the form.

 

[Catherine B]

Right, so if you record the dip on the last day of the month, you can get this date by going back from the first day of the month you are interested in. Say you are working on April 2005. Then you need the dip from 31 March 2005. I assume you will be providing a user form to capture the month of interest. You can 'build' the date 1 April 2005 from this selection and then use DateAdd() to subtract 1 day from this. Use this as the parameter to return the dip value from your table.

great, ok thanks neil.. I hadn't thought about going back to day 1, then subtracting a day, too caught up in how to go back a month

You seem to be making a mistake in thinking that all your calculations need to be done in a query or table. They don't. I suggest you use a form to 'assemble' all the data and then perform the bookstock calculation and the variance in the form.

right ok! thanks for pointing me in the right direction, I will go and investigate further...

all the best,
Catherine