Table relationship issues (1 Viewer)

Good Afternoon,

I am a project manager in construction and I'm currently looking to make my life easier on projects by tracking the progress and issues associated with high rise apartment builds.

I have created a database that does just that and has been working fine until I wanted to add a snag (defective works) tracker within this. I have created a separate table for snag monitoring but I can't get it to associate correctly with other tables.

The tables in the database are as follows:

Activities - which contains the fields "Activity ID" (key) and "Activity Duration"

Apartments - which contains just "Apartment ID" (key)

Apartment Details - which contains "Apartment ID", Activity ID", followed by several other fields containing apartment data for monitoring "Programme Start Date", Programme Completion", "Actual Start Date", "Actual Completion Date", etc.

Snags - which contains "Snag ID No.", "Description", "Date raised" and "Date Closed"

Each individual apartment can have a number of activities, each individual apartment can also have a number of snags.

The 3 tables Activities, Apartments and Apartment Details are linked by many to one relationships with the joining table being Apartment Details.

The issue is I don't know how to join the Snags table into the equation.

I've tried a many to one relationship with the Snags table and all three Activities, Apartments and Apartment Details tables with no joy. Apologies if a similar topic is covered elsewhere or this is relatively simple but I have not that long ago started using Access 2010.

Thanks in advance for any help.

The only relationship you've mentioned between Snags is that an apartment can have multiple ones. Sounds like you just need an ApartmentID in the Snags table.

If that won't do it, explain why and give an example.

This is correct there can be multiple snags per apartment only.

If I add an "apartment Id" field into the snags table and then create a many to one relationship between this and the apartment Id field in the apartments table I get errors asking to link parent and child fields which I don't understand.

Can you post your realtionship screen?

Hi DubSouth
Are the snags related to the apartment or the activity? I could see it going either way.

I agree with putting in an aprtmentID field in the snag table. it needs to be the same data type as the apartmentID as in the apartment table, most likely number as I'm guessing your apartmentID is an autonumber. snag.apartmentID will also need to be able to contain duplicate values as you will potentially have more than one snag per apartment.

To make the relationship work, all of the records in your snag table need to have the apartmentID field populated. If there are any blanks it will raise an error on making the join.

This would be the same process if you wanted the snags to relate to the activities. There is no point trying to link it to both activities and apartments as the activities and apartments are already linked.

Ron

Please find attached the relationship report pdf for the database.

The field and table names I used before are slightly different than I described before as I was trying to keep it simple when I was explaining in my first post.

The apartment fields throughout are Text and not Autonumber.

In this relationship screen the Snags.apartment field is indexed and allows duplicates.

When I try to run this I get the previously mentioned problem regarding parent and child fields. I have tried populating both Snags.apartment and Apartments.apartment fields as suggested with no luck.

Thanks

Why is there a Snag ID field in the Procedures table?

Apologies this was left over from when I've been experimenting with different relationships between tables. I've deleted it now and it has made no difference. To be specific it states Insert Subdatasheet and asks for the link Child and Master fields in dialog box.

You get this error when you enter data directly into the Snag table? Or are you using a form and getting this error?

I receive the error or request to link master and child fields when i work on the Apartments table once I have entered an apartment number and click the plus button to expand to the procedures table.

Then that probably has to do with no Activity being assigned in the Procedures table. Honestly, I'm not that familiar with what you are doing, I generally use forms for data entry.

Anyone else know what's going on?

Hi Dub

I'm not sure I understand what it is you are doing when you get the error you are describing. Can you please advise the error you get and what you are doing at the time.

Separate thing, why do you have a text primary key for apartment? I tend to go for autonumbers so there is no issue with checking for duplicates. You can then have a text field that identifies the apartment, like 3a or 201 whatever the naming convention.
In the related tables, when adding entries I then have the field bound to the autonumber field but show the actual usable identifier. Another benefit of this is that if the apartment label needs to change you don't need to update the other tables.

Ron