Solved Relationships problem (1 Viewer)

amir0914

Registered User.
Local time
Today, 04:22
Joined
May 21, 2018
Messages
151
Hi all,
I'm confused by the relationships between 3 tables. I have 3 tables include "Project name","Section","Samples", that every Project has several sections and every section has several samples. like this: (this example for 2 project)
Screenshot (1851).png

The first table (projects) and the second table (sections) related together by "project code" field (project code in the first table is unique) . and now I didn't know how can I link "Sections" and "Samples" tables with together, because "section name" in the second table is not unique so I couldn't find a way to relate "Section" and "Samples" tables. Of course the composite field of "project code" and "section name" is unique, because the section name field is not possible to be duplicate in each project.
Screenshot (1852).png

Can someone give me a solution?
 

Attachments

  • RelationShips.accdb
    468 KB · Views: 256

isladogs

MVP / VIP
Local time
Today, 11:22
Joined
Jan 14, 2017
Messages
18,186
It isn't clear what the issue is as there is no data in the tables supplied.
However why can't you join the Section fields in tblSection & tblSample?
 

amir0914

Registered User.
Local time
Today, 04:22
Joined
May 21, 2018
Messages
151
As I said Section fields in tblSection is not unique. because projects has common sections for example "Foundation"or "Column" section, but the combination of "section name" and "project code" in tblSection is unique.

Screenshot (1853).png


Screenshot (1854).png
 

isladogs

MVP / VIP
Local time
Today, 11:22
Joined
Jan 14, 2017
Messages
18,186
Sorry that doesn't help as you haven't showed any data for tblSample.
Suggest you post a sample copy of your dB with data changed to make it anonymous.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:22
Joined
Oct 29, 2018
Messages
21,357
Hi. I am guessing you actually don't have a 1-to-many relationship between Projects and Sections; but rather, you probably have a many-to-many relationship instead. If so, you'll need a junction table to link the two.
 

plog

Banishment Pending
Local time
Today, 06:22
Joined
May 11, 2011
Messages
11,611
As I said Section fields in tblSection is not unique.

Incorrect. In the database you posted, in design view of tblSection, [Section] is designated as the primary key of tblSection. If you look at the properties of it you will see it is both required and duplicates are nto allowed. So the statement you made is incorrect; tblSection.[Section] must be unique.

The way you have your tables set up you simply connect tblSection.[Section] to tblSample.[Section]. If that doesn't work, I suggest you better explain your organization and what you hope this database will allow it to do. 1. Without any database jargon tell us what it is you actually do day to day. 2. With minimal jargon, tell us why you are building this database--what problems will it solve for you?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:22
Joined
Feb 19, 2002
Messages
42,970
When you use "natural" keys as the PK, you will need to use multi-field PKs' on the lower level tables. For example. tblSection's primary key would need to be ProjectCode AND Section and tblSample's PK would need to be ProjectCode, Section, AND ID.

The best solution is to use autonumbers and allow the natural keys to be just data. You can create multi-column unique indexes to enforce business rules to prevent duplicates.

Add an autonumber named ProjectCodeID to tblProject as the PK. Add a Unique index on ProjectCode.

Add an autonumber SectionID to tblSections as the PK Replace ProjectCode in tblSections with the value for ProjectCodeID. Do NOT store ProjectCode in tblSections. Add a unique index on ProjectCodeID and Section to enforce uniqueness.

Rename ID to SampleID in tblSample. Naming all PKS as "ID" is poor practice and just leads to confusion. SampleID should be an autonumber. Replace Section in tblSample with the value for SampleID. Add a unique index on SectionID AND SampleCode to enforce uniqueness.

To make a multi-field unique index, you MUST use the indexes dialog. The picture below shows three indixes. PrimaryKey, UniqueIdx, and OtherIdx. Notice the "UniqueIdx" in the indexName list. by leaving Index Name blank on the second line, you tell Access that ClassID is part of the "UniqueIdx" group. Use whatever name you want.

UniqueIDX3.JPG
 

amir0914

Registered User.
Local time
Today, 04:22
Joined
May 21, 2018
Messages
151
Sorry that doesn't help as you haven't showed any data for tblSample.
Suggest you post a sample copy of your dB with data changed to make it anonymous.
 

Attachments

  • RelationShips.accdb
    992 KB · Views: 193

plog

Banishment Pending
Local time
Today, 06:22
Joined
May 11, 2011
Messages
11,611
Looking closer at this new database I see you have both ProjectCode and Section in tblSample. That is really incorrect. Now we really need to start from the beginning. Please provide a short paragraph on each:

1. Without database jargon, what is it your organization does? Pretend its career day at an elementary school and give us a 3000 foot view.

