Update query

[DucateAssignment via AccessMonster.com]

I am not sure how an update query works. I have a table named Downinfo that
is my primary table. I have three fields named CDCnum, InmateName, and
InmateHousing. How can I make an update query to import from a txt file and
have the query make changes in the InmateHousing field of the table? I need
to compare the old with the new information, delete any records that are no
longer at the institution, as well as include any new CDCnum, InmateNames and
InmateHousing that are not on the current table. Any advice would more than
welcome.


A little help please

 

[mcescher]

If the txt file includes only the records you need to see, why don't
you just overwrite the original table with the newly imported text
file.

You can automate that process with docmd.transfertext

HTH,
Chris M.

 

[tina]

does the text file data include a primary key value for each record, one
that can be matched to a unique key field in the Access table? if so, then
suggest you import the text file into a "temp" table. use the Query Wizard
to create an "Unmatched" query that returns records from tblTemp that have
no matching key value in tblDownInfo. then turn that query into an Append
query, to append those new records into tblDownInfo.

use the Query Wizard to create another "Unmatched" query that returns
records from tblDownInfo that have no matching key value in tblTemp. turn
that query into a Delete query to get rid of those records that "are no
longer at the institution".

as for updating data in existing records in tblDownInfo, suggest you create
a query that links tblDownInfo and tblTemp with an INNER JOIN on the
matching key fields. then convert the query to an Update query, and simply
overwrite the data in those three fields, in all records in tblDownInfo.

hth

 

[tina]

also posted, and answered, in microsoft.public.access.forms newsgroup.
please don't multi-post. see
http://home.att.net/~california.db/tips.html#aTip10 for more information.

 

[DucateAssignment via AccessMonster.com]

Thanks for the reply, however I am new to this and I do not know the code for
Docmd.transfertxt. I get the txt file from an external source (memory stick
or floppy or e-mail). So I need to tell access where to look for the file,
import it and work with it from there. The txt file has a great deal of
information however I am only interested in the three items I described.
Please note none of the information in the txt file is labeled. I usually
import the file into excel, delete the data I don’t need, label the fields to
match the fields in Downinfo and save the file in excel format, then import
into existing table Downifo. I’m not sure if this can be automated at all,
but this is what I’m trying to accomplish. Any ideas?

Thanks

If the txt file includes only the records you need to see, why don't
you just overwrite the original table with the newly imported text
file.

You can automate that process with docmd.transfertext

HTH,
Chris M.

I am not sure how an update query works. I have a table named Downinfo that
is my primary table. I have three fields named CDCnum, InmateName, and

[quoted text clipped - 9 lines]

 

[DucateAssignment via AccessMonster.com]

Tina

Thanks for the reply, however I am new to this and I do not know the code for
Docmd.transfertxt. I get the txt file from an external source (memory stick
or floppy or e-mail). So I need to tell access where to look for the file,
import it and work with it from there. The txt file has a great deal of
information and some of it is duplicate so I will need to delete the
duplicate data, and I am only interested in the three items I described.
Please note none of the information in the txt file is labeled. I usually
import the file into excel, delete the data I do not need, label the fields
to match the fields in Downinfo and save the file in excel format, then
import into existing table Downinfo. I have no primary key field due to some
of the data having duplicate info, so I think I will need to fix this as well.
I am not sure if this can be automated at all, but this is what I am trying
to accomplish. Any ideas?

Thanks

does the text file data include a primary key value for each record, one
that can be matched to a unique key field in the Access table? if so, then
suggest you import the text file into a "temp" table. use the Query Wizard
to create an "Unmatched" query that returns records from tblTemp that have
no matching key value in tblDownInfo. then turn that query into an Append
query, to append those new records into tblDownInfo.

use the Query Wizard to create another "Unmatched" query that returns
records from tblDownInfo that have no matching key value in tblTemp. turn
that query into a Delete query to get rid of those records that "are no
longer at the institution".

