More Select in a query

  • Thread starter Thread starter miyomo
  • Start date Start date
M

miyomo

Hi!
I Have a table like this

DateActivity Activity
01/01/2000 OFFDUTY

I would like to have a query to return
1) SundayOnDuty(Activitiy<>'OFFDUTY')
2) SundayOffDuty(Activitiy='OFFDUTY')
3) Total Sunday
between 2 date.

Is it possible to do it in a query or I have to run the
query 3 times? (Flights.Activity Like 'OFF*') / (Flights.Activity NOT LIKE
'OFF*') / (WEEKDAY(Flights.ActivityDate )=1);

SELECT COUNT(*) AS SundayOnDuty
FROM Flights
WHERE (Flights.Activity Like 'OFF*') And (WEEKDAY(Flights.ActivityDate )=1)
And (Flights.ActivityDate BETWEEN #Date1#,#Date2#);

Thanks
Miyomo
 
At first glance, I'd wager that you wouldn't be able to do this in just one
query, unless you use a subquery. (I don't typically use subqueries, as
they're more difficult to debug.)

Try breaking the data gathering into small pieces, then join all the data
together as needed.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

miyomo said:
Hi!
I Have a table like this

DateActivity Activity
01/01/2000 OFFDUTY

I would like to have a query to return
1) SundayOnDuty(Activitiy<>'OFFDUTY')
2) SundayOffDuty(Activitiy='OFFDUTY')
3) Total Sunday
between 2 date.

Is it possible to do it in a query or I have to run the
query 3 times? (Flights.Activity Like 'OFF*') / (Flights.Activity NOT LIKE
'OFF*') / (WEEKDAY(Flights.ActivityDate )=1);

SELECT COUNT(*) AS SundayOnDuty
FROM Flights
WHERE (Flights.Activity Like 'OFF*') And
(WEEKDAY(Flights.ActivityDate )=1)
 
miyomo said:
Hi!
I Have a table like this

DateActivity Activity
01/01/2000 OFFDUTY

I would like to have a query to return
1) SundayOnDuty(Activitiy<>'OFFDUTY')
2) SundayOffDuty(Activitiy='OFFDUTY')
3) Total Sunday
between 2 date.

Is it possible to do it in a query or I have to run the
query 3 times? (Flights.Activity Like 'OFF*') / (Flights.Activity NOT LIKE
'OFF*') / (WEEKDAY(Flights.ActivityDate )=1);

SELECT COUNT(*) AS SundayOnDuty
FROM Flights
WHERE (Flights.Activity Like 'OFF*') And (WEEKDAY(Flights.ActivityDate )=1)
And (Flights.ActivityDate BETWEEN #Date1#,#Date2#);


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could use the IIf() function to break out Off/On duty counts:

PARAMETERS BeginDate Date, EndDate Date;
SELECT COUNT(IIf(Activity = "OFFDUTY", 1) AS OffDutyCount,
COUNT(IIf(Activity <> "OFFDUTY", 1) AS OnDutyCount
FROM Flights
WHERE WEEKDAY(ActivityDate) = 1
AND ActivityDate BETWEEN BeginDate And EndDate

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFY2T4echKqOuFEgEQLcVwCgscmh/FAyrm2+e/fcax8BQthnuR0AoPF1
28yCWRAWU7ZjWU9ksZ0G966z
=UPjS
-----END PGP SIGNATURE-----
 
Back
Top