2. With minimal jargon, explain what it is this database will help you with.

Lastly, you need to use the right datatype for your fields. I see a lot of numeric data in your Short Text fields--those fields should be changed to an appropriate type (Long Integer, Decimal, etc.)
 

amir0914

Registered User.
Local time
Today, 04:22
Joined
May 21, 2018
Messages
151
Thanks plog for replying, the fields type is not important, it's a sample database and only relationships is important for me.
Indeed, we are a Concrete Laboratory, we have many projects (building, apartment, dam,..) in our city or different city. we test all sections (Foundation, Column,Roof,Beam , ...) of a project. we take 2 ~ 4 concrete sample from each section to test in laboratory. previously, we recorded the information in excel file so that each sheet includes information of each project.

Screenshot (1856).png

For second question, it is hard to storage the in all the formations in excel and I want to create database in access to manage data easily and make various report.
 

plog

Banishment Pending
Local time
Today, 06:22
Joined
May 11, 2011
Messages
11,611
Thanks for the explanation and that helps a lot. As Pat suggested prior, you need to use autonumber primary keys to uniquely identify records in a table. Those values then get used in other tables as foreign keys to relate your tables to each other.



In your data, Projects can have multiple Sections, that's a 1-many relationship. You accomplished this by using the primary key in tblProject (ProjectCode) as a foreign key in tblSections (ProjectCode). Although ProjectCode isn't an autonumber that's the correct way to do this.

You now need to establish a 1-many relationship between tblSections and tblSample. Right now tblSection is not set up to do this. You need to add an autonumber primary key to tblSections. I would call that field SectionID. Then you need to make 3 changes to tblSample

1. Remove [ProjectCode]
2. Remove [Section]
3. Add [SectionID]

Then you establish the relationship between tblSection and tblSample by connecting their [SectionID] fields.
 

amir0914

Registered User.
Local time
Today, 04:22
Joined
May 21, 2018
Messages
151
Thank you plog for spending time to this post, this good idea to link by autonumber but this action will there be no problem in the future?
, because I thought link by autonumber is not a right way to join (I knew that's possible). I was assuming that if "tblSections" is destroyed, we won't able to recovery the data, because Autonumber changes every time and is not a specific and fixed field in "tblSections", we use it only to relate between two table.
I had seen somewhere if we don't have a primary key, we could use composite keys. and now we have a unique key ( ProjectCode + Section ) in tblSections.
 
Last edited:

mike60smart

Registered User.
Local time
Today, 11:22
Joined
Aug 6, 2017
Messages
1,899
Thank you plog for spending time to this post, this good idea to link by autonumber but this action will there be no problem in the future?
, because I thought link by autonumber is not a right way to join (I knew that's possible). I was assuming that if "tblSections" is destroyed, we won't able to recovery the data, because Autonumber changes every time and is not a specific and fixed field in "tblSections", we use it only to relate between two table.
I had seen somewhere if we don't have a primary key, we could use composite keys. and now we have a unique key ( ProjectCode + Section ) in tblSections.
Hi Amir

Plog has given you the structure that you need.

I have attached your data which is now related correctly using Autonumber Primary Keys linked to a related Number Field in the Related Table.

See if this helps
 

Attachments

  • RelationShips.zip
    37.6 KB · Views: 191

amir0914

Registered User.
Local time
Today, 04:22
Joined
May 21, 2018
Messages
151
Hi Amir

Plog has given you the structure that you need.

I have attached your data which is now related correctly using Autonumber Primary Keys linked to a related Number Field in the Related Table.

See if this helps
That's great, thank you mike60smart, also thanks to plog to given the solution.
 

mike60smart

Registered User.
Local time
Today, 11:22
Joined
Aug 6, 2017
Messages
1,899
Several users will work with the database. I mean someone may delete the tables incorrectly.
Hi

You said someone would delete the tables?

If you set up the database correctly and Split it into a FrontEnd which contains just the Forms, Queries & Reports and a Backend contains just the data tables.

The users would not have access to the tables in the Backend.
 

isladogs

MVP / VIP
Local time
Today, 11:22
Joined
Jan 14, 2017
Messages
18,186
Following on from Mike's very valid point, users should never have direct access to tables or queries anyway.
One important security step is to hide the navigation pane and ribbon from end users
 

amir0914

Registered User.
Local time
Today, 04:22
Joined
May 21, 2018
Messages
151
Hi

You said someone would delete the tables?

If you set up the database correctly and Split it into a FrontEnd which contains just the Forms, Queries & Reports and a Backend contains just the data tables.

The users would not have access to the tables in the Backend.
Hi, you are right, I had no information about split. I accomplished it after searches.
 

Users who are viewing this thread

Top Bottom