Complicated Query question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is what I am trying to do with access: Have the user browse and select a
spreadsheet file, then when they click an execute button, the data on the
spreadsheet file will be queried against the current table of information
that the database has, and then if the ID numbers are the same, but the
prices are different, have it change the price to the new spreadsheets price.

I am assuming most of this will have to be written in VB, or at least some
of it. Any help with this will be greatly appreciated, even just a link to
information regarding this subject. Thanks
 
Its actually not that difficult...
What you will do is to actually import the spreadsheet into an Access table
(hopefully your spreadsheet has consistently named column headings since they
become the column names in the Access table).
Then you will write your VB to loop thru a recordset based on the imported
table, do the compares, and issue a series of UPDATE statements against your
master prices table.
All this could be done automatically on the click of a button.

-Dorian
 
Daniel

Before you do ANY of this, make a backup of the table or even the whole .mdb
file. Update queries don't have an "oops" button.

I may be missing something, but what you've described sounds like an update
query. This would operate on the entire set of current info and the entire
set of imported info.

You could first create a query that joins the two tables on the IDs. Then,
if Table1.[Price] <>Table2.[Price], you'd want to update Table1.[Price] to
Table2.[Price].

But I think you can make it simpler still! If both tables have the same
price for the same ID, updating Table1's price to Table2's price won't
change anything, right? So you could leave that comparison out entirely,
and just update all Table1.[Price] to Table2.[Price] where the IDs match.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top