Results 1 to 7 of 7
  1. #1
    char is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    29

    Look up relationship mismatch


    I have created got two tables:
    [FBWelding] which contains weld ID, Welder ID, Project ID, etc
    [Welders] which contains Welder ID, Surname, Firstname etc

    I want to be able to search by project name and then to view that as a report, I made this query using the table [FBWelding] and because my "Welder ID" field is a look up it shows the primary ID key instead of the Welder ID.
    On Office online, it says to resolve this create a relationship, so I changed the join from
    [FBWelding] WelderID - [Welders] IDkey ...
    to [FBWelding]Welding ID -[Welders]Welder ID

    When I attempt to run this query it is saying that I have a mismatch in my expression. In [Welders] the "Welder ID" is text, but then in [FBWelding] the "Welder ID" is a look up.

    Does anyone have any ideas how to enable the report to show the value of the look up and not have a mismatch in expression?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Sounds like the WeldersID field in FBWelding is actually saving the autonumber ID field from Welders, not the text WelderID.

    You need to either:

    query join on FBWelding!WelderID and Welders!ID

    or

    change the WelderID field in FBWelding to text and save the text WelderID also change the PK in Welders to the text WelderID and the autonumber ID is unnecessary
    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
    char is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    29
    You have to have the ID as a field in the web compatable version, it can't be deleted and you can't make any other field a primary key.
    When I try to join them in a query, that is when I get the 'type mismatch in expression' error message.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Join the tables on the pk/fk number fields then all related info of both tables, including the text WeldersID field, will be available.
    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.

  5. #5
    char is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    29
    on the normal Access I would be able to do this, however Im not aware of primary and foriegn keys on the web compatable version, I thought it had been disabled?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I don't understand how a web database join would be any different than normal db.

    If you save the pk autonumber Welders!ID to FBWelding table in a number field then this is a fk. These are the common fields that query join would be done on.
    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.

  7. #7
    char is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    29
    I was trying to join it with the Welders!WelderID field that was why I was getting the mismatch message. Joined it to the automated ID and it appears to work. Thanks for your help!

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

Similar Threads

  1. type mismatch
    By Compufreak in forum Access
    Replies: 5
    Last Post: 08-08-2012, 11:00 AM
  2. VBA strSQL mismatch
    By SPW_12 in forum Programming
    Replies: 12
    Last Post: 07-31-2012, 07:47 PM
  3. type mismatch
    By slimjen in forum Forms
    Replies: 21
    Last Post: 07-24-2012, 03:14 PM
  4. Data Mismatch?
    By Lockrin in forum Access
    Replies: 7
    Last Post: 06-14-2010, 03:17 PM
  5. Type Mismatch - HELP!
    By jgelpi in forum Programming
    Replies: 3
    Last Post: 07-17-2009, 03:53 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