alternate to "Switch"

[22strider]

Hello Friends
I need your help/opinion for the following situation:
I have three separate item master tables from three different MRP systems (say MRP1, MRP2 and MRP3). Each part is either of “Type” “Buy” or “Make”. A part can be in one, two or all three MRP tables (systems).
I need to create a query that will determine which MRP system a part exists in and add information like following in a new field (say MRP Info)
“MRP1-M, MRP2-B, MRP3-M”
Earlier I had tables from two (rather than three) MRP systems and I used function “Switch”. It has been working fine but recently another table (MRP system) has been added to the mix and I need to update my query for incorporating this third MRP. I am wondering if there is a better way of doing this because there may be another expansion coming (by adding fourth table) in near future.
Thanks for your time
Rajesh

 

[jackd]

I certainly don't know your application, but it sounds like your tables are NOT normalized.

I need to create a query that will determine which MRP system a part exists in ..

How do you identify a part? Do you have unique part identification throughout your system?

 

[22strider]

Hello jackd

The tables are normalized. And they have unique identifier as well; the Part Number.

Issue is that there are three tables from three MRP different systems. If a part number is common among all three systems it represent the same item (that's why part number is the same). Different sections of the business may use different MRP systems. A part may be a Buy item for one part of the business (MRP system) and it could be Make for another part of the business (MRP system).

Thanks for your replay though
Rajesh

 

[jackd]

OK. So then what exactly is the question? What is the field called that differentiates "Type", "Make" or "Buy"?

If the parts are uniquely identified by a number/identifier, then are you looking to see

PartNumber MRPSystemIdentifier PartNumberDifferentiator

1111 MRP1 Build
1111 MRP2 Buy
1111 MRP3 Make

Perhaps, I'm missing something basic.

 

[22strider]

You had that right in your table but the complication is that, I need your result set displayed as one record. So the result set that you have, should look like:

1111 MRP1-Make,MRP2-Buy,MRP3-Make

 

[jackd]

Have you tried something like this (untested)

SELECT PartNumber,MRPSystemID & "-" & PartNumberDifferentiator as NeededValue from Mrp1Table
ORDER BY PartNumber, MRPSystemId,PartNumberDifferentiator
UNION
SELECT PartNumber,MRPSystemID & "-" & PartNumberDifferentiator as NeededValue from Mrp2Table
ORDER BY PartNumber, MRPSystemId,PartNumberDifferentiator
UNION
SELECT PartNumber,MRPSystemID & "-" & PartNumberDifferentiator as NeededValue from Mrp3Table
ORDER BY PartNumber, MRPSystemId,PartNumberDifferentiator

 

[22strider]

Won't that result in one part number repeating it self three times? As in my previous message the, the goal is to have one record for one part number.
Assuming that the part exists in all three MRP systems as buy or make; the query result should appear like following table (with two columns P/N and MRP-Type):
P/N MRP-Type
111 MRP1-Buy,MRP2-Make,MRP3-Buy

 

[jackd]

Well how about the following, but it's custom and would need change if you get 4 systems etc.

SELECT
MRP2.PartNumber
, [Mrp3].[SystemId] & "-" & [Mrp3].[Partdifferentiator] AS Expr1
, [Mrp2].[SystemId] & "-" & [Mrp2].[Partdifferentiator] AS Expr2
, [Mrp1].[SystemId] & "-" & [Mrp1].[Partdifferentiator] AS Expr3
FROM (MRP2 INNER JOIN MRP3 ON MRP2.id = MRP3.id)
INNER JOIN Mrp1 ON MRP2.id = Mrp1.id
GROUP BY MRP2.PartNumber
, [Mrp3].[SystemId] & "-" & [Mrp3].[Partdifferentiator]
, [Mrp2].[SystemId] & "-" & [Mrp2].[Partdifferentiator]
, [Mrp1].[SystemId] & "-" & [Mrp1].[Partdifferentiator];

 

[boblarson]

To put the values all on one line, try this:
http://allenbrowne.com/func-concat.html