2 tables 1 Query

[Maracay]

Hi Guys,

I have 2 tables both are the same fields, I want to put them in 1 query, but
when I include the numbers fields, and use the Total: Sum some of the total
makes no sense, look like repeating values in one side if the other side has
more records. How can I solve this problem.

PS: what I want to do I to a report showing the data from both tables but
the pounds needs to be in the same line. Ex

Material code Total Pounds (Table 1) Total Pounds
(Table 2)

Thanks

 

[John W. Vinson]

Hi Guys,

I have 2 tables both are the same fields, I want to put them in 1 query, but
when I include the numbers fields, and use the Total: Sum some of the total
makes no sense, look like repeating values in one side if the other side has
more records. How can I solve this problem.

PS: what I want to do I to a report showing the data from both tables but
the pounds needs to be in the same line. Ex

Material code Total Pounds (Table 1) Total Pounds
(Table 2)

Thanks

It sounds like what you want is a UNION query - see the online help for UNION
- but you don't post enough information about your table structure for that to
be more than a guess.

Why on Earth would you have two tables with the same fields?

 

[Maracay]

OK forget what I said before, this is the whole story

I have one table with

ActvityID: values are 1, 10…20
MaterialID: is just a code to identify different materials
Quantity: Quantity Produced of the material

Normally I use ActvityID Value 1 in some reports and what is not a 1 in
another reports, that why I created 2 queries 1 with ActvityID = 1 and
another with ActvityID<>1 to do reporting more easily.

But now I need to create a report to compare the Quantities Actvity = 1 Vs
Actvity <> 1 per material, must be totals no detail information, the report
should look like that. EX

Material, Actvity 1, Actvity <> 1
MAT001 100 150
MAT001 140 120

This look easy but I don’t know how to do it, any help will be appreciated.

 

[John W. Vinson]

OK forget what I said before, this is the whole story

I have one table with

ActvityID: values are 1, 10…20
MaterialID: is just a code to identify different materials
Quantity: Quantity Produced of the material

Normally I use ActvityID Value 1 in some reports and what is not a 1 in
another reports, that why I created 2 queries 1 with ActvityID = 1 and
another with ActvityID<>1 to do reporting more easily.

But now I need to create a report to compare the Quantities Actvity = 1 Vs
Actvity <> 1 per material, must be totals no detail information, the report
should look like that. EX

Material, Actvity 1, Actvity <> 1
MAT001 100 150
MAT001 140 120

This look easy but I don’t know how to do it, any help will be appreciated.

And I take it there are some materials with an Activity 1 and nothing in the
other activities, or vice versa? If so you need a "full outer join self-join
query"... nontrivial but doable!

Untested air code here:

SELECT A.Material, Sum(A.Quantity) AS [Activity 1], Sum(B.Quantity) AS
[Activity <> 1]
FROM tablename AS A
LEFT JOIN tablename AS B
ON A.Material = B.Material
WHERE A.Activty = 1
AND B.Activity <> 1
GROUP BY A.Material
UNION
SELECT A.Material, Sum(A.Quantity) AS [Activity 1], Sum(B.Quantity) AS
[Activity <> 1]
FROM tablename AS A
RIGHT JOIN tablename AS B
ON A.Material = B.Material
WHERE A.Activty = 1
AND B.Activity <> 1
GROUP BY B.Activity;