'intelligent' update

[Guest]

Hey there - I've created a database for tracking helpdesk tickets and I'm
hoping to get some assistance. I can export the tickets from our helpdesk
system into Excel format. I created an initial database simply by using the
import function and letting Access do the rest. Now I face the issue of
updates. Here's the example:

Originally my file looked something like this (simplified):
number info status
A123 blah closed
A124 junk updated
A125 gigo deadline alert

I initially removed the status column as I really only care if it's open or
closed. I imported the first two columns and then manually added a yes/no
field for a table def of:
(pk)number text
info text
closed yes/no

Now my concern is how to update. Once a ticket reaches closed status, I no
longer wish to update the database with it. However, if it's not already
there, I want to add it for historical purposes. How do I go about importing
future Excel files that can use status info to update the yes/no field, and
dynamically update the rest? I really appreciate the assistance. Thanks,
Mike

 

[Guest]

Hi Mike,

Create a linked table connected to the new worksheet, then use two queries.

1) an update query to handle any newly closed tickets, something like:

UPDATE MyTable INNER JOIN LinkedTable
ON MyTable.Number = LinkedTable.Number
SET MyTable.Closed = -1
WHERE My.TableClosed <> -1 AND LinkedTable.Closed = -1;

2) an append query to append the new records (the PK will prevent old
tickets being duplicated).

 

[Douglas J. Steele]

Actually, it's possible to do it in a single query.

I wrote about this in my November, 2003 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can download the column (and sample
database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[John Nurick]

Thanks, Doug. One more gap in my education filled!