How do I add a sequential count on my query? 1

[vzjfgm]

I have a query that envolves 3 tables. The resulting make table need to have a field with a sequential count. Please advise!!! Thank you

 

[cmmrfrds]

Put an AutoNumber field in the table you are making.

 

[vzjfgm]

I already tried that. I get an error message
Undefined function in expression.

 

[cmmrfrds]

Can you paste in your SQL.

 

[dhookom]

In addition to cmmrfrds request, please provide which fields (if any) in your SQL are autonumbers.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[vzjfgm]

SELECT Tbl_Employees.Last, Tbl_Cumm_OT_Hrs.[Total Cumm OT Hrs], autonumber() AS Emp_count, Tbl_Employees.Dept, Tbl_Employees.Group, Tbl_Employees.Shift, Tbl_Employees.Area, Tbl_Employees.Clock, Tbl_weekly_Manpower.Mon_E_Sch, Tbl_weekly_Manpower.Mon_O_Sch, Tbl_weekly_Manpower.Wk_End_dt INTO tbl_Wk_emp
FROM (Tbl_Employees INNER JOIN Tbl_weekly_Manpower ON Tbl_Employees.Clock = Tbl_weekly_Manpower.empclock) LEFT JOIN Tbl_Cumm_OT_Hrs ON Tbl_Employees.Clock = Tbl_Cumm_OT_Hrs.clock
ORDER BY Tbl_Cumm_OT_Hrs.[Total Cumm OT Hrs] DESC;

Thanks for any help!!!

 

[dhookom]

Is there a reason you want to do this in a query rather than in a report where this would be easy?
How do you want to handle records with the same number of TotalCummOTHrs?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[vzjfgm]

I use this make table in a datasheet on a form. It does not matter and not likely if records have the same TotalCumOThrs. Thanks again!!!

 

[dhookom]

Change the query into a select query and save it as qselA. Then create a query based on qselA:

SELECT *,
(SELECT Count(*)
FROM qselA A
WHERE A.[Total Cumm OT Hrs] >=qselA.[Total Cumm OT Hrs])
As EmpCount
INTO tbl_Wk_emp
FROM qselA
ORDER BY [Total Cumm OT Hrs] DESC;

BTW: I would avoid using Last as a column/field name since Last has a specific meaning in SQL. I also would never place spaces in object (table, field, form,...) names.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[vzjfgm]

That's exactly what I needed. Thank you for your help!!!