Updating Records

  • Thread starter Thread starter Haji
  • Start date Start date
H

Haji

Hello,

I was looking in an Access Book and still do not
understand Update queries. I have an MS Access
applications that gets updated every night. It has a
table I created called SalesData. What I want to do is
to update this table with a query to get the most current
transactions loaded into it. It is fed by an Invoice
table. How do I update the most current transactions
from the Invoice table into the SalesData table? Do I
use the update query or something else?

Thanks,

Haji
 
Haji said:
Hello,

I was looking in an Access Book and still do not
understand Update queries. I have an MS Access
applications that gets updated every night. It has a
table I created called SalesData. What I want to do is
to update this table with a query to get the most current
transactions loaded into it. It is fed by an Invoice
table. How do I update the most current transactions
from the Invoice table into the SalesData table? Do I
use the update query or something else?
Click to expand...

Update queries are for making changes to existing records. To add new records you
want an Append query.
 
Rick,

Thanks for your help with my question. My data may get
refreshed everyday or every other day. Is there a way to
append records where only the new records that are not
already in the SalesData database are added from the
Invoice table?

Thanks,

Haji

-----Original Message-----


Update queries are for making changes to existing
Click to expand...
records. To add new records you
 
Haji said:
Rick,

Thanks for your help with my question. My data may get
refreshed everyday or every other day. Is there a way to
append records where only the new records that are not
already in the SalesData database are added from the
Invoice table?
Click to expand...

Two ways. If the fields that constitute duplicates are uniquely indexed in the
target table (or make up the primary key) then the insert query will simply fail
on those rows and only insert rows that are not already in the table
automatically.

Otherwise you can use a Not In clause.

INSERT INTO Table2 Select * FROM Table1
WHERE Table1.PrimaryKey Not In(SELECT PrimaryKey FROM Table2)
 
Rick,

Sorry to keep bugging you about this. I tried both of
your suggestions but they did not work. I think it is
because the tables that I am working with don't have a
primary key. They are transaction detail tables. So if
a customer buys more that one thing, the same Transaction
Number appears. There is a line number field as well.
The combination of the Transaction Number and the line
number would create a unique key. I can also do a Date =
today but it would need to be run every day.

Thanks,

Haji
 
Haji said:
Rick,

Sorry to keep bugging you about this. I tried both of
your suggestions but they did not work. I think it is
because the tables that I am working with don't have a
primary key. They are transaction detail tables. So if
a customer buys more that one thing, the same Transaction
Number appears. There is a line number field as well.
The combination of the Transaction Number and the line
number would create a unique key.
Click to expand...

Well then, if you modify the design of the table you are inserting into by adding a
unique index on the combination of TransactionNumber and LineNumber then the append
query will automatically reject records which already have these values in the
destination table.
 
Back
Top