Calculation in report to subtract one 'group' total from Grand Total

[klarae99]

I am working on an Inventory Database in Access 2003. I am working on a report that I could print when its time to file our State Sales Tax paperwork. The figures I need for this report are Total Sales(not including tax), the Amount of Sales Subject to Tax, and the Total Sales Including Tax, all gathered for a specific time period.

I have a query (qrySalesTax) to find all sales within a specified date range that also calculates the Total Sales and the Total Sales Including Tax. I have a report based on the Queary that is grouped by Category, then by Month, with totals for each grouping and a Gross Total for the entire date range.

What I would like to do is to subtract the Total Sales for the category Clothing from the Gross Total Sales to arrive at the Amount of Sales Subject to Tax.

I tried creating a seperate query to calculate the Total Clothing Sales and then including it in qrySalesTax So that the Total Clothing Sales were listed seperately in the query, but the queries 'overlapped' and I got cycling listings for the clothing transactions on the same lines as the other category transactions so I abandonded this idea. I also used the Total Clothing Sales from the sepearate query as a control source for an unbound box in the footer section of the Sales Tax Report but when viewed in preview the field had only #Error displayed.

Any advice for how to do this would be appreciated. As you can probably tell I'm relatively new to this program. Also I have no real understanding of code yet so if I need to use code to facilitate this I will need some very detailed guidance. Thanks for all your help with this!

[nico5038]

You're problem can only be solved by "Grouping" both queries on the same fields.
There might be a problem when for a certain Category no data is found (no sales).
This is "solved" by using a tblCategories that holds all and "outerjoin" that with the table holding the tax amounts. Making tblCategories "Master", will show zero's for the missing amounts.

Getting the idea ?

Nic;o)

[klarae99]

Nic;o)

My lack of training is shining through again. You suggest that I "grouping" both quearies on the same field. How do you group in a queary?

Just for clarification both quearies are based on the same fields, there is just a different criteria in the Category field. (I actually created a genaric queary to find all categories for all the fields that I wanted; then I saved as to create an exact copy. Then I modified the criteria in the Category fields one only clothing and the other not clothing. I also added the prefix CLO to all the calculated fields in the Clothing only query.

From my understanding of your reply, I would create a third query to base the report on that 'grouped' both queries on the same fields.

In response to your comments on the potential problems if a category has no sales, right now tblCat is where I am storing the Category and the Tax Rate Information so I don't know how I would join them in an outer join. It seems redundant to join a table to itself. My thought would be that since they are in the same table this 'join' already exists. Is that correct?

I really appreciate your help with this!

[nico5038]

GroupBy queries can be created by pressing the "E" looking button.
A new line will appear above the criteria holding a combobox with "GroupBy".
All "groupingfields" should have GroupBy, but your amount should hold "Sum".
Thus you'll get for each combination of the "GroupBy" fields the sum.
Only using Categories give a sum per Category, adding an additional field will "split" the summing further.
Just try to see the effect.

Often more data is needed, but keep in mind that always a value from the combobox needs to be selected (also called "aggregation function) like Sum, Avg, Max, First, etc.

Nic;o)

[klarae99]

Nic;o)

I should have made the connection (I think of that feature as the total feature).

I tried to combine my two queries into one query and group by sum in each of the common fields, but left the GroupBy in the fields that were unique to each query (the calculation fields). I got an expression to complicated error. Should I rename my calculation fields the same in both original queries. So that I can sum them as well? Do I need to include the category field from both queries or should I include this field from the table?

This is the first time I have tried this and am having a hard time getting my head around it all. Thanks for all your help!

[nico5038]

Can you post the SQL text of the queries so I can have a look ?

Nic;o)

[klarae99]

Nic;o)

Here is the sql for the qryClothingSalesTax

