Prev: BrowseTo command
Next: Late Binding worked until Access 2007
From: Nuno Gomes on 18 Feb 2010 13:02
Hello,

If i use this query:
=================================================
select S.fam_code FAM, S.fam_nom NOME,
(
select sum(SB.vn_atelier)+sum(SB.vn_loja) from roa_stat01_det_f SB
where SB.dt>='20100101' and SB.dt<='20100104' and SB.fam_code=S.fam_code
)[VN PDV]
from roa_stat01_det_f S
where S.dt>='20100101'
and S.dt<='20100104'
group by S.fam_code, S.fam_nom
order by 1

The result is the following:
01 LUBRUFICANTES 2805.6100
02 MANUTENCAO/REP. 2574.3900
03 ELECTRICIDADE 6298.5900

=================================================

But i need change the query to: (change SB.dt>=S.dt and SB.dt<=S.dt)
=================================================
select S.fam_code FAM, S.fam_nom NOME,
(
select sum(SB.vn_atelier)+sum(SB.vn_loja) from roa_stat01_det_f SB
where SB.dt>=S.dt and SB.dt<=S.dt and SB.fam_code=S.fam_code
)[VN PDV]
from roa_stat01_det_f S
where S.dt>='20100101'
and S.dt<='20100104'
group by S.fam_code, S.fam_nom
order by 1

And the result now is:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'S.dt' is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'S.dt' is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause.

I change the query again to (add group by S.fam_code, S.fam_nom, s.dt):
select S.fam_code FAM, S.fam_nom NOME,
(
select sum(SB.vn_atelier)+sum(SB.vn_loja) from roa_stat01_det_f SB
where SB.dt>=S.dt and SB.dt<=S.dt and SB.fam_code=S.fam_code
)[VN PDV]
from roa_stat01_det_f S
where S.dt>='20100101'
and S.dt<='20100104'
group by S.fam_code, S.fam_nom, s.dt
order by 1

But the group by doesn't work as i wich. The result is:
01 LUBRUFICANTES 1000.00
01 LUBRUFICANTES 950.21
01 LUBRUFICANTES 500.00
01 LUBRUFICANTES 355.4
02 MANUTENCAO/REP. 766.66
02 MANUTENCAO/REP. 1100.01
02 MANUTENCAO/REP. 500.55
02 MANUTENCAO/REP. 207.17
03 ELECTRICIDADE 78.87
03 ELECTRICIDADE 1234.56
03 ELECTRICIDADE 2455.55
03 ELECTRICIDADE 2529.61

I need the result as the initial query:
One row per S.fam_code and S.fam_nom.


Can you help me?
Thanks.


Nuno Gomes



From: Salad on 18 Feb 2010 16:18
Nuno Gomes wrote:

> Hello,
>
> If i use this query:
> =================================================
> select S.fam_code FAM, S.fam_nom NOME,
> (
> select sum(SB.vn_atelier)+sum(SB.vn_loja) from roa_stat01_det_f SB
> where SB.dt>='20100101' and SB.dt<='20100104' and SB.fam_code=S.fam_code
> )[VN PDV]
> from roa_stat01_det_f S
> where S.dt>='20100101'
> and S.dt<='20100104'
> group by S.fam_code, S.fam_nom
> order by 1
>
> The result is the following:
> 01 LUBRUFICANTES 2805.6100
> 02 MANUTENCAO/REP. 2574.3900
> 03 ELECTRICIDADE 6298.5900
>
> =================================================
>
> But i need change the query to: (change SB.dt>=S.dt and SB.dt<=S.dt)
> =================================================
> select S.fam_code FAM, S.fam_nom NOME,
> (
> select sum(SB.vn_atelier)+sum(SB.vn_loja) from roa_stat01_det_f SB
> where SB.dt>=S.dt and SB.dt<=S.dt and SB.fam_code=S.fam_code
> )[VN PDV]
> from roa_stat01_det_f S
> where S.dt>='20100101'
> and S.dt<='20100104'
> group by S.fam_code, S.fam_nom
> order by 1
>
> And the result now is:
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'S.dt' is invalid in the select list because it is not contained in
> either an aggregate function or the GROUP BY clause.
> Server: Msg 8120, Level 16, State 1, Line 1
> Column 'S.dt' is invalid in the select list because it is not contained in
> either an aggregate function or the GROUP BY clause.
>
> I change the query again to (add group by S.fam_code, S.fam_nom, s.dt):
> select S.fam_code FAM, S.fam_nom NOME,
> (
> select sum(SB.vn_atelier)+sum(SB.vn_loja) from roa_stat01_det_f SB
> where SB.dt>=S.dt and SB.dt<=S.dt and SB.fam_code=S.fam_code
> )[VN PDV]
> from roa_stat01_det_f S
> where S.dt>='20100101'
> and S.dt<='20100104'
> group by S.fam_code, S.fam_nom, s.dt
> order by 1
>
> But the group by doesn't work as i wich. The result is:
> 01 LUBRUFICANTES 1000.00
> 01 LUBRUFICANTES 950.21
> 01 LUBRUFICANTES 500.00
> 01 LUBRUFICANTES 355.4
> 02 MANUTENCAO/REP. 766.66
> 02 MANUTENCAO/REP. 1100.01
> 02 MANUTENCAO/REP. 500.55
> 02 MANUTENCAO/REP. 207.17
> 03 ELECTRICIDADE 78.87
> 03 ELECTRICIDADE 1234.56
> 03 ELECTRICIDADE 2455.55
> 03 ELECTRICIDADE 2529.61
>
> I need the result as the initial query:
> One row per S.fam_code and S.fam_nom.
>
>
> Can you help me?
> Thanks.
>
>
> Nuno Gomes
>

Personally, I'd avoid the subselect. Instead I might create a second
query like
select fam_code, dt, vn_atelier)+.vn_loja As SumVal from roa_stat01_det_f

Then the first query could link the family codes between the table
roa_stat01_det_f and the query and set the criteria. Sometimes
subselects can be slow in Access and this method mugh speed it up.
 | 
Pages: 1
Prev: BrowseTo command
Next: Late Binding worked until Access 2007