Update query using 2 tables

  • Thread starter Thread starter dynasoft
  • Start date Start date
D

dynasoft

Hi

We need to update a table from another one. The target table
AdditItems has to be updated with values from another table called
AutoCRCustInvMain. The table AutoCRCustInvMain has more than one line
with the same data but we want to use the lines where DateTimeBilled
is the most recent one. I have been playing with sql but can't seem to
get it to work:

One attempt:

UPDATE AdditItems, AutoCRCustInvMain SET
AdditItems.AggregatedMinutesOrCalls =
AutoCRCustInvMain.AggregatedMinutesOrCalls, AdditItems.LastBilledDate
= AutoCRCustInvMain.LastBilledDate, AdditItems.NextBillPeriod =
AutoCRCustInvMain.NextBillPeriod WHERE AdditItems.CustomerIndex = 1
AND AutoCRCustInvMain.ContactIndex = 1 AND
AutoCRCustInvMain.ContactType = 0 AND AdditItems.ItemRecurrence =
AutoCRCustInvMain.ItemUsageType AND AdditItems.ItemName =
AutoCRCustInvMain.ItemCriteria AND (AdditItems.ItemType = 1 OR
AdditItems.ItemType = 2) AND AutoCRCustInvMain.ItemType = 4 HAVING
MAX(DateTimeBilled)

Another:

UPDATE AdditItems SET AdditItems.AggregatedMinutesOrCalls =
AutoCRCustInvMain.AggregatedMinutesOrCalls, AdditItems.LastBilledDate
= AutoCRCustInvMain.LastBilledDate, AdditItems.NextBillPeriod =
AutoCRCustInvMain.NextBillPeriod WHERE AdditItems.ID IN (SELECT
Max(AutoCRCustInvMain.DateTimeBilled) AS MaxOfDateTimeBilled,
AutoCRCustInvMain.AggregatedMinutesOrCalls,
AutoCRCustInvMain.LastBilledDate, AutoCRCustInvMain.NextBillPeriod,
AutoCRCustInvMain.ItemCriteria
FROM AdditItems INNER JOIN AutoCRCustInvMain ON
(AutoCRCustInvMain.ItemCriteria = AdditItems.ItemName AND
AdditItems.ItemRecurrence = AutoCRCustInvMain.ItemUsageType) WHERE
AutoCRCustInvMain.ContactIndex = 1 AND AutoCRCustInvMain.ContactType =
0 AND AutoCRCustInvMain.ItemType = 4 AND (AdditItems.ItemType = 1 OR
AdditItems.ItemType = 2) AND AdditItems.CustomerIndex = 1 GROUP BY
AutoCRCustInvMain.AggregatedMinutesOrCalls,
AutoCRCustInvMain.LastBilledDate, AutoCRCustInvMain.NextBillPeriod,
AutoCRCustInvMain.ItemCriteria)

I need this for Access.

Any help would be much appreciated.

Thanks.
 
We need to update a table from another one. The target table
AdditItems has to be updated with values from another table called
AutoCRCustInvMain. The table AutoCRCustInvMain has more than one line
with the same data but we want to use the lines where DateTimeBilled
is the most recent one. I have been playing with sql but can't seem to
get it to work:

No Totals query, nor any query containing a Totals query (a GROUP BY, HAVING,
MAX, etc. clause) is ever updateable, even when (as in your example) it
logically should be. It's an irksome Access limitation.

My first question is - do you really (really REALLY) need to store this value?
Bear in mind that if you do, then the instant a new record is added to
AutoCRCustInvMain with a different DteTimeBilled, the value in your table will
be *WRONG*, and there'll be no way to detect that it's wrong. Bear in mind
that you can base a Report, or a Form, or an export on the Query selecting the
most recent date; you don't need to store the value in order to see it.

If (perhaps for performance reasons) you do want to break the rules and store
the data anyway (perhaps rerunning this update query whenever
AutoCRCustInvMain changes), you can use the DMax() function to find the most
recent record (instead of a totals operation). It's not at all clear from your
example how you match up records in the two tables - your first query has no
join expression at all, and I don't know enough about the tables to interpret
the second. Does either table have a Primary Key? If so what is it? How (in
words) can you identify which record in the destination matches which record
in the source?

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
PERHAPS you can use something like the following. It is hard to say based on
the SQL statements you posted.

UPDATE AdditItems INNER JOIN AutoCRCustInvMain
ON AdditItems.ItemRecurrence =AutoCRCustInvMain.ItemUsageType
AND AdditItems.ItemName = AutoCRCustInvMain.ItemCriteria
SET AdditItems.AggregatedMinutesOrCalls
=AutoCRCustInvMain.AggregatedMinutesOrCalls
, AdditItems.LastBilledDate= AutoCRCustInvMain.LastBilledDate
, AdditItems.NextBillPeriod =AutoCRCustInvMain.NextBillPeriod

WHERE AdditItems.CustomerIndex = 1
AND AutoCRCustInvMain.ContactIndex = 1
AND AutoCRCustInvMain.ContactType = 0
AND (AdditItems.ItemType = 1 OR
AdditItems.ItemType = 2)
AND AutoCRCustInvMain.ItemType = 4
AND DateTimeBilled =
(SELECT MAX(DateTimeBilled) FROM AutoCRCustInvMain as Temp
WHERE Temp.ItemUsageType = AutoCrCustInvMain.ItemUsageType
AND Temp.ItemCriteria = AutoCrCustInvMain.ItemCriteria
AND Temp.ContactIndex = 1 and Temp.ContactType = 0 and Temp.ItemType = 4)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top