One to One relationship

[RainLover]

I would think he would have a Table that describes the Car.

The Primary Key would be Autonumber and Named something like VehicleIdentityPK

He would list all those Items that depend on the Primary Key.

Namely RegoNumber, ChassisNumber and EngineNumber. All of these Items depend on the Primary Key. That is they help to identify the Vehicle. Other things could be Make and Model.

He would then have a Table to handle the Service.

tblService

ServicePK Autonumber
VehicleIdentityFK as Long
DateOfService
Mechanic
Comments etc.
All these depend on the Primary Key which is the Service.
This Table would have a One to Many Relationship with tblVehicleIdentity
One Vehicle could have many Services

A Third Table would be tblServiceDetails

ServiceDetailsPK as Autonumber
ServiceFK as Long
Item
Comments
All these depend on the Primary Key which is the ServiceDetails.
This Table would have a One to Many Relationship with tblService
One Service could have many Service Items

There is much more that could be added. Namely

Owner Details which could easily be broken down into three tables.

You could have two Tables for the service. One for the Labour and another for the Parts

In this situation I would do ALL of the above and most likely more.

In closing I suggest that the Normalisation of your Database is far more complicated than you realise. It is a big job that you will have to do yourself.

You have reviewed Normalisation. It would do you good to read someone else’s perspective.

 

[MikeFar]

I fully agree with what you said but as I said in my post, forget the dates etc, this is just a report to survey the condition of all the parts. Parts which are broken, scratched, missing, cracked, rusted...all will have an entry beside them. Parts which are ok, will be skipped.

Imagine a printed form and you need to tick or remark on every item of a list consisting of Bumpers, doors, lights, brakes, upholstery, left side indicators, right side indicators.......an endless list. The MS access equivalent will it be in one table with the car reg as ID?

 

[RainLover]

an endless list

This goes against the norm. But I am thinking that in your situation breaking the Rule makes sence. I would put all these items in a Table of its own.

Other items requiring attention.

A primary Key should not be something that the user can see. I would suggest AutoNumber.

You need to Standardise your naming conventions and remove reserved Words like "Name"

There are some good articles on Google about this.

 

[RainLover]

You said that you are having problems understanding the different types of relationships.

tblPatientDrugs is a good example of Many to Many.

 

[MikeFar]

True. 1) I changed it to a many to many following advice from this Forum. It was nor like that in my version 1!
2) Drugs and Medical conditions are probably easy to identify as Many 2 many...with experience.

The fields in the long list will probably have to remain in the PtDetails table as they are all comments and measurements of different bones and body angles. (Similar to describing car parts). These comments are only done once and they are permanent for that particular individual. If anything had to change, that is taken care of in the Notes field of the appointments table.
I thought of creating smaller one to one relationships just to make tables smaller but it is not easy to work with such tables.

 

[RainLover]

I thought of creating smaller one to one relationships just to make tables smaller but it is not easy to work with such tables.

That is exactly what I would do. Two Tables. One to One is the easiest to work with. Just use the Query builder instead of writing SQL yourself. The Query will do that for you.

Good night from Me.

 

[MikeFar]

I will take your advice and try again with the one to one. Re SQL I am still far off. I can hardly convert Macros to VB at this stage. In fact I tried it and I had to go back to Macros!
Still, it will be a 40-50 field table.

 

[Aye]

This is how it is now. Unfortunately, it has plenty of jargon which might not make sense to most people. It is actually planned for a dental clinic so besides the medical aspect, there has to be information about each tooth, occlusion, defects etc.
I am open to all suggestions as this my first real assignment.

I'm also developing a database which is very similar to yours. I attach my tables and relationship which are largely one to many.

Problem I come across is when I run the query using more than 2 tables, I get duplicate data particulary in terms of dates.(eg-Demographic table - Lab investigation table - Imaging table - Management table - Follow up table)
For example, a patient has been operated only once on 2/4/2012, but it come up 6 times instead of one. Similarly, a patient has an investigation done only once on 2/8/2012, but this comes up 6 times.

However duplicates do not come up when I run a query with only 2 tables (eg-Demographic table - Lab investigation table/Management table/Follow up table). Please advise which is the best way to join more than 2 tables to avoid duplicate data in final query.

I already design the Query property sheet unique value to "Yes", and unique record to "No". But duplicates do not disappear.

Any advice is appreaciated.

 

[MikeFar]

If I were you, I would do a one to many relationship with one table having an appt ID as PK, date and test ID, then create a 1 to many from from that table to another table having all the tests in it. Each test ID would have the test details, result, date of result, consultant, value etc.
A query would then yield just the line you need.

Good luck.

 

[Aye]

I revise my tables as you suggested; only two tables with one to many relationship. Now Query really comes out with no duplicate. Thank you very much.

 

[RainLover]

Problem I come across is when I run the query using more than 2 tables, I get duplicate data particulary in terms of dates

The relationships may be incorrect.

Have a look at the query in design mode. Right click on the join and adjust accordingly.