Query update

Hi All

I have a query based on a simple table.

In the query one of the rows has a row count function:

SELECT RowCounter(CStr([Item_ItemNumber]) & [DN],False,CStr([Item_ItemNumber]) & [DoorType]) AS RowID, tblTemp1.Item_ItemNumber, tblTemp1.Sets_ItemNumber, tblTemp1.DoorType, tblTemp1.Batch, tblTemp1.Location, tblTemp1.DN, tblTemp1.Result, tblTemp1.ID
FROM tblTemp1
WHERE (((RowCounter(CStr([Item_ItemNumber]) & [DN],False,CStr([Item_ItemNumber]) & [DoorType]))=1));

You would imagine that with one row in the table that the row count would be 1 but the query seem to be using a row number from a previous record set where  that row was allocated a different number. It's as if it will not let go of that previous number.

Just to save wasting your time I have had very long conversations on EE about why I need row numbers. The purpose is not aesthetical it serves a very specific purpose that is very long winded, too long for this question.
Derek BrownMDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
You would imagine that with one row in the table that the row count would be 1 [..]
Nope, cause RowCounter() seems to be a custom function...
Derek BrownMDAuthor Commented:
I'll Check it Ta
Derek BrownMDAuthor Commented:
You are correct. Forgot I did this and can't see whats wrong with it, any ideas or do I need to send the entire thing? (It's Huge)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ste5anSenior DeveloperCommented:
You're only allowed to call it once per key. What is your order criteria for your groups?

As far as I see, you want the first [Item_ItemNumber] & [DN] per [Item_ItemNumber] & [DoorType]? Is ID the primary key?

Then this should do it:

SELECT O.Item_ItemNumber, O.Sets_ItemNumber, O.DoorType, O.Batch, O.Location, O.DN, O.Result, O.ID
FROM tblTemp1 O
	FROM tblTemp1 I
	WHERE I.[Item_ItemNumber] = O.[Item_ItemNumber] 
		AND I.[DoorType] = O.[DoorType]
	ORDER BY CStr([Item_ItemNumber]) & [DN] ASC

Open in new window

Dale FyeOwner, Developing Solutions LLCCommented:
Are you using this for a form or a report.

You can get a row counter for a report by simply adding an unbound textbox, setting it's ControlSource to: =1, and then setting the RunningSum property of the Textbox to either "Over All" or "Over Group".

Gustav BrockCIOCommented:
Here's the link to the previous question and code: RowCounter

The function is so clever that It stores the row number for an ID once it is read. This way it can read back the numbers at extreme speed when browsing as only one reading of the records is needed.

What you need is to reset the counter. How to do this, is explained in the in-line comments:

' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.

Open in new window

You'll see, that you must either modify your query to follow this syntax or - between your calls of the query - reset the counter manually:

RowCounter "",True

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Derek BrownMDAuthor Commented:
I will try that tomorrow.  Thanks All
Gustav BrockCIOCommented:
Answer provided.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.