Count Problem 1

[eatwork]

Hi everyone,
I have a problem with my query. I don't know how to put a record counter into the query that will number each record in the query. eg.
1) record 1
2) record 2
3) record 3
etc...

my query looks like this so far:

SELECT join_tbl_DispatchHist_DispLoadUnload.jobId, join_tbl_DispatchHist_DispLoadUnload.loadId, join_tbl_DispatchHist_DispLoadUnload.loadHistId, join_tbl_DispatchHist_DispLoadUnload.dispatchId, join_tbl_DispatchHist_DispLoadUnload.dispatchHistId, join_tbl_DispatchHist_DispLoadUnload.commodityId, join_tbl_DispatchHist_DispLoadUnload.dispatchLoadUnloadId, tbl_DispatchLoadUnload.dispatchLoadDate, tbl_DispatchLoadUnload.dispatchLoadTime, tbl_DispatchLoadUnload.dispatchLoadLocation, tbl_DispatchLoadUnload.dispatchLoadBy, tbl_DispatchLoadUnload.dispatchLoadMethod, First(tbl_DispatchLoadUnload.dispatchLoadRoute) AS FirstOfdispatchLoadRoute, First(tbl_DispatchLoadUnload.dispatchLoadInstructions) AS FirstOfdispatchLoadInstructions, First(tbl_DispatchLoadUnload.dispatchLoadPlacement) AS FirstOfdispatchLoadPlacement, tbl_DispatchLoadUnload.loadLoadUnloadId, tbl_DispatchHist.dispatchHistLoadUnloadId, "L" AS Loadunload, tbl_Commodity.commodityName, tbl_Commodity.commodityActualLength, tbl_Commodity.commodityActualWidth, tbl_Commodity.commodityActualHeight, tbl_Commodity.commodityActualWeight, tbl_Job.jobMeasuredIsMetric, Count(*) AS LineNum, tbl_Commodity.commodityUnitNum, tbl_Commodity.commoditySerialNum
FROM tbl_DispatchLoadUnload RIGHT JOIN ((tbl_Job RIGHT JOIN tbl_DispatchHist ON tbl_Job.jobId = tbl_DispatchHist.jobId) RIGHT JOIN (tbl_Commodity RIGHT JOIN join_tbl_DispatchHist_DispLoadUnload ON tbl_Commodity.commodityId = join_tbl_DispatchHist_DispLoadUnload.commodityId) ON (tbl_DispatchHist.loadHistId = join_tbl_DispatchHist_DispLoadUnload.loadHistId) AND (tbl_DispatchHist.jobId = join_tbl_DispatchHist_DispLoadUnload.jobId) AND (tbl_DispatchHist.loadId = join_tbl_DispatchHist_DispLoadUnload.loadId) AND (tbl_DispatchHist.dispatchId = join_tbl_DispatchHist_DispLoadUnload.dispatchId) AND (tbl_DispatchHist.dispatchHistId = join_tbl_DispatchHist_DispLoadUnload.dispatchHistId)) ON tbl_DispatchLoadUnload.dispatchLoadUnloadId = join_tbl_DispatchHist_DispLoadUnload.dispatchLoadUnloadId
GROUP BY join_tbl_DispatchHist_DispLoadUnload.jobId, join_tbl_DispatchHist_DispLoadUnload.loadId, join_tbl_DispatchHist_DispLoadUnload.loadHistId, join_tbl_DispatchHist_DispLoadUnload.dispatchId, join_tbl_DispatchHist_DispLoadUnload.dispatchHistId, join_tbl_DispatchHist_DispLoadUnload.commodityId, join_tbl_DispatchHist_DispLoadUnload.dispatchLoadUnloadId, tbl_DispatchLoadUnload.dispatchLoadDate, tbl_DispatchLoadUnload.dispatchLoadTime, tbl_DispatchLoadUnload.dispatchLoadLocation, tbl_DispatchLoadUnload.dispatchLoadBy, tbl_DispatchLoadUnload.dispatchLoadMethod, tbl_DispatchLoadUnload.loadLoadUnloadId, tbl_DispatchHist.dispatchHistLoadUnloadId, "L", tbl_Commodity.commodityName, tbl_Commodity.commodityActualLength, tbl_Commodity.commodityActualWidth, tbl_Commodity.commodityActualHeight, tbl_Commodity.commodityActualWeight, tbl_Job.jobMeasuredIsMetric, tbl_Commodity.commodityUnitNum, tbl_Commodity.commoditySerialNum
HAVING (((join_tbl_DispatchHist_DispLoadUnload.jobId) Is Not Null) AND ((join_tbl_DispatchHist_DispLoadUnload.loadId) Is Not Null) AND ((join_tbl_DispatchHist_DispLoadUnload.loadHistId) Is Not Null) AND ((join_tbl_DispatchHist_DispLoadUnload.dispatchId) Is Not Null) AND ((join_tbl_DispatchHist_DispLoadUnload.dispatchHistId) Is Not Null) AND ((join_tbl_DispatchHist_DispLoadUnload.commodityId) Is Not Null) AND ((join_tbl_DispatchHist_DispLoadUnload.dispatchLoadUnloadId) Is Not Null) AND ((tbl_DispatchHist.dispatchHistLoadUnloadId) Is Null));

