Unusual grouping problem

[Steel Banana]

Hi all! I’m new to this forum, and naturally I’m somewhat new to Access 2003.
I think my problem has an embarrassingly simple Access solution (no VBA), but
I can’t figure it out!

Each assembly (the field is called AssyNum) has a location (ShortLocation)
and many components (BOMComponent). Each component also has a location.
Currently, some components are not stored in the same location as the
assembly it goes into. I need to find which components do not share the same
location as the assembly.

Example Data:

RecordID AssyNum BOMComponent Location
1 03-34152 02-00092 5 09
2 03-34152 08-22201-WJ 5 09
3 03-34152 03-34152-TP 5 12
4 03-99659 03-99659-WJ 5 04
5 03-99659 15-66935-000-TP 5 04
6 G5-5698 G5-5698-TP 5 09
7 A17-55555-000 A17-55555-000 5 09
8 A17-55555-000 2022-0335 5 12
9 A17-55555-000 A17-55325-WJ 5 03
10 A17-55555-000 08-0001 5 09

The records I would like to collect (report, query…whatever) are 1,2,3 &
7,8,9,10

I hope that’s clear enough? Thanks for any suggestions!

 

[Golfinray]

If some assembly locations are different than the BOM locations we will need
the assembly locations to help you. I assume the location you gave us was the
BOM location?

 

[Steel Banana]

Oops. Ignore anything before the example data on my first posting; I
shouldn’t have mentioned anything about the assembly locations.

I only need to know which of the assemblies do NOT have all of the
components in the same location (I don’t care about actual assembly locations
right now). Using the given example, because the Locations of all
BOMComponents for 03-34152 are not the same, all records for 03-34152 (1,2,3)
would be selected, but the records for 03-99659 (4 & 5) would not.

Sorry for the confusion!

 

[John Spencer]

Seems to me as if you would need a DISTINCT query to get the BOM Components
and the location.

Then use that to do a count of locations and if there were more than 1
location return all the relevant records.

Something like the following.

SELECT *
FROM TheTable
WHERE TheTable.BOMComponents IN
( SELECT BOMComponents
FROM (SELECT Distinct BOMComponents, Locations
FROM TheTable)
GROUP BY BOMComponents
HAVING COUNT(*) > 1)



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Golfinray]

I am still not 100% sure of what you are trying to do, but I think you might
start with a parameter query with all your data then make assyum the
parameter you query on. It would then return all parts in the BOM form that
assembly and their locations. Do you need the part numbers that go with that
assembly also or just the location or do you just need to know if they are
all in the same location, like a yes/no?

 

[Steel Banana]

Is it possible to return all the records for a certain assembly when it's
components are stored in different locations? Yes/no won't be enough.

Notice there are multiple components ("BOMComponent") for each assembly
("AssyNum"). These components should all be in the same location ("Location")
so they are easy to reach when building the assembly, but sometimes they are
not (once built, the assembly is actually stored in a completely different
warehouse and is not important). I need to know which assemblies have
components that are not side by side on the same shelf.

How's that explanation?

 

[Steel Banana]

I don’t have much experience with SQL so please bear with me…. your query
seems to work, but my version of the code returns nothing. I think I
understand your idea, but I’m not sure if my syntax is correct:

SELECT*
FROM qrySortRawData1
WHERE qrySortRawData1.BOMComponent In (SELECT BOMComponent FROM (SELECT
Distinct BOMComponent, ShortLocation FROM qrySortRawData1) GROUP BY
BOMComponent HAVING COUNT (*) > 1);

It seems so close! I keep putting this one on the back-burner so it'll be
nice to wrap it up. Thanks again for the help.

 

[Ken Sheridan]

Chris:

Try this:

SELECT *
FROM YourTable AS T1
WHERE EXISTS
(SELECT *
FROM YourTable AS T2
WHERE T2.AssyNum = T1.AssyNum
AND T2.Location <> T1.Location);

A row will only be returned by the outer query if there is at least one row
returned by the subquery, i.e. where the assembly number is the same but the
location differs. Consequently the rows for any assembly where the
components are stored in two or more locations will be returned, but not
those where all components are stored in the same location, as in that case
the subquery would return no rows.

Ken Sheridan
Stafford, England

 

[Steel Banana]

For anyone that's curious, the problem's been solved without having to worry
about SQL or VBA. 1st I used a query to group the AssyNum and Location. 2nd I
used a find duplicates query to show me the AssyNum records that had more
than one location (all AssyNum records with the same location will be
eliminated here). So simple!

Thanks for your help.

 

[Steel Banana]

For anyone that's curious, the problem's been solved without having to worry
about SQL or VBA. 1st I used a query to group the AssyNum and Location. 2nd I
used a find duplicates query to show me the AssyNum records that had more
than one location (all AssyNum records with the same location will be
eliminated here). So simple!

Thanks for your help.