Count Combo Box Options

[Emilee]

Hi everybody,

I'm creating a database to collect time study data for a company. Currently I have a combo box field for each 15 minute segment of the day. The combo box contains 7 different departments from which the employee has to choose (picking the department that 15 minutes was benefiting). For payroll purposes I need to count the amount of times each department is chosen from all the 15 minute combo boxes. Any ideas on how to do this?

Thanks!

[June7]

How many comboboxes would that be - 96? There is a field for each segment? This is not a normalized data structure. The aggregate calc you want will not be simple and will likely involve VBA because 96 fields is too many for a UNION query (limit of 50 SELECT lines).

[Emilee]

Yes there are 96 combo boxes. Is there a better way to set this up? I need the employees to be able to pick a department for ever 15 minutes of the day. (Including all hours like 3AM which makes no sense to me!). I just don't know how to approach this. It seems simple but I can't think of how to automate it. Could I group and "count" them by hour and then combine the hours data?

[June7]

And how would you suggest to do the grouping and counting on 96 fields? Yes, can do a sum on each field but then adding 96 fields in an expression will probably be impossible. As I said, would likely involve VBA.

Instead of 96 fields and comboboxes, should be one field. Each selection of department and time period should be a new record. Then aggregate calcs in query will be simple.

[ssanfu]

Yes there are 96 combo boxes.

Properly normalized tables rarely exceed even 50 fields. 96+ fields
suggests that you are "committing spreadsheet", meaning that you are
storing your data as if you were using Excel instead of using a database.

Excel spreadsheets are typically short and wide.
Database tables are typically tall and narrow.

Your structure is not in 1st normal form: you have repeating fields.

Not knowing your requirements, I would suggest:

tblDepartments
---------------
Dept_ID_PK (Autonumber - primary key)
DeptName (Text)


tblTimeStudys
-------------
TimeStudyID_PK (Autonumber - primary key)
Dept_ID_FK (Long) ' foreign key - link to tblDepartments
TimeInterval (Date/Time) ' 9:00 AM, 9:15AM , 9:30AM, ...



Can 1 time interval benefit more than 1 department? Or can 1 time interval benefit only 1 department?