Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30

    Unanswered: Retrieving data based on multiple field values

    Novice alert!

    I've struggled with this for several hours, so it must be easy. A prod in the right direction would be much appreciated.

    I'm trying to design a simple database in Access 2003 to manage garden allotments. I have a table of Holders (name, address, etc. plus two fields called Status and Residency), a table of Plots (site, plot number, plot size) and a table of Prices. I've successfully linked Holders to Plots and now I want to calculate the annual rental for each plot. The rental is dependent on plot size but also the plot Holder's Status and Residency as per the Price table below:

    Code:
    PriceID    Status      Residency    Price
       1       Employed    Resident      $3
       2       Unemployed  Resident      $1
       3       Employed    Non-Resident  $4
       4       Unemployed  Non-Resident  $2
    My problem is, how do I query each Holder's Status and Residency and use the results to select the corresponding rental value from the Price table?

    Thanks for any help!!

    Mike

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Check help for "GROUP BY" as well as "Aggregate" functions.

    The jist of it is you want to "GROUP" your results by resident, and then use the SUM() aggregate function to produce the required result.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30
    Thanks for your prompt response. I'll give that a try tomorrow.

    Just knowing where to start is half the battle!

    Mike

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The docs are pretty good for this one. If you get stuck on something specific let us know.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30
    Yes, I've found the docs very useful, but the difficulty for a newbie is knowing what search terms to use!

    Mike

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I know that one all too well.

    I remember trying to explain to the first guy training me in development that he can't simply tell me to go look it up in the documentation because I have no idea what I'm looking for. I likened it to trying to learn intermediate German from a textbook... written in German. Problem being the learner doesn't know ANY german, so that textbook is a bit on the useless side until they acquire a decent working vocabulary.

    You'll get there.

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30
    It's me again

    I've spent a whole day wrestling with dlookup, and seem to be half way to where I want to be.

    For each holder, this query returns the allotment price from the Price table for the corresponding value of the allotment Holder's Status field:

    Code:
    Expr1: DLookUp("[Price]","tblPrice","[Status]='" & tblHolders!Status & "'")
    And this returns the price for the corresponding value of the Holder's Residency field:

    Code:
    Expr1: DLookUp("[Price]","tblPrice","[Residency]='" & tblHolders!Residency & "'")
    But when I try to combine them like this, it returns the price from the first row of the Price table for each Holder:

    Code:
    Expr1: DLookUp("[Price]","tblPrice","[Status]='" & [tblHolders]![Status] & "'" And "[Residency]='" & [tblHolders]![Residency] & "'")
    Please can you tell me what I'm doing wrong?

    Mike
    Last edited by P&T; 11-08-09 at 06:07. Reason: Error in field name

  8. #8
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30
    OK, I've found another way of doing what I want. Here it is:

    Code:
    SELECT tblHolders.HolderID, tblPrice.PriceID, tblPrice.Price
    FROM tblHolders, tblPrice
    WHERE (((tblHolders.Status)=tblPrice.Status) And ((tblHolders.Residency)=tblPrice.Residency));
    I'm still interested to know why my dlookup didn't work, if anyone can tell me please?

    Mike

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    don't rely on the order of records in the database. It isn't a spreadsheet and you can't ever guarantee the order in which records will be returned (short of using ORDER BY).

    If your DLookup criteria returns more than one record, you're going to get the "first" matching instance it comes across which may or may not be what you're actually looking for.

    Be more explicit.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Holders (name, address, etc. plus two fields called Status and Residency)
    Suggestion in your table Holders replace the fields Status and Residency with a field PriceID. The PriceID (Foreign Key) will be the same as the PriceID in your TblPrice.

    You can now use the Dlookup, using the PriceID in your Holders table to find the price in your TblPrice

  11. #11
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30
    Thanks guys. You make learning fun.

Posting Permissions

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