Results 1 to 2 of 2
  1. #1
    Phoenixenv is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Perth, WA
    Posts
    8

    preference the records returned from a distinct query based on particular field values

    I know it sounds odd as a query is always about getting the results you want - the preferred results.

    In this case i have a table with duplicate records for each job, species of plants and animals. Each species has its own unique number. The table contains species that were recorded by my company and species that were recorded by other people/companies, so that i can summarise and compare what we recorded versus what other people in the area recorded.



    I need a unique or distinct query that will return only a subset of those species (for a particular Job number AND those that are protected by law), BUT i want it to preference my company data over someone elses.

    The query is needed to complete a "likelihood of occurrence" assessment of all protected species. Therefore it is vitally important that our records are selected before anyone elses, because then i know it was "Recorded" in our study area, rather than being someone elses record, which means the species can only be assessed as "Likely" or "Possible".

    If i leave in the field that differentiates my data from someone elses, then i will not get a truly unique/distinct recordset.

  2. #2
    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
    How about an example of what you have, and what you would like as the result set based on the example?

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

Similar Threads

  1. Replies: 3
    Last Post: 10-30-2016, 05:50 AM
  2. Replies: 1
    Last Post: 03-31-2016, 02:52 PM
  3. Memo Field Cut Off - But Need Distinct Values
    By accessnewbie352 in forum Reports
    Replies: 1
    Last Post: 01-16-2015, 01:00 PM
  4. Replies: 1
    Last Post: 11-26-2013, 01:41 AM
  5. Counting returned records in a query
    By johncob in forum Queries
    Replies: 0
    Last Post: 02-11-2009, 05:30 PM

Tags for this Thread

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