as for updating data in existing records in tblDownInfo, suggest you create
a query that links tblDownInfo and tblTemp with an INNER JOIN on the
matching key fields. then convert the query to an Update query, and simply
overwrite the data in those three fields, in all records in tblDownInfo.

hth

I am not sure how an update query works. I have a table named Downinfo that
is my primary table. I have three fields named CDCnum, InmateName, and

[quoted text clipped - 6 lines]

A little help please

 

[tina]

well, if there is no "natural" unique key in the textfile data records
(either one atomic datum, or multiple atomic data taken together), i don't
see how you can automate this process. no unique key means that you'd have
to manually identify matching records between your native table and the
textfile data, and make updates as needed. is that how you've been doing it
so far?

hth

Tina

Thanks for the reply, however I am new to this and I do not know the code for
Docmd.transfertxt. I get the txt file from an external source (memory stick
or floppy or e-mail). So I need to tell access where to look for the file,
import it and work with it from there. The txt file has a great deal of
information and some of it is duplicate so I will need to delete the
duplicate data, and I am only interested in the three items I described.
Please note none of the information in the txt file is labeled. I usually
import the file into excel, delete the data I do not need, label the fields
to match the fields in Downinfo and save the file in excel format, then
import into existing table Downinfo. I have no primary key field due to some
of the data having duplicate info, so I think I will need to fix this as well.
I am not sure if this can be automated at all, but this is what I am trying
to accomplish. Any ideas?

Thanks

does the text file data include a primary key value for each record, one
that can be matched to a unique key field in the Access table? if so, then
suggest you import the text file into a "temp" table. use the Query Wizard
to create an "Unmatched" query that returns records from tblTemp that have
no matching key value in tblDownInfo. then turn that query into an Append
query, to append those new records into tblDownInfo.

use the Query Wizard to create another "Unmatched" query that returns
records from tblDownInfo that have no matching key value in tblTemp. turn
that query into a Delete query to get rid of those records that "are no
longer at the institution".

as for updating data in existing records in tblDownInfo, suggest you create
a query that links tblDownInfo and tblTemp with an INNER JOIN on the
matching key fields. then convert the query to an Update query, and simply
overwrite the data in those three fields, in all records in tblDownInfo.

hth

I am not sure how an update query works. I have a table named Downinfo that
is my primary table. I have three fields named CDCnum, InmateName, and

[quoted text clipped - 6 lines]

A little help please

 

[DucateAssignment via AccessMonster.com]

Thanks Tina for the response

I just delete the data in the Downinfo table and import the new data from an
excel file I set-up with the same field names as the table. The CDCnum
column and all the data is unique some rows will be duplicate data. Is there
a way to bring the file into access, delete the duplicate data first then
update the Downinfo table?

Thanks Again

well, if there is no "natural" unique key in the textfile data records
(either one atomic datum, or multiple atomic data taken together), i don't
see how you can automate this process. no unique key means that you'd have
to manually identify matching records between your native table and the
textfile data, and make updates as needed. is that how you've been doing it
so far?

hth

Tina

[quoted text clipped - 38 lines]

 

[DucateAssignment via AccessMonster.com]

Chris

The txt file has far more information than I need. I just import the txt
file into excel, delete the data I don't need, save the file in excel format
with the header rows named the same as the access Downinfo table. From that
point I delete the exiting data from the table and import the new data from
the excel file. This keeps my inmate information currentand I need to do
this process every two to three days.

Thanks

If the txt file includes only the records you need to see, why don't
you just overwrite the original table with the newly imported text
file.

You can automate that process with docmd.transfertext

HTH,
Chris M.

I am not sure how an update query works. I have a table named Downinfo that
is my primary table. I have three fields named CDCnum, InmateName, and

[quoted text clipped - 9 lines]

 

[tina]

okay. when you same "some rows will be duplicate data", do you mean that the
value in *every* field is identical in the "duplicate" records? if so, you
might try using a SELECT DISTINCT query on the imported data to return
unique records only, and then turn the query into an Append query to dump
the records into the native Access table. suggest you read up on the
DISTINCT keyword in Access Help, so you'll understand how it works in a
query.

