By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,658 Members | 1,636 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,658 IT Pros & Developers. It's quick & easy.

Compare Row Number to ID field

P: 5
I have an Access 97 database that is the back-end for a poorly-written custom program, for which we have lost the source code. Yay.

The problem is that if the row number and the ID field do not match, the program has issues. (The programmer used the row number in some parts of the code and the id number in other parts... grr)

So what I'd like to do is write a query that looks something like this:

select * from airports where rownumber <> airportID

How can I get the row number? I've done lots of searching and turned up nothing useful...

Thanks very much!
Jul 27 '09 #1
Share this Question
Share on Google+
6 Replies


P: 5
PS - the AirportID field used to be an autonumber, but I have changed it to a plain old Number field to make this a little bit better...
Jul 27 '09 #2

P: 5
Does anyone have a suggestion for me?
Aug 4 '09 #3

missinglinq
Expert 2.5K+
P: 3,532
What exactly is a "row number?" There is no such thing/concept native to Access! It has a to be a custom defined field.

And Autonumber fields should never be used for anything like this! Their sole reason for existing is to provide each record with a unique identifier.

Welcome to Bytes!

Linq ;0)>
Aug 5 '09 #4

P: 5
The row number is the physical position within the table. I know it's not a good thing to do, but like I said, the source code is gone, so I'm stuck with it.

If you open a table in Access and click on a record and look at the bottom of the screen you'll see that it says something like "82 of 153" -- that "82" is the row number I need to get. The ID number for row 82 might originally be 82 as well, but if you went in and deleted row 80, the 82nd row would now contain ID #83. Then the program goes totally haywire because it doesn't match.

So what I want to do is write a query that will let me know that row number 82 contains ID number 83, which doesn't match, which will cause a problem with the application.

Does that help clarify my issue?
Aug 5 '09 #5

P: 5
Hearing no responses... I guess I'll approach this a different way. Perhaps it'll be easier to figure out a way to determine if an ID number is missing. Something like "select ID from mytable where ID not in (select ID + 1 from mytable)"
Aug 24 '09 #6

100+
P: 675
If you created a new field in your table, for example RowNo as long, then you could fill these using VBA. A simple loop, until EOF would go through each record. You would increment a counter, assign to RowNo, and save the changes.
If any records have been deleted since this program was written (lost code), then this will not match what the original programmer was using. You know the message that asks if you want to save the table design after you do a sort, or resize a field, or some other cosmetic change? Well, if this has been done, you may not get the same "Row Number" as the original programmer did.
You are probably aware that if you change the "OrderBy" property of a Form, then row number 82 of 153 will be a different record than if OrderBy is left blank or set to a different field.
I think you got no response because you did not, or cannot, supply information about how the autonum field and the row number were managed.
And Autonumber fields should never be used for anything like this! Their sole reason for existing is to provide each record with a unique identifier.
Or to put this another way, "It Keeps Access Happy!"
If the autonum field was sequential, then the order the records/rows were created is the order of this field, unless you have messed it up when you made it a non-autonum field
the AirportID field used to be an autonumber, but I have changed it to a plain old Number field to make this a little bit better...
Any missing numbers would have to be restored to get the original row numbers.
Aug 25 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.