Counting Query Results 2

[Knape97]

Hi, hopefully someon can point out what I need to do to get this working as it has me pullin my hair out.

I have a query that has the following SQL;

SELECT Tbl_Staff.FirstName, Tbl_Staff.LastName, Tbl_Staff.Service, Tbl_Staff.Moving0405
FROM Tbl_Staff
WHERE (((Tbl_Staff.Moving0405) Is Null));


What I need is some was of that producing a count of the results and inserting that into a separate table, but I am at a loss as to how to perform the count.

Any help / advice would be appreciated.

Knape

 

[PHV]

I'm not sure to understand the issue ...
Something like this ?
INSERT INTO [separate table] (field1,field2,field3,field4)
SELECT FirstName, LastName, Service, Count(*) As CountOfNullMoving0405
FROM Tbl_Staff
WHERE Moving0405
GROUP BY FirstName, LastName, Service;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

INSERT INTO NewTable SET SomeField = (SELECT COUNT(*) FROM Tbl_Staff WHERE (((Tbl_Staff.Moving0405) Is Null)));

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Knape97]

Cheers guys for the responses.

To clarify what i need. I need it to scan the moving0405 field, and count the Null values. Then, say there were 5 Nulls it would enter "5" into the new table.

LesPaul's looks to be the one that would do that, but when I try to save the query i get the error,

"Syntax error in INSERT INTO Statement" and it highlights "SET" in the SQL.

Sorry to be a pain, SQL really isnt my forte

 

[PHV]

INSERT INTO [new table] ([count field])
SELECT Count(*) As CountOfNullMoving0405
FROM Tbl_Staff
WHERE Moving0405 Is Null


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

my bad, SET in updates, not INSERTS!!!

PHV's latest should do the trick!

 

[Knape97]

Excellent; Thanks guys, that works a treat.

Many thanks for your help

 

[Knape97]

Got a related question to this now. In a separate query I need it to Count where the StaffActive field is Null from Tbl_Staff. However, I need this to update to InactiveStaff in Tbl_Stats where StatID=1

Had a little play around with PHV's append query, but i coudnt get it to work as a modify query

Basically I need help converting...

INSERT INTO [Tbl_Stats] ([InactiveStaff])
SELECT Count(*) As CountOfNullStaffActive
FROM Tbl_Staff
WHERE StaffActive Is Null

Into a modify query.

Once again, chears for all your help.

 

[PHV]

Please post the sql code of your Update query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Knape97]

This is what I have so far, however it produces a "Opperation must use and updatable query" error

UPDATE Tbl_Stats_Temp SET Tbl_Stats_Temp.Moving0405Null = (SELECT COUNT(*) FROM Tbl_Staff WHERE (((Tbl_Staff.Moving0405) Is Null)))
WHERE (((Tbl_Stats_Temp.StatID)=1));

 

[PHV]

And this ?
UPDATE Tbl_Stats_Temp
SET Moving0405Null = DCount("*", "Tbl_Staff", "Moving0405 Is Null")
WHERE StatID=1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Knape97]

Once again PHV you have excelled yourself. Thank you, it works a treat.