Sum of X's in column not rows, possible?

[CoachBarker]

In a Staff Scheduling application I am writing in vb.net I populate a DatGridView control with a query from an Access db. The DGV shows what days staff are scheduled to work by an X in the day column.



Attachment 2667

Is it possible to write a query that will calculate how many X's are in a column, columns 8 thru 35, if first column in Access is based on 0.

Attachment 2666

Example:
SDay1 2, SDay2 1, SDay3 4, SDay4 2, SDay5 3, SDay6 0, SDay7 0

Here is the query I am currently using:

SELECT tblEmployeeSchedules.SelectedDaysID, tblEmployeeSchedules.EmployeeID, tblEmployeeSchedules.StartDate, tblEmployeeSchedules.EndDate, tblEmployees.EmpPosition, tblEmployees.EmpStatus, tblEmployees.EmpShift, tblEmployees.EmpHours, tblSelectedDays.SDay1, tblSelectedDays.SDay2, tblSelectedDays.SDay3, tblSelectedDays.SDay4, tblSelectedDays.SDay5, tblSelectedDays.SDay6, tblSelectedDays.SDay7, tblSelectedDays.SDay8, tblSelectedDays.SDay9, tblSelectedDays.SDay10, tblSelectedDays.SDay11, tblSelectedDays.SDay12, tblSelectedDays.SDay13, tblSelectedDays.SDay14, tblSelectedDays.SDay15, tblSelectedDays.SDay16, tblSelectedDays.SDay17, tblSelectedDays.SDay18, tblSelectedDays.SDay19, tblSelectedDays.SDay20, tblSelectedDays.SDay21, tblSelectedDays.SDay22, tblSelectedDays.SDay23, tblSelectedDays.SDay24, tblSelectedDays.SDay25, tblSelectedDays.SDay26, tblSelectedDays.SDay27, tblSelectedDays.SDay28
FROM tblEmployees INNER JOIN (tblSelectedDays INNER JOIN tblEmployeeSchedules ON tblSelectedDays.SelectedDaysID=tblEmployeeSchedule s.SelectedDaysID) ON tblEmployees.EmployeeID=tblEmployeeSchedules.Emplo yeeID;

If any more information is need please let me know.

[ssanfu]

It looks to me that you are committing "Spreadsheet". You seem to be creating your structure based on what the output looks like instead of a normalized structure.


From your query, I created this table structure.

Attachment 2690


From the table structure, I came up with this query:

SELECT Sum(Abs(nz([SDay1],"")="X")) AS D1, Sum(Abs(NZ([SDay2],"")="X")) AS D2, Sum(Abs(NZ([SDay3],"")="X")) AS D3, Sum(Abs(NZ([SDay4],"")="X")) AS D4, Sum(Abs(NZ([SDay5],"")="X")) AS D5, Sum(Abs(NZ([SDay6],"")="X")) AS D6, Sum(Abs(NZ([SDay7],"")="X")) AS D7
FROM tblSelectedDays INNER JOIN tblEmployeeSchedules ON tblSelectedDays.SelectedDaysID = tblEmployeeSchedules.SelectedDaysID
HAVING (((tblEmployeeSchedules.StartDate)>=#1/1/2011#) AND ((tblEmployeeSchedules.EndDate)<=#1/31/2011#));

The date fields in the table "tblEmployeeSchedules" appear to be the start and end dates for a month.

Although I know little about your project, I would suggest a table structure something like:

Attachment 2691

Good luck

[CoachBarker]

no your structure is the same as mine, 3 tales, one for employee, one for selected days and one for tying them together. Was only wondering how to sum the x's in a column. I actually worked it out in yb.net in a DGV. Thank you for the query example though.

[ssanfu]

I would suggest looking at your table structure again.

I noticed that the table "tblSelectedDays" is where you have the "X" for the days an employee worked. But the table "tblSelectedDays" is on the one side of the relationship.

Since each employee can work different days, you would need to have a record for each combination of days worked (is that 28 X 28? or 28 factorial?)

Plus, how do you record the hours worked per day? Can the employee position, status or shift change during the month(or from the 1st day to the 28th day)?

If you had a normalized structure, the day count query would be simple...

---
Just thinking out loud.... I can't seem to understand how this would work. Well, good luck....

[weekend00]

isn't the COUNT() exactly for this purpose?
e.g. select ID, count(sday1),count(sday2)... group by ID

[CoachBarker]

ssanfu, this is not an app to track staff hours, it is an app to fill out a schedule for a 28 day period, no hours are included here. And I am adding the x's in the columns not the rows.

This is the results I was looking for, I was able to acheive this not using a query but by counting the X's programmatically in vb.net in the app by looping through the rows in th DGV and assigning the value of 1 for each X in an array.
Attachment 2702

weekend00, does the count function count the X's in the rows or the columns?

[weekend00]

count function counts how many rows for each column.
just like what you describe in your first post:
SDay1 2, SDay2 1, SDay3 4, SDay4 2, SDay5 3, SDay6 0, SDay7 0

[CoachBarker]

thank you I will remember that for next time.