update query

Is there anyone that knows how to import a txt file into access and create an
update query to update a table?

Any help would be appreciated.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

On Sat, 10 Feb 2007 18:21:32 GMT, "DucateAssignment via
AccessMonster.com" u30108@uwe wrote:

Is there anyone that knows how to import a txt file into access and create an
update query to update a table?

Yes. Use File... Get External Data... Import (into an existing or a
new table), or File... Get External Data... Link, as appropriate, and
then create an Update query based on that table linked to your local
table.

If you would like a more specific answer, please post a more specific
question.

John W. Vinson [MVP]

Hey John

I can import the file and make a new table. I don’t know if I should import
the new data into an existing table or should I import the new data into a
new table. After that, how can I link one table to the other and complete
the update.

Thanks again

John W. Vinson wrote:
Is there anyone that knows how to import a txt file into access and create an
update query to update a table?

Yes. Use File... Get External Data... Import (into an existing or a
new table), or File... Get External Data... Link, as appropriate, and
then create an Update query based on that table linked to your local
table.

If you would like a more specific answer, please post a more specific
question.

John W. Vinson [MVP]

--
Message posted via http://www.accessmonster.com

On Sat, 10 Feb 2007 23:33:45 GMT, "DucateAssignment via
AccessMonster.com" u30108@uwe wrote:

Hey John

I can import the file and make a new table. I don’t know if I should import
the new data into an existing table or should I import the new data into a
new table. After that, how can I link one table to the other and complete
the update.

Since I cannot see your database, and you haven't posted any
information about these tables, the data they contain, how they are
related, or much of anything else, all I can say is "by joining them
on an appropriate field and putting [tablename].[fieldname] into the
appropriate update row cell".

John W. Vinson [MVP]

I am trying to update a table named Downinfo. The table has three fields
that I am interested in importing the new information into (all text). The
fields names are CDCnum, InmateNAME, and InmateHOUSING. I also have DucateTO,
DucatREASON, DucatTIME, DucatDATE in the table, however I only need the
first three fields updated. The imported table has three columns without
fields names but the data is CDCnum, InmateNAME, and InmateHOUSING (again all
text). Once the new table is created, how do I relate the two and how do I
make an update query and can this be done automatically with code?

Thanks

John W. Vinson wrote:
Hey John

I can import the file and make a new table. I donÂ’t know if I should import
the new data into an existing table or should I import the new data into a
new table. After that, how can I link one table to the other and complete
the update.

Since I cannot see your database, and you haven't posted any
information about these tables, the data they contain, how they are
related, or much of anything else, all I can say is "by joining them
on an appropriate field and putting [tablename].[fieldname] into the
appropriate update row cell".

John W. Vinson [MVP]

--
Message posted via http://www.accessmonster.com

On Sun, 11 Feb 2007 19:25:08 GMT, "DucateAssignment via
AccessMonster.com" u30108@uwe wrote:

I am trying to update a table named Downinfo. The table has three fields
that I am interested in importing the new information into (all text). The
fields names are CDCnum, InmateNAME, and InmateHOUSING. I also have DucateTO,
DucatREASON, DucatTIME, DucatDATE in the table, however I only need the
first three fields updated. The imported table has three columns without
fields names but the data is CDCnum, InmateNAME, and InmateHOUSING (again all
text). Once the new table is created, how do I relate the two and how do I
make an update query and can this be done automatically with code?

Do you want to find the record in your table for the imported CDCnum
and overwrite the existing InmateNAME and InmateHOUSING fields? Should
the old values simply be overwritten without any warning, of do you
want to check first? What do you want to happen if there is no record
the table for an imported CDCnum - do you want to add a new record? Do
you want the current date and time filled in to DucateTIME and
DucatDATE (which I'd suggest should be combined into one field, but
that's another issue)? Should DucateREASON be left as it was, or
blanked out if there is an existing reason?

I hesitate to give the following since it may Do The Wrong Thing
depending on these questions - but you can create a Query by joining
your imported table to your existing table, joining on CDCnum
(whatever that field is named in your import, it makes no difference).
Change it to an Update Query and put

[importtablename].[InmateNAME]

under the InmateNAME field (using the actual tablename and fieldname
of course); the brackets are required (otherwise it will store the
text string "importtablename.InmateNAME" in every record). Do the same
for the other field.

Store this query, and run it using the DoCmd.RunQuery method (see the
vba online help) or - probably better, since you can trap errors - the
slightly more complex Querydef Execute method.

John W. Vinson [MVP]

I would like to take the imported table lets call it (update) with field1,
field2, and field3, which contains the new data I need. Then compare the
data in field1 from update with CDCnum, from downinfo, field2 from update
with InmateNAME from downinfo and field3 from update with InmateHOUSING from
downinfo.

1. All the records that match no changes are needed.
2. If the CDCnum and InmateNAME from downinfo match the records in field1 and
field2 from update, only change (overwrite and update without warning) the
InmateHOUSING in downinfo table with field3 from update table for each record
that it applies to.

3. If there is a record(s) found in the downinfo table that are not found in
update table, that record(s) should be deleted (without warning).
4. If there are any records found in the newly imported update table, that
are not in the downinfo table, the new record(s) will need to be added to the
downinfo table in the appropriate columns field1 in CDCnum, field2
inInmateNAME, and field3 in InmateHOUSING in the downinfo table. I would
like the data in the other columns DucateDATE, DucateTIME and DucateREASON to
be blanked. I have another table named AM PM Ducats that I need to update
and leave the remaining data unchanged. So with downinfo, the other columns
should be blanked after the update and with AM PM Ducats the other columns
should remain unchanged after the update.

Thanks


John W. Vinson wrote:
I am trying to update a table named Downinfo. The table has three fields
that I am interested in importing the new information into (all text). The
[quoted text clipped - 4 lines]
text). Once the new table is created, how do I relate the two and how do I
make an update query and can this be done automatically with code?

Do you want to find the record in your table for the imported CDCnum
and overwrite the existing InmateNAME and InmateHOUSING fields? Should
the old values simply be overwritten without any warning, of do you
want to check first? What do you want to happen if there is no record
the table for an imported CDCnum - do you want to add a new record? Do
you want the current date and time filled in to DucateTIME and
DucatDATE (which I'd suggest should be combined into one field, but
that's another issue)? Should DucateREASON be left as it was, or
blanked out if there is an existing reason?

I hesitate to give the following since it may Do The Wrong Thing
depending on these questions - but you can create a Query by joining
your imported table to your existing table, joining on CDCnum
(whatever that field is named in your import, it makes no difference).
Change it to an Update Query and put

[importtablename].[InmateNAME]

under the InmateNAME field (using the actual tablename and fieldname
of course); the brackets are required (otherwise it will store the
text string "importtablename.InmateNAME" in every record). Do the same
for the other field.

Store this query, and run it using the DoCmd.RunQuery method (see the
vba online help) or - probably better, since you can trap errors - the
slightly more complex Querydef Execute method.

John W. Vinson [MVP]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1