Access Field INDEXING Help

**orozvik** · 10-01-2014, 11:26 AM

Hi Access Experts.
Can anyone offer up some helpful suggestions on how I can get a field in an Access Query to index the results from two columns?

Attached is a snippet of the data I am trying to index. Below is an extract from the attached file for explanation purposes.

I am hoping to get the formula in the NItem column to index (1, 2, 3, etc) the data in the DocumentNO (3400000237, etc) and GLcc (44551300) columns.

Thus, for document number 3400000237 it repeats twice and thus there are two GL Accounts associated with it, thus there is NItems 1 and 2 associated with it. Where there is only one GL account associated with the document number there will thus be one document number and one Nitem number or 1.

There are thousands of these rows in the access table, thus I was hoping to build a query where the NItem column would be smart enough to index as described above.

Can any one help?

Thank you so much in advance.

Fiscl DocumentNo GLcc AcctName AmountLC NItem
2014 3400000237 44551300 VAT recov.-domestic 37.43 1
2014 2 3400000237 62622000 Tel. costs - mobile 249.90 2
2014 2 3400000238 44551300 VAT recov.-domestic 5.25 1
2014 2 3400000238 60631000 Non-stock fact.suppl 104.95 2
2014 2 3400000239 44551300 VAT recov.-domestic 13.63 1
2014 2 3400000239 62621000 Tel. costs - fixed 93.91 2
2014 2 3400000240 44551300 VAT recov.-domestic 573.54 1
2014 2 3400000240 62440000 Freight admin 3,830.00 2

**ranman256** · 10-01-2014, 03:55 PM

(as mentioned on other board)

There is a formula that can do it, but I've found it to be buggy under certain conditions.
I would just write the data to a 'final' table that has autocounter for the index.
NOW, this means you need 2 tables:
1 and tFinal with autonumber (this you write your data to)
2 and a copy of tFinal, say tFinal COPY. this NEVER gets written to in order to keep the autonumber starting at 1.

build a macro:

''put all your data steps here

'clear the target copy using
CopyObject "", "tFinal", table, "tFinal COPY"

'now the table index will start with 1, so run append query to put your data in the target table
docmd.openquery "qaPostFinalData"

**alansidman** · 10-01-2014, 05:47 PM

crossposted: http://www.accessforums.net/queries/...dex-47293.html

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).