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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <pc-dc-doc@xxxxxxxxxxx> wrote in message
news:4509a3e0$0$24213$9a6e19ea@xxxxxxxxxxxxxxxxxxxxxxx
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
SNIP


.

Relevant Pages

  • Re: complex filter and calculations in access
    ... when using a subquery on the same table as the main query, ... switch to SQL view. ... of your database using the from address in this post. ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
  • Re: Multi-Select ListBox
    ... the selected values are then used as query criteria. ... > ' Remove the leading comma from the string ... > ' Build the new SQL statement incorporating the string ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... this clears up a lot of issues and jargon with SQL. ... I think I'll have to try to get a query that will take the BKitIDs and the KitIDs and bring that information together. ... Queries (just shows the QBE grid for convenience -- ... and It really helps to use Aliases for tablenames as it makes the SQL statement shorter. ...
    (microsoft.public.access.forms)