Setting a field equal to a sum total query

[peterlee516]

Thanks in advance for your assistance.

I have a project table, invoice table, a cost center allocation table and many inbetween. The first 3 mentioned all have 1 to M relationships respectively.

On the project form I have a Total Paid field that I want to populate as users pay invoices. I have created a sum total query from the cost center allocationt table. I just don't know how to populate the Total Paid field with the value. The only solution I could come up with is to create a subform of the results of the query and shrink it down to a field size.

Unfortunately, it doesn't look like I could hide the subform heading. But most importantly I'm try to keep minimize the size of the database because I already have many subforms.

Sounds like an easy solution. I just don't know it.

Thanks.

 

[Ziggy1]

create a join query between the "sum total query" and the invoice table ( or is it the project table?), this would be the same logic as you use when you create the subform..you will link the related field...which is likely "USER".

 

[peterlee516]

It works but now all the fields in the project form are locked. Did I do something wrong?

Thanks.

 

[Ziggy1]

no you didn't do anything wrong, I had "Report" on the brain...which it would have worked for.

you can try a Dlookup to the Sum query ( instead of using the join)


watch the syntax....



=DLookUp("[TTLPaid]","tblCostAlloc","[USERID] = [Forms]![FrmProj]!USERID")


This should return the total for the userID currently displayed

 

[peterlee516]

Would that equation work if the tables are setup like this?

Project Table:
ProjectID
ProjectName

Invoice Table: (Project can have many invoices)
InvoiceID
ProjectID

Cost Allocation Table (One invoice can be broken out to many different cost centers)
TransactionID
InvoiceID
Cost Center
Amount

 

[peterlee516]

I figured it out. I created a sum query.

=DLookUp("[SumOfAllocationAmount]","qryInvoicedPB","[ProjectID]=[Forms]![tblProjects]!ProjectID")


Thanks for your help!!