[AccessD] Need help with a query

Thu Apr 2 10:57:24 CDT 2015

Ooops - sorry - wrong address.

r 

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Rocky Smolin
Sent: Thursday, April 02, 2015 8:54 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Need help with a query

Eric:

To delete a record from a list, uncheck the Selected box.

R

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Rocky Smolin
Sent: Thursday, April 02, 2015 8:47 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Need help with a query

Your assumptions are correct.  I think that will work!  Thank you.

R

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
McGillivray, Don
Sent: Thursday, April 02, 2015 8:34 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Need help with a query

Assuming that your form is bound to the temp table, fldMailListTempSelected
is bound to a column in that table,  and the user is selecting the
fldMailListTempSelected flag(s) to indicate those records to include, how
about something like (air code):

INSERT INTO tblMailListPersons (column1, column2, column3, . . .) SELECT
column1, column2, column3, ...
FROM tblMailListTemp
WHERE fldMailListTempSelected = TRUE

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Rocky Smolin
Sent: Thursday, April 02, 2015 7:54 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Need help with a query

How about a follow up question? :)  Just trying to up my game, here.

I'm using your Not In approach to create the list in the temp table:

            db.Execute "INSERT INTO tblMailListTemp ( fldPersonsID,
fldMailListTempPersonsFirstName, " _
                & "fldMailListTempPersonsLastName, fldMailListTempCompany )
" _
                & "SELECT tblPersons.fldPersonsID,
tblPersons.fldPersonsFirstName, " _
                & "tblPersons.fldPersonsLastName,  " _
                & "tblCompanies.fldCompanyName FROM tblCompanies RIGHT JOIN
tblPersons ON " _
                & "tblCompanies.fldCompanyID = tblPersons.fldCompanyID WHERE
(" _
                & "((tblPersons.fldPersonsID) Not In (Select fldPersonsID
from " _
                & "tblMailListPersons  Where
tblMailListPersons.fldMailListID =     " _
                & Val(Me.cboMailList.Column(0)) & " )));"

Works a treat.  Now, when the user selects one of those records to be
included in the selected mail list I'm using DAO to add it and that's works
just fine. 

    If Me.fldMailListTempSelected = True Then
    ' add to list
        Set rsMLP = db.OpenRecordset("Select * FROM tblMailListPersons ")
        rsMLP.AddNew
        rsMLP!fldPersonsID = Me.fldPersonsID
        rsMLP!fldMailListID = Val(Me.cboMailList.Column(0))
        rsMLP.Update
        rsMLP.Close
        Set rsMLP = Nothing
    Else
.
.
.

So, just trying to up my game here I tried to do this append with a SQL
statement but couldn't figure out a way.  I played with it in the QBE grid
but no cigar.  Is there a simple way to insert a record using SQL when you
have the values in hand (in this case the selected Mail List and the
fldPersonsID, the selected Person's PK).

MTIA

Rocky Smolin
Beach Access Software
858-259-4334
www.bchacc.com
www.e-z-mrp.com
Skype: rocky.smolin

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Heenan, Lambert
Sent: Wednesday, April 01, 2015 12:09 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Need help with a query

My pleasure. :-)

Not In(select foo from bar) 

or

In(select foo from bar)

Is a very handy tool in the SQL kit.

Lambert

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Rocky Smolin
Sent: Wednesday, April 01, 2015 2:47 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Need help with a query

Not In?   Wow.  Who knew?  (well you, of course). :) After 20 years you'd
think I'd know pretty much the whole language.

I'll give that a try.  Thanks Lambert

R

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Heenan, Lambert
Sent: Wednesday, April 01, 2015 10:32 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Need help with a query

You don't need any joins, just a query on tblMailingListPersons with a sub
query on the user ID. Like this

SELECT tblPersons.ID, tblPersons.STREMAIL, tblPersons.STRFIRSTNAME,
tblPersons.STRLASTNAME FROM tblPersons WHERE (((tblPersons.ID) Not In
(select PersonsID from tblMailingListPersons  where
tblMailingListPersons.MailListID=1 )));

(made some guesses about field names). So all that's need is to replace the
where "tblMailingListPersons.MailListID=1" with a reference to the form
where the mail list is chosen.

Lambert

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Rocky Smolin
Sent: Wednesday, April 01, 2015 12:08 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Need help with a query

Dear List:

I'm trying to make a query do something I would usually just do with a bit
of code and am having some difficulty.

I have
1) a table tblMailLists (PK, list name),
2) a table tblPersons (PK & Persons Name) and
3) a connecting table tblMailingListPersons (MailListID FK and Persons ID
FK. Pretty standard stuff

So people can be assigned to one or more mailing lists.

I need to assemble into a temp table and display in a form all the people
not already on a specific mailing list so that the user can assign them to
the selected mailing list.

So I connected tblPersons with tblMailingListPersons - all records from
tblPersons and only those records in tblMailingListPersons - and used the
criteria:

MailListID <> (user's selected list) OR Is Null.  

This gives me all the people not assigned to the selected list. But if a
person is assigned to another list, of course they show up on this list as
well (Person A is assigned to lists 1 and 2 - so the criteria filter out
anyone who's on List 1 but passes through someone on List 2).  

I can't figure out how to suppress all the people who are on the user's
selected mailing list even if they're assigned to another mailing list.

And direction appreciated.  If it's too snaky I'll just write some code to
populate the temp table. 

MTIA

Rocky

--
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

--
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

--
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

--
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