Calculate monthly consumption, based on selection in combo box

[anb001]

In a table/query I among other things update the consumption of fuel, water etc., every day.

I then have a form, where I would like to see the montly consumption, the daily average etc (see attached).

The idea is that I can choose the year from a combo box, and then each monthly section will be calculated and filled out. But only months which are completely finished should be shown. If we are in e.g. mid March 2011, then only boxes for January and February 2011 should be filled out on the form.

Can anyone point me in the right direction, on how to do this?

/Anders

 

[SpentGeezer]

I would use a pivot table. If you already have your table, just go to Form > New > AutoFormivot Table wizard. If you need further help with pivot tables/charts there is a lot of information out there & also have a play around with the wizard.

 

[anb001]

I have looked a bit into the pivot thing, however it's not quite what I'm looking for.

I have updated the screenshot of the form in my first post, showing all months. I have also attached a screenshoot of part of the qry containing the data for the frmConsumption.

When choosing the year in the combobox, then the qry should only show records with a date in that year. Months which then are 'over', should have the data added together and shown in the respective txt boxes on the form.

I guess all txt boxes on the frm should have some sort of formula made in the expression builder. If 'month is over', then records for that specific field to be added. If 'month is not over', then txt box to be empty.

Is this possible??

/Anders

 

[vbaInet]

Shouldn't this sort of thing be done in a report?

What version of Access are you using?

 

[anb001]

Guess it could be done in a report as well. However, I chose a form which the user can open to see the consumption, and then close again when done. Personally I believe it to be easier/better than a report.

Well I'm using Access 2007 but the database have work in Access 2003 (work pc).

/Anders

 

[vbaInet]

On a form:

You need 12 subforms (obviously one for each month)


On a report:

You need 1 subreport - with main report set as a 3-column report. The year selection would have to be made on a form though.

Still want to go with the form approach?

 

[anb001]

I'd actually still prefer the form

 

[vbaInet]

Ok

Create one form which you will use as the template for the other forms, and lay it out like you have done in the Consumption.jpg image. Call this form frmCons1.

How are you going to get the Average Cons. and Total PW Made values?

 

[anb001]

Ok, done the first part.
the Average Consumptions will be each type added through the month, and then divided with number of days in that month (it's the average used per day)
Total PW made is Total Potable Water made (we make our on fresh water with a fresh water generator). DW is Drill water and FO is Fuel oil.

 

[vbaInet]

Don't create the other forms yet.

Drop a called txtMonth and type =1 as the Control Source. When it's time to create the other forms you will need to change that to the corresponding month numbers. Where, 1 corresponds to January, 2 for February ... etc.

In your query, you need to create two alias fields, one to return the Month of the date field and the other to return the year. So:

MonthOf: Month([DateReport])

YearOf: Year([DateReport])

Save the query and use it as the Record Source of the form.

So you didn't mention how Total PW Made is actually calculated?

 

[anb001]

All done

Oops, my mistake. Seems I just explained what PW made was. The Total PW Made is just the PW Made record for each day, added together throughout the month.

/Anders

P.s. Sorry for the slow replies yesterday. I'm relying on a satellitte connection to the internet and same is rather unstable at times.

 

[vbaInet]

Groovy! Looks like we can get all the data from that query easily. The only thing we need to work out is Average Cons.

So, I will take the Average Cons for PW for example. This will be the Control Source of that textbox:

=[SumOfPWUsed] / DateSerial([Forms]![FormName]![YearComboboxName], txtMonth + 1, 1 - 1)

Notice I called that SumOfPWUsed because I'm hoping you would have performed a Sum() on all of the fields in the query, excluding MonthOf and YearOf?

Replicate the code to the other Average cons textboxes.

Also, the form I mentioned you should create I meant just create one that will represent a month, not the whole gird as you had it in the image.

 

[anb001]

I used the formula for the Average PW consumption. I have used the cboYear on the main frmConsumption form for the calculation. And the formula is placed on the respective txt box on the frmCons1. Is that correct?

This i what the formula looks like:

=[SumOfPWUsed]/DateSerial(Forms!frmConsumption!cboYear;[txtMonth]+1;1-1)

The result, though, is not correct. So far I only have four records in the database (for testing only). The sum of PW used is 118 m3, which for entire January would give 3,8 m3/day. However, the result says 0,0008 m3/day. It could be that I have done something wrong, though

Yes, the Sum is used in the query.

The frmCons1 looks like the attached screenshot. Guess that's what you meant??

 

[vbaInet]

Oops...

=[SumOfPWUsed]/[COLOR=Red][B]Day([/B][/COLOR]DateSerial(Forms!frmConsumption!cboYear, [txtMonth]+1, 1-1)[COLOR=Red][B])[/B][/COLOR]

The form looks like what I envisaged. I can see that tekst287 (i.e. txtMonth) appears to be on the main form but should be in frmCons1 or do you just have it there for testing?

You won't get the correct result just yet because the query hasn't been filtered yet.

In the After Update event of cboYear, put this:

    Dim ctl As Control
    
    For Each ctl In Me.Detail.Controls
        If ctl.ControlType = acSubform Then
            With ctl.Form
                .Filter = vbNullString
                .Filter = "YearOf = " & Nz(Me.cboYear, 0) & " AND MonthOf = " & .txtMonth
                .FilterOn = True
            End With
        End If
    Next

You will need that code in the Load event of the frmConversion too.

Once you've tested it and it works, make a copy of the subform 11 more times and make amendments to txtMonth and month label controls.

Try to figure out this:

The idea is that I can choose the year from a combo box, and then each monthly section will be calculated and filled out. But only months which are completely finished should be shown. If we are in e.g. mid March 2011, then only boxes for January and February 2011 should be filled out on the form.

 

[anb001]

Ok, I might be a little confused now

1. I have attached a screenshot of the main form (frmConsumption) + subform1 (frmCons1). The main form still shows the initial layout. I assumed that I would keep the subforms invisible, and the data would show in the main form's text boxes? Am I wrong on that?? If not, how will I get the data transferred from the subforms to the mainform?

2. As you can see in the subform, the data shown is only from the first record in January. Nothing has been added (?)

/Anders

 

[anb001]

Ok, I think I got it now

I have set the subforms to visble=no, and then I use below for getting the data to the main form:

=IIf(IsError(frmCons1.Form!txtPWAverage);" ";frmCons1.Form!txtPWAverage)

There might be a better way, but this seem to work.

And regarding the 'SumOf', then I just had to remove the 'DateReport' field from the query.

Anyway, thanks a lot for all the help. I'll do what I can to figure out the rest

/Anders

 

[vbaInet]

I have set the subforms to visble=no, and then I use below for getting the data to the main form:

=IIf(IsError(frmCons1.Form!txtPWAverage);" ";frmCons1.Form!txtPWAverage)

There might be a better way, but this seem to work.

Why do you need to get the values from the subform to the main form?

 

[anb001]

In order to see all info on the same form (as the schreenshot in the first post). I tried to align the subforms, however didn't get a proper result, hence this way of doing it

 

[vbaInet]

Create the form with the 12 subforms in it and post the db. I will align them.

Obviously do this on a copy.

 

[anb001]

Ok, will do. I have some connection problems, though, and it won't let me upload anything. However, I will keep trying until I succeed.