[AccessD] Problem Making Query

Fri Dec 11 12:46:04 CST 2015

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]![frm
> > > Da
> > > tabaseMapping]![fraShowProjects])
> > > or
> > >
> > > IIf([forms]![frmDatabaseMapping]![fraShowProjects]=2,0,[forms]![frmD
> > > 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