Results 1 to 9 of 9
  1. #1
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45

    Matches not being found in two tables

    I have a database of invoice lines.
    When a new batch of invoices is imported, I add additional fields to each record, such as product category.
    I have a lookup table that contains each product description, together with it’s associated category.
    After I’ve run an update query to add the categories to the new records, I then check for any blank category fields and if any are found, I add the new product description and category to the lookup table and rerun the update query.
    I’ve noticed that my lookup table contains duplicated product description records i.e. when I’ve run an update, a product does not find a match in the lookup table – even though one is there.
    I’ve checked for leading and/or trailing spaces in duplicated lookup table records and also checked the length of duplicated product descriptions and found nothing that would suggest why a match was not found.
    Any ideas as to why this is happening?
    Thanks

  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 your lookup table keyed to prevent duplicates?

    if you are trying to match on [decscription] field, then every single letter must be exactly the same. There may also be invisible characters.
    otherwise, if they are exact, they will link.
    (tho you may not be able to match MEMO ,aka Long Text) it used to be denied in old access, I dont know if they fixed it.

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

  4. #4
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    Thanks for your reply.

    I think the problem may be due to invisible characters. Is there a function like Excel's TRIM that would remove them?

  5. #5
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    No sorry I can't show any records as they are invoice lines.

  6. #6
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by keith2511 View Post
    I have a lookup table that contains each product description, together with it’s associated category.
    After I’ve run an update query to add the categories to the new records, I then check for any blank category fields and if any are found, I add the new product description and category to the lookup table and rerun the update query.
    Looks like the procedure that you describe is not properly. You should keep the categoryID and the productID in lookup table, not the product description. Also, by this way, you repeat data, that be able to change, in many tables. When a description change in table of products, you have to update the descriptions in lookup table as well.

    But, why a lookup table in many-to-many relationship? Does products belongs to many categories? If true, which category displayed in the invoice detail?

    Unfortunately, I don't have an idea about your current issue; we need more info (field types, data types etc) or some example to be able to help. Is any field multivalue type?

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Keith,

    As has been said, there is little anyone can do without seeing the issue in context and more related details.
    John has asked some specific questions, and readers will await your responses.

  8. #8
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    45
    Thanks but I'll leave it there. Bye.

  9. #9
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    one thought: it is not a good idea to use lookups for invoice lines. Invoices should always be printed as the original. If you reprint an invoice line after the product description has been changed you need to print the old one as it was on the moment the invoice was created and not with the new description or you have a legal issue.

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

Similar Threads

  1. Replies: 6
    Last Post: 01-27-2021, 02:38 AM
  2. Counting the number of found matches
    By dbri in forum Database Design
    Replies: 14
    Last Post: 12-18-2018, 06:30 PM
  3. Replies: 7
    Last Post: 02-29-2016, 06:13 PM
  4. Finding matches between two TABLES
    By DAbbot in forum Queries
    Replies: 4
    Last Post: 11-13-2015, 08:28 AM
  5. Query for not exact matches in two tables
    By FrankBone in forum Queries
    Replies: 1
    Last Post: 06-03-2015, 12:49 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