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: 2017-04-11

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license! Start your 7-day free trial.

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.

LVL 59

Ryan ChongSoftware Team LeadCommented: 2017-04-11

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

LVL 53

Dale FyeOwner, Dev-Soln LLCCommented: 2017-04-12

How are using this query? Are you displaying this in a form, or a report?

Evert JorDVM/ResearcherAuthor Commented: 2017-04-12

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

LVL 59

Ryan ChongSoftware Team LeadCommented: 2017-04-12

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/ResearcherAuthor Commented: 2017-04-12

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.

LVL 61

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented: 2017-04-13

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

LVL 59

Ryan ChongSoftware Team LeadCommented: 2017-04-27

any progress so far?

Evert JorDVM/ResearcherAuthor Commented: 2017-04-28

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.

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