Access 2000 - Autonumber in Query

Dear Experts,

Can you please advise how to add autonumber to a query?

So for example if there is a table with three records with headers "Item" and "Qty"

Item     Qty
1010     5
1011     10
1012     14

then the autonumber would be 1 for the first record, 2 to the second and so on.

Basically I have no chance to put this to a table, because there are several ones and linked to the access as outer excel and text files. So there would be needed to calculate in a query

thanks,
LVL 1
csehzIT consultantAsked:
Who is Participating?
 
dqmqConnect With a Mentor Commented:
I would like to see your query, please try again to post it

>But perceived that for example if in one table have the Mtrl like 1010 and Qty like 20pcs twice or generally multiple times, the union query gives it only once as result.

The "autonumber" approach isn't the best because it's still remotely possible for 1010 20pcs to coincidentally pick up the same autonumber.   If you use UNION ALL, the duplicates will not drop out.

Finally, my solution naively assumed that item was unique, which I now understand is incorrect. My solution requires a unique key, so is not viable as it seems a unique key is exactly what you are missing.


0
 
dqmqCommented:
You can't add an autonumber to the results of a query.  You can add a row number, but performance suffers.  I will post that solution in the next frame.  In the meantime, would you please elaborate about the reason for the row number.
0
 
dqmqCommented:
Select T.*, (Select count(*) from YourTable as C where C.item <= T.item) as RowNumber from YourTable as T
order by t.item
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
csehzIT consultantAuthor Commented:
Dqmq thanks, basically the reason that I need to do a union query from three tables, like in the attached code.

But perceived that for example if in one table have the Mtrl like 1010 and Qty like 20pcs twice or generally multiple times, the union query gives it only once as result.

I would need this example three times after the union
0
 
csehzIT consultantAuthor Commented:
Sorry somehow I am not able to attached the code as it is never posting it. I will come back soon how were able to apply your solution, thanks very much
0
 
csehzIT consultantAuthor Commented:
SELECT Overdue_RSD_estimate.[Psi Sbgrp] AS PSI, Overdue_RSD_estimate.[Mtrl No(backlog)] AS Mtrl, Overdue_RSD_estimate.[Qty n/a not in trans] AS Qty, Overdue_RSD_estimate.[Fisc Week (RSD)] AS Week, Overdue_RSD_estimate.[Plant Id(backlog)] AS Plant, "RSD" AS File
FROM Overdue_RSD_estimate;
0
 
csehzIT consultantAuthor Commented:
this is the part of it
0
 
peter57rCommented:
No points wanted - I just feel that the solution - which was mentioned as part of a post- may be lost in all the other stuff.

dqmq said .. and this is all that is required...

change 'Union' to Union All' in your union query.
0
 
csehzIT consultantAuthor Commented:
Sorry finally I am able to post a full comment from home, somehow from the company had very slow network which unabled more characters so like code attachement.

Dqmq, Peter, thanks exactly the UNION ALL is the solution like in the attached code. and so giving the points to Dqmq
SELECT Overdue_RSD_estimate.[Psi Sbgrp] AS PSI, Overdue_RSD_estimate.[Mtrl No(backlog)] AS Mtrl, Overdue_RSD_estimate.[Qty n/a not in trans] AS Qty, Overdue_RSD_estimate.[Fisc Week (RSD)] AS Week, Overdue_RSD_estimate.[Plant Id(backlog)] AS Plant, "RSD" AS File
FROM Overdue_RSD_estimate;

UNION ALL SELECT AssemblyPlan.[Psi Subgroup] AS PSI, AssemblyPlan.[Mtrl No] AS Mtrl, AssemblyPlan.[open - transit] AS Qty, "" AS Week, AssemblyPlan.[Plant Id] AS Plant, "AP" AS File
FROM AssemblyPlan;

UNION ALL SELECT "" AS PSI, Query_Count.[Mtrl] AS Mtrl, Query_Count.Qty AS Qty, "" AS Week, "" AS Plant, "FC" AS File
FROM Query_Count;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.