[AccessD] Problem Making Query

[Rocky Smolin]

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