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
.....

 

[Clifford Bass via AccessMonster.com]

Hi,

What are the rules regrading the Part# field and how its contents are
structured? Please note that this is not good database design because you
are using one field to hold multiple types of information. And what happens
when you have part # x999z that is a part of the main item x123? How will
you know that they belong together? A better design might be to make the
table self-referencing: Also, get rid of the # as that complicates things.

Part
Drawing
SubPartOf

Then you would have:
x123 8978 <null>
x123a 8979 x123
x123b 8990 x123
x123c 8991 x123
x999z 8992 x123
x999y 8993 x999z

Now you would have a good way to get all the related items and you can
have a tree of parts, subparts, sub-subparts, etc. as far down as you need to
go.

Clifford Bass

 

[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)

 

[Clifford Bass via AccessMonster.com]

Hi,

Is the end item part number always exactly four characters in length?

Clifford Bass

 

[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

 

[Clifford Bass via AccessMonster.com]

Hi,

In which case, try this which should work regardless of length of the
end item part number length. It will also work if the subpart number has
more than one character after the end part number.

SELECT A.[Part#] AS Main_Assembly_Part_Number, A.[Drawing#] AS
Main_Assembly_Drawing, B.[Part#] AS Component_Part_Number, B.[Drawing#] AS
Component_Drawing
FROM tblPartsDrawings AS A, tblPartsDrawings AS B
WHERE (((B.[Part#]) Like [A].[Part#] & "?*"));

As mentioned by one of the other posters, just put it into a report to
get the desired format. If that results in some bad rows you can add one or
more like conditions to the main part side of the thing:

SELECT A.[Part#] AS Main_Assembly_Part_Number, A.[Drawing#] AS
Main_Assembly_Drawing, B.[Part#] AS Component_Part_Number, B.[Drawing#] AS
Component_Drawing
FROM tblPartsDrawings AS A, tblPartsDrawings AS B
WHERE (((A.[Part#]) Like "[A-Z]###" Or (A.[Part#]) Like "[A-Z][A-Z]##-#####")
AND ((B.[Part#]) Like [A].[Part#] & "?*"));

Hope that helps,

Clifford Bass