updateable query based on 2 unrelated tables (1 Viewer)

109bow · Jun 29, 2017

Afternoon all, this is a follow up from an earlier post, which I am still struggling with.
I have 2 tables which have no common fields, which I need to combine into Query1, from which I can build forms etc. I can't find a way of making Query1 updateable, so I can add [passed on] dates and also change the value in [valid for]
I have attached a paired down db. To open the shift key needs to be held down as this is a result of the format of the original db.
Any assistance very welcome

plog · Jun 29, 2017

You really need to step back from whatever it is you posted about and work on your tables.

1. Unused ID fields. Why do you even have autonumber primary key ID fields in employee_teams and employee_grades? It seems you know that you shoudl have them, but you don't know why. Their purpose is to be used as foreign keys in external tables. that means the table operators shouldn't have the fields [grade] and [team] in it. Instead, it should have the ID field of those tables and JOIN to them that way.

2. Redundant fields. Seems that [current staff] and [left team] are complementary data (have to be one or the other, but can't be both). If that's the case, you don't need both fields. If [current staff] is false, then that means they left the team.

3. Spaces in field names (e.g. [on duty]). You used underscores in your table and query names, but you used spaces in field names. You should only use alphanumeric characters and underscores in names. Makes coding and querying easier down the line.

Again, fix your table issues before moving on. And when you do, follow this rule--forms that directly interact with data (add/edit/delete) should be based only on tables.

109bow · Jun 29, 2017

plog, thanks for your reply and taking the time to explain some fundamentals. As you can guess I am a novice at bd design and finding my way as I go!
I will persevere to get the results I'm after.

plog · Jun 29, 2017

Looking at your new database you only fixed #3 (field names with spaces). You need to fix #1 and #2 as well. I'm looking at your Relationships (Database Tools -> Relationships):

1. You have joined employees.gradeID to employee_grades.grade. That is an incorrect foreign key (https://en.wikipedia.org/wiki/Foreign_key). employees.gradeID needs to link to employee_grades.ID. You use the primary key value of one table (employees_grade.ID) as the foreign key in another table (employees.gradeID). You've made the same mistake when linking employee_team to operators.

2. [currentstaff] and [leftteam] give you the same information--you do not need both of them. Is it ever possible that currentstaff be true and leftteam have the same values? Can they ever both be true at the same time? Can they ever both be false at the same time? If not, get rid of one of those fields.

109bow · Jun 29, 2017

plog, many thanks once again. I seem to be making slow progress, I think!
I still don't seem to be able to link training table with operators table to get the results I desire. I will persevere however.

plog · Jun 29, 2017

Tables look good. Now, can you explain what real world process this database models? Don't use any database terms (tables, queries, forms, etc.) -- just tell me about what your organization does and what purpose this database is to serve.

After that, explain why you are having trouble linking your tables like you want. Seems from the Relationship Tool its all linked. How come this doesn't serve your needs? If you can, provide some data as an example

109bow · Jun 29, 2017

plog, thanks again.
I work in the rail industry and I have a need to track training of employees in my department and show when their training has expired for any particular type of training.
As for linking the tables, I don't know if it works as I'm expecting, but I would like for each operator in operators table to have a list of all training types, how long the training is valid for and when they passed that training.
I have created a crosstab query and form previously, see attachment, but it was very clumsy. I aim to create another, based on the newly linked tables.
I have also created a form previously which allowed me to enter passedon training dates, but I could not edit the validfor dates, I think because of the badly designed tables, see attachment.

Many thanks again

plog · Jun 30, 2017

Forms are the last piece of Access, so put those out of your mind for now. It goes Tables then Reports and the queries needed to produce them, then finally Forms. You still need to fix your tables some.

First, rename the [type] field in training_type to [trainingtype]. 'type' is a reserved word and makes coding harder later on. Second, remove [passedon] from training_type, this field doesn't relate to the training itself, but to the employee and training--we will deal with that in a minute.

Next, you have many operators and they can have many training types, right? That's a many to many relationship and the way you sort that out is via an associative entity (https://en.wikipedia.org/wiki/Associative_entity). In practice this means you need a new table to sort all that out. That new table should look like this:

operator_training
employeenumber, number, links to operators.employeenumber to determine which employee received training
trainingtypeid, number, links to training_type.trainingtypeid to determine which training employee received
trainingdate, date/time, holds what data training occured

With that table you can now determine who got what training and when. Further, when you join it with training_type you will be able to know which training has expired. [trainingdate] is now serving the purpose of that [passedon] field you removed from training_type.

Make that change, populate it with some data and let's see if you are able to produce the results you need.

109bow · Jun 30, 2017

plog,
many thanks for your assistance. It seems to make bit of sense, now you have taken the time to explain it. I can get the results I need in a crosstab. A bit more work is required to make a user friendly form to add / amend training dates for each employee.
Thanks

plog · Jun 30, 2017

Looks almost good. [trainingtype_ID] needs to come out of operators. Because of operator_training, operators and training_types are not directly related. Also, you need to clean up your sample data--operators.gradeID and operators.teamID should no longer be text fields, but numbers. They need to contain the respective ID values from the foreign tables, not the text values.

None of that is going to affect your cross-tab query, so that means as long as it works in getting you the data you need you can move on to forms. As I said before, forms that directly interact with data (add/edit/delete) need to be based on tables, not queries.

109bow · Jun 30, 2017

plog,
I have done as you suggested, many thanks

109bow · Jun 30, 2017

plog, many thanks for your help, one last question, I hope!
I cant find a nice user friendly way of entering trainingdate for each operator, for each type of training. I was thinking I could use form training_record where it would list all types of training in the subform, then all a user would have to do would be enter the trainingdate.
Any ideas how this might be done?
Many thanks

plog · Jun 30, 2017

It really depends on how you want to enter that data.

Are you going to know the training and then enter a bunch of operator?
--then you would have main form based on training_type and a subform based on operator_traning. You would load the main form to one particular training, then use the subform to add operators (via a drop down) to that training.

Or, are you going to know the operator and then add a bunch of training?
--then you would do basically the same thing as above, but have the main form be based on operators and the subform would still be based on operator_training but allow you to add training to it (via a drop down).

Or, would it be random? You would add operators and training and need to select both?
--in this instance you would have one form based on operator_training and you would use drop downs to select the operator and the training for each record.

109bow · Jun 30, 2017

I plan on entering a bunch of training dates for each operator. I will have a go a your instructions.
Thanks again

109bow · Jun 30, 2017

plog, I'm very nearly there I think!
One problem, when I open Form1, I can select an employee from the combo box. For example, if you click on B THOMAS the sub form is populated with training types and dates. I think this is along the lines of what you described. However to make the rest of the db to work correctly I need the trainingtypeid number entered. Is there any way of filling this in automatically? so as not cause any errors in the data.
I have spent most of the afternoon / evening trying to find a way, but with no luck.
Many, many thanks
Ben

plog · Jul 1, 2017

Forms aren't really my forte. I suggest you post a new thread in the Forms section.

109bow · Jul 1, 2017

thanks for your help.

updateable query based on 2 unrelated tables (1 Viewer)

Registered User.

Attachments

Banishment Pending

Registered User.

Attachments

Banishment Pending

Registered User.

Attachments

Banishment Pending

Registered User.

Attachments

Banishment Pending

Registered User.

Attachments

Banishment Pending

Registered User.

Registered User.

Attachments

Banishment Pending

Registered User.

Registered User.

Attachments

Banishment Pending

Registered User.

Similar threads

Users who are viewing this thread