1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Newby Question

Discussion in 'Business Applications' started by DangerBoy32, Nov 20, 2018.

Thread Status:
Not open for further replies.
Advertisement
  1. DangerBoy32

    DangerBoy32 Thread Starter

    Joined:
    Nov 20, 2018
    Messages:
    11
    I am having trouble getting my relationship setup properly. I was going to post the DB on here, but I don't think the site will allow you to post Access databases here because when I navigated to the folder the file wasn't visible. How can I post it so you can see what is going on?
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Compact & Repair it and then put it in a zip file.
     
  3. DangerBoy32

    DangerBoy32 Thread Starter

    Joined:
    Nov 20, 2018
    Messages:
    11
    OK, Thx.

    I have the zip file attached. I am trying to build this simple database for my wife to organize her sideline hobby. It is in the early stages, but I am having difficulty adding the Item Cost field (out of the tblInventory table) to qryInvoice query. I am getting a type mismatch error and I'm not sure why. They have been joined by using the lookup datatype, but something is still conflicting. I am trying to setup a simple invoice for her to be able to pull the cost in from the Inventory table for billing purposes. Any help anyone can give would be appreciated!!
     

    Attached Files:

  4. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    542
    First Name:
    Jack
    Hello DangerBoy,

    Try the following:
    1. Open Query - qryInvoice
    2. Add the Table - tblInventory
    3. (Important - From within the Query window - NOT the Database Tools/Relationship window) Delete the relationship between Tables tblInventory and tblPurchases.
    4. Add the Item Cost field to the query
    5. Save and run the query.
    BTW - I would recommend adding a Close Window command to your Main Menu button macro. ;)
     
  5. DangerBoy32

    DangerBoy32 Thread Starter

    Joined:
    Nov 20, 2018
    Messages:
    11
    It worked! I'm not sure how it worked, but that's why I'm the newbie and your giving advise. Thx a ton and I like the suggestion of close window too!!
     
  6. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    542
    First Name:
    Jack
    Glad to hear it is working well for you.

    My apologies for not explaining why this worked, I was in a bit of a hurry and was attempting to provide a quick functional work around, until I could take a deeper dive into the underlying problem.

    I identified that the linked field "Item Number" is designated as a text field in the Table "tblInventory"; but, the "Item Number" field is designated as a numeric field in the Table "tblPurchases." :) By removing the relational link within the query the Data Type Mismatch is circumvented for that particular query. Matching these fields is recommended to prevent future issues with adding fields to queries that support Forms and Reports (assuming that your database may grow in the future). Also once the datatypes are matched, you can reestablish the relationship within the query.

    Good Luck
     
  7. DangerBoy32

    DangerBoy32 Thread Starter

    Joined:
    Nov 20, 2018
    Messages:
    11
    That makes sense now. Thanks! No apologies necessary though, I just appreciate the help and knowledge
     
  8. DangerBoy32

    DangerBoy32 Thread Starter

    Joined:
    Nov 20, 2018
    Messages:
    11
    I reopened this, but the original problem was solved. However, there is still something wrong with my logic here and the way I am trying to make this work. I made quite a few changes, but the biggest change was adding a subform so that multiple items could be purchased on one form (or that is the goal). On my queries, it is returning everything in the inventory - even if no one has purchased any of them. Hope I am explaining it clearly.
     

    Attached Files:

  9. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    542
    First Name:
    Jack
    On Queries (in general)... The scope of information returned from a query can be limited through an acceptable annotation into the Fields criteria section within the query. For instance, if you wanted to dynamically display information based on a specific last name, you would find the LastName field within the Query and type [Enter Your Search Criteria] (include the brackets) into the criteria section. This will display all records that match the information entered into the pop-up box that appears when you run the query. Similarly, if you were pulling information based on a combo box within a Form, you would type [Forms]![FormName]![ComboBoxFieldName] into the criteria field.
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You also need to ensure that the Main Form & Sub Form Master/Child links are set the same as your Table Relationships.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1219373

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice