Re: Help With Multiple Tables, Fields and 'query' -

First, in the existing table(s), can you manually change the data in the
table view. If you can't do this then we have to explore other methods.

Do the existing tables have a field (Column) for FirstName, LastName, and
Organization?

If you can manually update the table contents and the fields exist in the
existing table, you can use an update query to update each record.

Create a query that shows you the fields you want to update (populate).
FirstName, LastName, Organization
Add ClientID and the Name field (FieldName)

Now,
Select Query: Update from the menu

Under FirstName in the UPDATE TO cell enter the formula
TRIM(Left([tblName].[FieldName],Instr(1,[tblName].[FieldName],"~")))

UnderLastName enter the formula
TRIM(MID([tblName].[FieldName],1+Instr(1,[tblName].[FieldName],"~")))

Under the FieldName in the criteria enter
Like "*~*"

Select Query: Run from the menu

That should split out the names.

If you don't want to replace existing data add criteria
Is Null under FirstName and Is Null under LastName
that will only update records where both FirstName and LastName is currently
blank.

Organization can be done separately.
Field: ORganization
Update To: [TblName].[FieldName]
Criteria: Is Null

Field: FieldName
Update To: <<Leave blank>>
Criteria: Not Like "*~*"


"Doc" <pc-dc-doc@xxxxxxxxxxx> wrote in message
news:4505ba9c$0$24213$9a6e19ea@xxxxxxxxxxxxxxxxxxxxxxx
John Spencer typed this:
You're not all that clear in what you want to do. Do you need to edit
the data? Are you importing the data into Access tables and want to
set the fields up when you do? Is ClientID in a separate field from
the Name information?

You could use an append query to shove the ClientID and names into
fields in a predefined table.

INSERT INTO YourTempTable (ClientID, LastName, FirstName,
Organization) SELECT ClientID , IIF(TblName.FieldName Like
"*~*",TRIM(Left(tblName.FieldName,Instr(1,tblName.FieldName,"~"))) ,
IIF(TblName.FieldName Like
"*~*",TRIM(MID(tblName.FieldName,1+Instr(1,tblName.FieldName,"~"))) ,
IIF(TblName.FieldName NOT Like "*~*",TblName.FieldName) FROM tblName
WHERE TblName.FieldName is not null

Now you should be able to set up a join between the client id in
YourTempTable and the SourceTable (tblName).

If all you need to do is view the information, then you could use the
above calculations to show LastName, FirstName, and Organizaion

"Doc" <pc-dc-doc@xxxxxxxxxxx> wrote in message
news:45058f59$0$24187$9a6e19ea@xxxxxxxxxxxxxxxxxxxxxxx
Hello, I use MS Access 2003 on Win XP (SP2). I am opening
'exported' (DTS) data from a Solomon DB that has about 1400 tables
and from 10 to 50 fields per table (Yuk!).

Several major tables have a 'field name' of NAME and within those
columns (~140,000 rows) I have a 'last name' ~ 'first name' -
importantly I also have a 'client ID' field that must stay
'related' to the NAME.

I want to take my single column (Field) and bring out the names,
rid myself of the ~ between the last and first name, have it create
"two" fields (Last name, First name) and YET retain my connection
with the "client ID" field for 'importation' back INTO the DATABASE
(which uses the CLIENT ID as the Key).

One "last" complication. In the "name" field there are SOME single
named entries WITHOUT a ~ because they are 'businesses' - I would
love to pull those out and place them in a field called
"organization" and ALSO still retain the "key" relationship.

Any helps is greatly appreciated. Thanks very much. Doc in "Los
Angeles" mailto: pc-dc-doc@xxxxxxxxxxx


yes... "client ID" field is separate from the "name" field.
No. I just want to separate the current "name" field into two fields
(first name, last name) and remove the ~ that resides in between the
names in the current "name" field.
I do NEED to bring the 'newly' created FIELDS (first name, last name)
back into the DATABASE along with the separated "Organization" field...
all to be linked with the "Client ID" field (key).

I won't be taking anything out or adding anything to the table regarding
population of fields.

Not sure here (INSERT INTO YourTempTable) what you mean? Are you saying
to 'create' a new temporary table or will the commands you provided just
create it in memory?

Thanks. I don't know much about access but I was tasked with doing this.
I do networking and all aspects of IT but not DB. Thanks again.

Doc in "Los Angeles"
mailto: pc-dc-doc@xxxxxxxxxxx


.

Relevant Pages

  • Re: Show last records by date?
    ... When you said, "The results should look like what you have listed below in your query example," bear in mind that that Query's only purpose was to associate with each record having a date within your window the last such date. ... It's because he has 3 [Tracking] records attached to him. ... **Tracking table is connected to Contacts table through ClientID. ... Since I believe that it usually makes no sense to expose raw foreign key values to human beings, I almost always define a Lookup property for such fields. ...
    (microsoft.public.access.queries)
  • Re: Help With Multiple Tables, Fields and query -
    ... Create a query that selects the data and shows the fields you want to update as ... Switch to SQL view ... Desire to dump the ~ *and* to separate the first and last names INTO ... FirstName, LastName, Organization Add ClientID and the ...
    (microsoft.public.access.gettingstarted)
  • Re: Help With Multiple Tables, Fields and query -
    ... Create a query that selects the data and shows the fields you want to update as ... Switch to SQL view ... Desire to dump the ~ *and* to separate the first and last names INTO ... FirstName, LastName, Organization Add ClientID and the ...
    (microsoft.public.access.gettingstarted)
  • Re: Show last records by date?
    ... Query and not grouping on those other fields seems to fix the problem, ... The Lookup property will mostly benefit you, if you choose to use it, as ... **Tracking table is connected to Contacts table through ClientID. ... -622026935 Harry Truman ...
    (microsoft.public.access.queries)
  • Re: Show last records by date?
    ... When you said, "The results should look like what you have listed below in your query example," bear in mind that that Query's only purpose was to associate with each record having a date within your window the last such date. ... It's because he has 3 [Tracking] records attached to him. ... **Tracking table is connected to Contacts table through ClientID. ... Since I believe that it usually makes no sense to expose raw foreign key values to human beings, I almost always define a Lookup property for such fields. ...
    (microsoft.public.access.queries)