Update Values (A2000)

[MichaelC]

I am trying to correct improper numeric identifiers in a field. Here is the plan. I have run a Query that gives me all records that = "DVD" in the format field. I sort them in ascending order by the Date in the release date field. I create a new field called temp number and I want to sequentially number the records beginning with 7001, and continuing by incrementing by one for each record in the query that is already sorted in the proper date order. I run an update query and the number of rows is exponentially higher than in reality...i.e. 32,384 rows as opposed to the actual 690 rows...then the update proceeds but there is no sequential numbering I get 7001 in all records in the query. What am I doing wrong? Thanks

[MarkLiquorman]

Well, hard to say without seeing your code. It sounds like your SQL is doing a cartesion join, which essentially means it is joining each record from one table with each record in another table. This happens when you don't specify the join criteria.

As for the numbering, this is very difficult to do via an update query. I'd opt for doing it via code.

[MichaelC]

Thanks for the response Mark. I am more of a "designer" than a "coder" ... can you recommend any good books on writing code in Access? VB? VBA? Thanks

[MarkLiquorman]

For learning VBA, I'd recommend "Beginning Access 97 VBA" by Smith and Sussman (I'm sure other versions are available also).

[WendellB]

Agreed - it's a WROX book and is how I got serious about VBA.