Group Report by Multiple Fields 1

[primagic]

I have a table which has certain information and also 5 checkboxes.

How can I group a report by the 5 checkboxes.

Ie.

group all the records that have checkbox 1 ticked, then group the records with checkbox 2 ticked then group records with checkbox 3 ticked and so on. Certain records may have more than 1 checkbox ticked.

 

[PWise]

select pkfieldname,sum(cint(check1)+cint(check2)+cint(check3)+cint(check4)+cint(check5) as numberchecked
from yourtable
group by pkfieldname

 

[primagic]

Hi PWise

Thanks for your response

I get an syntax error = "Missing Operator in query expression...."

Then it highlights "AS"

And ideas

Thanks again

 

[primagic]

sorry forgot to include my code

select ID,sum(cint(IssueTitle)+cint(IssueMap)+cint(IssueMortgage)+cint(IssuePlanning)+cint(IssueOther) AS NumberChecked
from Titles
group by ID

 

[primagic]

I found the error, was missing a closing parentheses. So query produces results but this shows me ID and counts the number of checkboxes ticked but how can I sum and show the records ticked for checkbox 1 and seperately checkbox 2 and so on...

so essentially i want to finish with results like

Checkbox 1

Record A
Record B
Record C

Total: 3 records

Checkbox 2

Record B
Record D

Total: 2 records

 

[PWise]

sorry i misunderstood your question

Create this query

Select *,"IssueTitle" as issuetype
from Titles
where IssueTitle=-1
Union
Select *,"IssueMap"
from Titles
where IssueMap=-1
union
Select *,'IssueMortgage"
from Titles
where IssueMortgage=-1
Union
Select *,"IssuePlanning"
from Titles
where IssuePlanning=-1
Union
Select *,"IssueOther"
from Titles
where IssueOther=-1


and sort the report on issuetype

 

[primagic]

Works perfectly. Thank you

What I also need is a parameter field (Criteria) as on the DateCompleted field. Something like

Between Forms!frmDashboard!FromDate And Forms!frmDashboard!ToDate.

And lastly, I also need to display records where no checkboxes are ticked.

Once again, many thanks for your help. Much appreciated.

 

[dhookom]

IMO, this primary issue is the table structure not being normalized. I doubt you want to go through the process of normalizing which is basically what PWise's SQL performs.

Duane
Hook'D on Access
MS Access MVP

 

[primagic]

Dhookom, I have taken what you said on board but how else would I record true/false for 5 different options without seperating the fields?

 

[PWise]

Select *,"IssueTitle" as issuetype
from Titles
where IssueTitle=-1
And DateCompleted Between Forms!frmDashboard!FromDate And Forms!frmDashboard!ToDate

Union
Select *,"IssueMap"
from Titles
where IssueMap=-1
And DateCompleted Between Forms!frmDashboard!FromDate And Forms!frmDashboard!ToDate

union
Select *,'IssueMortgage"
from Titles
where IssueMortgage=-1
And DateCompleted Between Forms!frmDashboard!FromDate And Forms!frmDashboard!ToDate

Union
Select *,"IssuePlanning"
from Titles
where IssuePlanning=-1
And DateCompleted Between Forms!frmDashboard!FromDate And Forms!frmDashboard!ToDate

Union
Select *,"IssueOther"
from Titles
where IssueOther=-1
And DateCompleted Between Forms!frmDashboard!FromDate And Forms!frmDashboard!ToDate

union
Select *,"noIssue"
from Titles
where IssueOther=0
And IssuePlanning=0
And IssueMortgage=0
And IssueMap=0
And IssueTitle=0
And DateCompleted Between Forms!frmDashboard!FromDate And Forms!frmDashboard!ToDate

 

[dhookom]

how else would I record true/false for 5 different options without seperating the fields

Normalization of your Titles table
Create a table of every different Issue:
[tt]
tblIssues
=================
issIssID autonumber primary key
issIssue values like Title, Map, Mortgage, Planning
[/tt]
Then create a table that link Titles with Issues:
[tt]
tblTitleIssues
=================
tisTIsID autonumber primary key
tisTitID links to Titles.ID
tisIssID links to tblIssues.issIssID
[/tt]

Use a subform to select issues for each title.

This allows you to dynamically create as many unique issues types beyond the original 5 as you want without having to change tables, forms, queries, reports,...

Your report becomes much easier to create.

Duane
Hook'D on Access
MS Access MVP