Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2017
    Posts
    3
    Provided Answers: 1

    Answered: I'm having a problem getting MS Access to show all my entries in a query

    I have a database that shows donations for different purposes and a typical entry will look like this:
    Date | First Name | Last Name | Donation A | Donation B | Donation C | Donation D
    Not all donors donate to each category, so its not unusual to have entries with a mixture of dollar amounts in one field and zero's in the other fields.
    (ex. 01/01/2017 | Harry | Jones | Donation A: $20 | Donation B: $0 | Donation C: $0 | Donation D: $50)

    I ran a query for donors who donated to category A & B with donations that are greater than $0 by using a criteria (>0). The problem I'm now having is MS Access is not showing any entries if one of the fields have a "0" value.
    So the example above doesn't show up in the query because Harry Jones had a $0 value for Donation B, so Access doesn't show the entire entry in the query even though he made a donation for Donation A.
    I'm amateur at Access so hopefully someone can get me across this hurdle.
    Last edited by natedj; 06-18-17 at 08:21. Reason: Title correction

  2. Best Answer
    Posted by natedj

    "
    Quote Originally Posted by slandy18 View Post
    In your query, did you have the criteria "where donationA>0 and donationB>0"?

    I don't think you want to have "AND" there. Did you try "OR"?

    Another way to skin the cat: "where (donationA+donationB)>0"
    Thanks Slandy18 this achieved what I was looking for .... awesome!
    Now that I've gleaned some info from the first few post I'll have to implement some of the info that was post here. Thanks again guys."


  3. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    Welcome to the insane world of Access!

    From the design of your table, you need to research database normalisation. You've managed to violate two rules of database construction in one table, which is impressive

    Firstly, any time that you have enumerated column names, you're going to have problems. As you've found here, having multiple "Donation" columns makes it very difficult to query back information.

    Secondly, a database table should only contain data regarding one type of thing. Here you have information about donors and donations, which are two different things.

    Based on what you've provided, you can split your table into at least three:
    • Donors (DonorID, Forename, Surname, [any other fields you need to keep])
    • DonationTypes (DTID, DTDescription, [any other fields])
    • Donations (DonationDate, DonorID, DonationTypeID, DonationAmount)

    You then define relationships between Donors and Donations, and DonationTypes and Donations on the relevant ID fields. Splitting your data then yields:
    Code:
    Donors:
    DonorID        Forename       Surname
    1              Harry          Jones
    
    DonationTypes:
    DTID           DTDescription
    1              Donation A
    2              Donation B
    3              Donation C
    4              Donation D
    
    Donations:
    DonationDate   DonorID        DonationTypeID DonationAmount
    01/01/2017     1              1              20.00
    01/01/2017     2              4              50.00
    You can then write a query to join the tables and show what you need:
    Code:
    SELECT
         dn.DonationDate
    ,    d.Forename
    ,    d.Surname
    ,    dt.DTDescription
    ,    dn.DonationAmount
    FROM
         (Donors AS d
    INNER JOIN
         Donations AS dn
    ON
         d.DonorID = DN.DonorID)
    INNER JOIN
         DonationTypes AS dt
    ON0
         dt.DTID = dn.DonationTypeID
    WHERE
         dt.DTDescription IN ('Donation A','Donation B')
    AND
         dn.DonationAmount > 0;
    
    Results:
    DonationDate   Forename       Surname        DTDescription  dn.DonationAmount
    1              Harry          Jones          Donation A     20.00
    HTH!
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  4. #3
    Join Date
    Jun 2017
    Posts
    7
    I think normalization will take long time, so avoid it your database is simple you do not need normalization.

    I think to get the fields you are running AND command due to which any donation less then 0 is causing not to display, use OR instead of AND.

  5. #4
    Join Date
    Jun 2017
    Posts
    3
    Provided Answers: 1
    Quote Originally Posted by weejas View Post
    Welcome to the insane world of Access!
    Thanks for the guidance, I'll definitely look into it as I need to learn all I can about databases. My database has a little over 8k records so if there is a recommendation you have to quickly split it up them I'm all ears.

  6. #5
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    8000 records is fairly small, so it would depend on the structure of your database. If all you have is the table that you originally described, the work could be done in less than a day:
    • Create the new tables
    • Optional - define the relationships (helps to prevent invalid records and highlight gaps or errors)
    • Populate the Donors table from the existing with an INSERT query
      Code:
      INSERT INTO Donors (Forename, Surname, [other fields]) SELECT DISTINCT Forename, Surname, [other fields] FROM Old_Table;
      Depending on how many donors you have, this might be easier to do manually. If you set the DonorID field to Autonumber, this will populate automatically when the query runs. Also, you'll need (at this point) to ensure that you have some other means of uniquely identifying each donor e.g. forename/surname/DoB combination (NOT always a guarantee); social security number; E-mail address.
    • Populate the DontationTypes table - probably easiest to do this manually
    • This is the tricky bit. You'll need to run another INSERT INTO query based on the old table, as many times as you have donation types, being careful to uniquely identify donors each time.

    Code:
     Using forename/surname/DoB:
    INSERT INTO Donations (
         DonationDate
    ,    DonorID
    ,    DonationTypeID
    ,    DonationAmount)
    SELECT
         ot.[Date]
    ,    d.DonorID
    ,    dt.DTID
    ,    ot.[Donation A]  --This line will need to be changed between each run
    FROM
         Old_Table AS ot
    ,    Donors AS d
    ,    DonationTypes AS dt
    WHERE
         ot.Forename = d.Forename
    AND
         ot.Surname = d.Surname
    AND
         ot.DoB = d.Dob
    AND
         dt.DTDescription = 'Donation A'  --This line will need to be changed between each run
    AND
         ot.[Donation A] > 0
    This query joins tables in the WHERE clause rather than the FROM clause because there is no join between the old table and DonationTypes. You can just run the query from the SELECT statement to see what records will be inserted, and the make sure that it does actually return some data!

    Take a backup of your database, have a play and come back if you get stuck.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  7. #6
    Join Date
    Apr 2017
    Posts
    14
    In your query, did you have the criteria "where donationA>0 and donationB>0"?

    I don't think you want to have "AND" there. Did you try "OR"?

    Another way to skin the cat: "where (donationA+donationB)>0"

  8. #7
    Join Date
    Jun 2017
    Posts
    3
    Provided Answers: 1
    Quote Originally Posted by slandy18 View Post
    In your query, did you have the criteria "where donationA>0 and donationB>0"?

    I don't think you want to have "AND" there. Did you try "OR"?

    Another way to skin the cat: "where (donationA+donationB)>0"
    Thanks Slandy18 this achieved what I was looking for .... awesome!
    Now that I've gleaned some info from the first few post I'll have to implement some of the info that was post here. Thanks again guys.

  9. #8
    Join Date
    Apr 2017
    Posts
    14
    Glad to be of help.

Posting Permissions

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