SOLVED

Group by Quarter

Super Contributor

Hello Experts,

 

I have 2 tables in the attached that I am having difficulty grouping by quarter based on the date but then also by the field [Group]. 

The field [Group] is either "P" or "T"

 

so the output would need to include:

  • The Year
  • The Quarter
  • The Company Name
  • The Sum of Amt
  • The Group ("P" or "T")

 

My goal is to compare the sum of amounts for each quarter of the year by [CoName] and by [Group]. 

 

I have tried to make a Union ALL but i had duplicate values.  

I also tried to combine the tables on [CoName] but also had duplicates. My approach might not be correct.  Looking for some guidance.  Grateful for the help. 

 

Example Output.

This is only to provide a visual.  I am open. 

Tony2021_0-1655688006443.png

 

 Let me know if any questions. 

Please see attached with 2 tables. 

 

4 Replies


You are using different datatypes for Amount in each table. Currency & Number. Be consistent.
The union query only works because Currency is a special type of number.
UNION ALL will return all records including any duplicates but UNION will only return unique records.

However both sets of data should be in one table as they have the same structure.
Merge the data then the grouping will be fairly straightforward.
best response confirmed by Tony2021 (Super Contributor)
Solution

@Tony2021 

i made small queries, but the final is qryFinal.

Thank you for the advice IslaDogs.

Arnel, that is amazing! I will incorporate into my production db and circle back if any issues arise.

Sincerely appreciate the help guys.

@Tony2021 

 

Please take into consideration the potential difference between a solution and a work around.

 

It reflects the difference between long-term and short-term thinking in a lot of ways.