One to One Relationship

[caniread]

I have a database that I inherited and it has the relationships below. I have not dealt with relationships much and I am not sure if this is the correct way to set it up. It is working but every so often if the ID on one of the tables doesn't match the rest it will make entries disappear from the query. The identity table has many different entries but all the other tables will only have one entry per the ID on the identity table. Hopefully I explained that correctly and it makes sense. Does someone have advice on how this can be setup correctly.

[CJ_London]

you shouldn't be matching primary key to primary key if the primary key is an autonumber because they are auto generated and can quickly go out of sync.

ID is not a good name to use for a field name - it should relate more to the table e.g.

identityID
CEID
ThicknessID
etc

Instead for example your CE table should have

CEID autonumber
identityID long - link to identityID in tblIdentity
CEShear1
etc

Without knowing what your db is supposed to do, it is not possible to comment on whether your relationships are correct except to say that normally you would put 1 to 1 relationships in the same table (thereby doing away with the need for the additional table(s)) and if in separate tables because they are not always required, you will need left joins between tblIdentity and the other tables.

[caniread]

Thank you for the reply,

The reason there are so many tables is that we have way more than 255 fields. I will try to explain what the database does. Identity is a table that has a time and date and that is always different upon every entry. All the other tables are all the data points for that time and date. Does that help?

[CJ_London]

A little and unusual to have more that 255 fields but cannot see how that would work with your setup

You haven't said what datatype your ID's are, so assuming they are autonumber

- you create a record in CE, ID is 1
- this creates a record in identity, ID is 1
- you create all the other records and their ID's are 1

So far so good

you start to create a another record in CE, ID assigned is 2
but you change your mind for whatever reason and cancel the creation

so you create the next record in CE, ID is 3
- this creates a record in identity, ID is 2
- you create all the other records and their ID's are 2

and you have no match.

And even if you don't start and cancel a record creation, autonumbers are only guaranteed to be unique, not necessarily consecutive. they should not be assigned any meaning.

Further if your app is multi user, user A 'starts' a new CE record, at the same time, user B 'starts' a new 'Tears' record - everything else being equal, both would be assigned the same ID.

And what if a new identity does not require an entry in 'thickness', again the ID's will be out of step.

it will make entries disappear from the query

I've already explained in my first post what to do if this is the case

It is working but every so often if the ID on one of the tables doesn't match the rest

hopefully the above explains why this might be happening.

Good luck with your project

[HiTechCoach]

From what I can see all the table related to the identity table are really attributes with the table name being part of the attribute. That tells me that the should all be ins a single table as rows. The would be a simple one-to-may relationship.

[caniread]

Thank you for the reply,

We unfortunately have many measurements that equal more then 255 columns. We need a minimum of two tables. I did a quick new database just to make it simple and see if anyone has any guidance. We do not always have to enter any data into all the tables and that seems to mess things up. So for table 1 there can only be one entry associated in table 2 and 3 but we do not always have any data for table 3. In a form I made I fill in all the data and everything works but if I do not fill in a data point on table 3 and then go back after and fill it in it makes the entry disappear from the query and the ID for that entry go from blank to 0 on the form. Also what are the top three relationships that were there when I opened the relationship tab? Thank you for the help.

[CJ_London]

Is there a way to essentially make two tables as one?

A possible solution was suggested in post#2. It is not ideal, but would be a way forward for now.

You originally asked

Does someone have advice on how this can be setup correctly.

When you have tables with field names like CEshear1, Thick0, LT1 etc, it sends up a warning flag that the data is not normalised and an (typically) Excel way of thinking is being applied. Excel and Access store data in completely different ways. This has been implied or pointed out in virtually every response you have had. On this and other forums, there is not a lot of enthusiasm to help someone do things the wrong way, or continue to do so. You need to focus on your original question and not on trying to make a bad solution work.

You would do better to explain what you business does (looks like something to do with manufacturing or assembly) and how the data relates to each other. Provide some examples. Explain where the data comes from - imported from another system? Manually input? mixture of both. Explain why the timestamp in tblIdentity matters.

[caniread]

I did try out the way that was explained in post#2, see edited post #6. I will be making the changes to better identify the fields but just want to make sure everything works before I put in the time to do so. I made a quick database that I show on post#6. Basically we manufacturer a part and then check it using the date and time as the identification of the part as there will only be one with that date and time. We then have many different tests that are conducted and those test results are manually input in a form. The problem I saw when I tried the new relationship is that when I did not fill in tear in table3 it caused problems later after I had made other entries and went back to update the tear.

[CJ_London]

If these are all tests then you would have something like

tblIdentity
IdentityPK autonumber
TestDate date

tblTests
TestPK autonumber
TestName text

tblIdentityTests
IdentityTestPK autonumber
IdentityFK - long - link to tblIdentity
TestFK - long - link to tblTests
TestValue text? number?

populated with say

tblIdentity
IdentityPK...TestDate
1................01/01/2017
2................01/02/2017

tblTests
TestPK...TestName
1...........Thick0
2...........Tear1
3...........Thick2
4...........LT1

tblIdentityTests
IdentityTestPK...IdentityFK...TestFK...TestValue
1.....................1................1.......... .10
2.....................1................3.......... .24
3.....................1................2.......... .12
4.....................2................1.......... .15
5.....................2................4.......... .22

to get the 'horizontal' view aka excel, you would use a crosstab query.