SELECT tblCat.Categories, tblCat.TaxRate, tblTrans.TransDate, tblTrans.Sold, tblTrans.SaleDiscount, Round([Price],2) AS CloTruePrice, ([CloTruePrice]-[SaleDiscount])*[Sold] AS CloGrossSales, [CloGrossSales]*[TaxRate] AS CloTaxCollected, [CloTaxCollected]+[CloGrossSales] AS CloGrossSalesIncludingTax
FROM tblEveInfo INNER JOIN ((tblCat INNER JOIN tblProd ON tblCat.CatID=tblProd.CatID) INNER JOIN tblTrans ON tblProd.Item=tblTrans.Item) ON tblEveInfo.EventID=tblTrans.EventID
GROUP BY tblCat.Categories, tblCat.TaxRate, tblTrans.TransDate, tblTrans.Sold, tblTrans.SaleDiscount, tblProd.Price
HAVING (((tblCat.Categories)="Clothing") And ((tblTrans.TransDate) Between [Start Date] And [End Date]) And ((tblTrans.Sold)>0));

And here is the sql for the qryNotClothingSalesTax

SELECT tblCat.Categories, tblCat.TaxRate, tblTrans.TransDate, tblTrans.Sold, tblTrans.SaleDiscount, Round([Price],2) AS TruePrice, ([TruePrice]-[SaleDiscount])*[Sold] AS GrossSales, [GrossSales]*[TaxRate] AS TaxCollected, [TaxCollected]+[GrossSales] AS GrossSalesIncludingTax
FROM tblEveInfo INNER JOIN ((tblCat INNER JOIN tblProd ON tblCat.CatID=tblProd.CatID) INNER JOIN tblTrans ON tblProd.Item=tblTrans.Item) ON tblEveInfo.EventID=tblTrans.EventID
GROUP BY tblCat.Categories, tblCat.TaxRate, tblTrans.TransDate, tblTrans.Sold, tblTrans.SaleDiscount, tblProd.Price
HAVING ((Not (tblCat.Categories)="Clothing") And ((tblTrans.TransDate) Between [Start Date] And [End Date]) And ((tblTrans.Sold)>0));

And this is the qrycombinetax as it stands

SELECT Sum(QryNotClothingSalesTax.Categories) AS SumOfCategories, Sum(QryNotClothingSalesTax.TransDate) AS SumOfTransDate, QryNotClothingSalesTax.GrossSales, QryNotClothingSalesTax.TaxCollected, QryNotClothingSalesTax.GrossSalesIncludingTax, QryClothingSalesTax.CloGrossSales
FROM QryClothingSalesTax, QryNotClothingSalesTax
GROUP BY QryNotClothingSalesTax.GrossSales, QryNotClothingSalesTax.TaxCollected, QryNotClothingSalesTax.GrossSalesIncludingTax, QryClothingSalesTax.CloGrossSales;

if you need anything else let me know, I'll do my best to get it to you. Also if you need a closer look at the database you requested that I post it on the thread, Printing Multiple Copies of the a Record in The Same Report. I will be posting there shortly. Thanks for all your help with this!!

[nico5038]

Hmm, bit puzzled why you have this many fields in the GroupBy clause.
Also the split into "Clothing" and "Non Clothing" is a bit vague.

Can you try:

SELECT IIF(tblCat.Categories="Clothing","Clothing","Non Clothing") AS CategoryGroup, Round([Price],2) AS TruePrice, ((Round([Price],2)-[SaleDiscount])*[Sold]) AS GrossSales, ((Round([Price],2)-[SaleDiscount])*[Sold])*[TaxRate] AS TaxCollected, (Round([Price],2)-[SaleDiscount]*[Sold])+((Round([Price],2)-[SaleDiscount])*[Sold])*[TaxRate] AS GrossSalesIncludingTax
FROM tblEveInfo INNER JOIN ((tblCat INNER JOIN tblProd ON tblCat.CatID=tblProd.CatID) INNER JOIN tblTrans ON tblProd.Item=tblTrans.Item) ON tblEveInfo.EventID=tblTrans.EventID
WHERE tblTrans.TransDate Between [Start Date] And [End Date] And tblTrans.Sold>0
GROUP BY IIF(tblCat.Categories="Clothing","Clothing","Non Clothing");
 

