Counting specific records

[ccondran08]

I am working on a Human Resources database for a big project that has a field called 'Position Number' (eg. 290) which is unique for each position. But the position can have up to 4 different stages over the life of the project which is shown in the primary key field 'Position ID' (eg. 290-A, 290-B, 290-C). There are columns for each month of the life of the project and If the Start Date and End Date of the position are in between these months then a number one will appear in the Month column.

For example, position 290 will start at level A (290-A) and will start on Jan-14 and finish on Aug14. The next stage of the project will start and position 290 will do the same role in a different area of the project (290-B) and will start on Nov-14 until May-15. Therefore the 48 columns with months as headings from Jan-14 to Dec-17 will show a number one in those months from Jan-14 to Aug-14 and Nov-14 to May-15.

There are over 150 position numbers with up to 4 different position ID's which is phased throughout the 4 years of the project. I need to do a count of how many "number ones" are in the monthly columns per Position. Any suggestions ?:banghead:

 

[Galaxiom]

Your data structure is incorrect.

Firstly the PositionNumber and the Stage should be separate fields. The PK could either be a composite key on these fields or a separate number. I would not combine the fields to make the key.

Secondly the records for the Months should be held in a related table with one record for each Month, PositionNumber and Stage combination. Use a date such as the first of the month to record the month. Don't use the actual word Month as the fieldname because it is the name of a function in VBA.

Querying will easy once you fix this.

Moreover your database won't stop workng when you run out of Month columns.

 

[jdraw]

I'd like to recommend this tutorial that should help with table design and relationships. You have to work through the tutorial.