Use Access to track personnel on site

[sufianmalik]

hi, we are currently using Excel to track personnel in field on a daily basis. Personnel normally attend site for a rotation of several weeks before their back to back takes over.

Just wanted to test using Access instead but aware that a data table is constrained by 255 columns, so looking at ways to get round that. Is the best way to do this to transpose the roles into fields and with each day as a record?

Welcome any help/ideas.

 

[Joe4]

The key to using Access is to use a "normalized" table design (a design that follows the rules of Data Normalization).
One of those rules say that you should NOT have similar repeating fields in your table design (like a field for each day).
Rather, you would have a Date field, and have multiple records, one for each day.

One of the key indicators on whether or not you have designed your table properly is that you should seldom have to create new fields (i.e. a field for each date).
If you then want to present the data like you show above, you would use something like a CrossTab Query to show those dates going across in a query.

 

[Micron]

Don't even think of starting a db design before reading these or similar. Best to research how to name things and why not to use multi value fields.
Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com