Append and Update

[Capt Jim Cook]

I have a table of work orders that has a primary key on OrderNumber. About
8:00 to 10:00 every morning we download new work orders from the mainframe
(using a text report that is parsed via a query). The mainframe report lists
all orders created anytime yesterday or today (several orders drop about 4:00
am and we want these listed on todays worksheets). I'm using an append query
and it works fine. There are some duplicate records (orders created 4:00
a.m. yesterday get reported again on todays report), but they get thrown away
due to the primary key in the history table. That's exactly what we wanted
the system to do.

However, reporting requirements have changed. The tables contain:
Order # - unique id for the order
Created date - Julian date when the order was created
Pick date - Julian date when the items were picked in the warehouse for
shipment

Orders can be created anytime of day, but our warehouse only picks items on
the day or evening shift.

Yesterday's transactions will have a Pick_date of 0 for orders created after
midnight because our pickers don't start until after the mainframe report is
run. That means today's mainframe report will have a valid [Pick_date] but
these transactions get thrown away due to the duplicate key.

My question is this: Can an update query both update matching transactions
and append new (not matching on Order#) transactions? Do I need to perform
two separate queries: update first and append second?

Thanks,
Jim

 

[Tom Ellison]

Dear Capt Jim:

No single query can both update and append.

The query work can update those rows that exist, limiting itself to those
rows that are found. It is useful to have a column in this table to receive
the information that the row has been updated, to prevent duplicate updates.

You can then append all the rows not updated, using the Updated column to
filter out those that were updated.

This is a sensitive process. If all access to the table(s) being
updated/appended can be locked during this processing, you're safe. If not,
much more elaborate steps may be necessary to prevent corruption due to
simultaneous access.

Tom Ellison

 

[Michel Walsh]

Hi,

Jet does, and rumors are that MS SQL Server envisions a way to be able to do
it (not in 2005 release).


With Jet, it is a matter to update the unpreserved side of an outer join.
Imagine the actual SELECT statement that makes the join, but replace some
the columns (mentioned in the SET) with the value specified by the right
side of each expressions following the SET keyword. The UPDATE (on the
unpreserved side) has to be so that after it is done, the SELECT then shows
what we just describe.

The SQL is relatively simple:

===============
UPDATE oldInventory AS o RIGHT JOIN newList AS n
ON o.ItemID = n.ItemID
SET o.ItemID = n.ItemID,
o.UnitPrice = n.UnitPrice,
o.whatever = n.whatever
===============

and, in Jet, that does, indeed, an update of existing records, and also
append new ones (new in n, absent in o ), in just one query.


Hoping it may help,
Vanderghast, Access MVP

Dear Capt Jim:

No single query can both update and append.

(...)

 

[Capt Jim Cook]

You made it look so simple.

What would I have to change if the history table is “MRO_Table†and the
transactions are in a query named “DSS Data� Since tables and queries are
both datasets, I don’t think anything is different (except changing the names
to protect the innocent).

Can I just modify the code sniplette and insert into the command button’s
“On Click†event replacing where I previously ran the “Add MROs†append query?

My contract with this employer technically prevents me from writing code.
Can I “hide it†from obvious view by editing the Access query in “SQL Viewâ€
by replacing:

INSERT INTO MRO_Table
SELECT [DSS Data].*
FROM [DSS Data];

With

UPDATE MRO_Table AS o RIGHT JOIN [DSS Data] AS n
ON o.Doc_Number = n.Doc_Number
SET o.Stk_Number = n.Stk_Number,
o.MRO_Drop_Date = n.MRO_Drop_Date,
o.MRO_Drop_Time = n.MRO_Drop_Time,
o.Pick_Date = n.Pick_Date,
o.Pick_Time = n.Pick_Time,
o.Type = n.Type;

I guess the real question, is will Access let you change the SQL of a query
that it generated using a query wizard?

OK… OK… OK… That’s way more than 1 question. Sorry, I’m somewhat new to
doing much coding in Access. Most of my background has been using the
built-in features.

You know… There are 3 kinds of people in this world: Those that can count
and those that can’t count.

Sincerely,
Capt Jim

 

[Michel Walsh]

Hi,

You know. There are 3 kinds of people in this world: Those that can
count
and those that can't count.

(eh eh eh... )


TECHNICALLY, you can change the code generated by the wizard, in theory,
unless the database is protected (security or otherwise) against that.
Indeed, unless the access to the database is quite seriously restricted, you
can access its data from another database application with links to the
tables, or by opening the database through another application (like VB6,
C++, Delphi, etc.)


As for your contract, or legally, I cannot suggest you to bypass it, but
writing an SQL statement is sometimes considered to be of the domain of the
administrator, not necessary of the domain of the developer. If your job
involves a "run once" sequence of instructions, that may technically be
considered "administrative" and allowed by the terms of your contract: why
not asking about the party with who you got the contract? SURE, writing an
SQL statement that can modify a LOT of records has to be "backed-up" by
applicable procedure allowing to recover the data in case that an error (of
conception) should occur.

Note that I am not a lawyer, and I consider your question was on a technical
matter.



Hoping it may help,
Vanderghast, Access MVP

You made it look so simple.

What would I have to change if the history table is "MRO_Table" and the
transactions are in a query named "DSS Data"? Since tables and queries
are
both datasets, I don't think anything is different (except changing the
names
to protect the innocent).

Can I just modify the code sniplette and insert into the command button's
"On Click" event replacing where I previously ran the "Add MROs" append
query?

My contract with this employer technically prevents me from writing code.
Can I "hide it" from obvious view by editing the Access query in "SQL
View"
by replacing:

INSERT INTO MRO_Table
SELECT [DSS Data].*
FROM [DSS Data];

With

UPDATE MRO_Table AS o RIGHT JOIN [DSS Data] AS n
ON o.Doc_Number = n.Doc_Number
SET o.Stk_Number = n.Stk_Number,
o.MRO_Drop_Date = n.MRO_Drop_Date,
o.MRO_Drop_Time = n.MRO_Drop_Time,
o.Pick_Date = n.Pick_Date,
o.Pick_Time = n.Pick_Time,
o.Type = n.Type;

I guess the real question, is will Access let you change the SQL of a
query
that it generated using a query wizard?

OK. OK. OK. That's way more than 1 question. Sorry, I'm somewhat new to
doing much coding in Access. Most of my background has been using the
built-in features.

You know. There are 3 kinds of people in this world: Those that can
count
and those that can't count.

Sincerely,
Capt Jim