[Resolvido]Agrupar registros na tabela utilizando GROUP BY e SUM

Boa noite pessoal

Preciso de ajuda para manipular alguns registros na tabela. Tenho uma tabela como o exemplo:

Apostilas QtdeAlunos
A 16
B 12
C 03
A 04

Estou tentando agrupar por Apostilas, somando o campo QtdeAlunos para que fique assim:
Apostilas QtdeAlunos
A 20
B 12
C 03

Mas não estou conseguindo montar uma rotina que realize isso. tentei utilizar um recorset com group by e sum, mas sem sucesso. Podem me ajudar por favor?

Desde já agradeço!

Pode ser numa consulta Select normal?
Ex:
SELECT TabApoAlu.Apostilas, Sum(TabApoAlu.NAlunos) AS NAlunos
FROM TabApoAlu
GROUP BY TabApoAlu.Apostilas;

Supondo que sua tabela se chamasse "TabApoAlu"

Bom dia Leandro,

Obrigado pela sua atenção. Eu não estou conseguindo utilizar o SELECT. Fiz um teste com o INSERT em outra tabela, assim eu consigo:

DoCmd.RunSQL "INSERT INTO TabTeste(Nalunos, Apostilas) SELECT SUM(QtdeAlunos) AS Nalunos, Apostilas FROM Cursos_Inic GROUP BY Apostilas" 'criei a tabela TabTeste apenas para testar a instrução

Mas não estou conseguindo agrupar na mesma tabela ( Cursos_Inic ) com o SELECT. Fiz assim:

DoCmd.RunSQL "SELECT Cursos_Inic.Apostilas, Sum(Cursos_Inic.QtdeAlunos) AS QtdeAlunos FROM Cursos_Inic GROUP BY Cursos_Inic.Apostilas"

Pode me ajudar por favor a corrigir?

De fato, o que você precisa?
Selecionar ou Inserir os dados numa tabela?

Deixe-me tentar lhe explicar..

Eu insiro dados nesta tabela através deste código:

DoCmd.RunSQL "INSERT INTO Cursos_Inic(QtdeAlunos, Apostilas) SELECT QtdeAlunos, Apostilas.Value FROM Cursos WHERE Cursos.Abreviatura = '" & Abrev_curso & "'"


A tabela Cursos_Inic fica assim:

QtdeAlunos Apostilas
----10-------------A----
----20-------------B----
----03-------------A----

Quero somar a coluna QtdeAlunos tendo como referência a coluna Apostilas. No exemplo, a 1ª linha na coluna Apostilas é "A" e na 3ª linha também é "A". Sendo assim, somo a columa QtdeAlunos (10 + 3):

QtdeAlunos Apostilas
----13-------------A----
----20-------------B----

O ideal (que eu acredito que seja) era que, ao inserir dados nesta tabela através do INSERT (logo no topo desta postagem) eu já realizasse o agrupamento com SUM e GROUP BY, mas não consigo, pois como pode notar, a fonte de dados possui uma coluna de múltiplos valores (Apostilas.Value)
Se for possível utilizar o GROUP BY mesmo tendo esse campo de múltiplos valores resolve meu problema, senão, quero agrupá-los depois de inseri-los.

Não sei se fui claro o bastante, se não fui por favor me avise para eu anexar as tabelas do banco de dados envolvidas.

Obrigado pelo tempo já dispensado!

Vamos supor que a tabela que tem os dados que você quer agrupar e somar tenha o nome "TabDados", altere conforme for o certo.
Abra uma nova consulta e cole isso no Sql.

INSERT INTO Cursos_Inic ( QtdeAlunos, Apostilas )
SELECT Sum(TabDados.QtdeAlunos) AS SomaDeQtdeAlunos, TabDados.Apostilas
FROM TabDados
GROUP BY TabDados.Apostilas;

Depois salve a consulta e altere aquele VBA:

Coloquei como exemplo o nome: "ConsAgrupaSoma"

DoCmd.OpenQuery "ConsAgrupaSoma"

Abraço

Puxa vida Leandro, quanto mais eu uso campos de valores múltiplos mais eu amarro meus bancos. O Alexandre Neves já tinha me dado um toque sobre isso.

Tomando seu exemplo:

INSERT INTO Cursos_Inic ( QtdeAlunos, Apostilas )
SELECT Sum(TabDados.QtdeAlunos) AS SomaDeQtdeAlunos, TabDados.Apostilas
FROM TabDados
GROUP BY TabDados.Apostilas;

A tabela que contém os dados TabDados.Apostilas é de valores múltiplos e a consulta não aceita um insert into desses campos. (Emite uma mensagem dizendo isso).

