Help With Multiple Tables, Fields and 'query' -

  • Thread starter Thread starter Doc
  • Start date Start date
D

Doc

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: (e-mail address removed)
 
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
 
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
Click to expand...


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: (e-mail address removed)
 
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 "*~*"
 
John Spencer typed this:
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.
Click to expand...

Yes. I can change it... just opened it, selected a field and deleted a
number in that field. Opened it, was gone, re-entered that field data,
saved, opened, was correct and present.
Do the existing tables have a field (Column) for FirstName, LastName, and
Organization?
Click to expand...

No... those fields are NOT present. I have edited the table and ADDED
three new fields (first name, last name, 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.
Click to expand...

That is what you are displaying below these entries? Okay.
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 "*~*"
Click to expand...

I 'think' I understand this... I use the word 'like'? Okay...
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 "*~*"
Click to expand...

I will attempt this today, later - thanks very much. Hoping this works
as exporting to a smaller EXCEL sheet, then a .csv and then 'find -
replace' the ~ with a , and reimporting after 'adding' the first name,
last name, etc... what a drag. I need to learn ACCESS better. Thanks
again...

Doc
 
OUCH - did the "update query" and under FIRST NAME, it WROTE "NAME" to
all of them -
Believe my error to be upon 'parameter' query as I started to run the
UPDATE, I placed "name" (thinking I was using the 'field' value to be
split at the ~ in the 'name' field - but I obviously don't know what I
did), and 'name' came out in the 'first name' new field (nothing in last
name)...

To recap
Original field is "name" - data in that field "first name ~ last name"
Desire to dump the ~ *and* to separate the first and last names INTO
new fields called "First name" and "Last Name" -
There are some "organizations" that do NOT have a ~ anywhere, I do
want to bring them out and place them into a NEW field called
"organizations" - and key them all with the USER ID field which has
always existed.

I can make changes to the DATABASE upon opening it.
I can add or subtract fields.

I must've misread the instructions. Ideas? Thank you very much John.


John Spencer typed this:
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 said:
John Spencer typed this:

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: (e-mail address removed)
Click to expand...
Click to expand...
 
You need to enter in brackets the tablename a period and in brackets the fieldname

So now you need something like the structure below for the LastName

Field: TheFieldToPopulate
Table: TheTableToPopulate
Update To: TRIM(MID([TheTableToPopulate].[TheSourceField],1+Instr(1,[TheTableToPopulate].[TheSourceField],"~")))
Where: "Name"

Let's try this a slightly different way.

Create a query that selects the data and shows the fields you want to update as
well as the source fields.
Switch to SQL view (View: SQL)
Post the SQL Statement

I will try to rewrite that SQL statement and post it. Then you can copy the
SQL, paste it into a query (SQL View) and then switch back to the grid view to
see what it looks like.


OUCH - did the "update query" and under FIRST NAME, it WROTE "NAME" to
all of them -
Believe my error to be upon 'parameter' query as I started to run the
UPDATE, I placed "name" (thinking I was using the 'field' value to be
split at the ~ in the 'name' field - but I obviously don't know what I
did), and 'name' came out in the 'first name' new field (nothing in last
name)...

To recap
Original field is "name" - data in that field "first name ~ last name"
Desire to dump the ~ *and* to separate the first and last names INTO
new fields called "First name" and "Last Name" -
There are some "organizations" that do NOT have a ~ anywhere, I do
want to bring them out and place them into a NEW field called
"organizations" - and key them all with the USER ID field which has
always existed.

I can make changes to the DATABASE upon opening it.
I can add or subtract fields.

I must've misread the instructions. Ideas? Thank you very much John.

John Spencer typed this:
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 said:
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

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: (e-mail address removed)

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: (e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
John Spencer typed this:
You need to enter in brackets the tablename a period and in brackets the fieldname

So now you need something like the structure below for the LastName

Field: TheFieldToPopulate
Table: TheTableToPopulate
Update To: TRIM(MID([TheTableToPopulate].[TheSourceField],1+Instr(1,[TheTableToPopulate].[TheSourceField],"~")))
Where: "Name"

Let's try this a slightly different way.

Create a query that selects the data and shows the fields you want to update as
well as the source fields.
Switch to SQL view (View: SQL)
Post the SQL Statement

I will try to rewrite that SQL statement and post it. Then you can copy the
SQL, paste it into a query (SQL View) and then switch back to the grid view to
see what it looks like.
Click to expand...


John,
This is what I did - so far, so good.

added 3 new fields to said table: LAST, FIRST, ORGANIZATION
Created an Update query:
UPDATE Table1 SET for the last name:
IIf([name] Like "*~*",RTrim(Left([name],InStr(1,[name],"~")-1)))

for the first name:
IIf([name] Like "*~*",LTrim(Mid([name],InStr(1,[name],"~")+1)))

to pull the 'organizations' out:
IIf([name] Not Like "*~*",[name]);

Ran the Update Query -

ASIDE from ACCESS saying 'not enough memory to reverse' the changes, it
went well.

CAN YOU suggest a similar "query" to CLEAR or DELETE UN-POPULATED FIELDS
from a TABLE?

WOULD you be able to suggest a book on QUERY language that I person a
bit above beginner could understand?

MANY thanks!

Doc

Doc said:
OUCH - did the "update query" and under FIRST NAME, it WROTE "NAME" to
all of them -
Believe my error to be upon 'parameter' query as I started to run the
UPDATE, I placed "name" (thinking I was using the 'field' value to be
split at the ~ in the 'name' field - but I obviously don't know what I
did), and 'name' came out in the 'first name' new field (nothing in last
name)...

To recap
Original field is "name" - data in that field "first name ~ last name"
Desire to dump the ~ *and* to separate the first and last names INTO
new fields called "First name" and "Last Name" -
There are some "organizations" that do NOT have a ~ anywhere, I do
want to bring them out and place them into a NEW field called
"organizations" - and key them all with the USER ID field which has
always existed.

I can make changes to the DATABASE upon opening it.
I can add or subtract fields.

I must've misread the instructions. Ideas? Thank you very much John.

John Spencer typed this:
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 "*~*"


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

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: (e-mail address removed)
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: (e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Best book I know of is
Sql Queries For Mere Mortals by Hernandez and Viescas. It is a general text
and not specifically aimed at Access.

I don't understand what you want to do when you say CLEAR or DELETE
unpopulated fields from a table? Do you want to delete the field from the
structure permanently if there is no data in that field in any record in the
entire database?

Since you are working with tables exported from another source, I would tend
to leave the tables alone.

This is getting beyond what I can support on a volunteer basis. If I were
you, I would see if I could find a consultant (locally) or a class.


Doc said:
John Spencer typed this:
You need to enter in brackets the tablename a period and in brackets the
fieldname

So now you need something like the structure below for the LastName

Field: TheFieldToPopulate
Table: TheTableToPopulate
Update To:
TRIM(MID([TheTableToPopulate].[TheSourceField],1+Instr(1,[TheTableToPopulate].[TheSourceField],"~")))
Where: "Name"

Let's try this a slightly different way.

Create a query that selects the data and shows the fields you want to
update as
well as the source fields.
Switch to SQL view (View: SQL)
Post the SQL Statement

I will try to rewrite that SQL statement and post it. Then you can copy
the
SQL, paste it into a query (SQL View) and then switch back to the grid
view to
see what it looks like.
Click to expand...


John,
This is what I did - so far, so good.

added 3 new fields to said table: LAST, FIRST, ORGANIZATION
Created an Update query:
UPDATE Table1 SET for the last name:
IIf([name] Like "*~*",RTrim(Left([name],InStr(1,[name],"~")-1)))

for the first name:
IIf([name] Like "*~*",LTrim(Mid([name],InStr(1,[name],"~")+1)))

to pull the 'organizations' out:
IIf([name] Not Like "*~*",[name]);

Ran the Update Query -

ASIDE from ACCESS saying 'not enough memory to reverse' the changes, it
went well.

CAN YOU suggest a similar "query" to CLEAR or DELETE UN-POPULATED FIELDS
from a TABLE?

WOULD you be able to suggest a book on QUERY language that I person a bit
above beginner could understand?

MANY thanks!

Doc
Click to expand...
SNIP
 
HI John
Thanks. I'll look up the book.
What a 'did' want to do was to query the 'exported' (DTS) database
(which will NEVER go back to the original Solomon as we are migrating to
another whole application) Tables for (one at a time - 1400 tables!
Ouch) UNPOPULATED 'fields' - and yes, I 'did' want to delete that WHOLE
field, that column, whatever it would be properly called.

We have no budget for a consultant - the company is 'non-profit' (and I
mean 'non-profit') and is supported by donations. I dropped my IT rate
to help them but really have not done much SQL. I have learned a lot
about DTS and will see if I can massage that to export ONLY populated
fields as I export the 20 or so tables that look to be important - of
course to be honest, I am NOT going to touch the PRODUCTION DB or alter
it as NO ONE is sure what is used, not used, so forth. I do know that
EMPTY (unpopulated) fields that just have some 'heading' info that no
one even recognizes will have to go (in the 'exported' DB only).
Thanks,
Doc

John Spencer typed this:
Best book I know of is Sql Queries For Mere Mortals by Hernandez and
Viescas. It is a general text and not specifically aimed at Access.

I don't understand what you want to do when you say CLEAR or DELETE
unpopulated fields from a table? Do you want to delete the field
from the structure permanently if there is no data in that field in
any record in the entire database?

Since you are working with tables exported from another source, I
would tend to leave the tables alone.

This is getting beyond what I can support on a volunteer basis. If I
were you, I would see if I could find a consultant (locally) or a
class.


Doc said:
John Spencer typed this:
You need to enter in brackets the tablename a period and in
brackets the fieldname

So now you need something like the structure below for the
LastName

Field: TheFieldToPopulate Table: TheTableToPopulate Update To:
TRIM(MID([TheTableToPopulate].[TheSourceField],1+Instr(1,[TheTableToPopulate].[TheSourceField],"~")))
Where: "Name"

Let's try this a slightly different way.

Create a query that selects the data and shows the fields you
want to update as well as the source fields. Switch to SQL view
(View: SQL) Post the SQL Statement

I will try to rewrite that SQL statement and post it. Then you
can copy the SQL, paste it into a query (SQL View) and then
switch back to the grid view to see what it looks like.
Click to expand...

John, This is what I did - so far, so good.

added 3 new fields to said table: LAST, FIRST, ORGANIZATION
Created an Update query: UPDATE Table1 SET for the last name:
IIf([name] Like "*~*",RTrim(Left([name],InStr(1,[name],"~")-1)))

for the first name: IIf([name] Like
"*~*",LTrim(Mid([name],InStr(1,[name],"~")+1)))

to pull the 'organizations' out: IIf([name] Not Like "*~*",[name]);


Ran the Update Query -

ASIDE from ACCESS saying 'not enough memory to reverse' the
changes, it went well.

CAN YOU suggest a similar "query" to CLEAR or DELETE UN-POPULATED
FIELDS from a TABLE?

WOULD you be able to suggest a book on QUERY language that I person
a bit above beginner could understand?

MANY thanks!

Doc
Click to expand...
SNIP
Click to expand...
 
Back
Top