Sequential numbers for SQL output (1 Viewer)

[ukaquanaut]

Hi Guys

I'm helping to assemble data that will be passed to SAP as flat csv files to populate some of our stock. I need a column of sequential number just 1 to n, no fancy conditions or groups.

However, when I select stock from location A everything I need is there, but the RowNum (1 to n) simply reflects the AutoNunber ID for the table TblSAPCoded.

I have tried a dozen combinations for this Dcount clause but I'm failing miserably, I'm hoping you guys can point me in the right direction.

I have attached three pictures to show.

 

[arnelgp]

select T1.id, T1.field1, T1.field2, ..., T1.fieldN, (select Count(*) from thistable where id <= T1.id) As LineNum FROM thistable AS T1;

if that doesn't do you can create a user function to do it.

1. create a query that include the PK of your table, ie:

select id from table1 Order by id;
save this query.

2. now create your query, the one you want to show the linenumber, don't forget to set its Order same as you have Order on the first query you created.
insert a calculated column on this query, ie:
LineNum: fnLineNum("yourFirstQueryNameYouCreatedOnStep1", [ID])

3. on a module, create a public function (fnLineNum):

public function fnLineNum(sQry As String, ID as long) as Long
dim rs as dao.recordset
dim lngLine As Long
set rs=Dbengine(0)(0).QueryDefs(sQry).OpenRecordset(dbOpenSnapshot)
with rs
.FindFirst "[ID] = " & ID
while not .BOF
lngLine = lineLine + 1
.MovePrevious
Wend
.Close
End With
set rs=Nothing
fnLineNum = lngLine
End Function

 

[ukaquanaut]

Hiya

Thank you for your help, but I must be lacking in understanding with this, it seems such a simple task and I've wasted hours trying all sorts off the web - sighs

This is my attempt with your help and simply returns the total rows of the table in every row 1143

SELECT TblSAPCoded.ID, TblSAPCoded.SAPSKU, TblSAPCoded.StkDescription, TblSAPCoded.Batch, TblSAPCoded.Expiry, TblSAPCoded.CountryOfOrigin, TblSAPCoded.StockQty, TblSAPCoded.Location, TblSAPCoded.AbsLocation,
(SELECT Count (*) from TblSAPCoded where ID <= TblSAPCoded.ID) AS T1
FROM TblSAPCoded
WHERE (((TblSAPCoded.Batch)<>"") AND ((TblSAPCoded.Location) Like [Aisle]));

Thanks
Jerry

 

[arnelgp]

SELECT T1.ID, T1.SAPSKU, T1.StkDescription, T1.Batch, T1.Expiry, T1.CountryOfOrigin, T1.StockQty, T1.Location, T1.AbsLocation,
(SELECT Count (*) from TblSAPCoded where ID <= T1.ID And [Batch]<>"" AND Location Like [Aisle]) AS LineNum
FROM TblSAPCoded AS T1
WHERE (((T1.Batch)<>"") AND ((T1.Location) Like [Aisle]));

btw, what is Aisle there is it a field in your table?

 

[CJ_London]

your issue is not understanding aliasing, why it is necessary and it's implications - if you carefully compare what Arnlegp wrote with what you have, you will see the differences

try

 SELECT TblSAPCoded.ID, TblSAPCoded.SAPSKU, TblSAPCoded.StkDescription, TblSAPCoded.Batch, TblSAPCoded.Expiry, TblSAPCoded.CountryOfOrigin, TblSAPCoded.StockQty, TblSAPCoded.Location, TblSAPCoded.AbsLocation, 
(SELECT Count (*) from TblSAPCoded [COLOR=red]AS T1[/COLOR] where ID <= TblSAPCoded.ID) [COLOR=red]AS rownum[/COLOR]
FROM TblSAPCoded
WHERE (((TblSAPCoded.Batch)<>"") AND ((TblSAPCoded.Location) Like [Aisle]));

However, as you are also filtering so this will result in gaps in rownum.

If this does not matter then fine, if not you also need to include your criteria within the subquery

...
...
(SELECT Count (*) from TblSAPCoded AS T1 where ID <= TblSAPCoded.ID
and Batch<>"" AND Location=[Aisle]) AS rownum
FROM TblSAPCoded
...
....

note unless you are using wildcards you should use = rather than like - and not sure what [Aisle] so this may also have an impact - I'm assuming it is a field in tblSAPCoded.

 

[ukaquanaut]

Hi Thank You for your help

I see where I went wrong on the SQL thank you. I tried the adjusted code as you suggested and this resulted in a same result of the rownum being the same value as the auto update ID value, with sections missing.

Aisle is the text box used to prompt the use for an Aisle location to extract the data.

it uses a wildcard to say find the products in Aisle A, B C or D. from the Location field I put that in a screen shot for example 'UK-A*'

I have a feeling I will have to write what I have to a table and use some VBA to make a sequential column.

I appreciate your help, thank you

Regards
Jerry

 

[ukaquanaut]

Hi
I sometimes think I should give this up lol

I tried the vba function suggested and followed the steps as instructed and LineNum output is simply 1 on every line,

I have an SQL with just the PK of the TblSAPCoded, called QryCodedLines ( a column 1 to 1143 )

I called the function from my original SQL and get the products I expect with the wild card search (Aisle) but the function output is 1.

I had to change the Function to make it work, it was complaining about the set rs line

Set rs = DBEngine(0)(0).QueryDefs(sQry).OpenRecordset(dbOpenSnapshot)

I changed the db OpenSnapshot to dbOpenSnapshot and the error went. ( I expected it was a typo )

Any other thoughts are appreciated, thank you

regards
Jerry

 

[arnelgp]

upload your db with just the table tblSapCoded and ill test it.

 

[ukaquanaut]

Hiya

I have uploaded it - many Thanks

jerry

 

[arnelgp]

here is your query, QryAisleStock.

 

[ukaquanaut]

Awesome

Thank You. I see you reverted to your initial idea with the count - I appreciate your advise and effort, your a star

Thank You
Regards
Jerry