anyway to tell if a record exists? in update query?

[xcess2003]

we get model/Price updates in the form of Excel files, I have set up a database from these Excel files to make updating prices much easier.. I link the New Excel files in the new table and then run a simple update query to match the models and update the the new prices... But also on these excel files are New Models, is there a simple way to Query these New models and then have an update query add them as a new record? Going through 300 Lines to check if there are any new items manually is a real pain and takes too much time...

Also anyway to round Numbers up like you can in Excel?

Any Help much appreciated...

Paul

 

[dcx693]

Yes, you can run an APPEND query that adds new records to your existing records. The easiest way to write it is to start off with a regular select query that finds which records in the Excel file don't already exist in your database. (Do you know how to do this?) Then select "Append Query" from the Query menu to change it to an append query. (You cannot undo an append query, so it's a good idea to test this on a copy of your data.) Once you're sure it works, save this query so you can run it again in the future without rebuilding it.

As to your other question, I too noticed that there is no VBA "round" function. No big deal though. If you know some VBA you can write your own custom round function. But if you don't want to reinvent the wheel, and since you're looking to use a function that already exists in Excel, use the Round function that's built into Excel from within Access. I forget exactly how to do this, but I definitely know it's possible. Post the question on this forum, or do a search (maybe someone's asked before).

 

[xcess2003]

HI dcx, Thanks for the Reply... actually the part im stuck on is the part you skipped lol How do i Find out which records do not already Exist? i cant seam to figure this one out.. Im pretty handy at writing Queries.. but have not come across any way to cross reference the New Linked Excel file with my database and pull out the ones that dont exist.. can you explain how this is done...

Many Thanks

 

[dcx693]

No problem. One way to check for records that exist on one table, but not in another is to create a select query. Show both tables in the query view. Join them on the common field(s). This usually creates an equi-join (which only shows records from both tables that have the same values in the joined fields). What you want, though, is to have an outer join. An outer join will display records from one table, say table A, even if a corresponding record doesn't exist in table B.

To change an equi-join into an outer join, double-click the join line that Access creates between the tables. In the dialog box that comes up, choose one of the three options. Option 1 is for the equi-join. Choose Option 2 or 3 based on what you need. Here's how to tell: if you want to find records in table A that don't exist in table B, choose the option that says "show all records from table A and only those from table B" where the joined fields are equal. Click OK to save your choice, and you'll notice the join line now has an arrow on one side.

Finally, pull down whatever fields you want to show into the QBE grid, and be sure to include the model field (or whatever the join field is called). In the criteria line for the model field, place this expression: Is Null. Run the query.

What this will do is show all records from table A. If a corresponding record doesn't already exist in table B, the join field will have a null value for table B. The criteria "Is Null" will show only those records in table A that don't exist in table B.

Now, just change this to an append query, and you know the rest.

 

[Jon K]

There is an article on "Updating and Appending Records in One Update Query" in the Microsoft Knowledge Base.
http://support.microsoft.com/default.aspx?scid=kb;en-us;127977

 

[dcx693]

Totally cool! Actually, this is basically the same procedure I wrote about, but I didn't realize that the two actions would be combined.

 

[xcess2003]

Cheers Guys, Just when you think your getting the hang of Access , along comes a problem and you find there is still Loads and loads of stuff you dont know about...thats what i love about access... I`ll Try and get my head round this ........ wish me luck !!

Once again cheers !!

 

[xcess2003]

Thanks Guys.. It does indeed Work Fine... Another problem Solved...

 

[dcx693]

FYI: There is a Round function in Access 2000 and higher, but no Roundup or Rounddown function like in Excel.

See the Microsoft Knowledge Base article http://support.microsoft.com/default.aspx?scid=kb;en-us;198571 on how to call Excel Functions from within Microsoft Access 2000 (I imagine it will work with other versions as long as you set the correct reference to the Excel object model).

 

[xcess2003]

Once again you have saved my bacon, I really couldnt understand how access could not have any round Feature... I think the Tutorial on using Excel functions in access is a little beyond me for now but i got the Built in round Feature to work no problems and does what i need ..... number\1

Thanks again....

Paul