Acumular na coluna access

[Italiano]

OI tudo bem, tenho uma planilha como vou mestrar abaixo:

CLIENTE VENDA

a 12

b 11

c 8

d 5

e 4

Quero ter uma consulta com apenas os que representam 80% da receita, para isso acho que um caminho poderia ser criar uma coluna a acumular os valores, apose classificar em ordem decrescente, assim

CLIENTE VENDA ACUMULADO

a 12 12

b 11 12 + 11 = 23

c 8 23 + 8 = 31

d 5 31 + 5 = 36

e 4 36 + 4 = 40

ai no caso, filtrar os que representam 80% da receita ficaria mais fácil, é só falar os menores que 0,80* 40 = 32 no acumu assim só o primeiro iria aparecer.

o problema está em fazer esta coluna acumulada, não sei como fazer.

Tem uma função em SQL (TOP), porém não atende minha nescessidade, tem alguma função que pode classificar assim como o TOP, porém em porcentagem?

Se alguem tiver outro jeito de fazer isso, também é bem vindo...

veleu....

[EduardoS]

O MS-SQL possui "TOP 80 PERCENT", já testou isso no Access?

[Italiano]

Não sabia desta, vou testar....

[Clemente Silva]

Eduardo, a instrução "TOP PERCENT" existe também no engine "Jet SQL" usado pelo Access, mas nesse caso ela retornaria um percentual do total de registros, ou seja: ela não vai usar como critério o cálculo do percentual de um determinado campo, como quer o colega Italiano.

Quanto ao problema do colega, só não entendi uma coisa: é pra mostrar os clientes cuja receita representa 80% de todo o faturamento?

Se é assim, aí complica, pois teria que fazer 2 consultas: primeiro fazer uma consulta para levantar o total do faturamento e calcular os 80% (fácil) e depois aí sim fazer outra filtrando os clientes cuja soma alcançam esse valor (fácil também).

Não dá pra fazer tudo numa consulta só, pelo seguinte: se você for usar uma função agregada "SUM" no campo que tem os valores, e também adicionar o campo de clientes na consulta, o engine do SQL vai primeiro agrupar todos os clientes iguais, e depois retornar a soma por cliente.

Aí poderia fazer algo assim:

SELECT Cliente, Venda, Acumulado 
FROM Tabela
GROUP BY Cliente, Venda, Acumulado
HAVING (Acumulado>=(Sum(Acumulado)*0.8));

Então se você for calcular percentual desse resultado, vai estar calculando sobre o total do cliente, e não do faturamento total.

É por isso que primeiro você tem que obter o total do faturamento primeiro, em uma consulta separada.

Acho que é isso...

[Italiano]

Amigo Clemente, o q você falou é perfeito, bom, fiz uma consulta que soma tudo coloquei esta consulta junto com a consulta da tabela de cliente, e ainda coloquei p mostrar em todas as linhas.

P evitar problemas abri uma nova consulta, (consulta da consulta, sabe como é, não quero que de pau nesta consulta, se fizer e funcionar, ótimo, ai faço na outra com mais calma). A coluna Class.SumOFTotal_ é essa coluna que soma tudo.

O codigo ficou assim :

SELECT Class.COD_Cliente, Class.Cliente, Class.TIPO, Class.Vendedor, Class.Descrição, Class.[FAT Medio FY Anterior], Class.[FAT_Medio FY Atual], Class.[FAT MES Anterior], Class.ANEIS, Class.GAXETAS, Class.KITS, Class.ESPECIAIS, Class.ESPMT, Class.CHOMERICS, Class.PTFE, Class.COLA, Class.TRAVA, Class.TOTAL_, Class.Crescimento, Class.Status, Class.SumOFTotal_

FROM Class

