'intelligent' update

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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).
 
Back
Top