Thank you

 

[traingamer]

There are multiple ways - see the faqs in this forum or search on 'record counter'.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[eatwork]

Hi trainGamer,
Thank you for the post. I have checked out the forums but have not been able to solve the problem I am having. Any Ideas on how I can fix this? thanks

The situation is this.
Data Showing:
jId lId lHistId dId dHistId commId dlUnId LUn Cnt
6012 01 01 01 01 2969 5124 L 2
6012 01 01 01 01 2969 5124 L 2
6012 01 01 01 01 3951 5125 L 4
6012 01 01 01 01 3951 5125 L 4
6012 01 01 02 01 2969 5124 L 2
6012 01 01 02 01 2969 5124 L 2
6012 01 01 02 01 3951 5125 L 4
6012 01 01 02 01 3951 5125 L 4

Data Wanted:
jId lId lHistId dId dHistId commId dlUnId LUn Cnt
6012 01 01 01 01 2969 5124 L 1
6012 01 01 01 01 2969 5124 L 2
6012 01 01 01 01 3951 5125 L 3
6012 01 01 01 01 3951 5125 L 4
6012 01 01 02 01 2969 5124 L 1
6012 01 01 02 01 2969 5124 L 2
6012 01 01 02 01 3951 5125 L 3
6012 01 01 02 01 3951 5125 L 4

My SQL From statement looks like:

FROM qry_rptDispatchLoadingUnloading1 LEFT JOIN qry_rptDispatchLoadingUnloading1 AS QRDLU ON (qry_rptDispatchLoadingUnloading1.dispatchHistId = QRDLU.dispatchHistId) AND (qry_rptDispatchLoadingUnloading1.dispatchId = QRDLU.dispatchId) AND (qry_rptDispatchLoadingUnloading1.loadHistId = QRDLU.loadHistId) AND (qry_rptDispatchLoadingUnloading1.loadId = QRDLU.loadId) AND (qry_rptDispatchLoadingUnloading1.jobId = QRDLU.jobId) AND ((qry_rptDispatchLoadingUnloading1.commodityId >= QRDLU.commodityId) AND ((qry_rptDispatchLoadingUnloading1.dispatchLoadUnloadId >= QRDLU.dispatchLoadUnloadId) OR (qry_rptDispatchLoadingUnloading1.dispatchLoadUnloadId <= QRDLU.dispatchLoadUnloadId)) AND ((qry_rptDispatchLoadingUnloading1.Loadunload >= QRDLU.Loadunload) OR (qry_rptDispatchLoadingUnloading1.Loadunload <= QRDLU.Loadunload)))

 

[eatwork]

