Adding row number to a Query 2

[DaveMac]

I need a row number field in a query. The query is based on a table that has an autonumber field however there are deletions so I have gaps that I cannot have in the query results. I have read the many posts on this but none of them seem to fit exactly what I need.

In addition to the statement below I need a column that is simply the row number or a counter. Starts at 1 and goes on til the end incrementing by 1 each row.

SELECT DISTINCT dbo_tblPRDMSTR.ACTIV, dbo_tblPRDMSTR.CMPNO, dbo_tblPRDMSTR.PRDNO, dbo_tblPRDMSTR.DESCP, dbo_tblPRDMSTR.GITYP, dbo_tblPRDMSTR.PKGWT
FROM dbo_tblPRDMSTR
WHERE (((dbo_tblPRDMSTR.ACTIV)="1") AND ((dbo_tblPRDMSTR.CMPNO)=516) AND ((dbo_tblPRDMSTR.GITYP)="FG" Or (dbo_tblPRDMSTR.GITYP)="FR"));

This query runs on the click of a command button I made that outputs the results as a csv so the user can upload into another system. The other system has a requirement that there be row numbers.

Thanks!

 

[PHV]

a table that has an autonumber field
SELECT (SELECT COUNT(*) FROM dbo_tblPRDMSTR WHERE ACTIV='1' AND CMPNO=516 AND GITYP IN ('FG','FR') AND AutoNumberField<=A.AutoNumberField) AS RowNumber
, ACTIV, CMPNO, PRDNO, DESCP, GITYP, PKGWT
FROM dbo_tblPRDMSTR A
WHERE ACTIV='1' AND CMPNO=516 AND GITYP IN ('FG','FR')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DaveMac]

Thanks for the help PHV. I think you answered a question of mine 5 years ago! Have another issue. I posted a simple query I have so I could clearly understand the new functionality. It ran and worked in my simple query but as I tried to move it into my real query I had to tweek it and am missing something. Here is the SQL and I am getting a syntax error do you see it?

SELECT (SELECT COUNT(*) FROM TblItems of Pack Lineup WHERE Quantity >'0' AND AutoNumber<=A.AutoNumber)as rownumber,
tbl_plant.Loc, [TblItems of Pack Lineup].Prodno,
tbl_FGs_with_Family.qry_All_SKUs_1_DESCP,
[TblItems of Pack Lineup].Quantity, "Y" AS FirmPlanSW, "Y" AS U_FirmPlanSW,
tbl_plant.Production_Method, [TblItems of Pack Lineup].Description, [TblItems of Pack Lineup].autonumber,
[TblItems of Pack Lineup].autonumber
FROM tbl_plant, ([TblItems of Pack Lineup]
INNER JOIN Tbl_List_of_Families ON [TblItems of Pack Lineup].Formula = Tbl_List_of_Families.CHILD)
INNER JOIN tbl_FGs_with_Family ON [TblItems of Pack Lineup].Prodno = tbl_FGs_with_Family.PARNT
WHERE ((([TblItems of Pack Lineup].Quantity)>0));

Thanks

 

[MajP]

one problem may be
Quantity >'0'
likely
Quantity > 0

 

[DaveMac]

Just tried that no dice. I am in SQL view and when I try to jump to design view it give the error and then it highlights the sub query on the first line????

Strange

Any other thoughts?

 

[MajP]

FROM TblItems of Pack Lineup?

 

[MajP]

I guess that is the table name. Do yourself a favor and NEVER EVER put spaces in any name of any object in Access or vb.

 

[DaveMac]

I know I know. This is an access DB I put together 10 years ago and holy crap it is still in service. I moved to underscores a few years ago.

So you think the naming is the issue? I would change it but I think it is everywhere so that would scare me to change the table name.

 

[MajP]

if not it must be in [], [tbl with spaces]

 

[DaveMac]

DDDDDDDDDDDDAAAAAAAAAAAAAAAAAAAAAANNNNNNNNNNNNGGGGGGGGGGGGGG

brackets around the sub query table TblItems of Pack Lineup!!!!!!!!

But spaces are still not good and that is why you have to bracket!

