creating a query that lists drawings of a part #

[chynewalker]

i have a table built that has the following

Part# drawing#

x123 8978
x123a 8979
x123b 8990
x123c 8991

I need to make a report that will give me the following:

main assembly Part# main ass.drawing # This would be 8978
component drawing # this would be 8979
component drawing # this would be 8990
.....

 

[Lynn Trapp]

Without knowing more, this looks a lot like a bill of materials. For this
you will need another field in your table that can be used to relate each
record to the next level up. For Example:

DrawingID Part# Drawing# ComponentID
1 123 8978
2 123a 8979 1
3 123b 8990 2
4 123c 8991 2

You would need, then to do a self join query that connects the component ID
field to the drawing ID field

 

[Daryl S]

Chynewalker -

If you can rely on the fact that the main part number ends in a numeric, and
that all the component part numbers start with the main component, then you
can write a query like this (substitute your table name and field names):


SELECT tblName.PartNum, tblName_1.PartNum, tblName_1.DrawingNum
FROM tblName, tblName AS tblName_1
WHERE (((tblName_1.PartNum) Like [tblName]![PartNum] & "*") AND
((IsNumeric(Right([tblName]![PartNum],1)))=True));

That will show the main part number in the first column for all rows, but if
you want it formatted, you can base a report on the query.

 

[chynewalker]

all the part numbers are set up in a standard format such as:
x123 this being the "end item"
x123a is a component of x123

there is never a case where x99z would be part of the main item x123

yes this is basically a bill of material

yes i know this is not a good way to organize the data, but this is the way
the table was given to me, (there is about 20 years of data this way already)

 

[chynewalker]

nope, infact i just used a f digit number because i didnt want to type out
alot of digits, a real part number we use would be like this: xy99-01564