473,385 Members | 1,942 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

Use count twice in query

I have an attendance table that list the status of each student on a particular day.
e.g. P - present A for absent and L for Late.

I want to run a query where I count both the number of presents and the number of absents in the same query. However I cant figure out how to use count twice in the query. I can only use it once for present or absent.

Any suggestions from anyone

Thanks in advance
Oct 28 '07 #1
4 1893
missinglinq
3,532 Expert 2GB
Why not simply use the DCount() function twice against your query?

Welcome to TheScripts!

Linq ;0)>
Oct 29 '07 #2
NeoPa
32,556 Expert Mod 16PB
Try :
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(IIf([YourField]='A',1,Null)) AS Absent,
  2.        Count(IIf([YourField]='L',1,Null)) AS Late,
  3.        Count(IIf([YourField]='P',1,Null)) AS Present
  4. FROM [YourTable]
  5. GROUP BY [Employee]
It needs the table and field names changing obviously, but it should work more efficiently than multiple DCount()s (or multiple calls of any Domain Aggregate functions).
Oct 29 '07 #3
Thanks

I am going to try both methods. and let you guys know the results
Oct 29 '07 #4
NeoPa
32,556 Expert Mod 16PB
Yes please. That would be great :)
Oct 29 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Xah Lee | last post by:
if i have mytext.replace(a,b) how to find out many many occurances has been replaced? Xah xah@xahlee.org ∑ http://xahlee.org/
2
by: Paxton | last post by:
Hi, I'm trying to display the total page views per page within a given date range, but the correct SQL is seemingly beyond me. I get the correct result with a straightforward Group By and Count...
1
by: G | last post by:
I need to be able to count accounts as they are entered by operators. Each account number has fields such as "Statements req date" and "Application req date". I would like the database to count...
2
by: Chris | last post by:
Hello all, I'm having some trouble setting up a query. Background: The table TBLSCREEN stores data about screenings of patients/subjects for eligibility to participate in a health study. A...
17
by: keith | last post by:
I am trying to get a exact count of different distinct entries in an Access column. At first, I was trying to work with three columns, but I've narrowed it down to one to simplify it. I've searched...
6
by: dBNovice | last post by:
Hey group, I am trying to do a count of the number of papers in a table. The table has a PaperID that differentiates each paper , e.g. 004.1. Some papers are reused. The reused paper is given...
1
by: Propoflady | last post by:
I have a form where I enter each time someone looks at a horse or our farm. When I print the report it looks like Date Person a Person b Person b Person c Person b has looked at the horse...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
2
by: magmike | last post by:
I need to display the count of records there are for each unique value in a table. In this case, it is by date - how many calls are scheduled for each date. Each call is a record in a table. So the...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.