Adding Certain Values in Two Tables WITHOT Duping Records

[BTilson]

Ok, lets see if I can describe this enough to get my problem across.

I have two tables, each with equivalent, but different, data. What these tables are is the material requirements from our company for two different customers. The first field in the table will be the same in both tables, since this is the material ID number. For clarification, let me illustrate what I mean.

TABLE ONE:

MatCode     Week1Req    Week2Req
--------------------------------
456-182     117         189
452-192     132         173

TABLE TWO:

MatCode     Week1Req   Week2Req
-------------------------------
456-182     68         14
452-192     90         88

After adding the two tables, I want the results to be something like this:

RESULT TABLE:

MatCode     Wk1Req    Wk2Req
----------------------------
456-182     185       203
452-192     222       261

Thus far this doesn't seem that confusing, but it gets worse. Each material can produce many different parts, and the requirements sent in from our customers are in parts instead of amount of material, therefore, there can be more than one row in each table with the same material code, which makes things much more complicated.

Anyone have any idea how I can approach this problem? At the moment, I am at a total loss, so any sort of advice or suggestions of any sort will be grealy appreciated.

Thanks.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[PHV]

Something like this ?
SELECT MatCode, SUM(Week1Req) AS Wk1Req, SUM(Week2Req) AS Wk2Req
FROM (
SELECT MatCode, Week1Req, Week2Req FROM [TABLE ONE]
UNION
SELECT MatCode, Week1Req, Week2Req FROM [TABLE TWO]
) U
GROUP BY MatCode;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

My first question would be: Why aren't your tables normalized? Read 'The Fundamentals of Relational Database Design'

Secondly, to solve this scenario, I would use a union query to combine the information and normalize it:

SELECT MatCode, Wk1Req, "Week 1" As ReqTime From Tablename1
UNION
SELECT MatCode, Wk1Req, "Week 1" From TableName2
UNION
SELECT MatCode, Wk2Req, "Week 2" From Tablename1
UNION
SELECT MatCode, Wk2Req, "Week 2" From TableName2

now, when you run this query, you will have a normalized result set. If you save this query as qryNormalized then you can do:

SELECT MatCode, SUM(Wk1Req) As Wk1Req, SUM(Wk2Req) As Wk2Req FROM qryNormalized

and this query will give you the results as stated.

Now you mention that this gets worse, if you add some more details about that issue, perhaps we can create a query for you that normalizes ALL your data.

HTH




Leslie