Results 1 to 6 of 6
  1. #1
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70

    One tables data wont show in Query

    So this one has been stumping me for a while now and I cannot figure out why this is happening.

    I have two tables:

    tblPatient
    -PatientID [PK]
    -PatientName
    -DOB
    -PhysicianID*
    -RegistrationDate
    -...

    tblPhysician
    -PhysicianID [PK]
    -PhysicianName
    -FacilityName
    -FacilityAddress
    -...


    The query involves both tables and uses the tblPatient.PhysicianID to link to the tblPhsician.PhysicianID.
    The backend is not filled out through an access form (we have web based forms that push data over to the backend), however the Physician on both tables when pushed over match.
    When run through a query which has "joined fields are equal" nothing shows up, when I run it where it shows all the patient results only the patient data shows along with what is entered in tblPatient.PhysicianID, but all the physician results are blank.

    When I go to the form that the query is assigned to same thing, but I have a combo box on there that is used to amend the physician id if it is incorrect. Once you put the physician Id that is suppose to be there all the fields in the query and on the form are filled out.


    But looking back at the patient/physician table nothing has changed, no data has been transferred (which there shouldnt be anyways because the tblPatient already had the correct ID).

    Why cant the query see that I want it to bring up the physicians data that matches the ID to the tblPatient.PhysicianID? Is it something to do with the background relationships?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Is tblPatient.PhysicianID type= long?
    are you using a combo box to pick the doctor on the Patient form?

  3. #3
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Is it something to do with the background relationships?
    Probably not. The problem is probably has to do with the values in the PhysicianID fields. They may look the same - but are they really? What is the data type of that field? It should be the same in both tables. If it is character type, and the data is coming from a source that is not MS Access, then there may be some extra non-visible characters in there that cause Access to think the fields are not equal. The fact that you can change the PhysicianID to its "correct" value and then have the query work (in the form) suggests that is the problem.

    If you can, take a look at the code that transfers the data from the web to your back end tables - something may be adding an extra blank or a CRLF to the data, which you can't see but is causing the mis-match problem.

  4. #4
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70
    Quote Originally Posted by John_G View Post
    Probably not. The problem is probably has to do with the values in the PhysicianID fields. They may look the same - but are they really? What is the data type of that field? It should be the same in both tables. If it is character type, and the data is coming from a source that is not MS Access, then there may be some extra non-visible characters in there that cause Access to think the fields are not equal. The fact that you can change the PhysicianID to its "correct" value and then have the query work (in the form) suggests that is the problem.

    If you can, take a look at the code that transfers the data from the web to your back end tables - something may be adding an extra blank or a CRLF to the data, which you can't see but is causing the mis-match problem.
    Whats funny is that I can also copy the PhysicianID in the Physician table and put it in the patient table but yet sample issue, no physician data shows up in the query...

    You and ranman256 had a good point asking me if the fields were the same type; on the tblPhysician the PhysicianID is "Short Text" while on the tblPatient the PhysicianID is a lookup field to the PhysicianID.
    I assume this must be affecting the query? We have a form on the database that also creates new patients and physicians and that uses the drop down box to pick the physician ID, which would make sense why we dont have that issue from manual entries.

    To fix this, just make the PhysicianID a "Short Text" on the tblPatient and make the form combo box be a lookup to the PhysicianID? I assume this will work, I will try to do it in a couple of hours and let you know if i was successful or not. Thank you for taking time to help me solve this issue!

  5. #5
    NickWren is offline Advanced Beginner
    Windows XP Access 2013
    Join Date
    Dec 2015
    Location
    Connecticut
    Posts
    70
    Well I feel very special, changing the field to be just a plan textbox instead of a lookup field make the information correlate and now the query presents the physician and patient information on the form!

    Thank you for helping me wrap my head around this...

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

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

Similar Threads

  1. Query wont show records with a blank field
    By bignate in forum Queries
    Replies: 2
    Last Post: 09-03-2013, 04:45 AM
  2. Replies: 7
    Last Post: 12-30-2012, 03:59 AM
  3. Subform wont show in 2007
    By Jake in forum Access
    Replies: 1
    Last Post: 07-15-2011, 02:48 PM
  4. Its Only A Poor Zero...Why wont it Show???
    By chompgator in forum Access
    Replies: 2
    Last Post: 03-08-2009, 01:54 PM
  5. All Data wont show
    By Wrangler in forum Queries
    Replies: 1
    Last Post: 01-02-2006, 03:27 AM

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