[AccessD] Problem Making Query

Fri Dec 11 12:51:44 CST 2015

Charlotte:

No the active/complete is in the activities table - project-->activities is
one-->many with the ProjectNumberID as an FK in the activities table.

R

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

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]![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
>
--
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