Multi-part relationship

[PsyberFox]

Hi,

I am trying to put a relationship between two linked SQL tables in Access.
However, the link should be [table1].[field1] =
substring([table2].[field2],1,3) + [table2].[field3].

Is something like this possible, and where is it done?

Thank you in advance!

 

[Paul Shapiro]

It's not possible, and the reason is because that design violates one of the
first rules of db normalization. Each field should be a single atomic value,
without any additional meaning within the field. If you give more details
about the contents of those fields someone could give more specific advice.

As a minimum guess, table1.Field1 should be 2 fields. One would match
substring([table2].[field2],1,3) and one would match table2.Field3. In
table2, field2 should be separated into 2 fields, one containing the present
field2's first 3 characters and one containing the rest.

For example, if you have a partNumber which contains meaninful "sub-data"
within it, you would want to store that part number as multiple fields, one
for each sub-data element. It's easy to combine the fields in a query to
display the complete part number in the format users are accustomed to, but
impossible to work with substrings of a too-inclusive field. So if that part
number has 3 characters for the machine type, then a dash and 2 characters
for the machine subsystem code, and then a dash and 5 digits for the part
identifier, you would store that in 3 fields: machineTypeCode,
machineSubsystemCode, and partIdentityNumber. In a query, you could use
Select machineTypeCode & '-' & machineSubsystemCode & '-' &
partIdentityNumber As PartNumber. You can create relationships from
MachineType and MachineSubsystem to Part. You mentioned creating the
relationships in Access, but you really want to create them in SQL Server so
they can be enforced. I think Access will pick up the relationships from
SQL, although I'm not positive about that.
Paul Shapiro

 

[PsyberFox]

Hi,

This is specifically for a query where the two tables need to be linked to
update certain information in one of the tables from data derived from the
other table. In other words, I'm trying to forge a join in the query itself.

W

It's not possible, and the reason is because that design violates one of the
first rules of db normalization. Each field should be a single atomic value,
without any additional meaning within the field. If you give more details
about the contents of those fields someone could give more specific advice.

As a minimum guess, table1.Field1 should be 2 fields. One would match
substring([table2].[field2],1,3) and one would match table2.Field3. In
table2, field2 should be separated into 2 fields, one containing the present
field2's first 3 characters and one containing the rest.

For example, if you have a partNumber which contains meaninful "sub-data"
within it, you would want to store that part number as multiple fields, one
for each sub-data element. It's easy to combine the fields in a query to
display the complete part number in the format users are accustomed to, but
impossible to work with substrings of a too-inclusive field. So if that part
number has 3 characters for the machine type, then a dash and 2 characters
for the machine subsystem code, and then a dash and 5 digits for the part
identifier, you would store that in 3 fields: machineTypeCode,
machineSubsystemCode, and partIdentityNumber. In a query, you could use
Select machineTypeCode & '-' & machineSubsystemCode & '-' &
partIdentityNumber As PartNumber. You can create relationships from
MachineType and MachineSubsystem to Part. You mentioned creating the
relationships in Access, but you really want to create them in SQL Server so
they can be enforced. I think Access will pick up the relationships from
SQL, although I'm not positive about that.
Paul Shapiro

Hi,

I am trying to put a relationship between two linked SQL tables in Access.
However, the link should be [table1].[field1] =
substring([table2].[field2],1,3) + [table2].[field3].

Is something like this possible, and where is it done?

Thank you in advance!

 

[John W. Vinson]

Hi,

This is specifically for a query where the two tables need to be linked to
update certain information in one of the tables from data derived from the
other table. In other words, I'm trying to forge a join in the query itself.

Without a unique Index on the join field, the joined query won't be updateable
in any case. You may (or may not, depending on the details of the table
structure) be able to use a subquery like

UPDATE table1
SET table1.thisfield = (SELECT table2.thatfield FROM table2 WHERE
table1.field1 = Mid([table2].[field2],1,3) & [table2].[field3]);