Help with subform datasheet view looks good(the way I designed); but the form view in my main form looks like a datasheet.

[sribblett]

I'm sorry ....I'm back. I like associating with you because you've helped me more than you know. I changed my relationships and primary keys in my forms today due to having trouble entering new staff who I haven't received theer employee # yet(there's a lag time) so I changed some PK from that still had employee # as PK. I'm adding new staff in all my forms-everything as far as I can see - is working. However, when I tried to recreate my queries that I had before redoing my Main Employee table when changed PK is not working. So I know it something I did when I changed PK form my tables. Will you please look at my relationship diagram and let me know what I need to do to fix it so I can run queries when needed.

Also --- one more thing. What relationships need to be done to have employee F & L names go into other tables when I enter on my main form?

 

[sribblett]

I created a new database. There was a huge problem with it because I used Employee Number as a primary key on all my tables. So after some help on here I had to redo a couple of tables. I created different PK on all tables. I can enter all information on my Main form and all my Sub Forms with my main form without any issues. Now my problem is when I create queries, I don't get any data coming up. They are all blank.

 

[MajP]

What relationships need to be done to have employee F & L names go into other tables when I enter on my main form?

You don't. In a database you use queries to link two tables. You only store information once in a single table.

Take a look at query all. There are four tables with information. Last name and first shows in all records, but it is only entered once in the DB.


I think you are over thinking these relationships. You only use a junction table when you have a Many to Many. I think that is only for your training. The other tables look like standard 1 to many. Any chance you can provide this db without any data or a couple made up records? It would be easier to fix than try to explain. Also can you explain in simple words the tables Program Information and Staff Wavered Sites and how they relate to an employee?

