UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Counting/summing Subtotals Of Yes/no Checkboxes In A Report, Access 2007    
 
   
CaliLivChick
post Jul 9 2019, 09:52 AM
Post#1



Posts: 5
Joined: 9-July 19



I have a file with two tables (Properties and Categories), and I'm trying to make a report that uses data from both. Properties has about 10 lines of data with ID being the PK, and Categories has about 35 lines of data with ID also being the PK, but the one thing they have in common is PropertyID. I have a form that is the input method for populating the report, and almost every item that goes into the report is just a check box. The one thing that's not is the Unit Number, and that's a text box. Every property that's listed in the Properties table has the option to have every feature that's in the Features table.

In my report, I need to have the (sub)total number of Units that each Property has for each Category, and the (grand total) sum of all those totals as well. So if Unit_1 in Property_1 has Category_1, there will be a check box checked in that form entry, and that will carry over to the report, making the total for Property_1 a -1 (which I will change to a positive with the Abs feature) for Category_1, and the total for all the properties for Category_1 to also be a -1. But if Unit_3 in Property_2 has Category_1, then the total for Property_1 would still be a -1, but the total for Property_2 would also be -1, and the total for all the properties for Category_1 would now be -2. (I hope I'm explaining this clearly enough).

I've been able to get the grand totals for each feature across all the properties, but I haven't been able to figure out how to get the subtotals for each property to carry over. Any help would be appreciated, and if more info/clarification is needed, just let me know.

I've used =Sum(IIf([Accessible Parking]=True,1,0)) {also aware of the availability of =Abs(Sum([Accessible Parking])) } to get my grand totals, and I've tried =Sum(IIf([Accessible Parking] Is Not Null And ([Property Name].[Exec Office])=True,1,0)) to try and get the sub total for that individual property's particular category example, but it doesn't work. I've put in screen shots of my tables and report. I've spent so much time on this project, I just need to get it done for now, I can fix it up later so that it's fully properly set up, I just need the code to get the sub totals to work. Thank you for your help in advance!
This post has been edited by CaliLivChick: Jul 9 2019, 10:14 AM
Attached File(s)
Attached File  Help_Doc_page_003__.jpg ( 452.48K )Number of downloads: 5
 
Go to the top of the page
 
GroverParkGeorge
post Jul 9 2019, 11:53 AM
Post#2


UA Admin
Posts: 35,683
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

Unfortunately, this problem reflects another, more fundamental problem, which is an inappropriate table design. Take a look at this set of blog articles explaining the problem of repeated fields in tables.

You have a series of yes/no fields and that's the problem. This should be handled as a sub-table listed each of those attributes as separate records. Roger's articles (read them all, please) explain why and also how to fix it.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
CaliLivChick
post Jul 9 2019, 12:15 PM
Post#3



Posts: 5
Joined: 9-July 19



So even though I'm able to get the grand totals with what I have, there's no way to get the subtotals? Even if it's wrong, and just a temporary fix?
Go to the top of the page
 
GroverParkGeorge
post Jul 9 2019, 12:18 PM
Post#4


UA Admin
Posts: 35,683
Joined: 20-June 02
From: Newcastle, WA


Actually, supporting the shortcut will only put off the time when you have to address it, and that needs to be done sooner rather than later.

Here's another way to look at it. You're figuratively jumping through hoops, looking for advanced methods to handle the problem. And that's just an interim solution for now.

Invest in yourself and figure out how to do this right, or at least better.

I suspect that someone can help you with a work-around, but I prefer to avoid it myself.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th September 2019 - 03:49 PM