Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2011
    Posts
    8

    Unanswered: Help with sorting multple selections to get the first record per each client

    Hello,

    I am having an issue trying to retreive the first record for each client in the data base. In order to get this info I have to use both Visit Start date and and Visit Start Time. The complet einfo I need would be: Client ID, Visit Start Date (The first one for each client), Visit Start Time (the first time for the first Visit start date), element ID (to select a particular element), and element Value( the info needed for the first time and first date).

    So would I need multiple queries tied together to do this?

    I can cobble together a dummy table with the example info in there if needed to give you an idea of the layout.

    Any help is extremely appreciated.

    Thanks,

    Shawn

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please post a sample table. It will be easier (and quicker) than going back and forth with questions and answers. As you describe it it should not be hard to write the proper query.
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    8

    Sample Database attached

    Here is a small selective sample.

    A little more background info.

    I am using access as a front end to a database on a network.

    The connection to the database has to stay read only, it can't make any changes to the database.

    Thanks,

    Shawn
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is the query. It's not pretty (I'm surprised that Access does not throw a 'Expression too complex' error), I don't dare to think of the performances (or lack thereof!) but it works. I'm rather sure that there must be a better way to write it but I can't see any better solution right now. I've tried several satisfactory solutions within a SQL Server but none were accepted by Access.
    Code:
    SELECT Q.client_id, 
           Q.visit_start_date, 
           Q.visit_start_time, 
           Q.element_id, 
           Q.element_value
    FROM (SELECT Min(s.Tag) AS MinOfTag
          FROM [SELECT   Sample_Database.client_id, 
                         Sample_Database.client_id & Sample_Database.visit_start_date & Sample_Database.visit_start_time AS Tag
                FROM     Sample_Database
                ORDER BY Sample_Database.client_id, Sample_Database.visit_start_date, Sample_Database.visit_start_time]. AS s
          GROUP BY s.client_id) as M
    LEFT JOIN [SELECT   Sample_Database.client_id, 
                        Sample_Database.visit_start_date, 
                        Sample_Database.visit_start_time, 
                        Sample_Database.element_id, 
                        Sample_Database.element_value, 
                        Sample_Database.client_id & Sample_Database.visit_start_date & Sample_Database.visit_start_time AS Tag
               FROM     Sample_Database
               ORDER BY Sample_Database.client_id, 
                        Sample_Database.visit_start_date, 
                        Sample_Database.visit_start_time]. AS Q 
    ON M.MinOfTag=Q.Tag;
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    8

    Tried it but doesn't work well

    Hello,

    I tried your solution after substituting the correct table name in place of Sample_Database. Wow it was slow!!

    It took almost exactly 11 minutes to run and the results were inconsistant.

    When I used you SQL on the sample database I created, it worked fine.

    It returned 8 results when filtered for element_id = 2097.

    On the real database it returned 3 results when filtered down to Visit_start Date between 7/10/2011 and 7/15/2011, element_id = 2097, and client_id between 14890 and 14920.

    I'm not sure how to proceed from here.

    Also thought I would mention that the backend database is Progress, if that helps.

    Any other thoughts? your help is extremely welcomed.

    Thanks,

    Shawn
    Last edited by shobdy; 09-06-11 at 17:19. Reason: adding more info

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What you can do with SQL in Access is limited as compared to what a more robust database engine can do (MS SQL Server, MySQL, Oracle, etc.).

    The main problem here is the lack of sequential primary key. Usually you create a self join (cartesian product) the get a row count. In this case the results were even worse than with the solution I finally proposed. Carefully selected indexes could probably enhance the performances, though I did not try. I know almost nothing about Progress but it should probably be possible to have it perform parts or all of the computations.

    Another possible solution would consist in creating a temporary table to store the values.

    See also: http://www.dbforums.com/microsoft-ac...ategory-2.html
    Have a nice day!

  7. #7
    Join Date
    Jul 2011
    Posts
    8

    Should I look into a different front end?

    Hello,

    Don't mean to sound like a heretic but should I look into a different front end for the Progress database?

    What would you recommend?

    I need to design form for other people to do the searches. If it can be done through html or something I could roll out with limited capabilities for free, it would be a help.

    Thanks,

    Shawn

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't believe that the problem comes from Access used as a front-end. As far as I know, any other program used to create the front-end would have the same kind of problem. Some would probably run faster than Access but the problem of the query would remain the same.

    Such a query should be stored either as a view or as a stored procedure on the server which Access (or any other system used as front-end) could retrieve. The main burden comes from the lack of primary key. I was obliged to concatenate several columns to create a pseudo-unique identifier, this is the Tag computed column:
    Code:
    Sample_Database.client_id & Sample_Database.visit_start_date & Sample_Database.visit_start_time AS Tag
    that I used to create the relationship in the self join:
    Code:
    ON M.MinOfTag=Q.Tag
    If there was a column with a unique identifier it could be used for this purpose. Indexed (possibly as the primary key) this would yield faster results.

    In Access, the use of a mixed solution (VBA + dynamic queries + temporary table) would be faster by several orders of magnitude anyway.
    Have a nice day!

  9. #9
    Join Date
    Jul 2011
    Posts
    8

    Updated Sample Database

    Hello,

    I looked through the table in question and found the primary key. I have copied it into the sample database to see if it helps.

    Thanks for helping, it means a lot to me.

    Shawn
    Attached Files Attached Files

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    I'll have a look and post back what I'll come to, but I'm sure it'll be easier and faster.
    Have a nice day!

  11. #11
    Join Date
    Jul 2011
    Posts
    8

    Thank you

    I don't know if it will show up in the sample database but just because it is a lower number in the primary key doesn't mean that it is the earliest date and time for the element value. The earliest dated visit may have been entered a little later than another person who accessed it an hour later to add more info. Hope I didn't confuse the issue.

    Thanks a million,

    Shawn

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Using pt_element_id (defined as primary key), the query Query1 in your sample database becomes:
    Code:
    SELECT Q.client_id, 
           Q.visit_start_date, 
           Q.visit_start_time, 
           Q.element_id, Q.element_value
    FROM (SELECT Min(s.pt_element_id) AS MinID
          FROM (SELECT Sample_Database.client_id, 
                       pt_element_id
                FROM Sample_Database 
                ORDER BY Sample_Database.client_id, 
                         Sample_Database.visit_start_date, 
                         Sample_Database.visit_start_time)  AS s 
          GROUP BY s.client_id)  AS M 
    LEFT JOIN (SELECT Sample_Database.client_id, 
                      Sample_Database.visit_start_date, 
                      Sample_Database.visit_start_time, 
                      Sample_Database.element_id, 
                      Sample_Database.element_value, 
                      pt_element_id
                FROM Sample_Database 
                ORDER BY Sample_Database.client_id, 
                         Sample_Database.visit_start_date, 
                         Sample_Database.visit_start_time)  
          AS Q ON M.MinID=Q.pt_element_id
    WHERE (((Q.element_id)=2097));
    It should run faster because it can use indexed data and does not need to concatenate several fields to identify a row.
    Have a nice day!

  13. #13
    Join Date
    Jul 2011
    Posts
    8

    Thanks but still inaccurate

    Hello,

    I tried your new query on the sample database and it came up with the same inaccuracies that have been plaguing me!

    If you look at client 14896 after you run the new query you will notice the the time is 37800 with a pt_element_id of 4732855. The problem is I need the info that has the start time of 36000 with the pt_element_id of 4732988. That means the earlier time was actually recorded into the database later than the other one but is the info I need.

    You first query pulled the correct time but was inaccurate and slow on the full sized database.

    I appreciate everything you are doing for me but if you are tired of this I would understand. I am slowly learning by following what you try.

    Thanks,

    Shawn

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No problem, I'll keep on searching.
    Have a nice day!

Posting Permissions

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