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
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
Last edited: