Results 1 to 6 of 6
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Query Multiple Tables for Match with wildcards

    Hello. I have an SQL problem. Let me start by saying that I am not good at complex queries (nesting, joins, etc).

    I have a table that contains order information [Orders], and a table that contains shipping information [Shipping]. I am trying to build a shipping report query that will select orders that have been shipped by comparing order numbers in each table, getting specific fields from each matched order.

    First problem in the process is that in one table [Shipping] (due to bar code scanning) has a 3 digit suffix and a 2 digit prefix. The [Orders] table has neither of these. I am not sure how to have a wild card at the beginning and end of the matching portion.

    Next, it would lessen the search if I could search [Shipping] by restricting it to one vendor [VendorName].

    Finally, I intend to export this to a text file. I though about querying into a recordset, but I do not know if I can use a recordset name instead of a table name in the .TransferText method. If not, I would have to create a temp table with the results for the report.

    As always, any assistance would be greatly aprreciated.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suggest provide some examples of how your would match orders to shipping. You say shipping has two fields - what do they look like? and how do they compare with the orderID in the orders table. e.g. is it something like


    Ordertable field =12345
    shippingtable field=XY12345ABC

    Is the ordertable field length consistent? i.e. Always 5 characters, or can they sometimes be longer or shorter
    And what are the datatypes? all numeric? some numeric, some text? some alphanumeric?

    So provide examples of all the different variations you need to cater for

    Since you say you are not good with complex queries, always better to provide your actual table and field names

  3. #3
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    This is correct:

    Ordertable field =12345
    shippingtable field=XY12345ABC

    Neither of them are constant length, or will not be forever, once the numbering rolls over to a 6-digit number. Other vendors have longer or shorter numbers so basically the wildcards need to account for anything on either side of the string for Orders.OrderNo. I figured that its best to account for this in the beginning. That way it would be more easily adaptable for other vendors should the need arise.

    Orders.WONo and Shipping.OrderNo are Short Text (some vendors have order numbers with a letter in them).

    I will have to sterilize (remove data) the table before posting, as it contains client information. Here are the fields that have a bearing on this is.

    (All field are Short Text.)
    Shipping Field names: Shipping.OrderNo, Shipping. TrackingNo, Shipping.Company
    Orders Field Name: Orders.WONo, Orders.WOSeq, Orders.ProdID, Orders.QtyOrdered, Orders.WOStatus, Orders.DropReturn, Orders.Filler, Orders, Orders.ExpShipMeth

    I know that having the same information in two different tables is not good structure, but is there a reason not to add fields Orders.OrderShipped and Orders.OrderTracking, then just update these two fields when the shipping department makes the entry into the Shipping table? Or would this be bad form for the sake of simplicity?

    As a side not, I also have to get a count of the number of records that matched between the tables.





  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can create a match by using a non standard join

    SELECT *
    FROM Orders INNER JOIN Shipping ON val(mid(shipping.ordero,3))=orders.orderno

    Non standard joins cannot be shown in the query builder window so have to be created in the SQL window

    Another way would be to create a separate query for the shipping to extrapolate the order number

    SELECT *, val(mid(shipping.ordero,3)) as sOrder
    FROM Shipping

    then join that query to your orders table

    SELECT *
    FROM Orders INNER JOIN qryShipping ON shipping.sOrdero=orders.orderno


    Because the shipping orderno is a calculated value this could be quite slow for large datasets. It would be better to add fields to the shipping table to store the orderno, plus prefix/suffix if required for other reasons.

    EDIT: I should have stuck to insisting you providing all the different variations you need to cater for. You said

    This is correct:

    Ordertable field =12345
    shippingtable field=XY12345ABC

    Neither of them are constant length, or will not be forever, once the numbering rolls over to a 6-digit number.
    which is what the above solution handles.

    But later you say

    Orders.WONo and Shipping.OrderNo are Short Text (some vendors have order numbers with a letter in them)
    So now I'm confused - does the order number roll over to a 6 digit number? If the vendors order numbers can have text in them, then they are not a number and cannot roll up.

    So if your first quote is correct, my suggestion stands. However if your second quote is correct, then we need to start again - and this time provide examples of all the different variations you need to cater for

  5. #5
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Thanks. I will give these a try.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    have just edited my post - please reread

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

Similar Threads

  1. Replies: 4
    Last Post: 07-13-2017, 09:41 AM
  2. Replies: 1
    Last Post: 11-30-2016, 03:41 AM
  3. Query to match data from two tables
    By mpreston14 in forum Queries
    Replies: 4
    Last Post: 10-30-2014, 03:09 PM
  4. Replies: 14
    Last Post: 05-09-2013, 06:53 AM
  5. Replies: 14
    Last Post: 02-12-2012, 10:14 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