Stumped!

[JRB]

Hello Everyone, this is my first time posting here. I am having a problem with subdata sheets (One word or two? Sorry if I got it wrong). I will try to keep his a short as possible. It is similar to the last thread by Rabu5, but extended more.

I have a table with many columns for each of my companies. One of these columns is called Total. Also in the Companies table I have a subdatasheet that has 3 columns, ID, Company and Amount. There are numerous listings in each subdata sheet. Company in the subdata sheet is a foriegn key to the main table Companies.

My problem is this. What i would like to do it to total the subdata sheet Amounts and run some sort of update query to update the column Total in the main table.

The problems are A) I dont know how to do this, and B) I used the foriegn key idea for the subdata sheets, so there is a seperate table where all this data is stored. Here, there is a unique ID, and many duplicate values under companies (due to the numerous listings in the subdata sheet for each company).

Please Help any way possible!


Thank you.

 

[cogent1]

The real question is why you would want to store a Total in your table. Each new entry in your subdatasheet would necessitate a completely unnecessary update in your main table.

Totals can be calculated at will using current data, in forms queries or reports. So why store something which constantly changes?

ONE company has MANY transactions, so there should be a relationship between your tables that reflects this. That's not a fault, that's how it's done!

 

[JRB]

Thank you for your help. However, the problem lies in the fact that when i open up my subdatasheet table, and try to sum amounts, I can only get an aggregate total for all companies. I would like to get an individual total for each company (as each company has 2 or more amounts in it). I am relativly new to Access so I don't know how to link the subdatasheet table to give me the aggregate for each company, then run a query to display that aggregate. Please Help!

ID Company Amount
1 A 100
2 A 200
3 B 200
4 B 200
5 B 200

I would get a total of 900 when I try to sum the aggregate amount, when instead I would like some way of showing:
A - 300
B - 600

The reason why I wanted to put the aggregate back on the table, is because the table is sorted by company name (the primary key) and it makes data reading easier for my purpose. However, if it cant be done, a query works well.

Thank you for any help you can give.

 

[cogent1]

I understand what you are trying to do, but you can easily present the data in your tables ordered and totaled as you desire by means of queries. The subdatasheet simply shows you a list of related records for each customer. A totals query can show you all customers ordered by name, value of orders. the top 10 customers, what you will.

To get an aggregate total for each customer, create a query using the ONE table (Companies) and the MANY table(Transactions) joined between the Company Primary Key and the Transactions Foreign key. (This join should already exist) Use the Show Totals option. Group by Customer and Sum the transactions.

If your subdatasheet is showing all the transactions correctly for each company, your relationships are right, don't worry about that aspect.

 

[JRB]

Again, cogent1, thenk you for your help. I have followed your advice an created a query, with companies from the ONE table, and amounts from the MANY table.

My last question to you all (and sorry if I am being a bother) but once I get to this point, I seem to have trouble sorting by company name and after sorting, finding the total for each of the individual companies (the total being the sum of all the MANY amounts). I have 31 companies and would like 31 sums. However, with my brilliance, I can only get it so that I have one lump sum of all companies. Do I need to use the criteria option? When it shows sort all I see are ascending and decending.

Again thank you all for any help. Last time i disturb.

With appreciation.

 

[cogent1]

Please don't feel you are being bothersome. If I didn't want to help, I wouldn't volunteer my services.

I don't quite understand what you have done, but it seems that you haven't created a TOTALS QUERY.

In Query design view, there is an option under the VIEW menu of show totals . Selecting this gives an extra row to the design grid which contains options like Group by, Sum, Expression, Where and others.

Selecting Group By for the companies ensures that all values are aggregated by company, and selecting Sum for amounts will give the aggregate totals for each company.

See the attached sample

 

[JRB]

Thank you so much for your help, cogent. I really appreciate it.
Everything worked out...I wasnt aware of that sum button. Have a great day!