Query line counter

I need to use a value that I get from DLookUp("[ID_m]";"tbl_ID_MAKS_VARELNORDRE";"[ID_m]") (there is only ever one record in the table) and add +1 for each line in a query result. I have tried for a few hours now, so any help appreciated. Thanks in advance.

The original query result may look like this:

Sample1     Result 234,4
Sample 15  Result 114,3
Sample 27  Result 43,2

I need the query to return, assuming the DLookup value is 200:

201   Sample1      Result 234,4
202   Sample 15   Result 114,3
203   Sample27    Result 43,2


Best regards,
Evert
Evert JorDVM/ResearcherAsked:
Who is Participating?

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

x
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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try something like this:

SELECT (select count(*) as idx from  yourTable as b where a.ID >= b.ID) + nz(DLookUp("[ID_m]","tbl_ID_MAKS_VARELNORDRE","[ID_m]") ,0) as idx, 
a.Feidl1, a.Field2
FROM yourTable as a

Open in new window

29015636.accdb
0
Dale FyeOwner, Developing Solutions LLCCommented:
How are using this query?  Are you displaying this in a form, or a report?
0
Evert JorDVM/ResearcherAuthor Commented:
Ryan: Looked at your example. The problem is that there is no unique ID in the query (yourTable). That is the main issue. I need to get the value 200 into the query and add +1 for each line in the query result.

Best regards,
Evert
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
The problem is that there is no unique ID in the query (yourTable). That is the main issue
yes that's the main issue. we need an unique identifier to determine the sequence of records in Access. without that field, we are difficult to create a sequence number for it.
0
Evert JorDVM/ResearcherAuthor Commented:
OK. I will try to use your suggestion and some other scripts to get this to work. I will probably need until next week (Easter Holidays).
I will post the solution once I have it.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
If you're making a single pass through the recordset you can do a line counter easily.

But if you're going to use this in a form  where you're going to move back-and-forth through the records that then you cannot use that technique.  However depending on the situation at temp table often work.

Jim
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
any progress so far?
0
Evert JorDVM/ResearcherAuthor Commented:
I solved the problem by:
- First using a create table query to create a table on the fly without adding any records.
- Then modifying that table in VBA to add a ID autonumber field.
- Then copy all the records into it, so each record would get the ID 1, 2, 3, 4 etc.
- Finally using a new query to display the correct line number as ID field + add it to a Dlookup value (for example 45900), so the resulting line numbers would be 45901, 45902, 45903, 45904.

Maybe not the neatest solution, but it works.
0

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
Evert JorDVM/ResearcherAuthor Commented:
Thanks Ryan, your solution was the best suggestion - although I ended up with a workaround due to the missing ID-field in the original dataset.
0
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
Query Syntax

From novice to tech pro — start learning today.