By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,082 Members | 2,108 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,082 IT Pros & Developers. It's quick & easy.

Need to isolate specific detail total in report footer

P: 85
Hello, I am working in Access 2003 on an inventory database. Right now I am working on a report which will be used to fill in forms that our state requires periodically to collect taxes.

Right now I have a query that determines the items that have sold between two dates and calculates the Gross Sales, Tax Collected and Sales Including Tax. This 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.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));
(I retyped this sql, did not copy and paste {I'm on a different computer} so if there are typos I appologize, but the query itself is functioning correctly)

My report is set up based on this query with groupings for Category and then by Month (from TransDate). Each group has a footer with the total amount of Sales, the Total Tax Collected and the Total Sales W/Tax. Also, the Report Footer has Gross Sales, Gross Tax Collected and Gross Sales with tax, which is all information that I will need to enter onto the forms provided by the state. All of this is working well.

There is another piece of information that the state requires that I have not been able to figure out how to isolate. That information is the total sales that are taxable. In our state Clothing (one of my seven categories) is not subject to sales tax, but all of the other categories are. What I need to do is figure out a way to determine how much of our total sales is taxable.

I could do this by subtracting the total clothing sales from the gross sales, or I could add the total sales for the six other categories. However I arrive at this calculation I would like it to appear in the Report Footer.

Any ideas or advice on how to accomplish this would be greatly appreciated. Thanks for all your help!
Nov 5 '07 #1
Share this Question
Share on Google+
2 Replies

P: 11
Could you not add a field to state whether or not the category is taxable and then group the report into taxable and non-taxable? ?

Nov 5 '07 #2

P: 85
Briliant (I grouped by tax rate instead of creating a new field to identify if it was taxable but it did essentially the same thing) ...that gets me half way to my perfect sinario. Thanks!

Now I just need to figure out if it is possible to 'summarize' the tax rate footer in the report footer. So that there would be a field for Total Sales, Total Tax Collected, Total Sales with Tax (which I have now) and also a field for Total Sales 0% Tax Rate and Total Sales 8% Tax Rate (which is in the Tax Rate Footer currently). I would really like to have all the information in one location as this report may become several pages long.

If anyone has any ideas I would really appreciate it.
Nov 5 '07 #3

Post your reply

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