Results 1 to 11 of 11
  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108

    Creating Inner joins

    I have never created inner joins before but I think that's what I need to do. Do they need to be coded or can access build them for me?



    I have a table, let's call it tbl_Orders and a query qry_Vendors pulling data from another table

    My table tbl_Orders contains a field [Vendor_Name] and my query contains a field [Vendor_ID]

    The data in [Vendor_Name] is like "123456" or "R56845", the data in [Vendor_ID] is like "VendorName 123456" or "VendorName R56845"

    Basically [Vendor_Name] from the table is the vendor ID and [vendor_ID] from the query is the full vendor name and it's numerical ID.

    I am pulling data from tbl_Orders but instead of showing the vendor numerical ID "123456" from that table, I want to match "123456" with "VendorName 123456" from my query to display the full vendor name and its ID instead if just the ID.

    Thank you

  2. #2
    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
    Can you show us the SQL for the query?
    It is advisable in relational database to store values at the atomic level -- that is one fact, one field.
    Why does Vendor_ID have values "VendorName 123456" or "VendorName R56845"?
    If Vendor_ID (let's say the 123456 or R56845) is the identifying field for the record, then there is no need to include "VendorName" in this field.
    Inner Join (all Joins) must be done on fields of same data type, and if you are trying to match these they need to have a domain in common.

    Do you have a statement of requirements or a sample database that you can share?

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    by having the mismatch your query will be slow compared with a 'normal' query

    based on the examples provided the simplest solution would be to modify your qry_Vendors to extract and return the name?, then you can join on it

    VendorName:mid(vendor_ID,instrrev(vendor_ID," ")+1)

    then you can join vendor_name in your table to the vendorName in the query

    I have to say your naming convention seems the wrong way round but its your app.



  4. #4
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    The query is at this stage just a selection, there is nothing to it. On criteria prompt I type in the PO number I want to look up and it returns the details such as items, order qty, PO date and vendor.

    In these details it would say the vendor name is "123456" or whatever code but I want to know who the actual vendor name is, its code, which is a text, is not useful to me. But if I see "Coca Cola 123456", then I know that PO was sent to Coca Cola.

    The two tables or the one table and the vendor query pulling from the other table have in common that "123456" code, it can be any text or numbers without any logic behind it. The reason they are different between the two data sets is because they come form different systems and in one of the systems we added the vendor name in front of the vendor ID for another use.

    Unfortunately I can't share the db and it's quite large anyway too.

  5. #5
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    I know the naming is confusing, because they are not the same databases (ODBC links to SQL servers), not built by the same people and years appart. I'm trying to extract data from multiple databases for my own day to day use precisely because the systems are antiquated and not very practical.

    The qry_Vendors just returns the vendor names as it is, it's a selection on [Vendor_ID], no duplicates.

    So in the query that is pulling orders details, I'm hoping to match the [Vendor_Name] value which is just a code, with the [Vendor_ID] value which is the full name of the vendor plus the vendor code (also known as Vendor_Name) and display that instead of just the code.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    so try my suggestion

  7. #7
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Quote Originally Posted by Ajax View Post
    so try my suggestion
    I am trying to figure out your suggestion unsuccessfully...

    VendorName:mid(vendor_ID,instrrev(vendor_ID," ")+1) returns the vendor "code" so if my vendor is "Coca Cola 012345", it returns "012345"

    I am extracting data from a table in which the vendor name is in the form of "012345". Instead of showing "012345", I want to show "Coca Cola 012345" and that information is in my qry_Vendors.

    So in my new query it would be like something like

    Field: Vendor ID (from qry_Vendors)
    Criteria: "where Vendor_Name (from the table) contains the same partial string as in Vendor ID. In this example "where Vendor_Name contains "012345"



  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I am trying to figure out your suggestion unsuccessfully...
    so you have completed the first part - I presume in your query 'qry_Vendors'. Now in a new query join qry_vendors to tbl_orders on vendorname and vendor_name

  9. #9
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Quote Originally Posted by Ajax View Post
    so you have completed the first part - I presume in your query 'qry_Vendors'. Now in a new query join qry_vendors to tbl_orders on vendorname and vendor_name
    Yes but that's where I'm having trouble. Trying to write something like

    SELECT [Vendor_ID] FROM qry_Vendors WHERE [dbo_t_Scheduled_Order].[Vendor_Name] LIKE *[qry_Vendors].[VendorName]*

    My exact expression is as following, it returns no results

    SELECT qryVendors.[Vendor ID]
    FROM qryVendors, dbo_t_Scheduled_Order
    WHERE (((qryVendors.VendorName) Like [dbo_t_Scheduled_Order].[Vendor_Name]))
    GROUP BY qryVendors.[Vendor ID];

    or this

    Click image for larger version. 

Name:	Capture.JPG 
Views:	13 
Size:	38.2 KB 
ID:	38980

  10. #10
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    I made this work now, partially.

    However I have a problem with the vendor name last 6 digits extraction "VendorName:mid(vendor_ID,instrrev(vendor_ID," ")+1)"

    Some vendors in the table are in a numerical form rather than a combination of letters and numbers. When there is a numerical VendorName, the name (which is the ID) is preceded by 4 zeros.

    However, in the other table where the vendor query pulls the full vendor name (aka Vendor ID), the zeros have been stripped already.

    For example in table 1 it would be "0000142719"

    In table 2 it would be "COCA COLA 142719" instead of "COCA COLA 0000142719"

    The query is looking for a value equal to a string but when the value is on one side a 6 digits number and on the other side a 10 digits number (where the first 4 are zeros), it does not find a match.

    The current query SQL selection statement is
    Code:
    SELECT DISTINCT Forecasts.[Vendor ID], Mid([vendor ID],InStrRev([vendor ID]," ")+1) AS VendorName
    How can I rewrite this to be like

    IF SELECT DISTINCT Forecasts.[Vendor ID], Mid([vendor ID],InStrRev([vendor ID]," ")+1) AS VendorName IS NUMERICAL
    THEN EXTRACT THE 6 DIGITS AND ADD 4 ZEROS IN FRONT

    Or, in the other query that is looking for the match, how could I tell it to look for a partial match of the last 6 characters only?

    The current statement is as on the screen shot, I would need to change what is highlighted.

    So in the numerical example [dbo_t_Scheduled_Order].[Vendor_Name] is looking for a value of "0000142719" in a query where the value is "142719" then does not find the match.
    When the vendor name is like "COCA COLA C42719" then the vendor query returns C42719 as VendorName and the vendor name in table 1 is C42719 so it finds the match.


    Click image for larger version. 

Name:	Capture.JPG 
Views:	8 
Size:	44.2 KB 
ID:	39046

  11. #11
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    I fixed this too, closing the thread now, I replace the above expression with this below, it was easier than I thought.

    Click image for larger version. 

Name:	Capture.JPG 
Views:	8 
Size:	29.4 KB 
ID:	39047

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

Similar Threads

  1. just not getting joins
    By jmuirman1 in forum Queries
    Replies: 4
    Last Post: 06-11-2018, 06:54 AM
  2. Two joins in a report
    By apk19 in forum Reports
    Replies: 5
    Last Post: 06-05-2018, 01:03 AM
  3. mutiple joins
    By mothermugger in forum Queries
    Replies: 1
    Last Post: 11-15-2011, 04:06 PM
  4. Joins
    By jlgray0127 in forum Forms
    Replies: 2
    Last Post: 11-11-2011, 05:04 PM
  5. Need Help with Joins
    By usa_dreamer2002 in forum Queries
    Replies: 3
    Last Post: 01-31-2011, 10:58 AM

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