GROUP BY Class.COD_Cliente, Class.Cliente, Class.TIPO, Class.Vendedor, Class.Descrição, Class.[FAT Medio FY Anterior], Class.[FAT_Medio FY Atual], Class.[FAT MES Anterior], Class.ANEIS, Class.GAXETAS, Class.KITS, Class.ESPECIAIS, Class.ESPMT, Class.CHOMERICS, Class.PTFE, Class.COLA, Class.TRAVA, Class.TOTAL_, Class.Crescimento, Class.Status, Class.SumOFTotal_

HAVING ( Class.TOTAL_ >=( Class.SumOFTotal_ *0.8));

porém não está funcionando, na parte do HAVING já coloquei sum (Class.TOTAL_)*0.8 também não deu certo, da erro " Syntax error in quary expression"

valeu

Tentei também com este comando na ultima linha

GROUP BY Class.TOTAL_ HAVING ( Class.TOTAL_ >=( sum( Class.TOTAL_ )*0.8);

também nada....

[Clemente Silva]

Mas é aí que está o problema, a cláusula "GROUP BY" é justamente a que faz o agrupamento do qual eu estava falando.

E o erro aconteceu na cláusula "HAVING" pois tanto esta como a "WHERE" não permitem cálculos, a não ser que sejam feitos em conjunto com funções agregadas, e ainda em determinadas condições.

Eu acho que o ideal é você obter primeiro os 80% do total do faturamento numa primeira consulta, e depois usar esse valor como critério na consulta que pretende fazer.

Aí nem precisa trazer o campo "Class.SumOFTotal_", coloque uma cláusula "WHERE" mais ou menos assim:

WHERE (SUM(Class.TOTAL_) >= VALOR_OBTIDO_NA_PRIMEIRA_CONSULTA );

Em tempo, esse campo "Class.SumOFTotal_", para quê ele serve? Você mantem a soma atualizada de todo o faturamento nele?

[Italiano]

Sim, ele é uma consulta (select total_ from ....) porém coloquei ele p somar, esta consulta que você está vendo é obtida de outras consultas, para obter os clientes tivemos que fazer uma referencia com outra tabela que tinha n pedido e cod do cliente, depois agrupar tudo, para ter a soma total, peguei a tabela lá traz e fiz uma soma, bom é só colocar este valor *0,8, vou testar depois te falo....

[Italiano]

não deu certo, p facilitar fiz uma planilha, qualquer coisa faço uma consulta para apagar os dados e outra para adicionar, tem uma planilha, estou apenas na primeira consulta agora, o codigo está assim:

SELECT test.COD_Cliente, test.Cliente, test.TIPO, test.Vendedor, test.Descrição, test.[FAT Medio FY Anterior], test.[FAT_Medio FY Atual], test.[FAT MES Anterior], test.ANEIS, test.GAXETAS, test.KITS, test.ESPECIAIS, test.ESPMT, test.CHOMERICS, test.PTFE, test.COLA, test.TRAVA, test.TOTAL_, test.Crescimento, test.Status

FROM test, SOMA;

tentei com as duas opções, não deu certo, mas tem lógica o q você colocou lá, sestou achando estranho... coloquei aquela consulta soma tb, caso tenha que colocar a soma....

tentei também colocando um valor manualmente, também... nada...

será que fazer uma macro em vca para ler linha po rlinha dá certo? só não sei como faria p ler os dados da query... será que dá p fezer isso ???

[EduardoS]

Entendi o problema... Não entendi bem o que o Clemente falou, é algo mais ou menos que nem o a seguir?

Ah, não use e nem tenho Access aqui, mas considerando uma tabela cliente com os campos cliente e vendas no MS-SQL da para fazer assim:

  SELECT a.cliente
    FROM cliente a, cliente b
GROUP BY a.cliente
HAVING SUM(CASE WHEN a.vendas <= b.vendas THEN b.vendas ELSE 0 END) < 8 * SUM(b.vendas) / 10

[Clemente Silva]

Pérai, vamos recapitular tudo de novo, que agora eu já me perdi

Vamos colocar o problema novamente:

- Você tem clientes:

FULANO, BELTRANO, CICRANO, etc...

- Você tem a soma de todas as vendas feitas para esses clientes, que é a "receita"

VENDAS DE FULANO + VENDAS DE BELTRANO, + VENDAS DE CICRANO + ... = RECEITA

- Você quer saber quais são os clientes cuja soma dos valores das vendas seja MAIOR OU IGUAL A 80% DA RECEITA.

Então, vamos fazer assim:

TABELA_VENDAS

--------------------------
NOME_CLIENTE | VALOR_VENDA
--------------------------
FULANO            3.500,00 
BELTRANO            200,00
CICRANO             150,00
FULANO            2.000,00
--------------------------
RECEITA           5.850,00

Ok, então 80% de 5.850,00 seriam 4.680,00, logo numa consulta dessas, somete o FULANO iria aparecer, pois a soma de suas receitas é 5.500,00, sendo mais que os 80% to total, certo?

Transformando isso num algoritmo, teríamos:

SELECIONE CLIENTES

ONDE SOMA INDIVIDUAL DE SUAS RECEITAS

SEJA MAIOR OU IGUAL À RECEITA TOTAL

Vamos aos fatos:

1) A ideia de uma coluna para colocar o acumulado seria interessante, não fosse o fato de ser extremamente trabalhoso manter esse total além de ser passível de erro e tornar o sistema lento.

