Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-10-2018, 05:11 AM   #1
databasedesigner18
Newly Registered User
 
Join Date: Oct 2018
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
databasedesigner18 is on a distinguished road
Smile Sum Different Record Values With Same Descriptive Data

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

databasedesigner18 is offline   Reply With Quote
Old 10-10-2018, 05:17 AM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,730
Thanks: 55
Thanked 2,139 Times in 2,050 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Sum Different Record Values With Same Descriptive Data

Crete a new Query using Aggregate (Total) query.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 10-10-2018, 05:20 AM   #3
databasedesigner18
Newly Registered User
 
Join Date: Oct 2018
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
databasedesigner18 is on a distinguished road
Re: Sum Different Record Values With Same Descriptive Data

Ok. Thank you. How do I do that?

databasedesigner18 is offline   Reply With Quote
Old 10-10-2018, 05:33 AM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,730
Thanks: 55
Thanked 2,139 Times in 2,050 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Sum Different Record Values With Same Descriptive Data

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
databasedesigner18 (10-11-2018)
Old 10-11-2018, 04:20 AM   #5
databasedesigner18
Newly Registered User
 
Join Date: Oct 2018
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
databasedesigner18 is on a distinguished road
Re: Sum Different Record Values With Same Descriptive Data

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.
databasedesigner18 is offline   Reply With Quote
Old 10-11-2018, 05:18 AM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,730
Thanks: 55
Thanked 2,139 Times in 2,050 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Sum Different Record Values With Same Descriptive Data

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 10-11-2018, 05:42 AM   #7
databasedesigner18
Newly Registered User
 
Join Date: Oct 2018
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
databasedesigner18 is on a distinguished road
Re: Sum Different Record Values With Same Descriptive Data

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 is offline   Reply With Quote
Old 10-11-2018, 05:43 AM   #8
databasedesigner18
Newly Registered User
 
Join Date: Oct 2018
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
databasedesigner18 is on a distinguished road
Re: Sum Different Record Values With Same Descriptive Data

How do I paste an excel sheet sample without it turning to giberish when I send it.

Last edited by databasedesigner18; 10-11-2018 at 05:46 AM. Reason: It did not show properly
databasedesigner18 is offline   Reply With Quote
Old 10-11-2018, 05:51 AM   #9
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,730
Thanks: 55
Thanked 2,139 Times in 2,050 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Sum Different Record Values With Same Descriptive Data

zip it send it as attachment. use the Post Reply button.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 10-11-2018, 06:04 AM   #10
databasedesigner18
Newly Registered User
 
Join Date: Oct 2018
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
databasedesigner18 is on a distinguished road
Re: Sum Different Record Values With Same Descriptive Data

The sample of the Query result is attached.
Attached Files
File Type: zip Sample Query result (2).zip (7.4 KB, 4 views)

databasedesigner18 is offline   Reply With Quote
Reply

Tags
access 2013

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Descriptive text in the textbox EW1587 Forms 0 02-01-2012 05:46 PM
Want combobox to show descriptive value not ID snu Forms 3 05-06-2010 09:58 AM
Get descriptive value instead of FK with joiner tables snu Reports 10 04-30-2010 09:40 AM
Descriptive text in the textbox Henley12 Forms 1 09-04-2009 11:27 AM
How to view descriptive codes in a multiselect field PolarBear Queries 1 04-21-2004 11:48 AM




All times are GMT -8. The time now is 06:38 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World