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:
Thank you
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));