Counting in a query

[Sam Summers]

Hi,

Ultimately i am going to display a percentage utilization of different groups of personnel in a report by using 2 textboxes, 1 with the count of the whole group and the 2nd with the count of those who are working.
I will have a third Textbox which will give me the % utilization.

I have this query which gives me the total of the group and is working fine:

SELECT DISTINCTROW Employee.National, Count(*)
AS [Count Of Employee]
FROM Employee
GROUP BY Employee.National
HAVING (((Employee.National)=No));
_____________________________________________________________

But i have this one that i want to display only those from the group who are working? But instead of 1 record displaying the total i get 4 records each displaying 1?

SELECT DISTINCTROW Employee.National, Count(*)
AS [Count Of Employee]
FROM Employee

GROUP BY
Employee.National,
Employee.EmployeeID,
Employee.FirstName,
Employee.Surname,
Employee.MobDate,
Employee.DeMobDate

HAVING (((Employee.National)=No)
AND ((Employee.MobDate) Between Date() And Date()-7))
OR (((Employee.National)=No)
AND ((Employee.DeMobDate) Between Date() And Date()-7));
_________________________________________________________

My guess is that it is due to the final 4 lines (HAVING, AND, OR, AND) but how do i just display 4 (i.e the Count)?

Many thanks in advance

Sam

 

[pr2-eugin]

How about using the Query as a SubQuery.

[COLOR=Red][B]SELECT Count(*) As FinalCount 
FROM ([/B][/COLOR]SELECT DISTINCTROW Employee.National, Count(*)
AS [Count Of Employee]
FROM Employee

GROUP BY
Employee.National,
Employee.EmployeeID,
Employee.FirstName,
Employee.Surname,
Employee.MobDate,
Employee.DeMobDate

HAVING (((Employee.National)=No)
AND ((Employee.MobDate) Between Date() And Date()-7))
OR (((Employee.National)=No)
AND ((Employee.DeMobDate) Between Date() And Date()-7))[COLOR=Red][B])[/B][/COLOR];

 

[plog]

I think having DISTINCTROW in the SELECT clause and also having a GROUP BY clause is unkosher--just don't seem like things that should be mixed in a query. I can't parse my brain around exactly what it is you are trying to achieve when you put those both in.

So, can you provide sample data from Employee and then what you want as a result based on that sample data?

My gut is telling me it has to do with those 5 fields in the GROUP BY clause not in the SELECT clause, but again, that DISTINCTROW is screwing my brain up.

 

[Sam Summers]

Thank you both for your reply.

Basically the code that Paul Eugin placed here worked!

Thank you

 

[pr2-eugin]

I am glad its working !

But what plog is questioning is why you have it that way ! After reading the comments, I tend to agree with you. Normally GROUP BY will eliminate the multiple values, and result in a distinct row. Putting Distinct Row on top of that does not make much sense. Is there something that we are missing?

 

[Sam Summers]

I'm not sure what you mean, being a relative beginner with queries at this level?

I do have another problem with this though:-

So, i now have this:

________________________________________________________________

