Daily Observation Database

[sheckay]

Hello. I've already put up a post up on the premise that I just have to adjust a query to get what I want. But my design was not good. So I have to take a different approach. I'm just trying to create a database that allows users to record their observations while working on a machine. There are multiple machines, multiple users, even multiple shifts. And they will all access the db to record their observations randomly and independent of each other. But there will be one user at one machine at one time. So I thought I would create a table per machine, to prevent records from being overwritten. Each table includes the machine name, current date, notes for shift 1, and notes for shift 2. The problem is that the query that I used to collect that data, and show it is blank if one machine was not used that day (therefore, no record/notes for that machine). So I was advised to create a relational database with tables for each entity.


Here's what I got:



Am I on the right track? And, if I am, how can I make a form that allows for data entry if the tables are all separate? Thanks in advance.

[Micron]

There is nothing showing. Maybe review 'how to attach files' - see menu at top of forum window.

[xps35]

Your relations are not ok. The userIDFK should be connected to ID in employees. Likewise you need a "machineIDFK" to relate notes and machines.

I don't understand why you need two note tables. If it is for shifts, shift(IDFK) should be a field in the one and only notes table.

[NoellaG]

How about:

[Micron]

I dunno, but based on post timing, the most you could have devoted to db normalization topics seems to be about 2 hours. Not enough, really. I agree - notes are an entity so one notes table.

ID makes for a poor field name...ID of what? Better is something like NotesID_PK and NotesID_FK or NotesIDfk and NotesIDpk - then you can easily tell what the "ID" is and whether or not it is the primary or foreign key.

PK (primary key = parent values) joins to foreign key (child values) but the two fields must be similar data types. EmpID is likely a number; notes are text. The EmplID is related to the empl data in the notes table as xps35 is pointing out. Same goes for machine to notes.

[NoellaG]

seems to be about 2 hours. Not enough, really. I

lol, not 2 hours but it only took about 15 mins to draw up the schema

[Micron]

lol, not 2 hours but it only took about 15 mins to draw up the schema

That was directed to OP. I posted the suggestion to research db normalization and provided a link at 8:35-ish. This thread started soon after. Your post wasn't there (there's only a 3 minute gap).

[sheckay]

I'm guilty of all they said. Partly because I'm multitasking, partly because I'm pressed for time, and partly because I have a hard time reading that info and implementing it. I'm better at getting things going and figuring them out. But I will try to read again. In the meantime, thank you for your model of a solution. It does look promising. I'm going to get a DB going with this structure to see what happens. But, once these tables are set up how would a form that allows for entry into all of these fields be possible?

[sheckay]

Wow! You're incredible! I created what you suggested. With only a twist or two. Then I created the relationships that you suggested. And then I created a query on the tables, and then a form on the query. And it appears to be giving me a data entry form. I just have to tweak it and dress it up. Thank you! But I promise, I'll also read the DB info that was suggested. I want to be able to really understand this. Thanks again!

[mike60smart]

Hi

Upload a zipped copy of the database when you have the tables sorted.