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:
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.