Do I need a query or not?

[kstarnes]

I don't know anything about databases, but I'm learning quickly...in short, this is what I have.

A situation where a county council has many jobs that it contracts out, so there are lots of contracts which constitute individual records on a table. I have a form that is used to enter new records and one to update them by searching for the correct record. It has information like the Contract, contractor, date etc. Then I have a sub-form that is responsible for payments. Its correctly linked (hurray!). The sub form is designed to have multiple entries reflecting multiple payments made on each contract in a one to many relationship. So far so good.

However, now I want to format one of the fields in my sub form to total all of the previous entries in one of the other fields....so that each time a new payment is made, the new cumulative amount is reflected in that field. I need for the payments department to be able to quickly enter the amount of the cheque they are about to release and have the cumulative amount show them how much they would have paid on that contract to date to ensure that they don't overpay contracts. What is the best/simplest way to go about this?

 

[Luka]

Make a new field in your subform and put it in subform footer (I hope that your subform is datasheet type).

In control source of that field type =Sum(NameOfQueryField) or if you have multiple fields =Sum([Field1]*[Field2]) ... Dont use field names in formula, it wont work, you have to use name of the query field!

Now go to you main form and create another field. Go to control source of that field and click ... button. New form will open. Select your total field in your subform.

Your main form and subforms must be linked. Otherwise the results of sum will be wrong.

For additional info search this forum, its quite common question.

 

[kstarnes]

Thanks for responding so quickly Luka and for bein so specific, its often hard to understand responses to posts as they leave out information that someone more experience would know.
I am a little confused about some things

1)I don't think I have created a query yet...unless that is what I'm actually doing by creating a subform. I'm a bit confused about queries; if a query is an object that finds information about multiple records then would I be querying to find the multiple payments made in my subform? I was under the impression that as these records were linked to the contracts in the main form that each of the entries in the payments subform was only adding lots of information to a record, rather than creating more records all linked to my master records in the mast form. Do I have this wrong?
2) All of the information for the total is actually found in the subform-The main form only has information about the name of the contract, and who the contractor is. Its then linked to my subform by a serial number. Do I actually need to create another subform for this function?
Thank you!

 

[kstarnes]

Thanks for responding so quickly Luka and for bein so specific, its often hard to understand responses to posts as they leave out information that someone more experience would know.
I am a little confused about some things

1)I don't think I have created a query yet...unless that is what I'm actually doing by creating a subform. I'm a bit confused about queries; if a query is an object that finds information about multiple records then would I be querying to find the multiple payments made in my subform? I was under the impression that as these records were linked to the contracts in the main form that each of the entries in the payments subform was only adding lots of information to a record, rather than creating more records all linked to my master records in the mast form. Do I have this wrong?
2) All of the information for the total is actually found in the subform-The main form only has information about the name of the contract, and who the contractor is. Its then linked to my subform by a serial number. Do I actually need to create another subform for this function?
Thank you!

 

[Luka]

1) If you created form than you created query too, i sure hope soo. See record source in form options. if the field is empty then you need to create one. You can do it directly from form or

go to main access form on left site you have menu (Tables, Queries, Forms,...) In Queris you can create query, save it and connect it to form. I recommend that you do it from form. It's true what you wrote about form/subform.

2) You dont need to create extra subform. Use current one and insert one more field.

Can you post your db?

 

[kstarnes]

Thanks for the clarification. I have tried attaching a zip file and it continues to fail.

 

[Rabbie]

Thanks for the clarification. I have tried attaching a zip file and it continues to fail.

If it is failing to attach because it is too big. Strip out any unnecessary parts and only leave in enough data to show your problem. Then compact your DB and ZIP it. It shoould attach then

 

[kstarnes]

Actually, I think its our fire wall as the size of the file is fine.