Enforce all possible records in a many to many relationship

[timeck]

you DON'T add columns if you have many of the same type of data; you put them in ROWS instead. It works now only because you don't have much data yet. But If you add evaluators and, who knows, other info in the future, you will find it difficult without restructuring your table. Better do it at the beginning than later.

On my way home from work as I had pondered on the original intent of using date values to notate a completion. My best guess was it came from the idea that as these iterations start to age, so too does the retention of knowledge. Some of the skills are practiced nearly on a daily basis, however as I dug deeper into the list I realized opportunities to perform these could span over months, if not years. So I figured it would be prudent to restructure the evaluations to multiple records to accommodate a means for certification expiry.

Like you said, I need to make sure this db is built correctly the first time. I'm fixating on results instead of the functionality. I need to sit on this for a while and outline a framework to follow instead of building while I brainstorm.



I'd tag this thread "solved" but I'm not sure if I can. The original question has been answered and I thank you all for the helpful advice.

[keviny04]

On my way home from work as I had pondered on the original intent of using date values to notate a completion. My best guess was it came from the idea that as these iterations start to age, so too does the retention of knowledge. Some of the skills are practiced nearly on a daily basis, however as I dug deeper into the list I realized opportunities to perform these could span over months, if not years. So I figured it would be prudent to restructure the evaluations to multiple records to accommodate a means for certification expiry.

Like you said, I need to make sure this db is built correctly the first time. I'm fixating on results instead of the functionality. I need to sit on this for a while and outline a framework to follow instead of building while I brainstorm.

I'd tag this thread "solved" but I'm not sure if I can. The original question has been answered and I thank you all for the helpful advice.

I guess the short version of my last post is that: make sure a "one" table NEVER needs to have "many" data. The fields Iteration1, Iteration2, and Iteration3, to me, look like "many" data, and therefore should be moved to a "many" table.

[timeck]

I'm sure tons of experienced spreadsheet folks have issues picturing the proper way to store data in a database.

I'm working off of a dataset that dates back to at least 2010 and all done through Excel. My boss wants it in this format so I'm pitching this db as a data entry tool and keep the summary as is (just need to get the data links working properly).
I'm still waiting input on the categories as the engineers are working on ambiguous classifications. As it stands, I feel like I'm heading in the right direction.

I'm handling multiple rows for evaluation repetitions in a query.

As you can see, I'm building the means to handle certification expiry although I'm struggling to drive it home. My issue is figuring out how to properly compare the Latest Eval date in this query to a parameter in the skills table, certificationLength, which is an integer represented in amount of days (which will be applied to a DateAdd() on the Latest Eval date). Note, not all skills can expire (hence the checkboxes). We send engineers out to training at a remote facility and this is considered a one-time deal. I will be defaulting to no expiration untill we identify the need for one.
My best guess is to handle through query multipassing. So I'd run a new query that references this one.