[AccessD] Problem Making Query

[Charlotte Foust]

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