New to Access, Data Entry question

[nobbie1]

Hello everyone

Hopefully someone can point me in the right direction.

I have the following tables set up in my little database

tblLine(LineID, LineName)
tblTech(TechID, FirstName, LastName, etc...)
tblLineProblems(LineProblemID, TechID, LineID, Date, ....problem categories...)

I'm having trouble figuring out how to set it up so that the technician enters in his name, the line he worked on and what problems he had with that line. Normally one technician is responsible for one line, but occasionally he can work on any other line that night.

Just trying to elminate or reduce a paper trail that we have currently that's just archaic and hideous for our department.

Any comments or suggestions would be apprieciated. Thanks

 

[neileg]

Presumably tblLine and tblTech are more or less static, and it's tblLineProblems that collects all the data.

You don't want the users entering or viewing data in the tables, they are just for data storage.

You need, therefore, a form for data input based on this. Make the TechID and LineID fields based on combo boxes that look up the values in the relevant tables. This will ensure that the exact IDs are used, and there's no problem with spellings etc.

Your table structure looks OK, and this should be straight forward.

 

[nobbie1]

Thanks for the reply Neileg.

Yah I pretty much had this in mind as well. The tech table and the line table are pretty much static. The tech table would only be updated when someone left or we hired someone.

Is there a way I can set up the form so that when one of the techs opens the form it's blank and they aren't able to go back to a previous date and change the record?


I've been reviewing my tables and the rules of normalization and I think I might have to normalize them further. Since one tech can potentially work on many lines (covering during a break/lunch) and one line can possibly have more than one tech work on it that night or even throughout the week. I think I might pull out the date and put it in it's own table and redo the relationships. Later on I'll be making queries so that someone can look to see what work has been done on a line for either a specific date or a range of dates.

Again thanks for the help. I started this project as something to help familiarize myself with Access. Didn't realize what I'd be getting into, but it's kinda fun

 

[MrsGorilla]

You can try setting the form up as a data entry form by setting the Data Entry property to Yes. I would probably also set the edit and delete properties to No, so that only additions are allowed. Then create a Command Button to save the record when they are finished entering. This will cause the form to open as a blank form for creating new records only. Is this what you are looking for?

Cindy G.

 

[neileg]

I've been reviewing my tables and the rules of normalization and I think I might have to normalize them further. Since one tech can potentially work on many lines (covering during a break/lunch) and one line can possibly have more than one tech work on it that night or even throughout the week. I think I might pull out the date and put it in it's own table and redo the relationships. Later on I'll be making queries so that someone can look to see what work has been done on a line for either a specific date or a range of dates.

Since I'm not a trained programmer, I don't get too worried about normalisation, so long as I have an efficient design! Your suggestions look like they will amke the structure more complex, with absolutely no benefit. The sort of queries you describe will fall out of your original suggested setup.

I agree with Cindy's suggestion for the form, except you don't need to explicitly save the data. Unless you code it not to, Access will save the data when the form is closed, or when you move to the next new record.

 

[nobbie1]

Thanks for the suggestions.

Got busy with some other things lately at work and haven't had a chance to reply back.

I'll work those suggestions in and see what we come up with

Thanks again for the time and suggestions