Acho que vou ter que usar uma tabela temporária para separar o conteúdo deste campo de múltiplos valores e depois manipular os dados com o GROUP BY. O que acha?

Posta uma parte do banco por favor.

Favor é o que você está me fazendo

A tabela Excel_Temp recebe dados de uma planilha do excel, já deixei ela com os valores.
Tenho a tabela Apostilas, onde faço o cadastro das mesmas. Observe por favor que há também a tabela cursos, que possui a coluna apostilas. Este campo é de múltiplos valores, pois um curso pode usar uma ou mais apostilas.

Atabela Filtro é alimentada pelos cursos que vão iniciar em um período de 20 dias (extraio estes dados da tabela Excel_Temp).

Agora é a parte que tenho dificuldade.

Eu sei os cursos que irão iniciar e a quantidade de alunos (estes dados estão na tabela Filtro). O que quero saber é quais e quantas apostilas terei que comprar para os cursos.

Faço isso na tabela Cursos_Inic. Lá tem novamente o número de alunos e as apostilas (como este campo é de múltiplos valores copio para Cursos_Inic.Apostilas o código de cada apostila).

Agora obserse que na tabela Cursos_Inic na coluna apostilas repete alguns códigos, como por exemplo o 38, quero usar o GROUP BY, como nos exemplos já postados.

Espero que tenha dado para entender em meio a tanta confusão, me desculpe por isso.

Junior, eu tentei conveter aqui para access 2000, mas não foi possível por algum recurso exclusivo. Peço por favor que coloque só as tabelas envolvidas num outro BD no formato 2000.
Obrigado

Boa tarde, Júnior
Não entendi qual a finalidade da tabela Cursos_Inic nem onde pretende colocar a quantidade e tipo de apostilas

Leandro,

Eu anexei em 2000, porém o campo Apostilas da tabela Cursos deixou de ser múltiplos valores. Só se pode escolher uma apostila. Não precisa dize por favor não chefe.

Alexandre, Boa Tarde.

É esta tabela meu grande problema....

Ela contém a coluna QtdeAlunos, que é a quantidade de alunos por curso. Então tenho, por exemplo, alguns cursos com 16 alunos e outros com 32. Nesta tabela não ponho o nome do curso.
A Coluna Apostilas, possui o código da Apostila (cadastrada na tabela Apostilas) que o curso usa, porém uma apostila pode ser utilizada por mais de um curso. Olhe por favor na coluna Apostilas, e veja o número 38, está na linha 5, 14 e 19. As linhas estão representadas abaixo:

Tabela Cursos_Inic

-------------QtdeAlunos-----Apostilas
linha_5----------32------------38
linha_14---------16------------38
linha_19---------16------------38

Tenho então neste exemplo 3 cursos que usam a apostila 38. O primeiro curso de 32 alunos, o segundo de 16 e o terceiro também de 16. Então serão necessárias 64 cópias da apostila 38.
O que eu queria, era agrupar esta tabela com o SUM e GROUP BY para que, ao invés de me apresentar os dados como o acima, somasse para mim a coluna QtdeAlunos, quando Apostilas for igual. Assim:
-------------QtdeAlunos-----Apostilas
linha_5----------64------------38

Sou péssimo para explicar, não sou?


Valeu gente

vamo-nos situar para partirmos do mesmo ponto:
Quer utilizar a bd anterior (com campo de valores múltiplos) ou a última bd (sem campo de valores múltiplos, tem de criara alternativa)?
Pretende saber quantidade de apostilas por quebra por apostila e por curso ou apenas por quebra por apostila?

Desculpe,

Campo múltiplos valores. Apenas por apostilas.

tem de ter dados consistentes na bd.
Como liga as tabelas Excel_temp e Cursos? Não vi nenhuma relação possível

Bom dia,

Utilizo um insert com WHERE para filtrar os dados do Excel_Temp pata Filtro obedecendo um intervalo de datas:

DoCmd.RunSQL "INSERT INTO Filtro(Curso,Inicio,Status) SELECT Curso,Inicio,Status FROM Excel_Temp WHERE Excel_Temp.Inicio BETWEEN Format (date(), 'DD/MM/YYYY') AND Format (date()+19, 'DD/MM/YYYY') ORDER BY Curso"

E depois utilizo INSERT para popular a tabela Cursos_Inic:

'Verificando apostilas
DoCmd.RunSQL "INSERT INTO Cursos_Inic(QtdeAlunos, Apostilas) SELECT QtdeAlunos, Apostilas.Value FROM Cursos WHERE Cursos.Abreviatura = '" & Abrev_curso & "'"

Não estão relacionadas.