and let me know the result.

Nic;o)

[klarae99]

I entered your code into a new query and ran it. I got an error message: You tried to execute a query that does not include the specified expression 'Round([Price],2)' as part of an aggragate function. I'm not sure how to fix this.

Also I noticed that your if clause has "clothing","clothing", and "not clothing" listed. If these references are to the text in the category field than there will be an issue as "not clothing" is not a category, it was ment to mean all categories except clothing.

Maybe I am going about the whole process incorrectly. Essentially I have 8 categories, 7 of whom are taxed (all listed in tblCat with fields for CatID, Category and Tax Rate. I am trying to create a report that will tell me the total sales for all eight categories, and the amount of taxable sales (the total for the 7 categories that are taxed). What I was attempting to do with these three queries was: query 1 (Isolate sales of clothing, calculating total sales), query 2 (Isolate sales of the 7 other categories, calculating total sales and total tax collected), and query 3 (combine these two queries into one so that I could use the income from clothing indeptendent from the other categories, and preform calculation like adding the total clothing sales to the other cateogries sales to get a grand total, using only the sum of sales from the 'non clothing' categories as a taxable income amount, listing the total collected including tax, etc). Is there a better way to do this?

[nico5038]

Hmm, I was trying to create one query to do it all, but I guess we should start with a simplification.
Let's start with this query:

SELECT tblCat.Categories, Round([Price],2) AS TruePrice, 
((Round([Price],2)-[SaleDiscount])*[Sold]) AS GrossSales, 
IIF(tblCat.Categories="Clothing",0,[TaxRate])) AS GrossTaxRate
FROM tblEveInfo INNER JOIN ((tblCat INNER JOIN tblProd ON tblCat.CatID=tblProd.CatID) INNER JOIN tblTrans ON tblProd.Item=tblTrans.Item) ON tblEveInfo.EventID=tblTrans.EventID
WHERE tblTrans.TransDate BETWEEN [Start Date] AND [End Date] AND tblTrans.Sold>0;
 

Here we calculate the "basic" fields TruePrice, GrossSales and GrossTaxRate.
To cope with the exclusion from the taxes for "Clothing" we use an IIF() to set these records to a zero TaxRate.

This query will hold one line for each "basic" record.

The next query will be a groupby (without "Clothing" selection) just specifying for the field Categories the GroupBy option in the combobox and for the GrossSales and GrossTaxRate a Sum. Finally for the GrossSalesWithTaxRate use also Sum and the calculation (GrossSales + (GrossSales * GrossTaxRate)).

Getting the idea ?

Nic;o)

[klarae99]

Nic;o)

I still think that there might be a disconnect in the understanding of the problem, you mentioned an if clause to make the tax rate of the clothing equal 0. This is already accomplished in my table. Let me try to explain a different way, below are the tables and fields that I need to use for this report.

tblCat
CatID, autonumber, PK
Category, text
Tax Rate, number

tblProd
Item, number, PK
Price, number

tblTrans
TransID, autonumber, PK
Item, number, FK
TransDate, date
SaleDiscount, number

I want to create a query that will let me select a start and end date then calculate

For Each Category: Total Sales, Total Sales Including Tax and Total Tax Collected.

For the Report: Gross Total Sales, Gross Total Sales Including Tax, Gross Total Tax Collected and (this is the field that is causing me the issues) Total Taxable Sales. The Total Taxable sales would be equal to (Gross Total Sales - Category Clothing Total Sales).

Right now I have a report that I am using until I can get this final peice to add into it. The query is set up with the following sql

