[AccessD] Problem Making Query

[Stuart McLachlan]

Change the INNER JOIN to a LEFT JOIN in the Complete query.
And you can safely remove the HAVING clause in the Active query

With an inner join, the WHERE clause  restricts the result set to only active projects
.So the HAVING clause is unnecessary in the first query and won't apply in the second one. 

With a LEFT JOIN, you will return all projects and the HAVING clause will apply as desired.


On 11 Dec 2015 at 14:20, Rocky Smolin wrote:

> 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/> 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
>