Apply to every Record

[Alex]

I have an extermely long list of Names (around 236,000) which are separated into First, Middle, and Last. I need to come up with a way to join each individual part into a field on another table called Complete, so that the Complete name will be saved in one field and not in its individual parts. However, I need to do this in a way that I can push a button or something and apply the joining to all the records. I did something like this once, and put it on the OnCurrent event of the form, and then just ran through the records, whoever that was only around 500 records, and it took me quite a long time. So if someonce can help me with a way to work through this problem, I'd really appreciate it.

 

[Allen Browne]

1. Create a query into your table.

2. In the Field row of query design, enter something like this:
FullName: [First] + " " & [Middle] + " " & [Last]

3. Change it into an Append query (Append on Query menu).
Access will ask you which table you want to append to.

Note that the separate names are a better design, and you can show the
combined names like that any time, so it would not normally be desirable to
store the combined name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have an extermely long list of Names (around 236,000) which are

separated into First, Middle, and Last. I need to come up with a way to
join each individual part into a field on another table called Complete, so
that the Complete name will be saved in one field and not in its individual
parts. However, I need to do this in a way that I can push a button or
something and apply the joining to all the records. I did something like
this once, and put it on the OnCurrent event of the form, and then just ran
through the records, whoever that was only around 500 records, and it took
me quite a long time. So if someonce can help me with a way to work through
this problem, I'd really appreciate it.

 

[Van T. Dinh]

Since "Complete" is a Calculated Value that can be derived from other data
already stored in your database, storing Complete values violates the
Relational Database Design Principles.

Suggest you don't store these calculated values since storing them will
create inconsistecies in your database later.

--
HTH
Van T. Dinh
MVP (Access)

I have an extermely long list of Names (around 236,000) which are

separated into First, Middle, and Last. I need to come up with a way to
join each individual part into a field on another table called Complete, so
that the Complete name will be saved in one field and not in its individual
parts. However, I need to do this in a way that I can push a button or
something and apply the joining to all the records. I did something like
this once, and put it on the OnCurrent event of the form, and then just ran
through the records, whoever that was only around 500 records, and it took
me quite a long time. So if someonce can help me with a way to work through
this problem, I'd really appreciate it.

 

[John Vinson]

I have an extermely long list of Names (around 236,000) which are separated into First, Middle, and Last. I need to come up with a way to join each individual part into a field on another table called Complete, so that the Complete name will be saved in one field and not in its individual parts. However, I need to do this in a way that I can push a button or something and apply the joining to all the records. I did something like this once, and put it on the OnCurrent event of the form, and then just ran through the records, whoever that was only around 500 records, and it took me quite a long time. So if someonce can help me with a way to work through this problem, I'd really appreciate it.

Well, the first thing I'd suggest is... DON'T.

The concatenated name need not, and generally SHOULD not, be stored in
any table. It's redundant; if someone changes their name, you would
now have two fields to change, not one, and the full name would just
waste space in your table.

Instead, concatenate the name in a Query. Create a new Query based on
your table, and type

Complete: [First] & " " & ([Middle] + " ") & [Last]

in a vacant Field cell. This will concatenate the name; the tricky bit
with the ([Middle] + " ") will prevent people with no middle name from
showing up with an extra blank (like John Vinson).

Ths Query can now be used as the recordsource for a Form or for a
Report; it can be exported; it can be used for a mailmerge; just about
anything you'ld do with a table.

If (for some reason) you REALLY REALLY want to violate normal design
rules and store this field anyway, use an Update query, updating the
field Complete to the same expression as above.

 

[Alex]

Thank you everybody, I know this is strange, so I will put reasons behind it. I have another list of names...not as long as my previous one. In the extremely long list, I have other information, such as addresses and phone numbers, and in the short list (about 1000) I have just the names, however these are complete, they are not separated. So I wanted to join the other names, so I could find each record and put all of the information on the long list in the short list. Not all the records, but all the other info. So what I had in mind, was making a table with all the info of the long list, plus the complete names, then making a query that would only show me records whose names appeared on my short list. If there is an easier way to do this, then please elaborate

 

[John Vinson]

Thank you everybody, I know this is strange, so I will put reasons behind it. I have another list of names...not as long as my previous one. In the extremely long list, I have other information, such as addresses and phone numbers, and in the short list (about 1000) I have just the names, however these are complete, they are not separated.

If the names in the short list are in the form (let's say) "Vinson,
John W." you can create an Update query joining the two tables,
joining on the concatenation expression:

SELECT BigTable.*, SmallTable.Complete
FROM BigTable INNER JOIN SmallTable
ON SmallTable.Complete = BigTable.LastName & ", " & BigTable.FirstName
& " " & BigTable.MiddleName;