update query

[Guest]

I have a database with seven columns named CDCnum, Name, Housing, To, Reason,
Time and Date. I need help creating an update query to compare the data in
the CDCnum, Name and Housing columns and update any data that has changed.
My update source is a TXT file called downinfo and my table is named
downinfo. The downinfo.txt is provided daily from and external source, so
updating the downinfo table will need to be updated periodically bases. Do
you have any ideas? I am new to access and can use any help you can offer.

Thanks
Ducat Assignment


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...1ec743404d&dg=microsoft.public.access.queries

 

[Rick Brandt]

I have a database with seven columns named CDCnum, Name, Housing, To, Reason,
Time and Date. I need help creating an update query to compare the data in
the CDCnum, Name and Housing columns and update any data that has changed.
My update source is a TXT file called downinfo and my table is named
downinfo. The downinfo.txt is provided daily from and external source, so
updating the downinfo table will need to be updated periodically bases. Do
you have any ideas? I am new to access and can use any help you can offer.

What is the primary key of the table? You would need to join your table to a
link to the text file using the primary key field(s).

 

[fredg]

I have a database with seven columns named CDCnum, Name, Housing, To, Reason,
Time and Date. I need help creating an update query to compare the data in
the CDCnum, Name and Housing columns and update any data that has changed.
My update source is a TXT file called downinfo and my table is named
downinfo. The downinfo.txt is provided daily from and external source, so
updating the downinfo table will need to be updated periodically bases. Do
you have any ideas? I am new to access and can use any help you can offer.

Thanks
Ducat Assignment

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...1ec743404d&dg=microsoft.public.access.queries

Before you go any further, I would strongly suggest you change four of
your field names.

Date, To, Time, and Name are all reserved Access/VBA/Jet words and
should not be used as a field name, as unintended results may occur.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[Guest]

I do not have a primary key field, as some of the data is duplicate
particularly the CDCnum, name, and housing. The downinfo.txt will have
duplicate data as well. Is there a way to get access to delete the duplicate
data either before the update or after the update query? I am not sure if an
update or append query will work. This database is designed to print passes
(Ducats) for prison inmates population with medical/dental appointments. I
can enter all the necessary appointment information and print passes and
reports, I need to update changes in population meaning inmates that leave or
new arrivals and any housing assignment changes.

Thanks for any help
Ducat Assignment

 

[Guest]

Thanks for the advice, are there any suggestions for name changes that will
work? This is my first database attempt and I really don't have any idea.

This is where I am now, I do not have a primary key field, as some of the
data is duplicate particularly the CDCnum, name, and housing. The
downinfo.txt will have duplicate data as well. Is there a way to get access
to delete the duplicate data either before the update or after the update
query? I am not sure if an update or append query will work. This database
is designed to print passes (Ducats) for prison inmates population with
medical/dental appointments. I can enter all the necessary appointment
information and print passes and reports, I need to update changes in
population meaning inmates that leave or new arrivals and any housing
assignment changes.

 

[Rick Brandt]

Thanks for the advice, are there any suggestions for name changes that will
work? This is my first database attempt and I really don't have any idea.

PrisonerName, PassReason, PassTime PassDate, etc..

This is where I am now, I do not have a primary key field, as some of the
data is duplicate particularly the CDCnum, name, and housing. The
downinfo.txt will have duplicate data as well. Is there a way to get access
to delete the duplicate data either before the update or after the update
query? I am not sure if an update or append query will work. [snip]

It will be nearly impossible to do what you want if you do not have primary
keys. There has to be some way to say "this new data needs to be applied to
this existing record". The primary key is what would allow you to do that.

 

[Guest]

Would it be okay to let access add an ID/primary key field to the database?
My other tables are small lookup tables and most likely not suited for the
primary key. On the other hand, how can I delete the duplicate data already
in the table from my manual import of the downinfo.txt? There are times when
an inmate will need to go to more than one ducat destination in the same day,
so I may need to select the CDCnum, Name, and Housing more than once. Will
that be possible?

Thanks again
Ducat Assignment

Thanks for the advice, are there any suggestions for name changes that will
work? This is my first database attempt and I really don't have any idea.

PrisonerName, PassReason, PassTime PassDate, etc..

This is where I am now, I do not have a primary key field, as some of the
data is duplicate particularly the CDCnum, name, and housing. The
downinfo.txt will have duplicate data as well. Is there a way to get access
to delete the duplicate data either before the update or after the update
query? I am not sure if an update or append query will work. [snip]

It will be nearly impossible to do what you want if you do not have primary
keys. There has to be some way to say "this new data needs to be applied to
this existing record". The primary key is what would allow you to do that.

 

[Guest]

Probably the best thing to do is to simply to transfer your txt file into
Excel and produce a pivot table with your "repeat information" in the
columns. Then "special paste" the information. Each will then occur once and
give each a unique ID.

I hope this is a start and gets you thinking!

--
How many buildings collapsed on 9/11?

I can tell you the answer isn''t 2 !!

Would it be okay to let access add an ID/primary key field to the database?
My other tables are small lookup tables and most likely not suited for the
primary key. On the other hand, how can I delete the duplicate data already
in the table from my manual import of the downinfo.txt? There are times when
an inmate will need to go to more than one ducat destination in the same day,
so I may need to select the CDCnum, Name, and Housing more than once. Will
that be possible?

Thanks again
Ducat Assignment

Thanks for the advice, are there any suggestions for name changes that will
work? This is my first database attempt and I really don't have any idea.

PrisonerName, PassReason, PassTime PassDate, etc..

This is where I am now, I do not have a primary key field, as some of the
data is duplicate particularly the CDCnum, name, and housing. The
downinfo.txt will have duplicate data as well. Is there a way to get access
to delete the duplicate data either before the update or after the update
query? I am not sure if an update or append query will work. [snip]

It will be nearly impossible to do what you want if you do not have primary
keys. There has to be some way to say "this new data needs to be applied to
this existing record". The primary key is what would allow you to do that.