Need help to create a query with a Row number
Posted on 2014-09-17
I need a some help with the following query.
I was trying to use the DCount at the start of the query to count how many entries each day will have so I can then use the count in a cross tab query that will end up looking something like the following. Some days may have multiple entries, others may have one or none. Trouble is I can't use DCount because I can't get the unique values I need from just tblJobDetails because one of the criteria I need is in a linked table. The only way I can think of is to write a function that will give me the progressive count and use that in place of the DCount. However that will use a lot of overhead so before I do it I thought I would see if there is a more professional approach.
Seq Mon Tue Wed ...
1 a b c
2 d e
SELECT DisplaySeq: DCount("Activity","tblJobDetails","[DatePlanned] is null AND IncludeInWeekView = -1 AND [DateRequested] = " & CLng([DateRequested])) AS DisplaySeq,
"##" AS SupplierIde, "TBA" AS Supplier, tblJobs.Jobnum, tblJobDetails.daterequested, Abbrev([LinkedCustomer],[tblJobDetails].[Activity],[JobName],[Supervisor],[DetailId],[fkActivityID],[fkJobNum]) AS Details, tblActivities.IncludeInWeekView, tblJobDetails.DateRequested, tblJobDetails.DatePlanned
FROM (tblJobs LEFT JOIN tblSupervisors ON tblJobs.fkSupervisorId = tblSupervisors.id) RIGHT JOIN (tblSuppliers RIGHT JOIN (tblJobDetails LEFT JOIN tblActivities ON tblJobDetails.fkActivityId = tblActivities.Id) ON tblSuppliers.SupplierId = tblJobDetails.fkSupplierId) ON tblJobs.Jobnum = tblJobDetails.fkJobNum
WHERE (((tblActivities.IncludeInWeekView)=True) AND ((tblJobDetails.DateRequested) Between Date() And Date()+6) AND ((tblJobDetails.DatePlanned) Is Null))
ORDER BY tblJobDetails.DateRequested;