If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > How to combine two fields into one?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 358
How to combine two fields into one?

Hello,

I need to merge a multi-choice prefix field to my ID field and have both fields combined into one field called "FullID" in my table.

The ID field is an auto number, and the prefix choice will come from a drop-down list lookup field in the same table e.g. AA, BB, CC. So the combined FullID will be AA001, AA002, BB003 etc etc.

What would be the best way of doing this? Im thinking a query to merge the two fields, then another query to append into the FullID field, all executed from a macro?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2005
Location: Nevada, USA
Posts: 2,793
Typically you wouldn't save a field like that, as it can be calculated from existing fields. You'd just display it by concatenating the two together:

PrefixField & AutoNumberField

If you really needed to have the field, you could do the same thing in a simple UPDATE query (the above would be the Update To). Of course you wouldn't want to keep doing that (which is why the field shouldn't exist), so you'd do it on the form where users added records.
__________________
Paul
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 358
Quote:
Originally Posted by pbaldy View Post
Typically you wouldn't save a field like that, as it can be calculated from existing fields. You'd just display it by concatenating the two together:

PrefixField & AutoNumberField

If you really needed to have the field, you could do the same thing in a simple UPDATE query (the above would be the Update To). Of course you wouldn't want to keep doing that (which is why the field shouldn't exist), so you'd do it on the form where users added records.
Hello, I know I can concatenate the two fields by using a query ( [PrefixField]&" "&[AutoNumberField] ) but I do need to have it saved in a field in the table after the query has concatenated the two fields.

So what would be the best way to save it into a field? The only way I have made it work so far is to have 2 tables (a temp table, and an ammended table). Then use a query to concatenate, then use a query to append into the append table, then use a query to delete from the temp table, all run via a macro! It works, but its very long winded, so surely there is a simpler option.

I havent been able to make an update query work yet. I get the message "operation must use an updateable query".

Last edited by moss2076; 07-24-10 at 06:12.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2005
Location: Nevada, USA
Posts: 2,793
Presuming the two fields are populated this should work:

UPDATE TableName
SET JointField = PrefixField & AutoNumberField

But what you would want to do is do it on the form where user's enter data, rather than be constantly updating the table.
__________________
Paul
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Aug 2004
Posts: 358
Yup that has sorted it, many thanks

I found it strange that Access wasnt able to do it when I selected an update query from the Query Type button, but your code has got it working!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On