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

Row Number query - PK unknown

P: n/a
I've been fishing in the collective knowledge pool of cdma posts gone
by for a solution to this for a while - but no joy. Needless to say,
I've also done my own head in trying to figure this out.

Basically, I need to run a query agains a table that can identify what
row a value is on. This would be easy enough - except it's to be run
againt a number of indeterminate tables or indeterminate primary keys.
It's not that the tables arent indexed, most are - but i'm trying to
build a generic sql building function that can run against any table as
part of a verification process.

Greatly Simplified Example:

Table
Name Age
---- ---
Bob 6
Ann 10
Tim
Sam 5

Query Result:
Record No Field Problem
--------- ----- -------
3 "Age" Null Value

This is greatly simplified from what i'm doing and I have no problems
with the data validation stuff (driven from a seperate validity spec
table) - In reality it involves cross checking table vales etc - but
the problem remains the same:

When you dont know what the primary key is, how do you get a record
number? Given the width of some of these tables and the fact that for
some have no requirement for records to be distinct, grouping is out :(

....probably not possible

Aug 4 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a

of course it's possible - as soon as i clicked post, the answer came to
me ...sorry about that.

I use a function which increments and returns a modular variable, and
call it using any random field (to prevent jet caching the answer) -
from a sub query alongside * from the table.
Then just reset the modular variable to 0 before every query call.

....duh!!!

Aug 4 '06 #2

P: n/a
Bill, as you already know, there's not a simple way to do that is Access
queries.

Could you live with a form in datasheet view instead of a query? If so,
there is a dead-simple solution: just add a text box with ControlSource of:
=[Form].[CurrentRecord]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BillCo" <co**********@gmail.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...
I've been fishing in the collective knowledge pool of cdma posts gone
by for a solution to this for a while - but no joy. Needless to say,
I've also done my own head in trying to figure this out.

Basically, I need to run a query agains a table that can identify what
row a value is on. This would be easy enough - except it's to be run
againt a number of indeterminate tables or indeterminate primary keys.
It's not that the tables arent indexed, most are - but i'm trying to
build a generic sql building function that can run against any table as
part of a verification process.

Greatly Simplified Example:

Table
Name Age
---- ---
Bob 6
Ann 10
Tim
Sam 5

Query Result:
Record No Field Problem
--------- ----- -------
3 "Age" Null Value

This is greatly simplified from what i'm doing and I have no problems
with the data validation stuff (driven from a seperate validity spec
table) - In reality it involves cross checking table vales etc - but
the problem remains the same:

When you dont know what the primary key is, how do you get a record
number? Given the width of some of these tables and the fact that for
some have no requirement for records to be distinct, grouping is out :(

...probably not possible

Aug 4 '06 #3

P: n/a
Hmm. That can still cause issues, Bill.

If you have a reasonably large recordset, jump to the end, and scroll
backwards, check to see if the function is called in the order you expected.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BillCo" <co**********@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...
>
of course it's possible - as soon as i clicked post, the answer came to
me ...sorry about that.

I use a function which increments and returns a modular variable, and
call it using any random field (to prevent jet caching the answer) -
from a sub query alongside * from the table.
Then just reset the modular variable to 0 before every query call.

...duh!!!

Aug 4 '06 #4

P: n/a

Thanks Allen - dont think I could live with a data sheet - but thanks
for the thought, I might use that approach in the future

the solution I posted works - kinda:

select
fields...
from
(select getRowNumber(randFieldName), * from tableName) as IndexedTable
left join otherCrap;

function getRowNumber(varJunk as variant) as integer
mInt = mInt +1
getRowNumber = mInt
end function

but it turns out that jet won't evaluate the row number for all of the
items in the sub query ...it evaluates the field only for the returned
records in the main query - results then being numberd 1, 2, 3 etc. -
probably makes sense for most queries.

workaround:
run the query without parameters and open a recordset against it. Then
do the validation against the recordset, forcing the row field to be
evaluated on every loop and appending values to the error log where
certain criteria are matched

all of the weird hidden crap data jet stores and it doesnt have a
simple table row index available to the user! it makes grown men cry :'(

Aug 4 '06 #5

P: n/a
If you have a reasonably large recordset, jump to the end, and scroll
backwards, check to see if the function is called in the order you expected.
yeah, true - the trick is to open the query as a recordset and loop
through it in order from 0 to n and force the calculation exactly once
at every recordset move - it's not pretty, but it does assign row
numbers in the order the records appear in the table... i've tested it
a bit and it's working so far - fingers crossed!

Aug 4 '06 #6

P: n/a
"BillCo" <co**********@gmail.comwrote
but it does assign row numbers in the order
the records appear in the table... i've tested it
a bit and it's working so far - fingers crossed!
Of course, by "order the records appear," you mean exactly that and no more
because I am certain you know that in a relational database table the
records are, by definition, unordered. Of course, when you display them,
they have to be displayed in _some_ order and that is usually, if you are
viewing datasheet view or retrieving with VBA DAO, in primary key order (if
any). But, you shouldn't count on that _always_ being the case.

Larry Linson
Microsoft Access MVP

Aug 4 '06 #7

P: n/a
"BillCo" wrote
all of the weird hidden crap data jet stores
and it doesnt have a simple table row index
available to the user! it makes grown men cry :'(
I suppose some grown men cry easily, but some are happier the closer their
favorite database is to "truly relational" and only begin to "tear up" when
the Boys and Girls in Redmond take it farther from "truly relational."

If you want a row index, there are products that include it. IMNSHO, they
lack a great deal in being as capable and as flexible as Access is, so, I'll
stick with Access, thankee-very-much.

Larry Linson
Aug 4 '06 #8

P: n/a
"BillCo" <co**********@gmail.comwrote in
news:11*********************@75g2000cwc.googlegrou ps.com:
all of the weird hidden crap data jet stores and it doesnt have a
simple table row index available to the user! it makes grown men
cry :'(
You are clearly an idiot.

But I see I determined that already because you're already in my
killfile.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 4 '06 #9

P: n/a

David W. Fenton wrote:
"BillCo" <co**********@gmail.comwrote in
news:11*********************@75g2000cwc.googlegrou ps.com:
all of the weird hidden crap data jet stores and it doesnt have a
simple table row index available to the user! it makes grown men
cry :'(

You are clearly an idiot.

But I see I determined that already because you're already in my
killfile.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
i'm hugely offended - i've never once insulted anyone on this news
group or given smart arse answers to people who are asking genuine
questions. if i say something in a post that doesnt make sense, the
appropriate response would be to correct my error - not to make nasty
comments. While you might consider it an idiot thing to say that there
should be a row index (on reflection, you're probably right), there is
no need to be arrogant and cruel.

fyi, David, up to now i've actually found your posts useful and
interesting. since i'm in you killfile you probably wont read this, but
if you do read it - then perhaps you should (if nothing else), adjust
your attitude to people who know less than you.

Aug 8 '06 #10

P: n/a

Larry Linson wrote:
"BillCo" wrote
all of the weird hidden crap data jet stores
and it doesnt have a simple table row index
available to the user! it makes grown men cry :'(

I suppose some grown men cry easily, but some are happier the closer their
favorite database is to "truly relational" and only begin to "tear up" when
the Boys and Girls in Redmond take it farther from "truly relational."

If you want a row index, there are products that include it. IMNSHO, they
lack a great deal in being as capable and as flexible as Access is, so, I'll
stick with Access, thankee-very-much.

Larry Linson
Yeah, you're probably right - there's little sense trying to identify a
record by it's position in the table... I'm going to take the problem
back to the drawing board and try a different approach to reporting
errors. Thanks for you help.

Aug 8 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.