[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2000 - Autonumber in Query

Posted on 2011-05-10
9
Medium Priority
?
503 Views
Last Modified: 2012-05-11
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,
0
Comment
Question by:csehz
  • 5
  • 3
9 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 35735337
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
 
LVL 42

Expert Comment

by:dqmq
ID: 35735343
Select T.*, (Select count(*) from YourTable as C where C.item <= T.item) as RowNumber from YourTable as T
order by t.item
0
 
LVL 1

Author Comment

by:csehz
ID: 35735386
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:csehz
ID: 35735417
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
 
LVL 42

Accepted Solution

by:
dqmq earned 2000 total points
ID: 35735476
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
 
LVL 1

Author Comment

by:csehz
ID: 35735574
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
 
LVL 1

Author Comment

by:csehz
ID: 35735652
this is the part of it
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35736230
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
 
LVL 1

Author Comment

by:csehz
ID: 35738644
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question