If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
2 tables 1 Query
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 |
#2
|
|||
|
|||
2 tables 1 Query
On Fri, 28 Nov 2008 14:21:01 -0800, Maracay
wrote: 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? -- John W. Vinson [MVP] |
#3
|
|||
|
|||
2 tables 1 Query
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 ActvityID1 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" wrote: On Fri, 28 Nov 2008 14:21:01 -0800, Maracay wrote: 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? -- John W. Vinson [MVP] |
#4
|
|||
|
|||
2 tables 1 Query
On Sat, 29 Nov 2008 09:28:04 -0800, Maracay
wrote: 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 ActvityID1 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 he 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; -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|