Motivo: teria que buscar sempre o ultimo registro, pegar o último total e somar com o valor do registro atual. Isso num sistema em rede com N máquinas jamais funcionaria.

2) Você não pode incluir a coluna NOME_CLIENTE na consulta se quiser fazer a soma da coluna VALOR_VENDA

Motivo: o agrupamento feito por NOME_CLIENTE não deixará chegar a um total geral, mas sim apenas a totais por cliente.

3) Pelo fato 2 acima, não dá pra fazer tudo numa consulta só.

...

Agora vamos a uma provável solução:

1) Você tem que fazer uma consulta para levantar o total de receita no momento:

SELECT SUM(VALOR_VENDA) AS RECEITA FROM TABELA_VENDAS;

2) Dessa consulta, calcule os 80% (ou o percentual que quiser)

3) Então execute outra consulta, assim:

SELECT NOME_CLIENTE
FROM TABELA_VENDAS
GROUP BY NOME_CLIENTE
HAVING (SUM(VALOR_VENDA)>=4.680,00);

Pronto. Agora já posso ir dormir.

[Italiano]

Ok,, no exemplo acima funciona, pois o valor do primeiro vendedor atende o critério de ter um valor igual ou superior aos 80%, porém quando o primeiro valor não atende esta condição, a coisa complica.

vocês entenderam o q eu quero fazer, quanto a isso n a problema....

tente diminuir os valores da sua tabela, c vai ver o problema que tenho ex:

Cliente | Valor

a | 12

b | 10

c | 9

d | 4

e | 4

f | 3

total 42

bom 80% é 33,6

a + b + c = 31, atende os 80%, somando o d ou o e dá um valor um pouco superior 35, não atendenco a condição, desta forma só será exibido o a, b, c

neste caso, se colocar-mos a instrição have sum(valor)>=33,6... ele não vai pegar nenhum valor pois n atende a condição.

não se preocupe com a rede, o sistema é apenas um SIG, os dados obtidos ai estão fechados, só haverá mudança o mes que vem.

mesmo sendo demorado, acumulando uma vez, dopemos fazer uma consulta de adição, montando uma tabela fixa, onde todos podem consultar sem demora.

bom, tem outro caminho, fazer um codigo VBA que le a tabela, ou mesmo a query, e soma, utilizando qualquer um loop, porém não sei acessar estas informações em VBA, sou um pouco cru em programação, não sei tembém se é uma boa ideia...

obter a soma e os 80% através de consulta está pronto, segui o conselho de nosso amigo... aporem só falta esta parte.....

outra maneira que fiquei matutando no fim de semana é fazer algum colculo para isso, porém não cheguei a uma conclusão.....

valeu...

[EduardoS]

