Pulling totals from several forms

[kevin19]

Dear All

I need to pull the calculated values from approximately 10 forms into another form. This is a summary form that should have all the totals pulled from the other forms.
e.g. Form A has a textbox that reflects the sum of the amount. This is the total balance of form A.
Form B, Form C, etc. all have a total Balance.
Now, i need to pull all these totals into a summary form
Form A Total Balance: £x
Form B Total balance: £y, and so forth.

How and what is the best method to approach?

I have tried using Forms![Formname]![Total] to get the data. This necessitates the need to hide all these forms and I ended up with blank forms, etc.
Even so, the total sometimes appear and sometimes it does not. so it is very unstable.

i hope someone can help asap!

Thank you.

 

[Beetle]

Have you considered using a Totals query to sum the values directly from the underlying record sets, then basing your summary form on that query?

 

[boblarson]

Forms do not store data. They display data for viewing or editing. Queries, as Sean has pointed out, are for gathering data, and can include calculations. So I think you are going down the wrong path and need to take a step back and create one, or more, queries that pull together the data you want.

 

[kevin19]

Hi Sean and Bob!

I have been trying to create a query for the totals but it merely works for each table. When I add a few tables in and do the sum totals i get wrong answers.
say, Table A total is 1000 and table B total is 3000
When I do a total query on table A alone, I get 1000 but when I add table B into the same query the table A comes up with a different total and table B total is also incorrect.
I do not know why?

Thanks for your help!

 

[boblarson]

Hi Sean and Bob!

I have been trying to create a query for the totals but it merely works for each table. When I add a few tables in and do the sum totals i get wrong answers.

That would be because you have not set it up properly. You can create the totals for table A and then another to do the same for table B and pull them together with a Union query.

 

[kevin19]

Thank you All!

I have created the queries as Bob has taught me. True enough I get the sums for all the tables correctly.
But I now face the problem of a blank form. The form appears only when the sum totals have a value. If some tables have a sum total and some tables have no sum totals, the form appears blank.
How can i make the form appears eventhough the query returns no results when certain tables have yet to get data?

cheers!

 

[Beetle]

Can you provide any details on specifically how you created the query (or queries - I'm not sure if there's one or several). If you can upload a sanitized sample file, that would work also.

 

[kevin19]

Hi Sean
The .rar file after compact & repair is still 2.8mb. Any other way i can forward to you?
thanks for your help.

 

[Beetle]

Try importing only the relevant tables and query(s) into another db, leaving out everything else, and upload that.

 

[kevin19]

Hi Sean!
Will try to do what I can to upload within the next few days!
Thanks for your help!

regards

 

[kevin19]

Hi Sean!

Please find attached file for your perusal pertaining to the above subject!
Sorry there was a delay as I was assigned somewhere else.

View attachment db2.zip

Thanks and regards!

 

[Beetle]

OK, so there are a couple of issues here.

First, the sample file contains no representative data, so I'm unable to determine what exactly it is you want to summarize or how.

Second, you've got normalization problems with your table structure;

• You're storing calculated values in your tables.
  
• You're storing data as field names. For example, you track monetary denominations, which is fine, but you're doing so using fields named Fifty, Twenty, Ten, Five, etc. This data should be stored in a related table in a field named Denomination (or something similar).
  
• You're storing data redundantly. For example, you're storing RestID and RestName in four different tables (in the sample file, could be more in your actual file, I don't know). The only value that needs to be stored in a related table is the PK value (in this case RestID) as a foreign key.

If you plan on using this application on a regular basis, I would suggest that you do some research on relational database normalization. There may be a way to query what you have now to get the results you want, but you're building on a faulty foundation. The whole thing is likely to come crashing down at some point, and the longer you put that off, the worse it will be when you ultimately have to re-design the database.