Numbering records in a query??? 1

[rodrunner79]

I have a SELECT query. When this query is run, it prompts for a PO Number. If PO is entered, it will filter the records for that PO.

Now, what I want to accomplish is... How can I put a line number on each record that is associated with that PO?

For example, it the filtered PO has 10 records. I want it to number each record, starting from 1 to 10. How do I do this? What is the expression? Is it =DMAX([ME]) + 1? Because, I've tried that and it didn't work... Please help.

-=True wisdom comes from knowing you know nothing.=-

 

[PHV]

Which field(s) in the SELECT clause will UNIQUELY identify each row ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rodrunner79]

A field called BarCd...

-=True wisdom comes from knowing you know nothing.=-

 

[rodrunner79]

SELECT tblSNDetails.ItmCd, tblSNDetails.PONo, 'WHSE' AS OrdArea, tblSNDetails.BarCd
FROM tblSNDetails
WHERE (((tblSNDetails.PONo)=[Enter PO]));

-=True wisdom comes from knowing you know nothing.=-

 

[PHV]

You may try something like this:
SELECT A.ItmCd, A.PONo, 'WHSE' AS OrdArea, A.BarCd, Count(*) AS Rank
FROM tblSNDetails AS A INNER JOIN tblSNDetails AS B
ON A.PONo = B.PONo AND A.BarCd >= B.BarCd
WHERE A.PONo = [Enter PO]
GROUP BY A.ItmCd, A.PONo, A.BarCd;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rodrunner79]

First, thanks for your responses PHV, I really appreciated it. But I'm sorry, I don't quite get it, what does the inner join has to do with autonumbering the records? There's no second table to process the inner join from. So where did you get Table B? Please explain to me as if I'm a two year old.. Thank you.

-=True wisdom comes from knowing you know nothing.=-

 

[PHV]

A and B are alias I used to differentiate the 2 instances of tblSNDetails.
Have you tried to paste my code in the SQL view pane ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rodrunner79]

Hey PHV, thanks for the response again. The query/code you provided works well with the example I provided. However, when I applied it to the actual query I have, it did something funny... First, let me show you my original query.

SELECT tblLUMCodes.LumCd, qryShipCstTtls.PAR, qryShipCstTtls.PONo, 'WHSE' AS OrdArea, '' AS LineNo, qryShipCstTtls.PAR, tblLUMCodes.DptObj, '' AS Status, tblLUMCodes.LumCdDesc, 1 AS OrdQty, 'EA' AS UM, 1 AS UMSz, Sum(qryShipCstTtls.ShipItmTtl) AS SumOfShipItmTtl, Sum(qryShipCstTtls.ShipItmTtl) AS SumOfShipItmTtl1, 'Some Unknown Vendor' AS MfgName, 12345678 AS VndrCtlgNo, 'XYZ' AS MfgCd, tblLUMCodes.TaxCd, 'Orders' AS Comments, 1234567 AS MfgCtlgNo, 12345 AS ContractNo, Date() AS NeedDt, 'N' AS Hazmat, 'N' AS MSDS, 1234 AS ReqNo, '' AS SubUOM, '' AS UPN
FROM qryShipCstTtls LEFT JOIN tblLUMCodes ON qryShipCstTtls.LumCd = tblLUMCodes.UnqID
GROUP BY tblLUMCodes.LumCd, qryShipCstTtls.PAR, qryShipCstTtls.PONo, 'WHSE', '', qryShipCstTtls.PAR, tblLUMCodes.DptObj, '', tblLUMCodes.LumCdDesc, 1, 'EA', 1, 'Prof Hospital Supplies', 12345678, 'PFH', tblLUMCodes.TaxCd, 'LUM Orders', 1234567, 12345, Date(), 'N', 'N', 1234, '', ''
HAVING (((qryShipCstTtls.PONo)=[Enter PO]))
ORDER BY qryShipCstTtls.PAR;

What I did was added the ",Count(*) AS RANK" to it (before the beginning of the FROM clause and it numbered it incrementally but had duplicate numbers. For example, I had 200 records but only had up to 43 counts because 4 records had a duplicate number count. Hope this is making sense...

Can you please explain to me, how to put/use rank properly. Thank you very much for helping.


-=True wisdom comes from knowing you know nothing.=-

 

[rodrunner79]

Hello, somebody help???????????????

-=True wisdom comes from knowing you know nothing.=-

 

[cmmrfrds]

Did you run and test the query provided by phv? Once you have seem that it is generating the row numbers you want did you save it as a query and give it a name?

 

[rodrunner79]

Yes, I did test the query that Phv provided... And I responded back with a reply (please see my 5th post on this thread).

-=True wisdom comes from knowing you know nothing.=-

 

[danvlas]

A database is a collection of UNORDERED records.
The number you are speaking about could/will change everytime you run the query, therefore it's highly unreliable.
If you just want a 'sequential number' that does not mean anything, use a report, create a textbox in detail section, controlsource=1 and RunningSum = over all.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[rodrunner79]

Thanks PHV, I figured it out... Really appriated the help.

-=True wisdom comes from knowing you know nothing.=-