Hi,
I am trying to get count of records in a report from a query where [TYPE of work] is "PW" and [Status] is "CLOSED"
I am able to count the Status CLOSED but not with the criteria of type of work.
Please advise with the expression.
Regards
Hi,
I am trying to get count of records in a report from a query where [TYPE of work] is "PW" and [Status] is "CLOSED"
I am able to count the Status CLOSED but not with the criteria of type of work.
Please advise with the expression.
Regards
Send a copy of the query, "convert it to sql" and send that. then we can see what your doing and go from there.
Count is a totals option for queries, how you are doing may have a bearing on what is going on, but from the simple description it should work.
Thanks Here is the copy from sql view of the query I am using.
SELECT [CEP Register].Area, [CEP Register].[Type of Work], [CEP Register].[Tag Number], [CEP Register].[Section of Works], [CEP Register].[CEP Number], [CEP Register].[CEP Name], [CEP Register].[Area Project Engineer], [CEP Register].[Area Construction Manager], [CEP Register].[Construction Completion Date], [CEP Register].[Construction Complete], [CEP Register].[Joint Inspection], [CEP Register].[Construction Area Manager and CPS Review], [CEP Register].[Const Team and Quality Manager Joint Sign Off], [CEP Register].[Final Review & Sign], [CEP Register].[As-Builts - FInal], [CEP Register].[Site Engineer], [CEP Register].[Due Date for CEP Handover], [CEP Register].[Close Out Meeting Held], [CEP Register].[CEP Currently With (Person Name)], [CEP Register].[CEP Closed Out Date], [CEP Register].[CEP Final Status], [CEP Register].Comments, [CEP Register].[Step #], [CEP Register].[Added to Snag List], [CEP Register].[Number of Files Per CEP], [CEP Register].[CEP As Built Hyperlink]
FROM [CEP Register];
What you have there is a basic select sql, it gets the fields you place in the grid, if you want all records, to make it easier to read try -
SELECT [CEP Register].* FROM [CEP Register];
This puts all records from that table into the report, you then have to do the calculations in the report based on the data supplied.Or you could use a totals query for both counts and place the results in the report also. See report totals in access help for that
THE BELOW CODE ONLY SELECTS RECORDS AS REQUESTED, CODE IN RED ADDED TO YOUR QUERY
SELECT [CEP Register].Area, [CEP Register].[Type of Work], [CEP Register].[Tag Number], [CEP Register].[Section of Works], [CEP Register].[CEP Number], [CEP Register].[CEP Name], [CEP Register].[Area Project Engineer], [CEP Register].[Area Construction Manager], [CEP Register].[Construction Completion Date], [CEP Register].[Construction Complete], [CEP Register].[Joint Inspection], [CEP Register].[Construction Area Manager and CPS Review], [CEP Register].[Const Team and Quality Manager Joint Sign Off], [CEP Register].[Final Review & Sign], [CEP Register].[As-Builts - FInal], [CEP Register].[Site Engineer], [CEP Register].[Due Date for CEP Handover], [CEP Register].[Close Out Meeting Held], [CEP Register].[CEP Currently With (Person Name)], [CEP Register].[CEP Closed Out Date], [CEP Register].[CEP Final Status], [CEP Register].Comments, [CEP Register].[Step #], [CEP Register].[Added to Snag List], [CEP Register].[Number of Files Per CEP], [CEP Register].[CEP As Built Hyperlink]
FROM [CEP Register]
WHERE ((([CEP Register].[Type of Work])="pw") AND (([CEP Register].[CEP Final Status])="CLOSED"));