hth

Thanks Tina for the response

I just delete the data in the Downinfo table and import the new data from an
excel file I set-up with the same field names as the table. The CDCnum
column and all the data is unique some rows will be duplicate data. Is there
a way to bring the file into access, delete the duplicate data first then
update the Downinfo table?

Thanks Again

well, if there is no "natural" unique key in the textfile data records
(either one atomic datum, or multiple atomic data taken together), i don't
see how you can automate this process. no unique key means that you'd have
to manually identify matching records between your native table and the
textfile data, and make updates as needed. is that how you've been doing it
so far?

hth

Tina

[quoted text clipped - 38 lines]

A little help please

 

[DucateAssignment via AccessMonster.com]

The txt file has the three fields I use in the tblDowninfo however I still
have the problem of some duplicate date. The field CDCnum, InmateName, and
InmateHousing (which are text values) is the unique fields I use. The txt
file is everything the institution has on each inmate currently housed there.
Most of that data is not used in the ducat assignment database. However, I
cannot import the file until I find a way to delete the duplicate data
already in the txt file. Right now, I do not use a primary key because that
duplicate data. Any ideas on that part? Then I can try to tackle the update.

does the text file data include a primary key value for each record, one
that can be matched to a unique key field in the Access table? if so, then
suggest you import the text file into a "temp" table. use the Query Wizard
to create an "Unmatched" query that returns records from tblTemp that have
no matching key value in tblDownInfo. then turn that query into an Append
query, to append those new records into tblDownInfo.

use the Query Wizard to create another "Unmatched" query that returns
records from tblDownInfo that have no matching key value in tblTemp. turn
that query into a Delete query to get rid of those records that "are no
longer at the institution".

as for updating data in existing records in tblDownInfo, suggest you create
a query that links tblDownInfo and tblTemp with an INNER JOIN on the
matching key fields. then convert the query to an Update query, and simply
overwrite the data in those three fields, in all records in tblDownInfo.

hth

I am not sure how an update query works. I have a table named Downinfo that
is my primary table. I have three fields named CDCnum, InmateName, and

[quoted text clipped - 6 lines]

A little help please

 

[tina]

see my previous post; delete all the data from the native Access table,
import *all* the textfile data into a "temp" table, then create a SELECT
DISTINCT query on the temp table to return unique records, and turn that
query into an Append query to dump the query dataset into the native Access
table.

hth

The txt file has the three fields I use in the tblDowninfo however I still
have the problem of some duplicate date. The field CDCnum, InmateName, and
InmateHousing (which are text values) is the unique fields I use. The txt
file is everything the institution has on each inmate currently housed there.
Most of that data is not used in the ducat assignment database. However, I
cannot import the file until I find a way to delete the duplicate data
already in the txt file. Right now, I do not use a primary key because that
duplicate data. Any ideas on that part? Then I can try to tackle the update.

does the text file data include a primary key value for each record, one
that can be matched to a unique key field in the Access table? if so, then
suggest you import the text file into a "temp" table. use the Query Wizard
to create an "Unmatched" query that returns records from tblTemp that have
no matching key value in tblDownInfo. then turn that query into an Append
query, to append those new records into tblDownInfo.

use the Query Wizard to create another "Unmatched" query that returns
records from tblDownInfo that have no matching key value in tblTemp. turn
that query into a Delete query to get rid of those records that "are no
longer at the institution".

as for updating data in existing records in tblDownInfo, suggest you create
a query that links tblDownInfo and tblTemp with an INNER JOIN on the
matching key fields. then convert the query to an Update query, and simply
overwrite the data in those three fields, in all records in tblDownInfo.

hth

I am not sure how an update query works. I have a table named Downinfo that
is my primary table. I have three fields named CDCnum, InmateName, and

[quoted text clipped - 6 lines]

A little help please