[AccessD] Problem Making Query

Fri Dec 11 16:27:46 CST 2015

Paul:

I tried that but it didn't work.  I think it needs a join from the project
table to the activities table, no?

Rocky

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

Rocky,

Haven't really much time, but if you can modify this, this will give you the
project id and A or C, C being only if all complete..I made two tables
tblProject and tblProjectActivities this query just uses the project
activites table but can link back to tblProject by the ID.....

SELECT tblProjectActivities.ProjectID,

iif(Count(tblProjectActivities.ProjectID)=Sum(IIf([ProjectStatus]="C",1,0)),
"C", "A") AS ActiveComplete
FROM tblProjectActivities
GROUP BY tblProjectActivities.ProjectID;

On 11 December 2015 at 18:40, Rocky Smolin <rockysmolin at bchacc.com> wrote:

> That's the problem.  I don't have any SQL for the active and complete.  
> I tried but could not get either one to work.
>
> The SQL for the case for both active and complete is trivial:
>
> SELECT DISTINCT 
> tblMarketingActivitiesProjectNumber.fldMAProjectNumberID,
> tblMarketingActivitiesProjectNumber.fldMAProjectNumber
> FROM tblMarketingActivitiesProjectNumber
> ORDER BY tblMarketingActivitiesProjectNumber.fldMAProjectNumber;
>
> R
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of Charlotte Foust
> Sent: Friday, December 11, 2015 10:24 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Problem Making Query
>
> Show us the SQL, Rocky.  Otherwise, we're just guessing.
>
> Charlotte
>
> Charlotte Foust
> (916) 206-4336
>
> On Fri, Dec 11, 2015 at 10:18 AM, Rocky Smolin 
> <rockysmolin at bchacc.com>
> wrote:
>
> > The query when both boxes are checked was easy - just a select - 
> > unique values=True.
> >
> > The problem came when I tried to make a list of projects where ALL 
> > activities in a project are Complete  and another one which would 
> > make a list of projects where ANY activity in the project was Active.
> >
> > R
> >
> >
> > -----Original Message-----
> > From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On 
> > Behalf Of David McAfee
> > Sent: Friday, December 11, 2015 10:16 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Problem Making Query
> >
> > if you can't get the where clause working, you can make a 3rd query, 
> > which shows all, and use that querywhen both checkboxes are checked.
> >
> > On Fri, Dec 11, 2015 at 9:59 AM, David McAfee 
> > <davidmcafee at gmail.com>
> > wrote:
> >
> > > Wow, I didn't even press send! WTF!
> > >
> > >
> > > I have a form where I used a list box with 3 optional boxes in a
frame.
> > > Active, Complete and Both are the options (0, -1 & 2 are the 
> > > values respectively).
> > >
> > > The query has a Boolean ID field that it looks at:
> > >
> > > IIf([forms]![frmDatabaseMapping]![fraShowProjects]=2,-1,[forms]![f
> > > rm
> > > Da
> > > tabaseMapping]![fraShowProjects])
> > > or
> > >
> > > IIf([forms]![frmDatabaseMapping]![fraShowProjects]=2,0,[forms]![fr
> > > mD
> > > at
> > > abaseMapping]![fraShowProjects])
> > >
> > >
> > >
> > > If the client doesn't want a framed optional box, you can have a 
> > > hidden text box with a value.
> > >  In the after_update event of each check box, call a sub named
> > CalcStatus:
> > > (warning all air code)
> > >
> > > Private Sub CalcStatus
> > >    me.txtShowStatus = abs(nz(me.chkActive,0)) +
> > > abs(nz(me.chkComplete,0)) end sub
> > >
> > > Then in your query
> > > switch([forms]![frmMyForm]![txtShowStatus ] = 1,"COMPLETE", 
> > > [forms]![frmMyForm]![txtShowStatus ] = 2, "Active", 
> > > [forms]![frmMyForm]![txtShowStatus ] =3,"*")
> > >
> > >
> > >
> > > On Fri, Dec 11, 2015 at 8: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
> > >>
> > >
> > >
> > >
> > --
> > 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

--
Paul Hartland
paul.hartland at googlemail.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