SQL Query Headache

P

poppy

Hi Experts

I'm so seriously sick of this query now - I hope you can help m
otherwise I will no longer have any hair left. I realize that this i
not a sql forum, but I thought to take a chance and see if there migh
be someone who can help me here.

I have the following two queries:
select a.group_descr, sum(b.qty) as qty, sum(b.value_incl_vat - b.vat
as value from py_group a
left outer join cb b on
a.py_mat_no = b.py_mat_no
and month(b.inv_date) = 01
and year(b.inv_date) = 2004
and upper(hstatus) <> 'D'
group by a.group_descr
order by a.group_descr

-----------------------------------------------------------------------
And
-----------------------------------------------------------------------
select group_descr, sum(a.qty) as qty, sum(a.value_incl_vat - a.vat) a
value from cb a
left outer join chain_group b on
a.mat_no = b.product_code
where month(a.inv_date) = 01
and year(a.inv_date) = 2004
and upper(hstatus) <> 'D'
and a.mat_group in ('CBS04', 'CBS09', 'CBS12', 'CBS14', 'CBS22'
'CBS42', 'CBS95',' CBS96')
group by group_descr
order by group_descr

My problem is that I need to join these two queries into one query s
they can extract data into excel. I tried using union like this:

select a.group_descr, sum(b.qty) as qty, sum(b.value_incl_vat - b.vat
as value from py_group a
left outer join cb b on
a.py_mat_no = b.py_mat_no
and month(b.inv_date) = 01
and year(b.inv_date) = 2004
and upper(hstatus) <> 'D'
group by a.group_descr
union select group_descr, sum(a.qty) as qty, sum(a.value_incl_vat
a.vat) as value from cb a
left outer join chain_group b on
a.mat_no = b.product_code
where month(a.inv_date) = 01
and year(a.inv_date) = 2004
and upper(hstatus) <> 'D'
and a.mat_group in ('CBS04', 'CBS09', 'CBS12', 'CBS14', 'CBS22'
'CBS42', 'CBS95',' CBS96')
group by group_descr
order by group_descr

--------------------------------------------------------------------------
But this is not what I'm looking for. I would like the program to rea
the first query then populate excel with the resulting data, then rea
the next query and dump the resulting data INTO THE NEXT EMPTY colum
in the same sheet AND MAKE SURE that the group_descr match, fo
example:


Code
-------------------
For example
Instead of this:

Both query results

A B C
January
Group_descr Qty Value
Matchs 45 56.98 (From Table 1)
Matchs 3 23.10 (From Table 2)
Firesticks 20 234.4 (From Table 1)
Firesticks 12 45.3 (From Table 2)


-------------------------------------------------------------------------

This is what I want to happen

First query result Second query result

A B C D E
January
Group_descr Qty Value Qty Value
Matchs 45 56.98 3 23.10
Firesticks 20 234.4 12 45.3
 
A

arno

Hi poppy,
I'm so seriously sick of this query now - I hope you can help me
otherwise I will no longer have any hair left.
Click to expand...

Hmm, your not gonna die withou hair ;)

Have a try with something like

select * from (select * from table1)

Here, your first query is the "from"-part of your second query.

regards

arno
 
P

poppy

Hi arno

This is the modifications carried out on my code:

------------------------------------------------------------------------------------
SELECT Group_A.group_descr, Group_A.qty, Group_A.[Value], Group_B.qty
Group_B.[Value]
FROM
(SELECT a.group_descr, SUM(b.qty) AS qty, SUM(b.value_incl_vat - b.vat

AS [Value] FROM py_group a
LEFT OUTER JOIN cb b
ON a.pymat_no = b.py_mat_no AND Month(b.Inv_date) = 01
AND Year(b.inv_date) = 2004 and Upper(hstatus) <> 'D'
GROUP BY a.group_descr) AS Group_A

(SELECT a.group_descr, SUM(a.qty) AS qty, SUM(a.value_incl_vat - a.vat

AS [Value] FROM cb a LEFT OUTER JOIN chain_group b
ON a.mat_no = b.product_code AND Month(a.Inv_date) = 01
AND Year(a.inv_date) = 2004 and Upper(hstatus) <> 'D'
AND a.mat_group IN
('CBS04','CBS09','CBS12','CBS14','CBS22','CBS42','CBS95',' CBS96')
GROUP BY a.group_descr) AS Group_B

WHERE Group_B.descr = Group_descr
ORDER BY a.group_descr
------------------------------------------------------------------------------------

When I try to run both queries I get the this error in line 10
"Incorrect syntax near the keyword 'SELECT' " and in line 16 thi
error: "Incorrect syntax near ')' "

When I try to run the queries on thier own I get this error for th
first query in line 1: "The column prefix 'Group_B' does not match wit
a table name or alias name used in the query."

And the second query gives me this error in line 7:"Incorrect synta
near the keyword 'AS'"

I would reallly apprciate some more help please.

Thanx

Kind Regard
 
A

arno

Hi poppy,

I really have troubles to understand what your're doing in your
queries. However, I'd suggest that you recreate your sql-statement
step by step. Leave out any aliases ("AS") that you do not really
need.

Create your first query, if you cannot make it work then you should
post your question in an Access newsgroup.

So, when this first query (here, lets call the whole query "q1") works
as you want then change it to exactly
select * from (q1)
and see what happens. Then change the * to the fields you want, see
what happens, then add your where clauses, then add the sum-syntax for
your fields and add group by.

Look at the results you get during that process, esp. the field names.
You have to use them to finally get your result.

regards

arno
 
J

Jamie Collins

poppy wrote ...
This is the modifications carried out on my code:

------------------------------------------------------------------------------------
SELECT Group_A.group_descr, Group_A.qty, Group_A.[Value], Group_B.qty,
Group_B.[Value]
FROM
(SELECT a.group_descr, SUM(b.qty) AS qty, SUM(b.value_incl_vat - b.vat)

AS [Value] FROM py_group a
LEFT OUTER JOIN cb b
ON a.pymat_no = b.py_mat_no AND Month(b.Inv_date) = 01
AND Year(b.inv_date) = 2004 and Upper(hstatus) <> 'D'
GROUP BY a.group_descr) AS Group_A

(SELECT a.group_descr, SUM(a.qty) AS qty, SUM(a.value_incl_vat - a.vat)

AS [Value] FROM cb a LEFT OUTER JOIN chain_group b
ON a.mat_no = b.product_code AND Month(a.Inv_date) = 01
AND Year(a.inv_date) = 2004 and Upper(hstatus) <> 'D'
AND a.mat_group IN
('CBS04','CBS09','CBS12','CBS14','CBS22','CBS42','CBS95',' CBS96')
GROUP BY a.group_descr) AS Group_B

WHERE Group_B.descr = Group_descr
ORDER BY a.group_descr
------------------------------------------------------------------------------------

When I try to run both queries I get the this error in line 10:
"Incorrect syntax near the keyword 'SELECT' " and in line 16 this
error: "Incorrect syntax near ')' "
Click to expand...

I think you merely missing a comma. Replace

AS Group_A

with

AS Group_A,

Jamie.

--
 
Top