Results 1 to 9 of 9
  1. #1
    Budro49 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    12

    Create qry to extract all variations of a base part number

    In attached qry I have two tables. Table A includes all parts in priced file. Table B are parts that have been sold and only uses the base part number without different variations like P0001,P0002, etc.



    I need to construct qry to use table B with some sort of wildcard that will pull all variations of each part from table A


    Example: Table B base part number 141A6350

    Output from Table A should be: 141A6350P0001, 141A6350P0005, 141A6350P0005, 141A6350P0009

    I need this done for each base part number in table B


    Thanks, Richard
    Attached Files Attached Files

  2. #2
    smithse is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    37
    Hi Budro49,

    How often do you need to process this amount of data? It might not seem a lot as two base tables, but writing that query will generate around 146 million rows, which I assume you want to the filter and query etc?

  3. #3
    Budro49 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    12
    Quote Originally Posted by smithse View Post
    Hi Budro49,

    How often do you need to process this amount of data? It might not seem a lot as two base tables, but writing that query will generate around 146 million rows, which I assume you want to the filter and query etc?
    I would want to run qry weekly. There will be many parts in table A that are not sold so I would think the amount generated would be much less. A lot of the part numbers are drawings not parts. That is my problem I receive this data dump and need to separate it via the qry.

  4. #4
    smithse is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    37
    Quote Originally Posted by Budro49 View Post
    I would want to run qry weekly. There will be many parts in table A that are not sold so I would think the amount generated would be much less. A lot of the part numbers are drawings not parts. That is my problem I receive this data dump and need to separate it via the qry.
    If, and only if the part number base is always 8 characters, then you could do the following:

    You could break it down into 2 queries. From what I have just done, I have 495 matches. Does this sound right?

    Create 2 queries, the first named: qryPNWithBase and containing the following SQL statement:

    SELECT [Table A].[Part Number] AS PartNumber, Left([Part Number],8) AS PNBase
    FROM [Table A];

    Create a 2nd query which is joined to the results of the first. You can name this query anything you like. It's SQL statement is:

    SELECT [Table B].[Part Number] AS PNBase, qryPNWithBase.PartNumber AS PNFull
    FROM [Table B] INNER JOIN qryPNWithBase ON [Table B].[Part Number] = qryPNWithBase.PNBase;

    The first query creates a column alongside the full length part number (if thats what you call it), and we then use this to join the part number bases in table b.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    smithse is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    37
    Hi orange,

    I avoided suggesting the cartesian join in this instance, as the result is around 146 million rows.... Access might have a little hernia... LOL

  7. #7
    Budro49 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    12
    Quote Originally Posted by smithse View Post
    If, and only if the part number base is always 8 characters, then you could do the following:

    You could break it down into 2 queries. From what I have just done, I have 495 matches. Does this sound right?

    Create 2 queries, the first named: qryPNWithBase and containing the following SQL statement:

    SELECT [Table A].[Part Number] AS PartNumber, Left([Part Number],8) AS PNBase
    FROM [Table A];

    Create a 2nd query which is joined to the results of the first. You can name this query anything you like. It's SQL statement is:

    SELECT [Table B].[Part Number] AS PNBase, qryPNWithBase.PartNumber AS PNFull
    FROM [Table B] INNER JOIN qryPNWithBase ON [Table B].[Part Number] = qryPNWithBase.PNBase;

    The first query creates a column alongside the full length part number (if thats what you call it), and we then use this to join the part number bases in table b.
    It looks right to me I will have to verify on Monday when I get to work on my work computer but I didn't expect many parts because the priced parts list was small even thought we was querying it with a huge list. Thanks and I will get back to you Monday morning.

  8. #8
    smithse is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    37
    Quote Originally Posted by Budro49 View Post
    It looks right to me I will have to verify on Monday when I get to work on my work computer but I didn't expect many parts because the priced parts list was small even thought we was querying it with a huge list. Thanks and I will get back to you Monday morning.
    I hope that it does help you. There are SQL gurus out there that will probably suggest it can be done in a single SQL statement, but when I put these examples together, I break them up so people can understand what is happening. Sometimes what I suggest isn't the only or the best approach according to some people, but "If it works for me", then I am happy LOL

  9. #9
    Budro49 is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2011
    Posts
    12
    Quote Originally Posted by smithse View Post
    I hope that it does help you. There are SQL gurus out there that will probably suggest it can be done in a single SQL statement, but when I put these examples together, I break them up so people can understand what is happening. Sometimes what I suggest isn't the only or the best approach according to some people, but "If it works for me", then I am happy LOL
    It works great! Thanks I just made a macro out of it and just run the macro to run the two qrys.

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

Similar Threads

  1. Looking for Person who can create data base
    By nemolist in forum Database Design
    Replies: 3
    Last Post: 02-02-2012, 01:42 PM
  2. Convert Number to String in .csv extract
    By CindyR19 in forum Import/Export Data
    Replies: 3
    Last Post: 08-17-2011, 02:58 PM
  3. Every Data Base I create gets corrupted
    By cvegas in forum Programming
    Replies: 2
    Last Post: 08-14-2011, 10:46 AM
  4. Replies: 2
    Last Post: 04-13-2011, 09:42 AM
  5. Part Number Lookup
    By jacobbiljo in forum Queries
    Replies: 1
    Last Post: 11-12-2009, 09:22 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