[AccessD] Problem Making Query

[Charlotte Foust]

So does that table, tblMarketingActivitiesProjectNumber, contain the
records that are complete or active or is that in a related table?

Charlotte

Charlotte Foust
(916) 206-4336

On Fri, Dec 11, 2015 at 10:40 AM, 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
>