Boa noite, Júnior
Se vi bem, na bd que remeteu, não tem nenhum dado que se possa trabalhar.
Confirme e informe

Olá Alexandre, boa noite pra ti também.

Não entendi sua observação "não tem nenhum dado que se possa trabalhar".

Pela descrição, penso que se devia começar a identificar os cursos a começar
Para tal, deve-se ligar a tabela Excel_Temp (Curso) com a tabela Cursos (Abreviatura). Certo?
Se sim, não encontro nenhum registo que fique ligado

Acho que entendi o que o Sr. quis dizer.

Eu não realizei nenhuma ligação (relação) entre as tabelas envolvidas neste assunto.

Mesmo que elas tenham campos em comum eu não as relacionei. Troca dados entre elas através de instruções INSERT, UPDATE, etc. Pelas postagens acima começo a perceber que eu deveria telas relacionado. É isso?

Eu não troco informações direto entre as tabelas Excel_Temp e Cursos pelo seguinte motivo:

A tabela cursos possui a coluna Abreviatura. O curso "Access(Office 2007)" por exemplo possui como abreviatura "ACCESS".

Agora, a tabela Excel_Temp contém a abreviatura mais o código da turma. Exemlo = "ACCESS 1201S". Este 1201S é o código da turma. Por isso que não relacionei Excel_Temp.Nome com Cursos.Abreviatura.

Eu realizo uma rotina para tirar sempre a última palavra do Excel_Temp.Nome para saber a abreviatura do curso. Por exemplo:

Quando Excel_Temp.Nome = "ACCESS 1201S"

retiro a última palavra, ficando então "ACCESS"

Faço isso e alimento a tabela Filtro com o nome do curso. Acha que devo refazer meu método para que eu consiga fazer relacionamentos?

Ah, o objetivo final é emitir um relatório dos cursos que vão iniciar e das apostilas necessárias.

Júnior,
O Mestre JPaulo diz, e bem, que existem mil maneiras de fazer Nestum
Porque não explicou todos os pormenores, apenas tentei interpretar as tabelas.
Diga quais as tabelas-base e como elas se relacionam, exceptuando as tabelas intermédias que servem para cálculos intermédios

Estou a montar um exemplo...

Desculpe pela minha falta de clareza
Vou tentar novamente. Ah, obrigado pela paciência.

Tabelas-base são estas:

Apostilas: Onde eu cadastro as apostilas e quantidade de estoque, bem como as quantidades mínima e máxima permitidas. Qtderecebe refere-se a quantidade de apostilas já solicitadas mas que ainda não chegaram.

Cursos: Onde cadastro os cursos e suas informações, como a abreviação de cada curso.

Excel_Temp: Tabela que recebe os cursos que iniciarão. Possui a Coluna "Curso" que é composta da abreviatura do curso mais o número da turma. Por exemplo:

Abreviatura = "EXCEL AV"
Número da turma = "1210S"

Sendo assim, a coluna "Curso" da tabela Excel_Temp será igual a: "EXCEL AV 1210S".

Meu objetivo: Gerar dois relatórios, sendo que um mostrará os cursos que iniciarão em 20 dias (Dia atual + 19). O outro relatório me mostrará as apostilas necessárias (incluindo o nome das apostilas e quantidade) para atender os cursos.

A primeira coisa que faço é:

DoCmd.RunSQL "INSERT INTO Filtro(Curso,Inicio,Status) SELECT Curso,Inicio,Status FROM Excel_Temp WHERE Excel_Temp.Inicio BETWEEN Format (date(), 'DD/MM/YYYY') AND Format (date()+19, 'DD/MM/YYYY') ORDER BY Curso"

Copio os cursos que se iniciarão no dia atual até 19 dias depois, da tabela Excel_Temp para a tabela filtro. Depois eu uso o recorset (linhas de comando abaixo) para descobrir a abreviatura de cada curso na tabela filtro.

Faço isso porque, como mencionei acima, recebo assim os cursos que vão iniciar: "EXCEL AV 1210S". Mas quero saber só a abreviatura (EXCEL AV), sem o número da turma(1210S)Faço da forma abaixo:


Dim rst As DAO.Recordset
Dim Abrev_curso As String
Set rst = CurrentDb.OpenRecordset("SELECT * FROM Filtro")
'Aqui inicio o loop
'Enquanto não chegar no ultimo registro estarei 'percorrendo a tabela
Do While Not rst.EOF
Abrev_curso = rst![Curso]
Dim separa As Variant
Dim Numpal As String
Dim Nome_cursos As String
separa = Split(Abrev_curso)
Nome_cursos = Join(separa)
Numpal = UBound(separa)
separa(Numpal) = ""
Abrev_curso = Join(separa)


