create field from two fields

[Guest]

I have a property table and a tenant table. I would like to create a new field
{propertyID} + {TenantID}. One property might have several tenants or none
at all. Also this field needs to be a primary key for new table

 

[Duane Hookom]

Create a new field where? Are the fields text or numeric?
Do you have a good reason for combining two values into a single value? This
isn't normally good practice.
Do you know how to create a make table query?
Do you know how to concatenate expressions together using syntax like:
[LastName] & ", " & [FirstName]

 

[Ed Warren]

A better approach is to keep the data fields seperate and use a 'combined
key' for the Primary key of the new table.

New Table:
[propertyID]
[TenantID]
[field1]
[field2]
etc.

PK ([PropertyID],[TenantID] ) You can set this combined key by highlighting
both fields in table design and selecing the "key".

Now you can set up your relationships to PropertyTable [PropertyID] -->
NewTable [PropertyID],[TenantID] <---[TenantID] TenantTable

Each property can have 0 to Many tenants and each tenant can be related to 0
to many properties.

Even better you can ask queries like "list all the tenant addresses for each
property".

If you really must 'combine' the fields then we must assume you want to
consider them 'text' fields:

e.g.
Propertyid
1
2
3
4

TenantID
1
2
3
4

should become:
1,1
2,1
3,4
And Not
2
3
7
So you use the "&" operator
[PropertyID] & "," & [TenantID]

Ed Warren

 

[Guest]

Thanks
This is working!

A better approach is to keep the data fields seperate and use a 'combined
key' for the Primary key of the new table.

New Table:
[propertyID]
[TenantID]
[field1]
[field2]
etc.

PK ([PropertyID],[TenantID] ) You can set this combined key by highlighting
both fields in table design and selecing the "key".

Now you can set up your relationships to PropertyTable [PropertyID] -->
NewTable [PropertyID],[TenantID] <---[TenantID] TenantTable

Each property can have 0 to Many tenants and each tenant can be related to 0
to many properties.

Even better you can ask queries like "list all the tenant addresses for each
property".

If you really must 'combine' the fields then we must assume you want to
consider them 'text' fields:

e.g.
Propertyid
1
2
3
4

TenantID
1
2
3
4

should become:
1,1
2,1
3,4
And Not
2
3
7
So you use the "&" operator
[PropertyID] & "," & [TenantID]

Ed Warren

I have a property table and a tenant table. I would like to create a new
field
{propertyID} + {TenantID}. One property might have several tenants or
none
at all. Also this field needs to be a primary key for new table