Kamis, 15 September 2022

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
_._

Tidak ada komentar:

Posting Komentar