Results 1 to 10 of 10
  1. #1
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116

    Use of select Top in tandem with another select query issue

    Hi Everyone,



    I have an issue with a query I am trying to build and need of some guidance please.

    I am building an on call system, where we have a list of workers who register the willingness (tblOnCallList), then from that list if we need to call in extra workers, we use this list as the means.

    The issue I have is I am attempting to build a query (based on this one table) the query will sort in order by various criteria such as who is qualified and has the minimum Hours worked already where = (tblOnCallList.fldQualified) and then (tblOnCallList.fldOnCallHrs) which sound simple enough right!

    The difficult part for me is one of the fields (tblOnCallList.fldQualified) that I am trying to sort on within this table is where we only want workers that are qualified to only to show the first two selected workers sorted by OnCallHrs of course, then from there if possible to encompace within the same query I wish to retrieve the rest of the workers based on the least amount of already accumulated On-Call hours(tblOnCallList.fldOnCallHrs) whether or not they are qualified or not.

    I had a search for way of achieving this and found that I could use “select TOP” within my query, but as soon I attempt to implement this, I can only retrieve the first two records , I thought there must be a way to combine the two together, but it’s currently beyond my skillset at the moment.
    My thoughts were if it can’t be done then I would build a form and attempt it to show the results from top on one sub form and directly below this have another subform showing the simple query results from (tblOnCallList.fldOnCallHrs)

    Here is the reasons why I need this to work!

    We want to call the 1st workers who have the least amount of on-call hours, but from an OHS point of view we also need sort and pick firstly the first 2 workers who have a certain qualification.
    So although we will pick from the sorted list the workers with the least amount of on-call hours , the top two (X2) picked will also need to be qualified even if they have a greater amount of On-Call Hours.

    So again the order of preference is as such:


    1. Get the first (X2)two workers who have the least amount of hours who are qualified!
    2. Then get the rest of the workers who have the least amount of hours whether qualified or not!

    I Hope this makes sense, if anyone can guide me on how or produce an example I’d be ecstatic grateful.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This might need a UNION query. Provide a sample dataset. Either build a representative table in a post or follow instructions at bottom of my post to attach file.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Quote Originally Posted by June7 View Post
    This might need a UNION query. Provide a sample dataset. Either build a representative table in a post or follow instructions at bottom of my post to attach file.
    I tried to strip my database down for confidentiality reasons, spent about two hours but found it way to difficult.
    So I basically created a test one, just to simulate my post really, although this is no where near like the actual tables.

    Any way, could you let me know what you think Thanks
    Attached Files Attached Files

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Seems the logic would be along this (generally):

    -Find all qualified workers(A)
    -Find the Top2 in (A) who have least number of OnCallHrs (B)
    then
    Get other workers (not in B) ordered by the OnCallHrs(C)

    Your result

    B
    Union C

  5. #5
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    sounds like a plan..thanks any suggestions om how to implement

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is an approach;

    Query A
    Code:
    SELECT tbl_OnCallList.FldWorkerID
    	,tbl_OnCallList.fldOnCallHrs
    	,tbl_OnCallList.fldQualified
    	,tbl_OnCallList.fldWrkLocation
    	,tbl_OnCallList.fldWorkerTypeid
    FROM tbl_OnCallList
    WHERE (((tbl_OnCallList.fldQualified) = True))
    ORDER BY tbl_OnCallList.fldOnCallHrs;
    Query B

    Code:
    SELECT TOP 2 A.FldWorkerID
    	,A.fldOnCallHrs
    	,A.fldQualified
    	,A.fldWrkLocation
    	,A.fldWorkerTypeid
    FROM A
    Query C

    Code:
    SELECT tbl_OnCallList.FldWorkerID
    	,tbl_OnCallList.fldOnCallHrs
    	,tbl_OnCallList.fldQualified
    	,tbl_OnCallList.fldWrkLocation
    	,tbl_OnCallList.fldWorkerTypeid
    FROM tbl_OnCallList
    WHERE tbl_OnCallList.FldWorkerID NOT IN (
    		SELECT b.fldworkerId
    		FROM B
    		)
    ORDER BY fldOnCallHrs;

    Query UnionQueryKeepingQualifiedAtTopOfList

    The FinOrder is just a temp field to keep the qualified with least OnCallHours at the top of the list.
    I used U1 and U2 just to keep qualified (Top 2) at the top, the others are in order of the ascending OnCallHours


    Code:
    SELECT TOP 2 A.FldWorkerID
    	,A.fldOnCallHrs
    	,A.fldQualified
    	,A.fldWrkLocation
    	,A.fldWorkerTypeid
    	,"U1" AS FinOrder
    FROM A
    
    UNION
    
    SELECT tbl_OnCallList.FldWorkerID
    	,tbl_OnCallList.fldOnCallHrs
    	,tbl_OnCallList.fldQualified
    	,tbl_OnCallList.fldWrkLocation
    	,tbl_OnCallList.fldWorkerTypeid
    	,"U2" AS FinOrder
    FROM tbl_OnCallList
    WHERE tbl_OnCallList.FldWorkerID NOT IN (
    		SELECT b.fldworkerId
    		FROM B
    		)
    ORDER BY FinOrder
    	,fldOnCallHrs;
    Good luck.

  7. #7
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Quote Originally Posted by orange View Post
    Here is an approach;


    Good luck.
    Well that seems logical enough to me, so now that I know that it can be done and by a really logical method, as its my day off work tomorrow I'll have a crack at it in the morning and get back to you.
    many thanks once again.

  8. #8
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    [QUOTE=orange;386862]Here is an approach;

    Thanks, now I no what you meant by saying ..Good luck.
    Well, I had a really good try at using your suggested "Union" method with my real/existing query but unfortunately was to complex for my little brain, I personally found it impossible to achieve (without the graphical design feature normally provided by access)although all the filtering and sorting is done on that one table in reality my query really spans across many other tables to get ID Values .

    Thanks though, for all your help and suggestions.
    I am just going to provide a message box to the end users to check for qualified and allocate accordingly!

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    gint32,

    If you take the SQL for queries A , B and C (separately) and place them in the query designer, you can change to "Design view" and see the graphic for each of these queries.

    But the Access graphical query builder does not handle UNION queries,so you have to use SQL view.

    Good luck with whatever approach you decide upon.

  10. #10
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    thanks, For all your effort

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

Similar Threads

  1. INNER JOIN SELECT DISCREET issue
    By trevt in forum Access
    Replies: 3
    Last Post: 09-17-2017, 11:47 AM
  2. Replies: 9
    Last Post: 01-31-2017, 05:13 PM
  3. Multi Select Listbox Issue
    By msk7777 in forum Access
    Replies: 12
    Last Post: 05-11-2015, 09:25 AM
  4. Replies: 5
    Last Post: 05-14-2014, 01:17 PM
  5. select range class issue
    By TheShabz in forum Programming
    Replies: 3
    Last Post: 03-20-2012, 07:37 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