merging two fields into one (with a twist)

[Callum]

Hi guys, first post

my problem is quite simple (as far as i can tell) but none-the-less i'm stumped.

what i want to do is merge 2 fields into a separate field with the first field being Unique_Id (autonumber, indexed and no dupes) merge with the second field Type (3 letter field) so it should populate the third field automatically as NDE123 etc , but what i also need (which looking around the internet for afew hours held no aveil) is for it to be automatic even when a new records is made, so when a new record is inevitably made the first field will be autonumberd, the 2nd will have a default value, so it will mostly be entirly automatic, is this possible? or am i clutching at straws.

Thanks, Callum

 

[plog]

Your not clutching at straws, your fighting a battle that probably shouldn't be fought. We know the autonumber field won't change, could the data in a record's Type field change, and if so, would it affect the 3rd field?

For example 2 months into the future the NDE123 record's Type field changes from NDE to ABC--does that mean NDE123 should become ABC123 or should it remain NDE123?

 

[jdraw]

You have described How you want something to happen, but you haven't said anything about Why. Sometimes if you describe the "business need" you'll find there are several options as to how it might be accomplished.

If you had an autonumber field (fld1), it would be populated automatically by Access.
You would not have control over the values, but those numbers would be unique. Normally this field would be identified as Primary Key(PK) and would have No NULLs, and no Dups. Many will tell you this field should be meaningful ONLY to Access. If you want some sort of identifier for yourself, that could be accomplished separately.
As for a second field of text data type (fld2), your field to be populated as your process dictates.

For queries/forms reports etc, you could concatenate fld1 and fld2, or fld2 and your separate identifier field, for whatever purpose.

Good luck.

 

[Callum]

NDE123 will be NDE123 indefinatly, all the records in the NDE table will be NDE(number) there is other tables with other three letter codes hence why just having number is NOT unique, the reason is after they are put into mapinfo, there shall be more than just one record with the number 123 etc.

 

[plog]

Since NDE will always be NDE, you do not need that 3rd field. You shouldn't store calculated values, which this is.

Whenever you need to have NDE123 appear (i.e. on a form, on a report, in a queryn) you simply take your 2 fields (Unique_ID and Type) and contencate them:

=[Unique_ID] & [Type]

 

[Callum]

you cannot concatenate within MapInfo, THAT is my problem, i want both to be in the same field within MapInfo. and to do that they need to be within access

 

[jdraw]

Perhaps you should tell us where and how MapInfo fits in your activity.

You can create a query in Access and create (MakeTable) to create a concatenated field, that could be passed to other processes.

 

[boblarson]

you cannot concatenate within MapInfo, THAT is my problem, i want both to be in the same field within MapInfo. and to do that they need to be within access

Normally, you concatenate inside a QUERY and not the table as you can usually use a query in about 99% of the places you can use a table. If MapInfo cannot use a query from Access as a table, then you will likely have to create the extra field (unfortunately). So, my question, before going on is what version of Access are you using?

 

[Callum]

i'm currently using Access 2000 , MapInfo cannot use queries for layers which is really the dilemma.

mapinfo fits into my activity when i plot the X and Y columns into mapinfo have 3 seprate tables, so when interogated for information the object on the map has the same data as the record it is linked to on the database. so three tables = 3 numbers the same, hence my need for a Unique Reference.

 

[boblarson]

So I guess you will need to create a query which can populate a table to include a field which concatenates those. Do it as a make table query first and then change it to be an append query. Run it once to make the table and then from then on you can use a delete query

Delete * From TableNameHere

to delete it all and then use the append query to append the needed records. Or I guess you could have another field in your normal table and then if you add records through a form, you could, in the form's Before Update event, concatenate the values to a hidden text box bound to the field in the table.

 

[Callum]

i haved used the Form dataentry to populate an Id automatically as best as needed, thanks to all that helped.