Correction:
Data Showing and wanted should be:
Data Showing:
jId lId lHistId dId dHistId commId dlUnId LUn Cnt
6012 01 01 01 01 2969 5124 L 2
6012 01 01 01 01 2969 5124 U 2
6012 01 01 01 01 3951 5125 L 4
6012 01 01 01 01 3951 5125 U 4
6012 01 01 02 01 2969 5124 L 2
6012 01 01 02 01 2969 5124 U 2
6012 01 01 02 01 3951 5125 L 4
6012 01 01 02 01 3951 5125 U 4

Data Wanted:
jId lId lHistId dId dHistId commId dlUnId LUn Cnt
6012 01 01 01 01 2969 5124 L 1
6012 01 01 01 01 2969 5124 U 2
6012 01 01 01 01 3951 5125 L 3
6012 01 01 01 01 3951 5125 U 4
6012 01 01 02 01 2969 5124 L 1
6012 01 01 02 01 2969 5124 U 2
6012 01 01 02 01 3951 5125 L 3
6012 01 01 02 01 3951 5125 U 4

Basically when the commId, dlUnId and lUN combination changes the count needs to increase. Not sure why it isn't doing it. Help please and thank you

 

[eatwork]

Hi everyone,
if I change my From Statement to the code below, my results are close but does not increment the count properly. Any ideas on how to change my FROM to solve this issue? thank you.
Data Showing:
jId lId lHistId dId dHistId commId dlUnId LUn Cnt
6012 01 01 01 01 2969 5124 L 1
6012 01 01 01 01 2969 5124 U 2
6012 01 01 01 01 3951 5125 L 2
6012 01 01 01 01 3951 5125 U 4
6012 01 01 02 01 2969 5124 L 1
6012 01 01 02 01 2969 5124 U 2
6012 01 01 02 01 3951 5125 L 2
6012 01 01 02 01 3951 5125 U 4

FROM qry_rptDispatchLoadingUnloading1 LEFT JOIN qry_rptDispatchLoadingUnloading1 AS QRDLU ON (qry_rptDispatchLoadingUnloading1.dispatchHistId = QRDLU.dispatchHistId) AND (qry_rptDispatchLoadingUnloading1.dispatchId = QRDLU.dispatchId) AND (qry_rptDispatchLoadingUnloading1.loadHistId = QRDLU.loadHistId) AND (qry_rptDispatchLoadingUnloading1.loadId = QRDLU.loadId) AND (qry_rptDispatchLoadingUnloading1.jobId = QRDLU.jobId) AND ((qry_rptDispatchLoadingUnloading1.commodityId >= QRDLU.commodityId) AND (qry_rptDispatchLoadingUnloading1.dispatchLoadUnloadId >= QRDLU.dispatchLoadUnloadId) AND (qry_rptDispatchLoadingUnloading1.Loadunload >= QRDLU.Loadunload))

 

[PHV]

A starting point:
SELECT A.jobId AS jId, A.loadId AS lId, A.loadHistId AS lHistId, A.dispatchId AS dId, A.dispatchHistId AS dHistId, A.commodityId AS commId, A.dispatchLoadUnloadId AS dlUnId, A.Loadunload AS LUn, Count(*) AS Cnt
FROM qry_rptDispatchLoadingUnloading1 AS A INNER JOIN qry_rptDispatchLoadingUnloading1 AS B ON A.jobId = B.jobId AND A.loadId = B.loadId AND A.loadHistId = B.loadHistId AND A.dispatchId = B.dispatchId AND A.dispatchHistId = B.dispatchHistId
WHERE A.commodityId & A.dispatchLoadUnloadId & A.Loadunload >= B.commodityId & B.dispatchLoadUnloadId & B.Loadunload
GROUP BY A.jobId, A.loadId, A.loadHistId, A.dispatchId, A.dispatchHistId, A.commodityId, A.dispatchLoadUnloadId, A.Loadunload

If either commodityId or dispatchLoadUnloadId aren't always the same length you have to play with the Format function in the WHERE clause.

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

 

[eatwork]

Hi PHV,
That worked perfectly! Thank you for your help. I was wondering if you could maybe give a short reasoning so I can try to figure this out on my own in the future. Thank you again