Now it is just returning the autonumber it is not starting at 1 and counting

plus it has picked up a cartesian spread or the sub q as .A has duplicated records


SELECT DISTINCT (SELECT COUNT(*) FROM [TblItems of Pack Lineup] WHERE Quantity > 0 AND AutoNumber<=A.AutoNumber) AS rownumber, tbl_plant.Loc, [TblItems of Pack Lineup].Prodno, tbl_FGs_with_Family.qry_All_SKUs_1_DESCP, [TblItems of Pack Lineup].Quantity, "Y" AS FirmPlanSW, "Y" AS U_FirmPlanSW, tbl_plant.Production_Method, [TblItems of Pack Lineup].Description, [TblItems of Pack Lineup].autonumber
FROM [TblItems of Pack Lineup] AS A, tbl_plant, ([TblItems of Pack Lineup] INNER JOIN Tbl_List_of_Families ON [TblItems of Pack Lineup].Formula = Tbl_List_of_Families.CHILD) INNER JOIN tbl_FGs_with_Family ON [TblItems of Pack Lineup].Prodno = tbl_FGs_with_Family.PARNT
WHERE ((([TblItems of Pack Lineup].Quantity)>0));

 

[DaveMac]

WOW this was a good one!

Got it thanks to all who helped!!!!

Here is the answer: Had to also join the autonumber field in the .A to the original so it was not a orphan table


SELECT DISTINCT (SELECT COUNT(*) FROM [TblItems of Pack Lineup] WHERE Quantity > 0 AND AutoNumber<=A.AutoNumber) AS rownumber, tbl_plant.Loc, [TblItems of Pack Lineup].Prodno, tbl_FGs_with_Family.qry_All_SKUs_1_DESCP, [TblItems of Pack Lineup].Quantity, "Y" AS FirmPlanSW, "Y" AS U_FirmPlanSW, tbl_plant.Production_Method, [TblItems of Pack Lineup].autonumber
FROM tbl_plant, [TblItems of Pack Lineup] AS A INNER JOIN (([TblItems of Pack Lineup] INNER JOIN Tbl_List_of_Families ON [TblItems of Pack Lineup].Formula = Tbl_List_of_Families.CHILD) INNER JOIN tbl_FGs_with_Family ON [TblItems of Pack Lineup].Prodno = tbl_FGs_with_Family.PARNT) ON A.autonumber = [TblItems of Pack Lineup].autonumber
WHERE ((([TblItems of Pack Lineup].Quantity)>0));

 

[dhookom]

I would save the distinct query without the subquery. Then create a new query based on the distinct query that will add the subquery.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

And this ?
SELECT (SELECT COUNT(*) FROM [TblItems of Pack Lineup] WHERE Quantity>0 AND AutoNumber<=A.AutoNumber AND Formula=Tbl_List_of_Families.CHILD AND Prodno=tbl_FGs_with_Family.PARNT) AS rownumber
, tbl_plant.Loc, A.Prodno, tbl_FGs_with_Family.qry_All_SKUs_1_DESCP, A.Quantity, "Y" AS FirmPlanSW, "Y" AS U_FirmPlanSW, tbl_plant.Production_Method, A.Description, A.autonumber
FROM tbl_plant, ([TblItems of Pack Lineup] AS A INNER JOIN Tbl_List_of_Families ON A.Formula = Tbl_List_of_Families.CHILD) INNER JOIN tbl_FGs_with_Family ON A.Prodno = tbl_FGs_with_Family.PARNT
WHERE A.Quantity>0;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

And hence a great lesson on why you are better off voluntarilly getting your teeth drilled than using spaces, illegal characters, or reserved words in any access or vb object name.

 

[DaveMac]

any easy way to fix these issues? Something that looks through the entire app and changes the name

 

[MajP]

There has been a couple of threads on that issue, and a lot of good links. There are several freeware apps that will search your database and do global changes. Do a search on this site, and I will see if I can find some of those threads.

 

[MajP]

Thread181-1628289

Towards the end are lots of links to applications that will do this.

 

[DaveMac]

Great! Thanks to all who helped. It is appreciated!