SELECT Count(*) AS FinalCount
FROM (SELECT DISTINCTROW Employee.National, Count(*)
AS [Count Of Employee]
FROM Employee

GROUP BY
Employee.National,
Employee.EmployeeID,
Employee.FirstName,
Employee.Surname,
Employee.MobDate,
Employee.DeMobDate

HAVING (((Employee.National)=No)
AND ((Employee.MobDate) Between Date() And Date()-7))
OR (((Employee.National)=No)
AND ((Employee.DeMobDate) Between Date() And Date()-7))) AS [%$##@_Alias];

______________________________________________________________________

But when i come to place it in the report via a textbox using the builder it doesnt work so i then tried to create a subreport based on the above but i dont have a field to link it to the main report. So i need to add the table 'Job' and then the field 'Type' in order to create the link?

 

[Sam Summers]

I shall explain what i am trying to do?
I have to select from the 'Employee' Table only Expatriate workers (i.e. Not 'Nationals) who are currently working hence the Date part of it and insert the sum of this into a textbox in a report and then i will select the Total of Expats and insert the Total of this into a second textbox in the report and then in a third textbox i will calculate the % of utilized personnel for the week.

 

[plog]

To place this on a report I would use a simple DCount (http://www.techonthenet.com/access/functions/domain/dcount.php). You would set the control of the source of the textbox to this:

=DCount("[FinalCount]", "NameOfTheQueryHere")

Replace NameOfTheQueryHere with the actual name you gave the query.

However, I'm fairly certain while this does exactly what you asked for, this isn't exactly what you want. If I'm right, instead of your next question, just post sample starting data (including field and table names) and then what the end result should be based on that sample data.

 

[Sam Summers]

Thank you for all your efforts

I tried the above and while the query itself returns the final count of 4, in the TextBox in the report it displays 1?

The report i have is dynamic in that it displays data for each department on demand so i need to insert a supreport with the DCount in it that is linked via the 'Type' field in the 'Job' table?

I have tried to upload items but whichever way i try, the files are too big!

 

[namliam]

If you do AND the select COUNT(*) query which returns 4 and do DCOUNT on that query... then you get 1 row

Do one other the other, not both.

 

[Sam Summers]

I have also just tried to convert this Query to the FinalCount version without success?

SELECT DISTINCTROW Employee.National, Count(*)
AS [Count Of Employee]
FROM Employee
GROUP BY Employee.National
HAVING (((Employee.National)=No));

 

[namliam]

You would either do your query, with or without the distinct:

SELECT Employee.National, Count(*)
AS [Count Of Employee]
FROM Employee
GROUP BY Employee.National
HAVING (((Employee.National)=No));

or do a DCount:

DCount("*", "[Employee]", "[National]=No")

One other the other, not both.

 

[Sam Summers]

But then how do i incorporate the date part?

AND ((Employee.MobDate) Between Date() And Date()-7))
OR (((Employee.National)=No)
AND ((Employee.DeMobDate) Between Date() And Date()-7)))

 

[namliam]

I have seen a very simular question someplace, I will try and keep the answer simular:
How can you expect an answer from something like:
Between 10 and 10 - 7
or
Between 10 and 3
or, written differently
Field >= 10 and Field <= 3

 

[Sam Summers]

I got the expression Between Date() and Date()-6 from Microsofts website

http://office.microsoft.com/en-001/...s-criteria-in-access-queries-HA102809751.aspx

If this wont work then i have to scrap the Database?

I need to count only records from the week?

 

[namliam]

Seems microsoft fixed their "Between" statement to understand this strange construction somewhere along the line....
The "proper" syntax normally should be
Between SMALLEST_VALUE and LARGEST_VALUE
Thus
Between Date() - 7 and Date()

This leaves only your other clause to be the issue, you probably have a Yes/No field as your national?
Try this to start with:

SELECT Employee.National, Count(*)
AS [Count Of Employee]
FROM Employee
Where MobDate Between Date() - 7 and Date()
or DeMobDate Between Date() - 7 and Date()
GROUP BY Employee.National
;

 

[Sam Summers]

Namliam i think that is it?!
Brilliant, thank you SO much.

You are right that 'National' is a Yes/No checkbox.

So if i want to display only non nationals i use 'Nationals = No' ?

I'll play about and check that it is working fine but i think i am nearly there as this particular bit was the icing on the cake so to speak.

 

[namliam]

As National now is confirmed as a Yes/No field, the syntax as shown earlier should work:

SELECT Employee.National, Count(*)
AS [Count Of Employee]
FROM Employee
GROUP BY Employee.National
HAVING Employee.National=No
[B][I]OR Employee.National is null[/I][/B];

However try it with the addition of NULL values which may be in there....
The total of the query should look something like:

SELECT Employee.National, Count(*)AS [Count Of Employee]
FROM Employee
Where ( MobDate Between Date() - 7 and Date()
     or DeMobDate Between Date() - 7 and Date() ) 
  and Employee.National = No
GROUP BY Employee.National