Results 1 to 7 of 7
  1. #1
    badmem is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2016
    Posts
    24

    Query not returning correct results

    I have two tables linked by serial number. The YEAR_IMPORTED has a list of dosimeters with serial numbers and readings for the whole year. The DOSL_ALLOCATION table has a list of the dosimeter serial number along with the person it was issued to for which quarter. So a dosimeter can be issued to one person in the 2nd quarter and a different person in the 4th quarter.

    When I run the query below I expected only the dosimeter readings for the 4th quarter to show up, but I'm getting the reading for both the 2nd and 4th quarters.

    How can I get only the dosimeters for the 4th quarter?



    Thank you.

    Click image for larger version. 

Name:	OSL_LINK001.jpg 
Views:	12 
Size:	148.4 KB 
ID:	25278you.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what are the values in the qtr_01 field? and what is the field type? text? number? calculated?

  3. #3
    badmem is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2016
    Posts
    24
    Quote Originally Posted by Ajax View Post
    what are the values in the qtr_01 field? and what is the field type? text? number? calculated?
    Thank you for your fast reply. The qtr_01 values are "1st-Jan-Mar";"2nd-Apr-Jun";"3rd-Jul-Sep";"4th-Oct-Dec" They are entered from a drop-down combo box. The choice is from a value list. The field type is text.

  4. #4
    badmem is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2016
    Posts
    24
    Other information - If I sort the query results by clicking on the Case_Serial# column I get an error "Syntax error (missing operator) in query expression "tbl15YEAR_IMPORTED_Case_Serial#" If I click okay the query sorts and shows two entries for the some serial numbers one for the 2nd quarter and one for the 4th quarter. The QTR_01 column shows all of the readings are for the 4th quarter, but the duplicates are for different quarters - they have different readings. This is a real mess. Is there something wrong with the way the tables are linked?

    Thank you.

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I suggest you take the "#" out of the field name in both tables. "#" is used to delimit dates in Access, and I suspect using it in a field name is confusing Access.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it may be to do with the # you have in the field name - using none alpha numeric characters in field and table names cause unexpected issues. I also presume this field is a normal field, not a calculated, multivalue or lookup - in either table.

    After that, would need to look at the data

  7. #7
    badmem is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2016
    Posts
    24
    Thanks for the replies. I think I've made a basic mistake. At the end of the year the 15YEAR_IMPORTED table will have between 1 and 4 readings for each dosimeter. There is nothing in the table to show which quarter the reading is for. When the query runs it will find all of the readings and doesn't know which reading is for which quarter.

    If I import each quarters readings into a separate table for each quarter this should make the query work. Is there a better way do this?

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

Similar Threads

  1. Replies: 5
    Last Post: 06-13-2015, 04:54 AM
  2. Getting correct date from query results
    By riggsdp in forum Programming
    Replies: 5
    Last Post: 11-11-2014, 04:01 PM
  3. Like query not returning all results
    By robbeh in forum Queries
    Replies: 3
    Last Post: 10-10-2014, 02:32 PM
  4. Replies: 9
    Last Post: 06-26-2011, 09:14 PM
  5. Returning correct rows in a join/nested query
    By goneaccessing in forum Queries
    Replies: 5
    Last Post: 03-03-2010, 12:21 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