Finding the "parent" of the members of a group

[strider235]

Hi,
I've written a number of different types of queries over the last few years, but I'm drawing a blank on this one. I have to reconstruct some information that has been "exploded". Basically I have some parts that may be part of an assembly. Each record has the following fields:

PartNumber, AssemblyNumber, StepNumber

that has been extracted from a Bill of Materials table and a Work Order table.
For a range of step numbers (200 - 299) all parts associated with that step are part of a single assembly. However, each part can be a member of a number of different assemblies. So, for all the parts in a particular step (200 - 299) find the assembly that they all belong to.

E.g.: PartNum AssemblyNumber Step
1 A 200
1 B 200
2 A 200
2 C 200
3 A 200
3 C 200
4 B 210
4 D 210
5 D 210
For step 200, the correct assembly is 'A'. For step 210, it is 'D'.
Thanks!

 

[mp9]

If you can explain in your example why the correct assembly for step 200 ISN'T B or C, and why the correct assembly for step 210 ISN'T B, then we might be able to help.

 

[strider235]

So, for all the parts in a particular step (200 - 299) find the assembly that they all belong to.
"
For step 200, parts 1, 2, and 3 all belong to assembly A. For step 210, parts 4 and 5 both belong to assembly D.

 

[mp9]

Don't think Access (or Jet SQL, to be precise) is too well geared for parts explosions like this. About the ebst I can offer is a crosstab query like this:

TRANSFORM CBool(Count(YourTableNameHere.AssemblyNum) Is Not Null) AS AssemblyNumPresent
SELECT YourTableNameHere.AssemblyNum, YourTableNameHere.PartNum
FROM YourTableNameHere
GROUP BY YourTableNameHere.AssemblyNum, YourTableNameHere.PartNum
PIVOT YourTableNameHere.Step;

In the query designer you can format the AssemblyNumPresent expresion as True/False to get nice-looking results.

http://www22.brinkster.com/accessory/