Do not use any special characters or spaces in a table or field name except for _. Do not use -/| &# etc.
For now do not worry about the many to many with training, but look at the example. Get rid of that junction table, you misunderstood what I tried to say. Besides training the other tables (with additional cleanup) are going to be 1 to many. So in those table the Employee_Number (not Employee_#) is the FK.

So the problem with waiting for an employee number is exactly what I cautioned about with natural keys. So If I was you i would have an auto number field called EmployeeID as your PK. In the other tables have EmployeeID_FK which relates back to the employeeID in the employee table. So in your other tables just rename EmployeeNum to EmployeeID_FK. The training table is the only exception because this is part of a many to many.

It looks like you are trying still to link PK to PK. This is almost never done. You link PK to FK. The PK is the one side the FK is the many Side. Employees have lots of courses. Each course belonging to that employee points back to the employee.

 

[sribblett]

You don't. In a database you use queries to link two tables. You only store information once in a single table.

Take a look at query all. There are four tables with information. Last name and first shows in all records, but it is only entered once in the DB.


I think you are over thinking these relationships. You only use a junction table when you have a Many to Many. I think that is only for your training. The other tables look like standard 1 to many. Any chance you can provide this db without any data or a couple made up records? It would be easier to fix than try to explain. Also can you explain in simple words the tables Program Information and Staff Wavered Sites and how they relate to an employee?

Do not use any special characters or spaces in a table or field name except for _. Do not use -/| &# etc.
For now do not worry about the many to many with training, but look at the example. Get rid of that junction table, you misunderstood what I tried to say. Besides training the other tables (with additional cleanup) are going to be 1 to many. So in those table the Employee_Number (not Employee_#) is the FK.

So the problem with waiting for an employee number is exactly what I cautioned about with natural keys. So If I was you i would have an auto number field called EmployeeID as your PK. In the other tables have EmployeeID_FK which relates back to the employeeID in the employee table. So in your other tables just rename EmployeeNum to EmployeeID_FK. The training table is the only exception because this is part of a many to many.

It looks like you are trying still to link PK to PK. This is almost never done. You link PK to FK. The PK is the one side the FK is the many Side. Employees have lots of courses. Each course belonging to that employee points back to the employee.

I did change 2 of my tables yesterday due to having the employee number as PK. Now it's Employee ID as PK (auto). So thank you for that. I did misunderstand you about the junction table. I will redo my relations and see I do. I did know not to use those special characters but had forgotten. So thank you. I clean my tables from those also. TY

 

[MajP]

I did know not to use those special characters but had forgotten

If you use spaces, reserved words, or special characters then when you refer to the field or table you always have to do extra work and put them in [].
ex
Select [Some Field] from [table-abc#]
vice
Select SomeField from table_abc_Num

 

[sribblett]

If you use spaces, reserved words, or special characters then when you refer to the field or table you always have to do extra work and put them in [].
ex
Select [Some Field] from [table-abc#]
vice
Select SomeField from table_abc_Num

OK... I clean up most of my mess. How am i doing so far? I took a picture of my screen since I'm working from home right now. Hope you will be able to look at my relationships now. Let me know if there should be any more revising that you can see.

 

[sribblett]

OK... I clean up most of my mess. How am i doing so far? I took a picture of my screen since I'm working from home right now. Hope you will be able to look at my relationships now. Let me know if there should be any more revising that you can see.

Now that I've gotten my relations right or better(hopefully), the exact queries I did in my previous DB (before redoing relations) I don't get any data. All is blank. I ran the simpliest, F. name and L. name and Employee numbers = that worked. However, all other queries are all blank. So there is something wrong but I can't figure it out. Even with the junction table I used the queries worked. So please look at the relation diagram I sent you and I sent you one of my queries that was good previously, now is not working.

 

[sribblett]

Now that I've gotten my relations right or better(hopefully), the exact queries I did in my previous DB (before redoing relations) I don't get any data. All is blank. I ran the simpliest, F. name and L. name and Employee numbers = that worked. However, all other queries are all blank. So there is something wrong but I can't figure it out. Even with the junction table I used the queries worked. So please look at the relation diagram I sent you and I sent you one of my queries that was good previously, now is not working.

OK...If you're still willing to work with me. I am so sure you're so sick of me, honestly I'm trying. Another sleepless night trying to figure why my queries are not working out. This is what I've found. When I did a query showing me who had not taken CPR before and their experation dates for both CPR and MAT. I looked in my previous database where all the relationships were wrong. I had 1 to 1 for all my tables. But the info I needed in my queries all worked for me then. I just couldn't put any new employees in and update other staff records - so I know that was wrong. But this very simple query at first (because I have F & L names in that table) I used 1st Aid table for F & L Names / Never Taken/expiration dates. I used the Staff Waivered table for the Home Site and the Program Table for the Resigned. When I ran it I got over 9 thousand records = so I took the I had double/triples/quadruple repeat of the same staff. So I took out the F & L Names out and added the Main Employee table. I see the relationship for that query. (None of the table showed relationship previously) but when I ran it again NO DATA is showing at all. What is wrong. Please don't give up on me now. I have to get this or I'm gonna go crazy. LOL

 

[MajP]

The image is not very useful. Seriously if you can post a sample DB with a few fake records, we can save you hours and hours of time. Without seeing the full tables, the datatypes, it is really hard to see if this is normalized correctly. I would think you can make up a few fake names and there should not be any PPI or proprietary info to worry about.

 

[MajP]

1. Is 1st Aid and MAT a one time thing or will a single person have multiple records in this table. If a person only has one record ever then you can get rid of this table and put these fields into the employee table. If they take this training more than once and you need to track the date and changes then you need it like it is.
2. Can you explain Required Training vs Employee Training. I get required training (it does need to be normalzed, I can fix that), but do not understand the EmployeeTraining. It looks it is a course that may have some modules in it that count for something. In your buisness rules (not database terms) explain how you use Employee Training?

 

[sribblett]

1. Is 1st Aid and MAT a one time thing or will a single person have multiple records in this table. If a person only has one record ever then you can get rid of this table and put these fields into the employee table. If they take this training more than once and you need to track the date and changes then you need it like it is.
2. Can you explain Required Training vs Employee Training. I get required training (it does need to be normalzed, I can fix that), but do not understand the EmployeeTraining. It looks it is a course that may have some modules in it that count for something. In your buisness rules (not database terms) explain how you use Employee Training?

Yes!!! 1st Aid/MAT is only done once by each staff. When it's ready to expire they redo it and I update the new expire dates. Required trainings are trainings every staff who work with children are mandated to take every 2 years. I keep track of all those dates to remind staff when it's time to redo. Employee trainings is the excel spreadsheet I have where all training hours are logged including the required training because it is also a regulation that every staff has minimum 30 hrs every 2 year (these 2 yrs run different time depending on what site they work at - different licensing times)

 

[MajP]

In designing a database correctly there is some art to it. Not all rules are hard and fast. What determines that, is how you plan to use the data and what your requirements are. Doing it in a purist way is always correct to ensure maximum useability and flexibility, but it can be overkill if your use is limited. So I know you are trying to fix this quickly, but to do it correct will take some work. The benefit of doing it correctly will be that data entry will be much simpler and flexible. Doing analysis, data integrity, management will be much easier and more capable.
I understand why you are finding this challenging because your data model is not trivial. This type of database that you are doing can be very difficult to design correctly. A lot of people would try to do it the way you are. If doing this correct, you will end up with several many to many relationships which are more challenging. Some of your current tables would be made into multiple tables. And some of your user interfaces will be more complex than now. It is like building a house. If everything is level and square in the framing, everything falls into place. It is more work up front, but if not everything else becomes a work around and a cludge.
So my question is do you have the time to do this correct? If so then I recommend hitting it one table at a time and getting each one rock solid. There is enough work to be done that I cannot give you a short answer on how to fix this. You can just try to band-aid this, but I would caution against it. You will get it to do something with a cludge and a work around, but everytime you want to add or do something else it will be a painful band aid and work around.

 

[sribblett]

In designing a database correctly there is some art to it. Not all rules are hard and fast. What determines that, is how you plan to use the data and what your requirements are. Doing it in a purist way is always correct to ensure maximum useability and flexibility, but it can be overkill if your use is limited. So I know you are trying to fix this quickly, but to do it correct will take some work. The benefit of doing it correctly will be that data entry will be much simpler and flexible. Doing analysis, data integrity, management will be much easier and more capable.
I understand why you are finding this challenging because your data model is not trivial. This type of database that you are doing can be very difficult to design correctly. A lot of people would try to do it the way you are. If doing this correct, you will end up with several many to many relationships which are more challenging. Some of your current tables would be made into multiple tables. And some of your user interfaces will be more complex than now. It is like building a house. If everything is level and square in the framing, everything falls into place. It is more work up front, but if not everything else becomes a work around and a cludge.
So my question is do you have the time to do this correct? If so then I recommend hitting it one table at a time and getting each one rock solid. There is enough work to be done that I cannot give you a short answer on how to fix this. You can just try to band-aid this, but I would caution against it. You will get it to do something with a cludge and a work around, but everytime you want to add or do something else it will be a painful band aid and work around.

OMG!!! Thank you so much for all this information. So it wasn't all me "not getting it"??? If you will work with me on getting this correctly I will definitely have the time. Is there some way to have a quick way for the database right now? Meaning getting reports in queries. What I thought I would do is continue using that one for the time being so management can get some answers. If not, I'll just deal with it. While I'm working at getting the most efficient way possible. I want to be able to use this DB until I retire. LOL. Once it's right and polished, then it won't be such a problem to track other things. Are yu will to work with me on this?

 

[MajP]

I started a new thread because of all the unrelated threads someone would have to wade through

Normalization of DB Tables

I am posting this for the OP as a fresh thread and continuation of thread https://www.access-programmers.co.uk/forums/threads/help-with-subform-datasheet-view-looks-good-the-way-i-designed-but-the-form-view-in-my-main-form-looks-like-a-datasheet.313409/ That thread was extremely long and did...

www.access-programmers.co.uk

 

[sribblett]

I started a new thread because of all the unrelated threads someone would have to wade through

Normalization of DB Tables

I am posting this for the OP as a fresh thread and continuation of thread https://www.access-programmers.co.uk/forums/threads/help-with-subform-datasheet-view-looks-good-the-way-i-designed-but-the-form-view-in-my-main-form-looks-like-a-datasheet.313409/ That thread was extremely long and did...

www.access-programmers.co.uk

ok. I'll follow you on the new thread.

 

[sribblett]

I started a new thread because of all the unrelated threads someone would have to wade through

Normalization of DB Tables

I am posting this for the OP as a fresh thread and continuation of thread https://www.access-programmers.co.uk/forums/threads/help-with-subform-datasheet-view-looks-good-the-way-i-designed-but-the-form-view-in-my-main-form-looks-like-a-datasheet.313409/ That thread was extremely long and did...

www.access-programmers.co.uk

I did figure out what indexes were. TY for keeping me focused!!! Please look over all the tables I've created. then I will do what's next.