Re: [MSAccessProfessionals] Update Subtotal row with cost center from previous line

oh. Duane. 

I like that a lot. Thank You so much

I will put that into production on monday.

Jim Wagner

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Duane Hookom <duanehookom@hotmail.com>
Sent: Thursday, September 15, 2022 10:42 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Update Subtotal row with cost center from previous line

 

Hi Jim,

You could simply create a totals query that filters out Subtotal:

SELECT [Cost Center], Sum(Charges) AS SumOfCharges, Sum([Usage Charges]) AS [SumOfUsage Charges], Sum(Taxes) AS SumOfTaxes 

FROM tblCellCharges 

WHERE [Cost Center] <>"Subtotal"

GROUP BY [Cost Center]; 

Or delete all the records with Subtotal and then create a totals query. 

Being a bit OCD about normalization, I would normalize the data with a union query like:

SELECT [Cost Center], "charges" As ExpenseType,  Charges As Amount

FROM tblCellCharges

WHERE [Cost Center]<>"Subtotal"

UNION ALL

SELECT [Cost Center], "Usage",[Usage Charges]

FROM tblCellCharges

WHERE [Cost Center]<>"Subtotal"

UNION ALL

SELECT [Cost Center], "Taxes", Taxes

FROM tblCellCharges

WHERE [Cost Center]<>"Subtotal";

Query1

Cost Center	ExpenseType	Amount
CC12345 PG11233	charges	$66.54
CC23456 PG23456	charges	$100.00
CC23456 PG23456	charges	$10.00
CC23456 PG23456	charges	$20.00
CC23456 PG23456	charges	$30.00
CC23456 PG23456	charges	$40.00
CC12345 PG11233	Usage	$0.00
CC23456 PG23456	Usage	$6.00
CC23456 PG23456	Usage	$2.00
CC23456 PG23456	Usage	$1.00
CC23456 PG23456	Usage	$3.00
CC23456 PG23456	Usage	$5.00
CC12345 PG11233	Taxes	$1.03
CC23456 PG23456	Taxes	$3.00
CC23456 PG23456	Taxes	$3.00
CC23456 PG23456	Taxes	$3.00
CC23456 PG23456	Taxes	$3.00
CC23456 PG23456	Taxes	$3.00

---

From:  Jim Wagner <josephwagner2@outlook.com>

To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] Update Subtotal row with cost center from previous line

 

Hello all,
I get a download from verizon with cell phone information. the data includes the cost center which is the account the phone is paid from. and other information like monthly access charges and usage charges taxes etc.
the spreadsheet has the cost centers grouped together with a subtotal for each cost center. Right now i copy data from the spreadsheet into the tables into the database. But I was trying to find a way of parsing out and getting the data to be aligned the tables are.

what i am trying to do is to create a query to get all of the subtotals with the cost centers. I am not sure how to get the associated cost centers to replace the word subtotal
I would like to get it to look like
CC12345 PG11233                             66.54               0.00                1.03                 
CC23456 PG23456                             200.00              17.00           15.00


I need to copy all of the subtotal information into a summary table but the data looks like below
Cost Center                     charges       usage charges     taxes
CC12345 PG11233             66.54               0.00                1.03
Subtotal                              66.54               0.00                1.03                 
CC23456 PG23456            100.00             6.00              3.00
CC23456 PG23456             10.00             2.00              3.00
CC23456 PG23456            20.00             1.00               3.00
CC23456 PG23456            30.00              3.00               3.00
CC23456 PG23456            40.00              5.00              3.00
Subtotal                             200.00              17.00           15.00


Is there a way of doing this in Access?

Thank You

Jim Wagner

_._