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.

 

[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

 

[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.