Hi from Australia and a long lost MS Access user

[Peter Peterson]

Good eveing everyone,
I am back preparing myself to ask for your much needed help again as I revisit my wife's client database which needs a front end designed and written.
I wrote the backend 17 years ago and the database has successfully stored thousands of my wife's clients.
Just that my wife uses the tables to directly add the data as I never got to do the front end, i.e. the forms.
So here I am revisitng the challenge one last time.
Wishing you all a happy day and looking forward to your support as I 'muddle' my way through designing and creating Forms.
Cheers
Peter

 

[bob fitz]

Good eveing everyone,
I am back preparing myself to ask for your much needed help again as I revisit my wife's client database which needs a front end designed and written.
I wrote the backend 17 years ago and the database has successfully stored thousands of my wife's clients.
Just that my wife uses the tables to directly add the data as I never got to do the front end, i.e. the forms.
So here I am revisitng the challenge one last time.
Wishing you all a happy day and looking forward to your support as I 'muddle' my way through designing and creating Forms.
Cheers
Peter

Hi Peter. Welcome to the forum

Before you start on the Front end it would be wise to ensure that your tables are designed and related correctly.
Can you post a copy of the db or show us a picture of your relationships window.

 

[Peter Peterson]

Good evening Bob,
Thank you for your welcome.
I have attached my table relationship view as requested.
My Control Table sits isolated and will eventually include the storage of a Password - only Windows Logon security at present.
There is a HealthInsurer Table also isolated which I need to include.
A change I need to make is getting rid of Health Insurer Groups which connected to Health Insurers - now there is only Health Insurers
I am most open to any suggesting/advice you or others may have.
Kind regards
Peter

 

[NauticalGent]

Welcome aboard!

 

[The_Doc_Man]

Hello, Peter, and welcome to the forum.

 

[Peter Peterson]

Good morning,
Thank you @NauticalGent & @The_Doc_Man for your welcome.
Cheers
Peter

 

[pbaldy]

Welcome aboard. FYI I moved the thread out of the intro forum since it's starting to get technical.

 

[jdraw]

Welcome to AWF, Peter.

 

[Pat Hartman]

Welcome. I'm looking at your schema and it looks OK but it has lots of inconsistencies. It would be great to fix them before you start building forms and reports. Consistency is your friend. It keeps you from making compile errors because this name has an embedded space but none of the other do. For Example:

• embedded spaces
• special characters such as the dash. The only standard characters are a-z, A-Z, and the underscore
• names with suffixes. Generally this implies the data should be moved to a separate table. Addr1 and Addr2 are only separate for the purpose of printing so that doesn't count.
• Some tables use ID rather than a descriptive name for the PK
• table naming is also inconsistent
• not all FK's have RI enforced

The only other issues I see are:

• Clients could have multiple doctors and multiple injuries so I would make injury a child table of Client and doctor would live there.
• Then you need to decide if you have visits as a child of Clients or as a child of injury.
• If you are going to include insurance, insurance would be a field or potentially child table of visit because insurance changes over time and if you ever had to go back historically, you would not be able to figure out what insurance company you billed for a visit last year.
• Rather than listing a bunch of doctors by type, you should have a child table. That allows you to also track doctors over time and eliminate the need for doctor-old
• I don't like starting a field name with a non-letter character but the underscore does make the FK's pretty obvious. I use a suffix of FK just to make them obvious.

Essentially you have Clients, Visits, Doctors, Medical Centers and the rest of the tables are lookups. I have a table maintenance mini-app that I include in every new app. It is used for ALL lookup tables and keeps you from having to either maintain them by hand or create forms for each separate table. If you wanted to adopt the concept, there would need to be a one-time conversion to get the new IDs for the FK's. Here is a link:

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

So, once you get rid of the lookup tables, the problem becomes pretty small. Probably just 3 main forms and a subform. Plus maybe a menu and of course the forms that go with the mini-app. The forms for suburb, state, and country don't technically fit in the mini-app so you could make forms for those or simply change them manually if you have to since these tables probably won't change once in 10 years.

If your wife is the only user of the database, then I would not put on any security at all unless the app is loaded on a shared computer. I would however still use the split database model to make changes easier. You get to make the changes on your test system and then replace her db's with your updated one. When I am changing an application like this and I need to change the schema, I take the production BE offline for a few minutes and add the new fields and columns as I discover them and then pick up a new copy of the BE to work with. Otherwise, you need to keep good track of the changes that need to be made to the BE when you implement the new FE version.

 

[Peter Peterson]