Veja meu post anterior.. Acho que o Access não tem o CASE, mas deve ter algum outro comando que faça a mesma coisa (desculpe, não conheço o Access).

[Clemente Silva]

Veja meu post anterior.. Acho que o Access não tem o CASE, mas deve ter algum outro comando que faça a mesma coisa (desculpe, não conheço o Access).

Não tem, o 'Jet SQL Engine' do Access é muito limitado, é só o basicão... quem quiser fazer algo assim tem que fazer 'gambiarra'.

...

Italiano, então você quer mesmo é fazer essa coluna do total... ok, isso é bem simples.

1) Antes de inserir um novo registro, execute um SELECT LAST na coluna do acumulado, para pegar o valor acumulado atual

Ex.:

SELECT LAST(SumOFTotal_) AS Acumulado FROM Class;

Suponto que o resultado aqui tenha sido 1.000,00

2) Depois execute sua instrução INSERT INTO para inserir um novo registro, e quando for inserir o valor do acumulado, some o acumulado atual com o valor do cliente atual.

Ex.:

INSERT INTO Class (Cliente, Total_, SumOfTotal_)

VALUES ('Fulano', 350,00, 1.000,00+350,00);

É por aí (eu acho), agora o resto é contigo

[Italiano]

Não deu certo, teria como você fazer um modelinho simples que faz esta função e me enviar por e-mail, tentei de tudo quanto é forma e nada....

valeu....

esqueci, meu e-mail é [email protected]

[Italiano]

bom, estou tentando fazer um modulo que pegue o valor anterior e some com o valor da linha, porém tb n está dando muito certo, da uma olhada no código:

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

Function Acumular(total As Double) As Double

Dim valor As Double

Dim anterior As Integer

Dim tabela As Database

Dim campo As Recordset

Set tabela = CurrentDb()

Set campo = tabela.OpenRecordset("ac", dbOpenDynaset)

campo.FindLast "[total]='" & anterior & "'"

valor = total + anterior

Acumular = valor

End Function

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

a consulta, é uma consulta da consulta, o que eu profiro, porém já fiz uma tabela com esses valores, e essa query consultar a própria tabela, tb nada, estou achando que eu vou ter que abrir a tabela em vb, aquela parte de open.... só que, vamos dizer ....., faltei nesta aula rrrrrrrrrr.

como posso fazer isso????

valeu....

[Italiano]

Pessoal, coloquei em modo SQL assim:

Select b.sal, sum (a.sal) as cum_sal

From emp a, emp b

where a.rowid <= b.rowid

group by b.rowid, b.sal;

porém não está funçcionando, soube que o acces não tem o rowid para reconhecer a linh, como faço, tem algum paty de atualização que coloca isso no access...

valeu....

[Wladyslaw]

Cara, sei la, eu posso estar falando besteira, porque acho que não entendi seu problema... se eu estiver falando de um problema que não é igual ao seu é só ignorar... mais vamos pensar o seguinte...

você esta querendo saber quais os clientes que, individualmente, representam 80% do valor da receita, não é isso...

O fato é que isso nunca vai ser plural... não tem como 2 clientes representarem 80% da receita... (senão a receita seria 160%, )

então você só vai ter no máximo 1 cliente que corresponde aos 80% do faturamento...

então, se você buscar o maior cliente e ver se ele passou dos 80%, esse é o cara que você ta procurando... agora se ele não passou dos 80% então ninguém mais vai passar...

Abraços

Wladyslaw

[Italiano]

Pessoal, consegui, já faz um tempo, mas ainda não consegui passar, estou agora na facu, até lembrarrrrrrrrrrrrr

bom eu passo como fiz, é sempre bom ter mais alguma coisa aqui, eu vi uma dica em um site sobre uma função que eu queria fazer do access, não tinha nada a ver com isso, porém me deu uma ideia, o unico problema é que tenho que usar a auto numeração do acces para fazer, o que não é nada bom, depois conto porque....

tento passar até este sábado p vocês... mas valeu mesmo pela atenção, muita coisa ai me deu novas idéias,...