Complex Query Question

[David W]

1st part;
id fields of both tables are the same [unit]
I am wanting to take the [tbl].[mileage] that was imported 1 week ago from
[tbl].

• and compare [tbl]![mileage].[reading1](which is mileage) that
  was dated 3/30/2005, to this weeks[tbl]!
  • .[reading1] to see if any
    changes were made in [tbl]!
    • .[reading1], then if there was a change,
      take the data in [tbl]![mileage].[reading2] and put it in
      [tbl]![mileage].[reading1], then replace the data in
      [tbl].[mileage].[reading2] with the data from 3/30/2005 [reading1]. Leaving
      records that hadnt changed.
      
      2nd part; same id number
      if the id field changed in [tbl].
      • .[unit] and it does not exist in
        [tbl].[mileage], add it to the list of records
        
        3rd part; same id number
        if [vin] of [tbl].
        • equals that of [tbl].[mileage], but the id number
          changed, update only [unit] and [reading2] in [tbl].[mileage], then update
          [tbl].[auction].[unit]
          
          Kinda complex, I am still working on this one.
          Any help provide would be appreciated deeply!

 

[Michel Walsh]

Hi,


1- Update everyone with the new data. If data has not changed, the update
won't "change" the unchanged value... ;-) and that is easier to update
everyone.


2-

UPDATE inventory RIGHT JOIN newdata ON inventory.id = newdata.id
SET inventory.id=newdata.id,
inventory.unitPrice = newdata.unitPrice,
inventory.description = newdata.description


will update the new unitPrice, if newdata.id is already in inventory.id,
else, it will append the new record. That also assumes id is a primary key,
for both tables (the inventory and the newdata updating the inventory).



3- That is an update from a second table, like 2, but with an INNER JOIN
rather than an outer join. As mentioned in 1, change a value by itself is
logically equivalent to no change it, so it is useless, logically, to make a
special case of "but do not change values that didn't changed", if you
UPDATE the data.


Your naming convention confuse me, that is the least to say. The syntax is
tableName.FieldName, no need of [ ] when the name is well formed and not a
reserved word. No bang (!), no tbl.tableName.fieldName.



Hoping it may help,
Vanderghast, Access MVP