Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Location
    Houston, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Writing Query to exclude records (2002/SP2)

    I have 2 tables: Contacts and Contributions.
    Pertinent fields from Contacts are: ContactID, LastName, FirstName.
    Pertinent fields from Contributions are: ContributionID, ContactID, ContributionYear.
    Tables are linked on ContactID.

    I am trying to write a query based on the following question: Which contacts that contributed in 2002 did not contribute in 2003?

    I have totally confused myself at this point. Any help would be appreciated.

    BTW, I know VERY little about SQL, if that is the only way to go about it.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing Query to exclude records (2002/SP2)

    Under ContributionYear in the query design grid place this for a criteria: Between #1/1/2002 And 12/31/2002#

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Location
    Houston, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing Query to exclude records (2002/SP2)

    That only tells me who contributed in 2002. I also need to know of those contributors, who did not contribute in 2003.

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Writing Query to exclude records (2002/SP2)

    Recommend use a subquery as criteria for ContactID, as in this example:

    SELECT Contacts.ContactID, Contacts.LastName, Contacts.FirstName, Contributions.ContributionYear
    FROM Contacts INNER JOIN Contributions ON Contacts.ContactID = Contributions.ContactID
    WHERE (((Contacts.ContactID) Not In (SELECT ContactID FROM Contributions WHERE ContributionYear = "2003")) AND ((Contributions.ContributionYear)="2002"));

    This subquery criteria expression:

    Not In (SELECT ContactID FROM Contributions WHERE ContributionYear = "2003")

    is what excludes those who may've contributed in 2002, but not in 2003. To test this copy SQL above & paste into Query Design SQL view window (modify table/field names as necessary). In quick test with dummy data this SQL returned correct results.

    HTH

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Writing Query to exclude records (2002/SP2)

    Another suggestion: use a crosstab query to list Contributors & which years they contributed in, and number of contributions in each year. Sample SQL:

    TRANSFORM Count(Contributions.ContributiontID) AS CountOfContributiontID
    SELECT Contacts.ContactID, Contacts.LastName, Contacts.FirstName
    FROM Contacts INNER JOIN Contributions ON Contacts.ContactID = Contributions.ContactID
    GROUP BY Contacts.ContactID, Contacts.LastName, Contacts.FirstName
    ORDER BY Contacts.LastName, Contacts.FirstName, Contributions.ContributionYear
    PIVOT Contributions.ContributionYear;

    See attached pic for sample results with dummy data. The crosstab results shown make it clear that Jones is the only Contributor who contributed in 2002 but not in 2003. This may or may not be practical with your actual data, if nothing else you can run query like this to verify that the subquery SQL is returning correct results.

    HTH
    Attached Images Attached Images

  6. #6
    New Lounger
    Join Date
    Mar 2004
    Location
    Houston, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing Query to exclude records (2002/SP2)

    Mark, you are my hero! <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15>

    This worked perfectly. I was also able to use the cross-tab query you suggested to doublecheck the results.

    Thank you!

  7. #7
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing Query to exclude records (2002/SP2)

    Hello,
    Reading this post is nearly the same problem 2 tables
    Entries and Classes Entries Tbl Name Age Class No Class Name
    Classestbl Class No Class Name
    Linked on the Class No. What i am trying to do on a crosstab is to create a query showing who entered which class eg

    Name Age Class1 Class2 Class3 Class4
    Fred 10 X
    bert 13 X X = Class not Entered
    george 15
    I have created various other queries to get the info i want, But I need to create a report like the example above with class columns empty unless they have not entered a particular class also Names have to be grouped by age, 3 age groups
    10 and Under 11yrs to 13yrs 14yrs and over
    Attached Copy of Data base saved as office 97 i am using Office XP
    Attached Files Attached Files

  8. #8
    Star Lounger
    Join Date
    Jun 2003
    Location
    Hadfield, Derbyshire, England
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing Query to exclude records (2002/SP2)

    Sorry to bother you,

    Now done used crosstab query wizard created report from the crosstab and in the properties of each Class Name, Changed
    Visable from yes to no Now works

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Writing Query to exclude records (2002/SP2)

    Note - - I downloaded the db you attached & tried to open - when Access tried to convert db from ACC 97 format (I'm using A2K) it acted real strange - no error msg, but it appeared like nothing happened. When you closed Access, there was no converted db in file folder. When you opened Windows Task Manager, MSACCESS.EXE was still listed, with CPU utilization at appx 95% - this not visible in UI. So something was wierd with the db that apparently did not allow it to be converted properly, and so was not able to look at it. Tried converting more than once with same results.

    This FYI since your new reply seems to indicate that looks like you got crosstab working OK.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •