Results 1 to 5 of 5
  1. #1
    jmblackstock is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    3

    Question on How to Create Desired Table Output from Query?


    Hello all,

    This is my first time to post on AccessForums.net and apologize if this is not the best forum to post in, but from my beginner's knowledge of Access, it seemed logical. I understand basic one-to-one and one-to-many relationships, but that's about it for me.

    Background of problem:

    I'm a hydrologist trying to set well measurements to a continuous list of dates, as the well measurements are not continuous.

    Specific problem:

    I've attached a sample of what I'm working with to add context to the problem. The measurements are stored as they appear in the table 'Well_Measurements'.

    What I have no clue of doing is creating a relationship and query that could possibly create the table 'Desired_Data_Output'.

    More info:

    I setup a function in Excel that would search for two criteria and pull the measurement into correct cell, but it only works when applied to a small portion of the worksheet. When applied to larger portions of the worksheet, Excel will freeze as it drains resources.

    Instead of the 6 wells and 25 associated measurements in my example, I have 7,000+ wells and 1,000,000+ measurements.

    Any help is appreciated. It would be great if you repost the database file "corrected" to help me out.

    A million thanks,


    jmblackstock

  2. #2
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Hi,

    You could likely get something similar to your results with a crosstab query, but to be sure, I'll need to know the answers to a couple questions first:

    a. Do you take measurements every day? If not, is it important to see those (empty) days in your results?

    b. Do you ever record two or more measurements on the same well on the same day?

    If a crosstab doesn't work for you, there are other ways.

    Cheers,

    P.S. - I'd be interested to see your attempt using Excel.

  3. #3
    jmblackstock is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    3
    ConneXionLost,

    Quick answers:

    a. Measurements are not everyday. Empty days (cells) are completely acceptable.

    b. Some wells do have multiple measurements per day, but if a value is overwritten, the values are generally close enough for the purpose of my study.

    The function in the Excel file is most certainly not my own creation (just throwing that out there). To describe the function,

    A1:A10 = first criteria array
    B1:B10 = second criteria array
    C1:C10 = the values you're trying to pull

    =INDEX(C1:C10, MATCH(cell location of first criteria & cell location of second criteria, A1:10 & B1:B10, 0))

    Excel file is attached (zipped).

    If it's not too much trouble, could you please post a sample of the cross-tab reference with using my original post if possible? It would be very much appreciated.

    Cheers,


    jmblackstock

  4. #4
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    In the example database:

    - I renamed your source table and some of the fields. "Date" isn't a good name for a date field (it's a reserved word), and I don't like using a generic "ID" name for a key in a table. It gets confusing when you use it more than once.

    - The crosstab query is [qxtbWellMeasurementSum]. I set the values to sum with the assumption you will only ever keep one measurement per well per day. That's why your answer to the question (overwriting) was important. You could also try averaging if it works for you.

    - In order to include the empty days, I created a table of dates [tblDateRange] and combined it with the crosstab query as [qselResultsWithNulls].

    Cheers,

  5. #5
    jmblackstock is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    3
    Thank you, thank you, thank you. This has helped tremendously! I hope the Excel double criteria search was at least entertaining in exchange.

    Cheers,

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-29-2010, 08:01 AM
  2. Input (storing) output question
    By spacewater in forum Access
    Replies: 0
    Last Post: 10-17-2009, 08:14 AM
  3. Create table out of union query
    By DKruse1969 in forum Queries
    Replies: 2
    Last Post: 08-28-2009, 09:55 AM
  4. Insert Query output into a table
    By ammu_sridhar in forum Programming
    Replies: 1
    Last Post: 06-12-2009, 01:09 AM
  5. Output Query to Text
    By denileigh in forum Queries
    Replies: 1
    Last Post: 05-27-2006, 12:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums