473,887 Members | 2,283 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
15 6191
85 New Member

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.

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

Item, number, PK
Price, number

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

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 (((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 qryClothingSale sTax 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!
Oct 19 '07 #11
3,080 Recognized Expert Specialist
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.

Oct 19 '07 #12
85 New Member
I think that there is still some misunderstandin g 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?
Oct 19 '07 #13
3,080 Recognized Expert Specialist
For this we'll need grouping (see the =] looking button).
First add a field in the GroupBy query like:
Expand|Select|Wrap|Line Numbers
  1. Select Year(tblTrans.TransDate) & Right("00" & Month(tblTrans.TransDate),2) As YearMonth, ...
  2. [
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 ?

Oct 19 '07 #14
85 New Member

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.
Oct 22 '07 #15
3,080 Recognized Expert Specialist
Keep me posted, I'll be happy to help you to complete the application.

Oct 22 '07 #16

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: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
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: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
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: 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.