Tracking Events by Date and Indvidual

[JJ Hoover]

Hello all,



I am working on a major project and very new to design with access. There may be a more appropriate forum but right now I am simply trying to get a direction to go. I am designing a database to track employees stress levels over the course of there time at a location. The easy part is getting there initial situation and the most recent snapshot. In addition my boss has asked that we be able to track by date as well. Basically such that we have all the times we have been given stress level information tracked for each individual. This database however covers over 300 people and every 2 weeks ill be getting new information for 10-15 individuals. I know I could create a new column for each new set of information received but I was wondering if there was a way to create a form or something that created a time stamped entry in the main table or in a secondary table.

Thank you for any help you can provide.

[ranman256]

you want a more scalable design.
tracking their items

tPersons table
------------
PersonID (autonum)
FirstN
LastN
addr
etc...


tSurvey table
------------------
SurveyID (autonum)
SurveyDate
etc...


tSurveyQ table (this would hold all the questions for the surveyID)
SurveyID (long)
Q#
Question


tSurveyReplys table
[personid] (long)
[surveyid] (long)
[Q#]
[Question]
[Answer]


these would be structured like:

tSurveyQ table
surveyid = 2
SurveyDate = 1/1/17


tSurveyReplys table:
[personid],[surveyid],[Q#],[Question],[Answer]
334,2,1, Pulse, 60
334,2,2, BP, 120/80
334,2,3, Like your job, yes
334,2,4, Wt , 200


Each new survey in tSurvey table could have the same questions,
some new, or all new quesitons.

[Micron]

This database however covers over 300 people and every 2 weeks ill be getting new information for 10-15 individuals. I know I could create a new column for each new set of information received...

That is not how it's done. Tables are for entities. Fields (not columns) are for attributes. If the entity is a user, then you have tblUser (or tblEmployee or whatever) with fields for EmplID, FName, LName, EmplNo, email, etc. If the entity is a questionnaire, survey, sales order, etc. the same logic follows; especially if there's a one to many relationship like Sales (1 table) and possibly several line items for a sale (another table). When it comes to surveys, the main table might contain only those attributes that apply to the survey as a whole. A benefit may be realized by putting the questions in a related table and not the same one as the survey/questionnaire table. I think you're using your Excel brain, which is bad when it comes to designing relational dbs. Suggest you review normalization at least, and armed with that knowledge, start out with pencil and paper (maybe flip chart sized sheets if available) and map things out. You should find that you're able to make a lot of decisions based on questions you should be asking yourselves, like "We'll store who made the change, but how do we figure that out?".
Here's my standard copy/paste list of things to get anyone going down the right path:

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

[ssanfu]

And maybe you could post your dB with just a few records (change any sensitive data) or an image of the relationships.......
It is a big help to try and understand the problem you are seeking help for.


And welcome to the forum......

[orange]

I didn't see anything that suggested a survey, but here's a draft model of Employee works at Location and has Stress Levels measured and recorded at various dates that may help as a starting point. This can be adjusted or dismissed as details evolve. Good luck.

[JJ Hoover]

Name	Dept	Div	Rank	Form completed	1	2	3	4	5	6	SC	7	8	9	10	11	12	13	SR	14	15	16	17	18	19	FC	20	21	22	23	24	25	26	27	28	29	PC	30	31	32	33	SS	ISLW	ISLH	CSW	CSH
*Member 1	E	ER	5	True	5.00	5.00	5.00	5.00	5.00	5.00	5.00	5.00	5.00	5.00	5.00	5.00	5.00	5.00	5.00	5.00	5.00	5.00	3.00	5.00	5.00	4.67	5.00	5.00	5.00	5.00	5.00	5.00	3.00	5.00	4.00	5.00	4.7	2.00	4.00	2.00	2.00	2.5	3.00	1.00
Member 2	S	S1	3	False

This is what I have for current data. Each number represents the response on question and the letter responses up to SS are averages of the previous set of numbers. The last 2 blocks are placeholders for now. The desire (Now it may not be a possible desire) is to create some method for a new line to be added to the table with a date each time a new survey with updated information for Current stress work (CSW) is gained. Such that not only is the initial point tracked but over the course of 3 years every data pointed gained on that person is tracked.

This might require a new connected table that the value of CSW is taken from as the most recent value added from that table.

I don't know if that explanation helps you understand my desire at all ssanfu.

__________________________________________________ ______

Micron:

I will slowly digest what you have suggested I read and see if I can get any idea of where to go.

Thank you everyone for the responses.

[Micron]

after seeing this, I definitely think I am on the right wave length when I said
" I think you're using your Excel brain,"

[ssanfu]

Your example in Post #6 definitely is a spreadsheet layout.
In Access, I see tables for:

tblMembers
------------
MemberID_PK (Autonumber)
MemberNumber (Text)
MemberFName (Text)
MemberLName (Text)
Other fields

tblMemberDetails
----------------
MemberDetailsID_PK (Autonumber)
MemberID_FK (Long) (foreign key to tblMembers)
Dept (Text)
Div (Text)

Not sure what "Rank" and "Form Completed" are for or where (what table) they belong in


Then the readings
tblStressReadings
----------------
StressReadingID_PK (Autonumber)
MemberDetailsID_FK (Long) (foreign key to tblMemberDetails)
Reading (Single or Double)
ReadingDate (Date/Time)
Sequence (Integer) (if order is important and there are multiple readings on the same date for an individual)



Would need to see some sample data to be sure about the table structures... but it is a start.....