Massive check-list database

[Rainlover]

Things are geting complicated.



You do need a Table and only one for Check List.

You would also need a Join Table where you select the equipment as a Foreging Key and the Check List items also as a Foregin Key.

Is it posible for you to design this then post a Database that contains just the Tables together with some dummy data. Also create your relationships as you believe they should be.

Please convert to Access 2003.

[JFo]

yes you are correct things are getting complicated. Additionally it is now extremely difficult for me to remove or enter dummy data into my database. I just tried for last hour and it breaks everything and have to create completely different forms, tables, etc

Perhaps much easier if you have an email address I can send you a copy

Thanks

[Rainlover]

Create a new database and import the tables.

Zip the database before posting.

Should get you out of trouble.

[JFo]

Haha, just finished as I got your latest message... Here this is best I could do but because I wanted to change names of forms, tables etc some things may not work as well, let me know any questions you may have?

Thanks

Attachment 4437

[Rainlover]

Your Relaionships need some work. I will have a go at fixing them but may take some time.

Is tblExample1 and tblExample2 the various thing that could be done for a particular Inspection?

[Rainlover]

Always use

Option Compare Database
Option Explicit

At the top of each Module. It helps to pick up some errors in your code.

[JFo]

Yep that's right, they are examples of the different checklists that may be used for different equipment in an inspection. I have removed area from my consideration although it still exists in the relationships

Currently the necessary relationships are

1 client may have many sites and 1 site may have many clients
1 site may have many inspections although 1 inspection is only on 1 site
1 inspection may have many equipment and one piece of equipment may have multiple inspections

for each piece of equipment there is a completely different check-list

Thanks for all your help

[Rainlover]

Thanks for the explanation. I will see what I can do but it will take a while. May not get back to you until tomorrow.

PS What is your location. Is it Australia, NZ or close by.

[JFo]

That's fine and yes I am in West Oz, that's why I seem to usually be playing overnight Q+A on these forums with usually a day between Qs+As. appreciate everything no-end, I just want to get the relationships and design correct in first place. Cheers

[Rainlover]

In tblInspections What is ReferenceNo for and who are you recording the Cont Details for.

[JFo]

Hi Rain Lover hope you are well today.

There is a number of independent fields that are not carried/ required in other tables and I think you have found one in reference number. I think reference number is our number that we use to refer to the inspection I am not sure if this can be interchangeable with the primary key at this stage although I have left it there for now, however there is also a number of other numbers throughout the database that will be similar for example equipment number of a certain piece of equipment is different from equipmentid but is supposed to be a unique descriptor of the equipment, however unfortunately it may change over time or for whatever reason with regulations and can also can change from state to state.

As far as contact details this is one of the main bits that is doing my head in as a number of contact details need to be added to database
- 1 main client contact for clients head office (in tblClient)
- multiple client contacts at different sites hence (hence tblClientSiteContact)
- Multiple contacts for the inspection or different areas of site where different pieces of equipment are located I need to link this with area somehow at the moment it is in the Inspection table

Another thought is that I started putting all the check-lists in different tables as I thought that would be the easiest to change order and fields frequently and also as I think every field will have some sort of different validation/ default value. The more I delve into this the more I work I have to do

Cheers

[Rainlover]

Stop work for about another Hour.

I am making dramatic changes to the Tables.

Will send ASAP.

[JFo]

No problem thanks for all

[Rainlover]

Could a site have more than one Contact?

Could a Client have more than one Contact?

[JFo]

Yes a site can have more than one client on site and thus can have more than one contact. Additionally a site may also have more than one clientcontact (multiple contacts for the same client on site)

For ease A client can only have one contact as this is the current contact for client ie Head Office contact and i think can be in client table

Thx