SELECT tblCat.Categories, tblCat.TaxRate, tblTrans.TransDate, tblTrans.Sold, tblTrans.SaleDiscount, Round([Price],2) AS TruePrice, ([TruePrice]-[SaleDiscount])*[Sold] AS GrossSales, [GrossSales]*[TaxRate] AS TaxCollected, [TaxCollected]+[GrossSales] AS GrossSalesIncludingTax
FROM tblEveInfo INNER JOIN ((tblCat INNER JOIN tblProd ON tblCat.CatID = tblProd.CatID) INNER JOIN tblTrans ON tblProd.Item = tblTrans.Item) ON tblEveInfo.EventID = tblTrans.EventID
GROUP BY tblCat.Categories, tblCat.TaxRate, tblTrans.TransDate, tblTrans.Sold, tblTrans.SaleDiscount, tblProd.Price
HAVING (((tblTrans.TransDate) Between [Start Date] And [End Date]) AND ((tblTrans.Sold)>0));
 

What I am really trying to do is to create a field on this report where I can view just the Total Income from Clothing so that I can use it in a calculation field and subtract it from the Gross Total Sales and get the Total Taxable Income.

Perhaps I just need a way to take the values calculated in my qryClothingSalesTax that I posted earlier and get that value to show in the footer of the Sales Tax Report.

I'm sorry that I can't seem to explain what I need concisely, I hope this helps!

[nico5038]

The GrossTotal is indeed handled by fields in the report footer.
When the TaxRate is already set to zero, then you need just one query to get the basic fields summed per category. When the taxrate can vary for the non-clothing,then you'll need first to calculate the GrossSales and GrossTax per row. Finally create the creary as described in my last comment to get the Categorie totals.

Nic;o)

[klarae99]

I think that there is still some misunderstanding as to what I am trying to accomplish. I have opened my Sales Tax Report that is based on my basic query as I have it currently, then I published it to Word, copied and pasted the text here.

Sales Tax Report
Books September 2007
Total Sales $34.66 Total Tax $2.77 Total Sales W/ Tax $37.43
Total Category $34.66 Total Category Tax $2.77 Total Category Sales W/ $37.43
Clothing September 2007
Total Sales $167.50 Total Tax $0.00 Total Sales W/ Tax $167.50
Total Category $167.50 Total Category Tax $0.00 Total Category Sales W/ $167.50
Crystal September 2007
Total Sales $18.52 Total Tax $1.48 Total Sales W/ Tax $20.00
Total Category $18.52 Total Category Tax $1.48 Total Category Sales W/ $20.00
Gross Sales $220.68
Gross Tax Collected $4.25
Gross Sales With Tax $224.93

This is for only one month September, but when we are using real data over an extended period of time you would be able to see Septembers total sales, Octobers total sales etc. for each category.
Also you will note that some categories are not here, as there were no sales in that category over this period of time.

All of this is fine, but what I would like to do is in the report footer I would like to have a text box labled Gross Total Taxable Income. The figure for this text box in this example would be 53.18 (220.68[GrossTotalSales] - 167.50[Category Total for Clothing]) I need a way to get the Category Total for Clothing (which is a detail total in the report) onto the report footer so I can use it in calculations. Is that possible?

[nico5038]

For this we'll need grouping (see the =] looking button).
First add a field in the GroupBy query like:

Select Year(tblTrans.TransDate) & Right("00" & Month(tblTrans.TransDate),2) As YearMonth, ...
[

This YearMonth field needs to be added to the Grouping and you can indicate that a Footer is needed, so Monthly totals can be specified.

When you want also a grouping for "Clothing" and "Non Clothing", the an IIF() as in one of my previous posts can be used to create another level in the query.

Getting the idea ?

Nic;o)

[klarae99]

Nic;o)

Thanks for all your help, I really appreciate all the time you have dedicated to try and find a solution for my issue. I'll work on it some more.

[nico5038]

Keep me posted, I'll be happy to help you to complete the application.

Nic;o)