Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count Problem 1

Status
Not open for further replies.

eatwork

Technical User
May 16, 2005
155
CA
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:
Code:
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
 
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
 
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:
Code:
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)))
 
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
 
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

Code:
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))
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top