linked tables not giving query answers I need

[krakahed]

Hi all. I hope that I can explain this problem clearly so that I may be understood, so please bear with me.

I am trying to build a query that will match data from one table to data of another table with an extension added to the number. At my company we must generate a report each week for one of our work sites that shows part numbers that show inventory levels lower than the minimum, letting the purchasing people know that part number needs ordered. Our problem occurs because out of the 32,000 part numbers on file at this site, we have up to THREE part numbers that are for the same part.

Each number is an identical 7-digits for each part, with an added extention showing that part's origin. An example would be: 7449836 (base part number) 7449836-C (company owned part number) 7449836-ISA (third party part number). As we are trying to liquidate the stock from the -C and -ISA parts numbers, we need to know if when the stock gets low on the base number parts, there is a corresponding part number in the other two categories that has sufficient stock to sell.

I have started this query process by creating two make table queries. The first one is a simple conversion of the purchase report query we use to show low stock numbers into a make table. I have added a field in the original query that generates the part number's first seven digits.

(NEW PROD #: Left([PRODUCT #],7))

I did the same thing for our inventory file, so that I would have a matching number to join each table to.

I have tried left statements such as LIKE LEFT([PRODUCT #],7)AND "-C" as well as IIF statements with the same criteria, with no positive results.

I need some suggestions as to a new approach to the logic, because I am running out of ideas fast!

Any and all help is greatly appreciated!

Thanks!

 

[biggoober]

instead of LIKE LEFT([PRODUCT #],7)AND "-C"

Did you try LIKE LEFT([PRODUCT #],7) & "-C"

& is the operator for concatenation (joining strings)

 

[Robertd]

HI

Just a thought, break your problem down

try to identify the total stock of parts already on hand with a Total's query.

when you get the leftstr() function to group the items and extract the totals for the base part of the part number.

make another query that compares the totals from the first query with the reorder qty weed out the result based on your base part number.

As you get the results you need you can either leave the queries as seperate queries or combine (not always posible) them into one query.

Robert Dwyer
rdwyer@orion-online.com.au

 

[krakahed]

Thanks to BigGoober and Robertd for their replies. Yes, I did try using the '&' in the left statement. No positive results. I have been able to break the problem down somewhat using queries sorting on the part number extensions, i.e. LIKE "*-C". I've had limited success taking this approach, but it's still not been to my satisfaction. I will, however, work on the problem a little more taking that approach.

Thanks again for your replies, and more are greatly appreciated!

 

[krakahed]

I have finally come closer to my goal! It has taken a few steps, but I'm getting there. First, I have created four make table queries, breaking down the part numbers. The first is for the purchase report, the second for all '-C' parts, the third for all '-ISA' parts, and the fourth for all base number parts. In each make table query I added a column using this example:

NEW PROD #: Left([PRODUCT #],7)

This gave each table a common field to link to. I linked all the tables to the purchase report table, using the join property showing all records from the purchase report table and all matching records from the other tables. In the query, I added a column for each table's data to show the corresponding part number records that were the same as the purchase report's records using this example:

ISA PRODUCT #: IIf([PURCHASE REPORT TABLE 3]![PRODUCT #]=[PURCHASE REPORT TABLE]![PRODUCT #],Null,[PURCHASE REPORT TABLE 3]![PRODUCT #])

The only snag I have encountered is for a part number section that has a possible TWO extensions instead of one. These are for metal stamps, labeled A-Z and 0-9. An example of this part number would be 7894567-Q-ISA, which would indicate this is a stamp for the letter 'Q'. The query in its current form is jumbling the stamp records, but accurately sorting the other data.

Does anyone know how I could possibly work around this?

Any and all help is greatly appreciated!

 

[Robertd]

Hi,

maybe consider writing a function that preforms a similar function to left$ but with some smarts.

like it needs to return the first 7 digits if and only if the suffix is a simple "-C" or "-ISA"

however if the suffix has a "-*-" you would want to return the 7 digit p/n and the suffix to identify it as a metal stamp where -*- means a dash any letter or number followed by a dash. i.e. the function would act like left$ if there were only the sequence nnnnnnn-* or nnnnnnn-*** for the -C and -ISA but would return the p/n's nnnnnnn-* no mater for the actual suffix -C or -ISA or even just nnnnnn-* with no in house or third party identifier.

to do this write the function and test it then add it to your global module and use it as if it was an inbuilt function in your queries.

the only problem is would there be an nnnnnnn-C as opposed to an nnnnnnn-C like are all stamps either nnnnnnn-*-C or nnnnnnn-*-ISA, mmm X-)

hope this helps
regards

Robert Dwyer
rdwyer@orion-online.com.au