How to SUM only Selctive Records from a table? (1 Viewer)

[TomS1954]

I have a problem that is simple in Excel, but I am stumped how to do it in access.

Here is the table in simplified form:

[JobID] [ConsolJob#] [Job#] [FYr] [Month] [Customer] [TotalREvenue] [TotalCost] [GrossProfit]
1
2 [ABC1] [1234] [Fy17] [Feb] [Customer1] [1000] [800] [200]
3 [ABC1] [4567] [Fy17] [Feb] [Customer1] [900] [600] [300]
4
5

Large table of records.

Easy: I can manually tag select records that need to be added together and treated as one. I use a common, newly added field [ConsolJob#]

Easy: to write a Query that filters just the records with common [ConsolJob#] and they come out one record at a time for the filter.

Easy: make a form that does sum([TotalRevenue]) BUT I need these sums broken out for the common job number, not ALL the Queried records !

HARD Challenge: essentially For each [ConsolJob#] show the customer name, FYr, Month etch… but SUM the components and show me as if It’s just one record on the form! Then go on and same for the next group of common records…!

At the end of the day, I want a continuous form that lists out all the jobs. I think a simple separate form that lists out the job#'s that have been added together / consolidated by customer by Fy/Month.

Have already successfully done forms for showing customers, or jobs in a month on a record by records basis. Where I am stuck is now doing same for the clustered jobs.

Cheers
Tom

 

[Minty]

You have a query that consolidates the jobs, Use that as a basis of a query to provide a sum of just those jobs. You can then either link this back to your another query or use a subquery.

Using a sub query and display it once on your form. http://allenbrowne.com/subquery-01.html

 

[TomS1954]

Wow. That's a new one for me! - Subqueries.. Never heard of that. Will have to study the URL hard.

So I imagine, do this subquery thing, then link the subquery back to a form to display totals for each [ConsolidatedJob#] ?

If there are easier ways, let me know.

Many thanks!

 

[TomS1954]

Have done some experimenting on your subquery solution. Surely it has merit, but think that I need more handholding or experience with SQL to make this work.

Will continue to work this method but it's slow.

Hopefully there is another, more simple solution out there.

Thanaks