@Pat Hartman,
Thank you for your comprehensive email which I have been addressing, and still have more to do.
I appreciate your approach of not specifically telling me where I could make changes, but highlighting best practice which has caused me to find the errors of my ways - I will learn from this.
Have returned to discuss with my user (my wife) how I could make things simpler, which has forced me to focus on the data collected/confirmed at the visit, as opposed to the client table, as per your comments above.
I have a database relationship structual question which I am strugling with:
1. Some Clients are covered by health insurance.
2. These Clients are covered by a Health Fund.
3. Clients can and do change their health insurance provider.
4. For a massage therapist to provide a servcie under a Health Fund, they must meet certain criteria.
5. If a massage therapist meets the certain criteria they are allocated a Provider Number.
6. The Provider Number must be recorded on the Receipt allowing the Client to claim their rebate.
7. For the most part Provider Numbers are provided by a Health Fund Group.
8. Provider Numbers are unique to each Health Fund Group with no similar format.
9. Health Fund Groups include any number of Health Funds as members.
10. However, not all Health Funds are included in a Health Fund Group.
11. Health Funds not part of a Health Fund Group provide their own Provider Number.
12. Some Health Funds, until recently were identified as only one entity, have started partnerships with compnaies who rebrand their Health Fund in the name of the Company, e.g. our national airline Qantas, now has Qantas Health Insurance which is simply rebranded from NIB, but no one would know this specifically, people simply believe they are insured by Qantas.

I hope my explaination is somewhat clear and I need some guidance on how to structure the data tables and relationship. I have attached a picture of what I think I need to do whilst I try and get my head around it. Note, I can not include the FK number in the Health Funds table for some reason???

On another topic - I'm not sure what you mean by getting rid of the lookup tables? I have viewed your Table Maintenance Mini-app but need to spend more time to understand it.

Once again thank you for your time and support - it is much appreciated.

Kind regards
Peter

 

[Pat Hartman]

1-3 == I talked about this in my overview. You will need a table to define the insurance companies and possibly plans. You would need a m-m table to define which plans a patient uses. Here you would keep start and end dates. Since you need to keep history, you would simply mark the old plan with an end date. Then when a person visits, you record the insurance that is used for that visit. Your code will list the plans connected to the patient but only allow selection of a plan that has no end date.
4 == I would not attempt to implement 4 in the application. Maybe after everything else is working, you can investigate it but once you do that, you will need to plan on updating the application whenever the government changes the rules. I don't know how much warning they give you. Do they tell you in July that the rules will change in January so you have a chance to modify the application before the rules take effect. Also, how will you handle other changes that would also have to be implemented during this time frame. If you have a change that will take two months to implement, how can you make a 5 minute change? You will need to have extremely good source control and functional isolation.
5 -7 == are not something you would ever put in the app. Either you have a provider number or you don't. If you have one, it goes on the receipt.
8,9 == you just need a field in the table that holds the Health Fund Group definition
10,11 == I would create a dummy group for the non-group plans. This allows all future processing to not require differentiation.
12 == Red herring. If you think you need to know this, add an additional field to the Health Fund Group definition table for a "parent" Group.

I can not include the FK number in the Health Funds table for some reason??

Not enough information. Are you getting an error message?

On another topic - I'm not sure what you mean by getting rid of the lookup tables? I have viewed your Table Maintenance Mini-app but need to spend more time to understand it.

While there is no design issue with having each lookup list as a separate table, I thought I explained that the mini-app combines all single field lookups into ONE table so instead of 10 tables/forms/queries to manage your lookups you have 1 table/form/query. Hopefully, when you review the table maintenance app, you'll understand the concept. It actually uses two tables, two forms and two reports.

 

[Peter Peterson]

@Pat Hartman
Sorry for the delay in responding - life, work and children (20+yo) have consumed my time.
I am working through tidying up my database tables - finding more opportunities of being more efficient (errors)
I have revisited your mini-app and I can't get past the macro security message on my computer, however, that aside, I still can't grasp what you are trying to share with me.
I have had a 'crack' at solving this - see attached file.
If I have this correct, I then expect to create queries for each column which is used on my forms.
I know that I need to resolve the single column table before proceeding.
I look forward to your guidance and thank you for your patience.
Regards
Peter

 

[mike60smart]

Hi Peter

What Pat is trying to explain is normally most users would have a separate table listing the Titles to be used in a Combobox on your Forms whenever you need to define the Title of a Person.

The same applies to each of the Columns in your uploaded screenshot.

However, if you use Pat's method your opnly need to maintain this 1 table which contains all data for every Combobox you would need.

Using the 1 table means you can create Combobox's based on this one table.

 

[Pat Hartman]

I have revisited your mini-app and I can't get past the macro security message on my computer, however, that aside, I still can't grasp what you are trying to share with me.

The messages are not coming from the application. They are from Windows.
When you download file from the internet, Windows "protects" you from it so you have to right click on the file name and unlock it. You should also move it to a trusted folder. If you do both things and still get a message, you will need to post it.