Update query

[DucateAssignment via AccessMonster.com]

Hey everyone

I need help with an up-date query. I have a txt file I would like to import
into access and update a table containing three columns. The txt file has
far more data than I need so I would have to delete unwanted data, then
update the table. I have no idea how to make this work. I would appreciate
any help or ideas in the matter.

Thanks

 

[Guest]

First import the test file into the database as it's own table. Then delete
the unneeded data. Next run an update query on the original table. Simple?
Not!

Let's tackle the import of data first. Open up the text file and see what it
looks like. Hopefully it's either fixed width or deliminated.

Fixed-width has the same amount of characters for each field. For example a
first name will start at column 1 and end at column 30. Column 31 starts the
last name to column 60. And so on.

Deliminated has a character such as a comma "," or pipe "|" between each
field. If the text file ends in CSV, that's a well known identifier for a
Comma Seperated Value file. Access can easily handle fixed width or delimited
files.

If your text file has a field per line like below, things get very difficult
indeed.
Jerry
Whittle
555-555-5555

Assuming that the file is either fixed width or delimited, open up your
database. Got to File, Get External Data, Import. In the next dialog box go
down to the Files of type dropdown and find the one that matches the
extension on your text file. For TXT you have to scroll down some. Next find
the file and press the import button. At the first Import Wizard dialog box
you need to select fixed width or delimited.

Press Next. If you selected fixed width, you may need to create the breaks
for the data. There are instructions. If delimited you may need to select the
delimiter such as comma or tab. Select Next. (Notice that there is a Back
button if you need to change something.

Put it into a new table. Next. You may be prompted to change the field names
and type of data. Next. Let Access choose a primary key unless you know for
sure that the text file as a piece of data that is unique for each record.

When you are all done, check out the new table. Hopefully you didn't get an
error message. If you like what you see, great! If not you can delete the
table and start the import over again.

That's step 1!

 

[DucateAssignment via AccessMonster.com]

Hey Jerry

Thanks for the note

I'm ok with everything you said, however the txt file contains way more data
than I need and I am not sure how to deal with that extra data. I am not
sure how to delete the unwanted data in access mainly because it would
consist of unwanted columns of data and not rows. I currently import the txt
file into excel in the deliminated format, make any changes or deletion, add
the heading that match the access table I'm replacing/updating, then import
it into access. The data I need from the txt file is CDCnum, InmateName, and
InmateHousing, names I assign and I delete the data in a table named downinfo,
which is approximately 6500 records. I save the table empty, then import the
excel file named Update Housing with the new data. My problem is this needs
to be done daily.

I would appreciate any advice
Thanks in advance

First import the test file into the database as it's own table. Then delete
the unneeded data. Next run an update query on the original table. Simple?
Not!

Let's tackle the import of data first. Open up the text file and see what it
looks like. Hopefully it's either fixed width or deliminated.

Fixed-width has the same amount of characters for each field. For example a
first name will start at column 1 and end at column 30. Column 31 starts the
last name to column 60. And so on.

Deliminated has a character such as a comma "," or pipe "|" between each
field. If the text file ends in CSV, that's a well known identifier for a
Comma Seperated Value file. Access can easily handle fixed width or delimited
files.

If your text file has a field per line like below, things get very difficult
indeed.
Jerry
Whittle
555-555-5555

Assuming that the file is either fixed width or delimited, open up your
database. Got to File, Get External Data, Import. In the next dialog box go
down to the Files of type dropdown and find the one that matches the
extension on your text file. For TXT you have to scroll down some. Next find
the file and press the import button. At the first Import Wizard dialog box
you need to select fixed width or delimited.

Press Next. If you selected fixed width, you may need to create the breaks
for the data. There are instructions. If delimited you may need to select the
delimiter such as comma or tab. Select Next. (Notice that there is a Back
button if you need to change something.

Put it into a new table. Next. You may be prompted to change the field names
and type of data. Next. Let Access choose a primary key unless you know for
sure that the text file as a piece of data that is unique for each record.

When you are all done, check out the new table. Hopefully you didn't get an
error message. If you like what you see, great! If not you can delete the
table and start the import over again.

That's step 1!

Hey everyone

[quoted text clipped - 5 lines]

Thanks

 

[John Vinson]

I'm ok with everything you said, however the txt file contains way more data
than I need and I am not sure how to deal with that extra data. I am not
sure how to delete the unwanted data in access mainly because it would
consist of unwanted columns of data and not rows.

One of the options in the "Advanced" import wizard is to import tab
delimited data, *and to selectively import some fields and ignore
others*. You can save the Import Specification for later use.

John W. Vinson[MVP]

 

[DucateAssignment via AccessMonster.com]

Hey John

All I could say is wow. I was able to import the file with just the
information I needed into a new table. Where do I go from here?

Thanks

 

[Guest]

For the next step we need to know the the table names and the fields in both
tables. Also the data types such as text or numbers of each field.

Then comes the important part. You said that you want to update data in the
original table. How do you know which records need updating and with which
fields. Hopefully you have a primary key field in both tables that match.
Something like an EmployeeID or a PartNumber field.

 

[DucateAssignment via AccessMonster.com]

Hey John

I named the new table TRIAL, and the original table is named DOWNINFO. The
fields in downinfo I need to update are CDCnum, InmateNAME, and InmateHOUSING.
I do not have a primary key for downinfo as some data is duplicate. I do
have a primary key for Trial table. I am not sure how to create a primary
key for the already established downinfo table. There is a catch, with this
database. I use an unbound combo box to select the inmate CDCnum, InmateNAME,
and InmateHOUSING, I also utilize combo boxes to select appointment location,
appointment reason and time. I finish the data entry by typing in the
scheduled date. So prior to importing the new update, I save and date the
old table for a history record. The txt file I now imported directly into
access (thanks to you) has the new data as Inmates depart and arrive at the
institution regularly. I need to add new arrivals as well as housing changes
for the inmates that remain at the institution. Inmates that have departed
are no longer needed in the database. Please note some inmates can go on as
many as four appointments in one day. With this in mind, I copy and paste
the new data in an excel file I call UpdateHousing four times. I sort the
data so the info is together prior to importing into access. Is this
possible importing directly into access and using an update query? If not, I
still have a need for the update query for other medical database projects I
am attempting.

Thanks Again

For the next step we need to know the the table names and the fields in both
tables. Also the data types such as text or numbers of each field.

Then comes the important part. You said that you want to update data in the
original table. How do you know which records need updating and with which
fields. Hopefully you have a primary key field in both tables that match.
Something like an EmployeeID or a PartNumber field.

Hey John

[quoted text clipped - 13 lines]