Somar Com Critérios Diferentes Com Relacionamento

[Vilmar]

Olá


Preciso somar a quantidade das vendas que estão na tabela esquerda, porem com critérios diferentes para cada supervisor da tabela direita. Como fazer isso no Access?

Excel https://1drv.ms/x/s!AlT1IBz5Y6AO6wxQne28GNXemGct?e=OSXCTL


Veja neste neste exemplo em Excel onde na tabela direita a fórmula SOMASES tem critérios diferentes para os supervisores:

[CJ_London]

for the benefit of those who cannot read Portuguese.

HelloI need to add the amount of sales that are in the left table, but with different criteria for each supervisor in the right table. How to do this in Access?Excel https://1drv.ms/x/s!AlT1IBz5Y6AO6wxQ...ct?e=OSXCTLSee this example in Excel where in the right table the SOMASES formula has different criteria for supervisors:



@vilmar - please avail yourself of a translator such as google translate https://translate.google.com/
por favor, aproveite um tradutor como o google translate https://translate.google.com/

[June7]

How do you know which Vendas records go with with supervisor?
If this data is in an Access database, then provide that file.

[mike60smart]

Hi Vilmar

Unable to open the Excel File can you attach again?

No se puede abrir el archivo de Excel, ¿puede adjuntarlo de nuevo?

[Vilmar]

Obrigado pela resposta e pela dica

Obrigado pela resposta e a dica

[mike60smart]

Sorry but I don't speak the language. Luck with your project.

[Vilmar]

Thanks for answering
I can only find out which records are for each supervisor if I do a SUMIFS for each supervisor in Excel, something that is unfeasible due to the number of supervisors with different combinations.


I believe the model would be something like access for users of a system.


In this image below, it shows what the sum of the amount would look like for supervisor Ajani.Excel&Access.zip

[June7]

Use Access query builder to construct SQL that joins tables on Regiao fields. Use LEFT JOIN or RIGHT JOIN, apply filter criteria on the Categoria field. Then click the Totals button on ribbon to add aggregation. Probably should not include Tipo, Loja, Meio fields.

Doesn't really seem to be anything extraordinary about requirement.

[mike60smart]

Hi

Your Relations ships between Regiao and Vendas would be on a PrimaryKey RegiaoID in Regiao table and Foreign Key RegiaoID in table Vendas.

[mike60smart]

Your Data Input Form for Regiao and Vendas would look like the attached

[Vilmar]

It would almost be. The difficulty now is to create a query that brings the sum of the amount that is in the Sales table, but that meets all the combinations of the Criteria table

[CJ_London]

I think I now understand the principle of what you are trying to achieve and suspect you need to use what is called a cartesian query (no joins) and use criteria

Something like

SELECT C.Supervisor, C.Relao, C.Tipo, C.LojaMelo, C.Categoria, sum(V.Qtd) as Total
FROM Vendas V , Criteros C
WHERE (V.Relao=C.Relao OR C.Relao is Null) AND (V.Tipo=C.Tipo OR C.Tipo is Null) AND (V.LojaMelo=C.V.LojaMelo OR C.LojaMelo is Null) AND (V.Categoria=C.Categoria OR C.Categoria is Null) 
GROUP BY C.Supervisor, C.Relao, C.Tipo, C.LojaMelo, C.Categoria

Edit:


You have not explained the full business rules but for example assuming if Relao is not null, then you are to ignore the values in Tipo and LojaMelo then the query would be something like

SELECT C.Supervisor, C.Relao, C.Tipo, C.LojaMelo, C.Categoria, sum(V.Qtd) as Total
FROM Vendas V , Criteros C
WHERE (V.Relao=C.Relao OR C.Relao is Null) AND (V.Tipo=C.Tipo OR C.Tipo is Null OR C.Relao is NOT Null) AND (V.LojaMelo=C.V.LojaMelo OR C.LojaMelo is Null OR C.Relao is NOT Null) AND (V.Categoria=C.Categoria OR C.Categoria is Null)
GROUP BY C.Supervisor, C.Relao, C.Tipo, C.LojaMelo, C.Categoria

[mike60smart]

Hi

Is there only 1 Supervisor for each Regiao ?

[mike60smart]

This query Design will give you the required output

[Vilmar]

Hey guys!
Thank you all for the tips, it helped me a lot.


I couldn't find the button that marks the topic as completed.