Count checked and unchecked boxes 1

[EasyOnly]

Hi All,

I am trying to summerize data in a table. I have two check boxes 1) Travel and the second Hotel and there is start date.

I want to be able to count how many checked box for travel and how many boxes are unchecked from the total. Same for the second box(hotel).

I will need to report result in percentage. example for the month of June, we had 95% travel and 87% Hotel.

I have no clue how I am going to do this. Any suggestions?

 

[dhookom]

Assuming you have Yes/No fields for Travel and Hotel, you can aggreggate these in a totals query like

SELECT Sum(Abs([Hotel])) as NumHotel, 
  Sum(Abs([Hotel]))/Count(*) As PctHotel,
  Sum(Abs([Travel])) as NumTravel,
  Sum(Abs([Travel]))/Count(*) As PctTravel
FROM [ATable];

You haven't provided any table or other field names to help us understand your dates or filtering.

Duane
Hook'D on Access
MS Access MVP

 

[EasyOnly]

The query will have the following fields: OrgName from Org_tbl, Description from Sub_tbl, the two fields travel and Hotel will come from Project_tbl then finally startdate.

Example, the trip on 07/08 have 40 traveler. we booked 99% for travel and 95% hotel and so on. For the month of July, we may have total 15 events.

OrgName Descript Startdate Hotel Travel Totalparticip
CVC Management 07/08/2009 95% 99% 40

 

[dhookom]

No one can help you without understanding how your tables are related. You need to provide more information if you want more help.

Duane
Hook'D on Access
MS Access MVP

 

[EasyOnly]

This is the SQL statement:

SELECT Organization_tbl.OrganizationName, ProjectSub_tbl.Description, ProjectAct_tbl.StartDate, ProjectAct_tbl.Travel, ProjectAct_tbl.Hotel
FROM ((Organization_tbl INNER JOIN Projects_tbl ON Organization_tbl.OrganizationID = Projects_tbl.OrganizationID) INNER JOIN ProjectSub_tbl ON Projects_tbl.ProjectID = ProjectSub_tbl.ProjectID) INNER JOIN (Invoice_tbl INNER JOIN ProjectAct_tbl ON Invoice_tbl.InvoiceID = ProjectAct_tbl.InvoiceID) ON ProjectSub_tbl.ProjectSub = Invoice_tbl.ProjectSub;

 

[dhookom]

I'm a bit confused since StartDate is in the same table as you Travel and Hotel so I don't know if this should be grouped by or min. Try something like:

SELECT Organization_tbl.OrganizationName, ProjectSub_tbl.Description, 
  ProjectAct_tbl.StartDate, Sum(Abs([Hotel])) as NumHotel, 
  Sum(Abs([Hotel]))/Count(*) As PctHotel, Sum(Abs([Travel])) as NumTravel,
  Sum(Abs([Travel]))/Count(*) As PctTravel, Count(*) As NumOf
FROM ((Organization_tbl
 INNER JOIN Projects_tbl ON Organization_tbl.OrganizationID = Projects_tbl.OrganizationID)
 INNER JOIN ProjectSub_tbl ON Projects_tbl.ProjectID = ProjectSub_tbl.ProjectID)
 INNER JOIN (Invoice_tbl
 INNER JOIN ProjectAct_tbl ON Invoice_tbl.InvoiceID = ProjectAct_tbl.InvoiceID)
 ON ProjectSub_tbl.ProjectSub = Invoice_tbl.ProjectSub
GROUP BY  Organization_tbl.OrganizationName, ProjectSub_tbl.Description, 
ProjectAct_tbl.StartDate;

Duane
Hook'D on Access
MS Access MVP

 

[EasyOnly]

Thank you so much dhookom. you are the man!