Sum Different Record Values With Same Descriptive Data (1 Viewer)

databasedesigner18

New member
Local time
Today, 02:11
Joined
Oct 10, 2018
Messages
6
Hi.

I have created a database to track invoices across many different contracts. Each contract has multiple tasks over at least 3 or 4 years. Each task under a contract has invoices that are submitted for payment. Each invoice(attached to a contract and specific task, has the same Contract/task#, Dept, and Description. They differ only in Net Amount,which is the gross dollar amount less retainage amount.

I am trying to combine(& sum) all the net amounts of invoices that share the same contract/task combo.

I hope this is not confusing
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:11
Joined
May 7, 2009
Messages
19,169
Crete a new Query using Aggregate (Total) query.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:11
Joined
May 7, 2009
Messages
19,169
create new query. select your table. add the contract/task and the net amount field by double clicking them. in the ribbon click Total. change the net amount from Grouo to Sum.
 

databasedesigner18

New member
Local time
Today, 02:11
Joined
Oct 10, 2018
Messages
6
Good morning arnelgp,

Part-1
I did create a query before. However, I do need to provide a little more detail. Each contract/task may have multiple invoices per contract, meaning anywhere from a few records to at least 30. Additionally, the records could increase based on how many different analysts are tracking each contract(with multiple subsets of tasks(hence my "contract/task" field.

Each record has two dates that tell me when I should update the total expenditures for each contract with new information. They are the date when invoices are received from a contractor and the date the AP, accounts payable, receives the invoice to pay from the analyst. For example, all invoices that have a Date Received and a Date to AP greater than 7/31/2018. Since there is a running total of Contract expenditures, I only want to add up all recent invoices that have gone to AP and add that to the
running expenditures total. I can't figure out how to combine the most recent contract/task invoices when there are more than one.

Part 2- How should I create a query that will automatically update the expenditures every time a new invoice goes to AP, for reporting purposes.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:11
Joined
May 7, 2009
Messages
19,169
1. the general rule is not to save the running contract expenditure on the table but to calculate it on the query.

2. you need an Update query, calling it from VBA. the condition is if the AP date has Changed, update the expenditure.


maybe you can share a little bit of your table, using excel as an example.

give more detail on how the process goes.
 

databasedesigner18

New member
Local time
Today, 02:11
Joined
Oct 10, 2018
Messages
6
Here is a sample of the Query I created that shows the issue of two invoices w/same contract/task not combining. This is the same query I would use to update expenditures when new invoices are sent to AP.
 

databasedesigner18

New member
Local time
Today, 02:11
Joined
Oct 10, 2018
Messages
6
How do I paste an excel sheet sample without it turning to giberish when I send it.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:11
Joined
May 7, 2009
Messages
19,169
zip it send it as attachment. use the Post Reply button.
 

databasedesigner18

New member
Local time
Today, 02:11
Joined
Oct 10, 2018
Messages
6
The sample of the Query result is attached.
 

Attachments

  • Sample Query result (2).zip
    7.4 KB · Views: 50

Users who are viewing this thread

Top Bottom