Monthly total from an unbound field in a continuous form

[ghard123]

I have a continuous form (one for each day of the month). On each day certain data is collected in several unbound fields which I would like to sum at the end of each month. I would like to generate a report from these totals.

I am having trouble doing this. Do the fields need to be bound??



Thanks.

[alansidman]

Firstly, why would you have unbound fields for data you wish to maintain? 30+ Tables/forms sounds like a normalization issue which will continue to confound you when you try to extract data. Look at this link for how to design a RDBMS so that you can extract data easily.

http://r937.com/relational.html

[June7]

If you don't save daily inputs to a table, how do you expect to retrieve data at end of the month?

Bind controls to fields and data saves directly to table or use code to save data from unbound fields.

A separate form for each day - kind of data collected varies each day? Like what?

As alansidman points out, sounds like normalization issue.

[ghard123]

Thanks for the advice. Due to a retirement i have inherited a work database which is about 15 years old. Not sure it should have been a database to start with but I now have the job of maintaining it.

[aytee111]

As the others are asking - if the fields on the form are unbound, does that mean that the user is entering them new every time? Or are they stored on a table? If they are stored on a table then the form is not needed in this discussion, create a query from that table(s) which displays all the fields that you need and then a your report based on that query. If the user is entering the data new every day then the report can be created pointing to the values in those fields.

[ghard123]

Each day the person in charge of the area has to submit a report which is the 'continuous form' I refer to. At the end of the day the user counts the number of times certain things occurred and places that number in the relevant box on the side of the form. At the end of the months those totals are summed and included in a monthly report.

Aytee111. Thanks for your reply. Can a report be generated from unbound fields on a form???

[aytee111]

Yes. Every field will have a control source of =Forms!formname!fieldname.

You are missing the point of a database, however, which is to store data.

[June7]

So you really have only 1 form for the daily data entry?

If you have to issue a report at the end of the month that summarizes daily entries, then you must be saving the daily entries to a table. Unless you are manually adding up daily data on a calculator and typing totals into the unbound form which would be a total waste of time and resources.

Build a report that references table or query or SQL statement as its RecordSource and apply filter to the report. Options for filter:

1. dynamic parameterized query

2. manually set report Filter property

3. code (VBA or macro) that sets WHERE CONDITION argument of OpenReport command

[ghard123]

I will try that. Thanks for your help with this.