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

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.


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 wrote:
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" <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: query error
    ... If you're not familiar with SQL, open the query in Design view, then select ... relationship is between ClientID in Client and customer in company. ... "John W. Vinson" wrote: ...
    (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)
  • Re: nasty SQL query, please advise...
    ... I don't think I can do this in a single query. ... My guess is that the following is correct SQL: ... check that the clientid is not null. ... I don't have an SQL reference book; if I need one I look up the T-SQL ...
    (comp.lang.pascal.delphi.misc)
  • 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)