No final desta rotina, a string Abrev_curso será igual a abreviatura do curso ( no exemplo que dei, será igual a EXCEL AV)

Depois realizo um update para alimentar a tabela Filtro com as informações da tabela Cursos, utilizando como parâmetro a abreviatura do curso. Olhe, por favor, a tabela no bd em anexo como a tabela fica.


DoCmd.RunSQL "UPDATE Filtro AS F, (SELECT * FROM Cursos) AS C Set F.Nome = C.nome, F.QtdeAlunos = C.QtdeAlunos WHERE C.abreviatura = '" & Abrev_curso & "' AND F.Curso = '" & Nome_cursos & "'"

Depois emito um relatório da tabela Filtro. Não inclui o relatório no arquivo postado, mas eu já o fiz.

Agora vou trabalhar com as apostilas. Na tabela Cursos eu cadastro as apostilas de cada curso. Lembrando que, esta coluna é de múltiplos valores, porque um curso pode usar mais de uma apostila.

Na tabela Cursos_Inic eu insiro a quantidade de aluno de cada curso e o código das apostilas que cada curso usará:


DoCmd.RunSQL "INSERT INTO Cursos_Inic(QtdeAlunos, Apostilas) SELECT QtdeAlunos, Apostilas.Value FROM Cursos WHERE Cursos.Abreviatura = '" & Abrev_curso & "'"

rst.MoveNext
Loop
rst.Close

Meu problema é que, uma apostila pode ser usada por mais de um curso. Vou dar aqui um exemplo, mas olhe por favor a tabela.

Exemplo de como fica a tabela Cursos_Inic.

Tabela Cursos_Inic

-QtdeAlunos-----Apostilas
-----32------------38
-----16------------02
-----16------------38

Neste exemplo nota-se que 2 cursos usam a postila de código 38, um curso de 32 alunos e um de 16 alunos. Quero usar o SUM e GROUP BY para que some a tabela QtdeAlunos, quando apostila for igual. Tipo assim:

DoCmd.RunSQL "SELECT Apostilas, Sum(QtdeAlunos) FROM Cursos_Inic GROUP BY Apostilas"

Para que fique assim:

-QtdeAlunos-----Apostilas
-----48------------38
-----16------------02


Somou-se 32 + 16 porque apostilas era igual ( no caso 38). Não consegui fazer isso, é essa minha grande dúvida. Não sei usar o GROUP BY em uma instrução SELECT. Por isso em minhas primeiras
postagens neste tópico perguntei como fazer isto, pois não consegui. Tentei utilizar um recordset, e no INSERT deste utilizar um SUM e um GROUP BY.

Depois que eu agrupar a tabela Cursos_Inic, vou criar nela mais uma coluna chamada Nome_APO. Vou fazer um update para buscar o nome das apostilas. mais ou menos assim:

DoCmd.RunSQL "UPDATE Cursos_Inic AS C, (SELECT * FROM Apostilas) AS A Set C.Nome_APO = A.Nome WHERE C.Apostilas = Apostilas.Código"

Feito isso vai ficar assim por exemplo:

-QtdeAlunos-----Apostilas--------------Nome_Apo
-----48------------38---------------Apostila avançada de excel
-----16------------02--------------Tecnologia e metrologia

Depois vou gerar um relatório com o nome Solicitação de Apostilas. É isso.

A última bd está em formato não reconhecido
Tentei de várias formas e não vejo possibilidade de trabalhar com o campo de múltiplos valores. Terá de desdobrar em duas tabelas
No entanto, pelo que desenvolvi, veja o resultado:
- crie a função
Function NomeCurso(Texto As String) As String
If InStr(1, Texto, " ") > 0 Then
NomeCurso = Mid(Texto, 1, InStr(1, Texto, " "))
Else
NomeCurso = Texto
End If
End Function

- execute a consulta
SELECT NomeCurso([Excel_Temp].Curso) AS Curso, QtDeAlunos, Inicio, Apostilas FROM Excel_Temp LEFT JOIN Cursos ON NomeCurso(Cursos.Abreviatura)=NomeCurso([Excel_Temp].Curso) WHERE Inicio between date() and dateadd('d',date(),20);

Boa noite Alexandre,

Simplesmente excelente o código que você postou.

Utilizando sua função e seguindo a sua bem elaborada consulta consegui diminuir não só o código mas também o número de tabelas.

Muito obrigado pelas postagens e principalmente pelo interesse genuíno em querer ajudar.
Agradeço a você também Leandro.

Valeu!

Faltou o resolvido