05-22-06, 16:41 #1Registered User
- Join Date
- Jun 2005
Unanswered: Autonumber records in a SELECT QUERY-
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.
Here's the 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;The only true wisdom is in knowing you know nothing.
05-22-06, 16:58 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
where are you using this item number?
if its a report then you could fake an item number by creating a control on the fly and addiing to it in the reports on detail format event, or sub forms on current event.
however that isn't going to cut if you need to rely on the item number, as unless you use soem form of index you can never guarantee that the dispaly order will be consistent over time. if your sub records are using an autonumber then you cna probably get away with it. however this isn't a 'smart' way to do it as on some db systems you can set autonumber to run from a different base an change in other sequences thant one.
The 'smarter' solution is to create a column for item number in the data.
a solution is to create an unbound control in your report, decalre a private varaible in the reports code
assign the variable a value of 0 in the reports group (presumably PO) header, and add one to the variable and assign the the variable to the unbound control.
youmay be able to do the same tinkering around with the form - the probelm is going to be that you can't use unbound controls in continuous forms in that manner.
HTHI'd rather be riding on the Tiger 800 or the Norton
05-22-06, 17:31 #3Registered User
- Join Date
- Jun 2005
Thanks for your response healdem. What I'm trying to accomplish is do a text transfer (delimited) from this query to a FTP server. However, their requirements everytime I submit a file, that all records must be numbered. For example, if I submitted a file today and has a total of 100 items. Then each line should have a number starting from 1 - 100. And this is on a daily basis. The file being submitted should always begin with count 1 - to total lines. Hope that made things a little clearer.The only true wisdom is in knowing you know nothing.