Learning Opportunities (1 Viewer)

ChoiceTom

New member
Local time
Today, 11:32
Joined
Aug 6, 2020
Messages
9
1. Your schema is not properly normalized. Any time you add a numeric suffix, you should know that you have a 1-many relationship and the many-side data should reside in a separate table. You have PNA_1- PNA_22 with a couple of a's as well as PQ's, If PQ and PNA go together, they can both be in the same table.
2. The visit table is NOT needed in any of the subform queries. the Master/Child link is providing the Foreign Key when the record is saved.
3. The perioperative data table is bound to three subforms. You are probably getting three records because the main form is set to data entry and so as you move from form to form, Access is making sure you have a "new" record. I would fix the design issue with this table. Add a "group" to the table so you can have three groups of data. That way each form can be bound to the same table but each question ends up in a separate row. You will have to populate the "group" in the subform's BeforeUpdate event.

You can verify that the duplication is being caused by the visit form being opened as data entry. Just temporarily change the Switchboard Items table to test.

I just tried removing the unnecessary joins as well as changing the way the form is opened and neither solve the duplication. Fix the table design issue and move on.
A sideline question, generated because of the knowledge demonstrated in your reply above.

Is there an MS-Access set of classes from someone you'd either recommend, or at least have good things to say about?

I'm having to build some tools to get things out of paper where I am at, and scraping the rust off old skills. Used to be a programmer/analyst many years ago. Probably the best way to do that is to refresh with current online coursework and exercises.

Thanks in advance.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:32
Joined
Feb 19, 2002
Messages
42,987
Welcome aboard Tom. I moved your question to a new thread.

There's lots of stuff available. Several members here have youtube channels that discuss all things Access. If you want to learn about database design specifically, you could take a SQL Server course. The fundamentals of database design are the same for all relational databases. SQL Server does have slightly different SQL syntax and data types and obviously is much more feature rich but if you want an in person class, that is your best option. The Access classes are few and far between and they just skim the surface of database design.
 

ChoiceTom

New member
Local time
Today, 11:32
Joined
Aug 6, 2020
Messages
9
Welcome aboard Tom. I moved your question to a new thread.

There's lots of stuff available. Several members here have youtube channels that discuss all things Access. If you want to learn about database design specifically, you could take a SQL Server course. The fundamentals of database design are the same for all relational databases. SQL Server does have slightly different SQL syntax and data types and obviously is much more feature rich but if you want an in person class, that is your best option. The Access classes are few and far between and they just skim the surface of database design.
Thanks. Makes sense, I wasn't paying attention.
 

ChoiceTom

New member
Local time
Today, 11:32
Joined
Aug 6, 2020
Messages
9
Welcome aboard Tom. I moved your question to a new thread.

There's lots of stuff available. Several members here have youtube channels that discuss all things Access. If you want to learn about database design specifically, you could take a SQL Server course. The fundamentals of database design are the same for all relational databases. SQL Server does have slightly different SQL syntax and data types and obviously is much more feature rich but if you want an in person class, that is your best option. The Access classes are few and far between and they just skim the surface of database design.

I have a background in DB work and programming, but it's been a bunch of years (crisis of faith, completely changed how I put a roof over my head). So what this amounts to is a fair degree of scrubbing the "rust" off and becoming current in tools and techniques.

My thought was to focus on MS-Access as that's the tool I'm using at work. Learn to drive that car, if you will.

Thanks for the reply, it is appreciated.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:32
Joined
Feb 19, 2002
Messages
42,987
The point is - there is little real education out there specifically relating to database design for "Access". If you want to learn about database design, IT DOESN"T MATTER WHAT RDBMS you are going to use. The design concepts are the same. Most common courses settle on SQL Server and you could implement the designs using an Access database just as easily as with SQL Server Express (free version)

Look for youTube videos on Access data base design. They may be enough to get you started.
 

Users who are viewing this thread

Top Bottom