new at Access: help with fields

[maxlupo]

Hello,
sorry for the simple question. I started using access since few days..
How can I create a calculated field to be used in a table: Example : I have a field [street] and a field [type] with 2 values: st., rd. in a table [address]

I want to create a third field (the primary key)which should be: fulladdress = [type]&[street]

but i dont find any way to do that...
i tried use expression to enter a default value such that in the property without sucess...and calculated fields seems just for forms, reports, not for tables (like in filemaker for example)

what can I do?

thanks!

massimo
italy

 

[RoyVidar]

Hi!

Creating a new field containing a concatination of two other fields is not a good idea. Data should only be stored one place.

If the two fields (adress and type) is unique and you want to use them as Primary Key, the best way would probably be to create a "combined/composite" Primary Key consisting of both fields.

To do that, you can select both fields in the table design view, and hit the Primary Key button.

Whenever you need to display them concatinated, something like the concatination sample you provide should the trick.

HTH Roy-Vidar

 

[Golom]

Because Access is a RDBMS it doesn't support calculated fields as the definition of a field in a table. You do however have several options.

Primary Key

In the design view for the table select both the "Type" and "Street" fields (use the 'Ctrl' key) and then select / Edit / Primary Key. That will create a composite primary key consisting of the concatenation of both fields.

Displaying the Concatenated Fields

You can build a query that shows the concatenated fields if you want to display a full name with some SQL like

   Select ([Type] & " " & [Street]) As [FullAddress], ...

 

[maxlupo]

Thanks Roy!
i started guessed it but then I didnt know how to create a relationship among the tables using this "double field" key:
will the engine read the total value for the double key, starting from the first of the 2 key fields?

For example "avenue fifth" will be the primary key value, compared in a realtionship?

 

[Golom]

I'm not sure what you mean by "... read the total value ...". The engine will require that the combination of [Type] and [Street] is unique and that neither value is NULL (i.e. you must supply a value for both fields.)

"Primary Keys" and "Relationships" are not quite the same thing. A primary key is a field or combination of fields that uniquely identify a record in a table.

A "relationship" (foreign key) is a field or fields in a table that identify a record or records in another table. A relationship also establishes required data values between tables. You should do a bit of reading about relational database theory to get up to speed on this stuff. It's a different mindset than what you're used to if you come from a ISAM background (e.g. FileMaker, dBase, ...)