combine 2 fileds to make a primary key field using formulas (not SQL programing)

[plyd]

I am a new Access user and not quite up with SQL programming yet. I am trying to combine 2 fields (Build and Unit) into 1 and make that my primary key field. I was hoping to use a formula similar to that in EXCEL but cant find anyway to do this - can someone guide a novice through this (Access2010)?

Thanks
Plyd

[orange]

The best advice I can suggest is to read the first 3 topics and the Hernandez process at this site.
http://www.rogersaccesslibrary.com/forum/topic238.html


Table structure and design are important as you are learning Access. And note Access is NOT Excel.
You could use Access and its interfaces for a lot of things before becoming proficient in SQL.

Good luck

[NTC]

its pretty simple to concantenate two fields during user data input; i.e. presuming you have 3 fields in the table and form: Build, Unit, KeyBU

then in the AfterUpdate Event of Build & Unit you put:
me.KeyBU=Me.Build&Me.Unit

you have to put that code in both because the human could change the data in either at any time; which brings up the issue that in my experience it is better to have the key as an autonumber not seen by anyone. humans are error prone - - and one way or another will screw things up.... perhaps I'm a bit cynical in my old age....

hope it helps.

[orange]

I agree with NTC that auto number as a PK is great. I would set up a field ID as autonumber, and make it the PK. If you want to have an index on BUILD & UNIT, say KeyBU as NTC suggested, then make a unique index on that combination of fields. That will prevent duplicates in your data. (when making that unique index use Ignore Nulls = No.)

[plyd]

This is a big help!!! I am already looking at some of these references

Thanks!!

Plyd

The best advice I can suggest is to read the first 3 topics and the Hernandez process at this site.
http://www.rogersaccesslibrary.com/forum/topic238.html


Table structure and design are important as you are learning Access. And note Access is NOT Excel.
You could use Access and its interfaces for a lot of things before becoming proficient in SQL.

Good luck

[orange]

Great stuff! Keep at it!

[plyd]

thank you for this i will try to find the after update event and put this in.

this is a great help thank you

its pretty simple to concantenate two fields during user data input; i.e. presuming you have 3 fields in the table and form: Build, Unit, KeyBU

then in the AfterUpdate Event of Build & Unit you put:
me.KeyBU=Me.Build&Me.Unit

you have to put that code in both because the human could change the data in either at any time; which brings up the issue that in my experience it is better to have the key as an autonumber not seen by anyone. humans are error prone - - and one way or another will screw things up.... perhaps I'm a bit cynical in my old age....

hope it helps.