Results 1 to 8 of 8

Thread: Simple query

  1. #1
    Join Date
    Oct 2002
    Posts
    14

    Unanswered: Simple query

    I have 2 tables. [Streets] and [donations].
    The 2 are linked by a common field.

    I simply need to build a query for those who have not donated this year. I can't build a query asking for "is null" on the amount field because it will pull out previous years record. I need to exclude these.

    Simply put.. I need this years donations that =0.

    Any thoughts as to the best way to do this?

    Street Table is names, addresses id# etc...
    Donations table is date, amount, id#.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: Simple query

    Originally posted by rickyzicky
    I have 2 tables. [Streets] and [donations].
    The 2 are linked by a common field.

    I simply need to build a query for those who have not donated this year. I can't build a query asking for "is null" on the amount field because it will pull out previous years record. I need to exclude these.

    Simply put.. I need this years donations that =0.

    Any thoughts as to the best way to do this?

    Street Table is names, addresses id# etc...
    Donations table is date, amount, id#.

    Thanks
    I supouse that Id# is the link field, this is the sql :

    SELECT Street.Names, Street.Addresses, Donation.Date, Sum(Donation.Amount) AS SumaDeAmount
    FROM Street INNER JOIN Donation ON Street.[Id#] = Donation.[Id#]
    GROUP BY Street.Names, Street.Addresses, Donation.Date
    HAVING (((Sum(Donation.Amount)) Is Null Or (Sum(Donation.Amount))=0));
    Saludos
    Norberto

  3. #3
    Join Date
    Oct 2002
    Posts
    14
    Swapping your code to mine give me this:

    SELECT Streets.STREET_NAM, Streets.STREET_NUM, Donations.DateEntered, Sum(Donations.Amount) AS SumaDeAmount
    FROM Streets INNER JOIN Donations ON Streets.SEQ = Donations.seq
    GROUP BY Streets.STREET_NAM, Streets.STREET_NUM, Donations.DateEntered
    HAVING (((Sum(Donations.Amount)) Is Null Or (Sum(Donations.Amount))=0));
    ---------------------------------------------------------------------

    All this does is return a few records that = $0.00
    Does not get null records.

    Thanks

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    I thing you want null or cero if is null only then elimitate the string for cero like this:

    SELECT Street.Names, Street.Addresses, Donation.Date, Sum(Donation.Amount) AS SumaDeAmount
    FROM Street INNER JOIN Donation ON Street.[Id#] = Donation.[Id#]
    GROUP BY Street.Names, Street.Addresses, Donation.Date
    HAVING (((Sum(Donation.Amount)) Is Null));

    This return only the record that have null in the field Donation.Amount.
    Saludos
    Norberto

  5. #5
    Join Date
    Oct 2002
    Posts
    14

    Simple query

    Originally posted by Norberto
    I thing you want null or cero if is null only then elimitate the string for cero like this:

    SELECT Street.Names, Street.Addresses, Donation.Date, Sum(Donation.Amount) AS SumaDeAmount
    FROM Street INNER JOIN Donation ON Street.[Id#] = Donation.[Id#]
    GROUP BY Street.Names, Street.Addresses, Donation.Date
    HAVING (((Sum(Donation.Amount)) Is Null));

    This return only the record that have null in the field Donation.Amount.
    Returns no records. records in the domations table only exist when entered so there are no null fields in the Donations table.

    If I ran a query on thos who donated in 2003, how do I select the opposite of the records returned for the streets table?

  6. #6
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    This query return the records that is in Street table and don't exist in
    Donation Table:

    SELECT Street.[Id#], Street.Names, Street.Addresses, Donation.[Id#]
    FROM Street LEFT JOIN Donation ON Street.[Id#] = Donation.[Id#]
    WHERE (((Donation.[Id#]) Is Null));

    Like you say the opposite.
    Saludos
    Norberto

  7. #7
    Join Date
    Oct 2002
    Posts
    14

    Simple Query

    This does give me people who have not ever donated, dut I now need to include those who may have donated last year, but not this year.

    RZ

  8. #8
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465

    Re: Simple Query

    Originally posted by rickyzicky
    This does give me people who have not ever donated, dut I now need to include those who may have donated last year, but not this year.

    RZ
    Use two query:
    The first query select only the Records of Donation with year = current year - 1 (2002) like this:

    Consulta3
    SELECT Donation.Date, Donation.KeyDonation, Donation.Amount, Year([Date]) AS Date1
    FROM Donation
    WHERE (((Year([Date]))=Year(Date())-1));

    The second query return the records that is in Street table and don't exist in first query ( that mean don't have donation in 2002):

    Consulta2
    SELECT Street.Key, Street.Names, Street.Addresses, Consulta3.KeyDonation
    FROM Street LEFT JOIN Consulta3 ON Street.Key = Consulta3.KeyDonation
    WHERE (((Consulta3.KeyDonation) Is Null));

    ok
    Saludos
    Norberto

Posting Permissions

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