[AccessD] Problem Making Query

[Rocky Smolin]

Gary:

Brilliant.  Took about 6 minutes to implement. Worked perfectly. Thank you
so much.

And thanks to all who rang in on this problem.  I don't have to lean on the
list very much these days, but when I do, it's a slam dunk.

Best,

Rocky
 

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Gary Kjos
Sent: Friday, December 11, 2015 3:00 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Problem Making Query

What I would do is make a totals sub query that joined the two tables and
grouped by the project ID and did a MIN on the status.

Then I would have two other queries that used that sub query as the input.

The Active version would have a critiera of "Active"

The Complete would have a criteria of "Complete"

If a project has any activities active it would be in the first.  If all the
activites are Complete status, the MIN would be Complete so it would show in
the second.

GK

On Fri, Dec 11, 2015 at 10:50 AM, Rocky Smolin <rockysmolin at bchacc.com>
wrote:
> 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/> 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



--
Gary Kjos
garykjos at gmail.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