Query line counter

Evert Jor
Evert Jor used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead
Commented:
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
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
How are using this query?  Are you displaying this in a form, or a report?
Evert JorDVM/Researcher

Author

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
Ensure you’re charging the right price for your IT

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

Ryan ChongSoftware Team Lead
Commented:
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.
Evert JorDVM/Researcher

Author

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.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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
Ryan ChongSoftware Team Lead

Commented:
any progress so far?
DVM/Researcher
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.
Evert JorDVM/Researcher

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial