473,725 Members | 1,720 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

85 New Member
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!
Oct 16 '07 #1
15 6171
3,080 Recognized Expert Specialist
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 ?

Oct 17 '07 #2
85 New Member

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!
Oct 18 '07 #3
3,080 Recognized Expert Specialist
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 "aggregatio n function) like Sum, Avg, Max, First, etc.

Oct 18 '07 #4
85 New Member

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!
Oct 18 '07 #5
3,080 Recognized Expert Specialist
Can you post the SQL text of the queries so I can have a look ?

Oct 18 '07 #6
85 New Member

Here is the sql for the qryClothingSale sTax

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. GROUP BY tblCat.Categories, tblCat.TaxRate, tblTrans.TransDate, tblTrans.Sold, tblTrans.SaleDiscount, tblProd.Price
  4. HAVING (((tblCat.Categories)="Clothing") And ((tblTrans.TransDate) Between [Start Date] And [End Date]) And ((tblTrans.Sold)>0));
And here is the sql for the qryNotClothingS alesTax

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. GROUP BY tblCat.Categories, tblCat.TaxRate, tblTrans.TransDate, tblTrans.Sold, tblTrans.SaleDiscount, tblProd.Price
  4. 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

Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(QryNotClothingSalesTax.Categories) AS SumOfCategories, Sum(QryNotClothingSalesTax.TransDate) AS SumOfTransDate, QryNotClothingSalesTax.GrossSales, QryNotClothingSalesTax.TaxCollected, QryNotClothingSalesTax.GrossSalesIncludingTax, QryClothingSalesTax.CloGrossSales
  2. FROM QryClothingSalesTax, QryNotClothingSalesTax
  3. 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!!
Oct 19 '07 #7
3,080 Recognized Expert Specialist
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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. WHERE tblTrans.TransDate Between [Start Date] And [End Date] And tblTrans.Sold>0
  4. GROUP BY IIF(tblCat.Categories="Clothing","Clothing","Non Clothing");
and let me know the result.

Oct 19 '07 #8
85 New Member
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","clo thing", 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?
Oct 19 '07 #9
3,080 Recognized Expert Specialist
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblCat.Categories, Round([Price],2) AS TruePrice, 
  2. ((Round([Price],2)-[SaleDiscount])*[Sold]) AS GrossSales, 
  3. IIF(tblCat.Categories="Clothing",0,[TaxRate])) AS GrossTaxRate
  4. 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
  5. 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 GrossSalesWithT axRate use also Sum and the calculation (GrossSales + (GrossSales * GrossTaxRate)).

Getting the idea ?

Oct 19 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to Create a Dynamic Crosstab Report PRODUCT :Microsoft Access PROD/VER:1.00 1.10 OPER/SYS:WINDOWS
by: bmoos1 | last post by:
I have two reports: 2004 New Enrollments and 2004 Cancellations. 1. Each report shows by month: "Monthly", "BiMonthly"(*2), "Weekly" (*52/12), "Total Monthly" (++) and "Projected Annual"(*12). 2. At the end of the report is the "Grand Total Projected Annual"(=sum()
by: Pecanfan | last post by:
Hi, I've got an access report which contains a sub-report. The sub-report contains various items in a group Footer which culminates in a running sum text box called txtGrandTotal. I want to use this 'total' in the main report so I have a text box with the following:- =(!!.!) For some reason, which I'm sure is perfectly obvious but I'm buggered if I
by: jimfortune | last post by:
In: http://groups.google.com/group/comp.databases.ms-access/msg/60d7faa790c65db1 james.ea...@gmail.com said: :Ok, I've answered the first half of my own question. From a MSDN :article, I can now use the following to reference the last primary :key's values: :
by: pat | last post by:
I'm working on a report. I have two categories (groups) with a field labeled "Amount Funded." I want to create a subtotal column (for each group) that subtracts the "Amount Funded" from a number ($60,000.00 and $140,000 respectively), and a "grand available total" that subtracts the total "Amount Funded" from $200,000.00. How do I build that expression so that I have a running "funds available" subtotal for each category, a grand "funds...
by: lorirobn | last post by:
Hi, I have a report displaying items that are missing from a room. I created 2 queries, the first getting the items IN the room, and the second being an "unmatched" query that references the first query where Item is Null. I use a subreport for the details, and the results display correctly. However, the Report_Details event of the subreport is executed about 2 or 3 times more than I would expect (I think 3 times when I have a
by: Bobh | last post by:
I am trying to figure out some code for a report issue; I have an employee who has a incentive scheme. I have a report showing various income amounts and in the report footer I vae totals for these feilds =sum() these work fine. I also have in the detail feild Overall text boxes for the three feilds concerned I then have in the report footer =++ which give me a grand total for the report also works fine. I now need to have a more...
by: JCMcAbee | last post by:
Hello. Access 2003. I hope this makes sense. I request assistance with the following calculation problem: In a grouped(?) footer of the report I have the following calculations in text boxes, and they are working well: Column1: =Sum() Column2: =Sum() Column3: =Sum() Column4: =(Count()+Count()+Count()-Count())/4 Column5: =Sum()+Sum()+Sum()+(Count()+Count()+Count()-Count())/4
by: ollyb303 | last post by:
Hi, I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor. I have one crosstab query (Query1) which has the following fields: SPID (supervisor ID), total:group by, as row heading Date, total:group by, as column heading Calls handled, total:sum, as value Date, total:where, criteria between and - this is taken from a form,
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.