[AccessD] Problem Making Query OT

Rocky Smolin rockysmolin at bchacc.com
Sat Dec 12 08:22:36 CST 2015


Thanks Martin.  As we discussed earlier on your birthday, we're really old
now. :)

Would like to celebrate one of these with you and fam some year.  We keep
talking about coming over to do Scotland and Ireland - maybe this spring.
We'll see.  Could be a busy spring though as we sold the house and have to
find a new one and move.  

Noah comes home from school tonight for three weeks - that'll be nice . Max
comes home from school next week for a couple weeks as well - he's up in the
Bay area getting his doctorate in psych. So that'll be nice as well. Have
the whole family back in the house for a few days. :)

r

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Martin Reid
Sent: Saturday, December 12, 2015 6:09 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Problem Making Query OT

Happy birthday Rocky

Martin
________________________________
From: Rocky Smolin<mailto:rockysmolin at bchacc.com>
Sent: ‎11/‎12/‎2015 22:23
To: 'Access Developers discussion and problem
solving'<mailto:accessd at databaseadvisors.com>
Subject: Re: [AccessD] Problem Making Query

Lambert:

Close.  The active query works and returns the right projects.  But the
Complete one does not - it returns no records although I know there are
three projects that are complete.

If I take the zero out of the criteria line it returns the same list as the
active  query.

Thanks

Rocky

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Heenan, Lambert
Sent: Friday, December 11, 2015 11:31 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Problem Making Query

Rocky,

Active Projects:

SELECT  Projects.[Project ID]
FROM Projects INNER JOIN [Project Activities] ON Projects.[Project ID] =
[Project Activities].[Project ID] WHERE ((([Project
Activities].STATUS)='ACTIVE')) GROUP BY Projects.[Project ID] HAVING
(((Count([Project Activities].STATUS))>0));

Completed Projects:

SELECT  Projects.[Project ID]
FROM Projects INNER JOIN [Project Activities] ON Projects.[Project ID] =
[Project Activities].[Project ID] WHERE ((([Project
Activities].STATUS)='ACTIVE')) GROUP BY Projects.[Project ID] HAVING
(((Count([Project Activities].STATUS))=0));

Plug in the correct table and field names and that should do it, I think.
The only difference between the two is STATUS))>0 vs STATUS))=0.


Lambert

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Rocky Smolin
Sent: Friday, December 11, 2015 11:50 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Problem Making Query

Dear List:

I need some help structuring 2 queries and can't seem to figure how to do
it.

I have two tables: Projects and Project Activities, where Project Activities
has the FK of Project ID - PK in the project table.

Activities table has a status field 'ACTIVE' or 'COMPLETE'

The client wants to add checkboxes on the calling form - one labeled
'Active', one  labeled 'Complete', so that the combo box of Projects on the
called form will show only Active projects, only Complete projects or both.

Showing both is easy - I already had that before he thought up this dandy
enhancement

A project is Active if ANY project activity is status = ACTIVE.

A project is complete if ALL activities are status = COMPLETE.

So I'm trying to create two queries - one which would list active projects
and one which would list complete project per the definition above then I'll
change the row source of the combo box based on which boxes are checked.

But I've been fooling around with it long enough without success that I
think I need some help.

(help!)

MTIA,

Rocky Smolin
Beach Access Software
858-259-4334
www.bchacc.com<http://www.bchacc.com> <http://www.bchacc.com/>
www.e-z-mrp.com<http://www.e-z-mrp.com>
<http://www.e-z-mrp.com/>
Skype: rocky.smolin


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus




More information about the AccessD mailing list