Use two queries in one report?

[Bokazoit]

Hey I'm kind of new in here. I had a profile here 1½ year ago ^^

I'm a little locked down on ideas here so I hope You can help.

I have 2 queries were the structure is the same but each query contains data from two different periods.

The columns contains data or economical numbers such as revenue, margin etc., and with the use of grouping I have colapsed it to show only 18 rows in each report were each row contains the economical data for each of our business segments. The only difference between the two queries is that the first contains current data while the other contains data from last year same period.

So now I could build a report with figures from one of the queries. But I would like to compare today figures with last year figures. So I need a way to take a figure from query one and divide with a figure from query two.

How can I do that?

I have placed the SQL for one of the queries below.

In advance tx

This is the SQL:

SELECT Segsort.Sort, [RDRQ-0grouptable].Segment, Segsort.score, Count([RDRQ-0grouptable].KontoNummer) AS AntalOfKontoNummer, Sum([RDRQ-0grouptable].[SumOf# Kat Akt]) AS [SumOfSumOf# Kat Akt], Sum([RDRQ-0grouptable].[SumOfM Omk]) AS [SumOfSumOfM Omk], Sum([RDRQ-0grouptable].SumOfOmsætning) AS SumOfSumOfOmsætning, Sum([RDRQ-0grouptable].[Aktiv i Q0]) AS [SumOfAktiv i Q0], Sum([RDRQ-0grouptable].[SumOf# Fak]) AS [SumOfSumOf# Fak], Sum([RDRQ-0grouptable].[SumOf# Lin]) AS [SumOfSumOf# Lin], Sum([RDRQ-0grouptable].SumOfVareforbrug) AS SumOfSumOfVareforbrug, Sum([RDRQ-0grouptable].MaksOfUnmailable) AS SumOfMaksOfUnmailable, Sum([RDRQ-0grouptable].[SumOfWEB Fak]) AS [SumOfSumOfWEB Fak], Sum([RDRQ-0grouptable].[MaksOf# eMailTilmeldt]) AS [SumOfMaksOf# eMailTilmeldt], Sum([RDRQ-0grouptable].[SumOfWEB Lin]) AS [SumOfSumOfWEB Lin], Sum([RDRQ-0grouptable].[SumOfWEB Oms]) AS [SumOfSumOfWEB Oms], Sum([RDRQ-0grouptable].[Aktiv i Q0 (web)]) AS [SumOfAktiv i Q0 (web)]
FROM Segsort INNER JOIN [RDRQ-0grouptable] ON Segsort.Segment = [RDRQ-0grouptable].Segment
GROUP BY Segsort.Sort, [RDRQ-0grouptable].Segment, Segsort.score
ORDER BY Segsort.Sort;

 

[Bokazoit]

I found the solution myself

I forgot that I could join two queries in one as long as they could be joined together with a common variable such as my segmentation (Its the same in both queries).

So I joined them with a inner join. Here is the SQL:

SELECT ReportQ0.Sort, ReportQ0.Segment, ReportQ0.[Q0 ktnr], ReportQ3.[Q3 ktnr], ReportQ0.[Q0 # Kat Akt], ReportQ3.[Q3 # Kat Akt], ReportQ0.[Q0 M Omk], ReportQ3.[Q3 M Omk], ReportQ0.[Q0 Omsætning], ReportQ3.[Q3 Omsætning], ReportQ0.[Aktiv i Q0], ReportQ3.[Aktiv i Q3], ReportQ0.[Q0 # Fak], ReportQ3.[Q3 # Fak], ReportQ0.[Q0 # Lin], ReportQ3.[Q3 # Lin], ReportQ0.[Q0 Vareforbrug], ReportQ3.[Q3 Vareforbrug], ReportQ0.[Q0 Unmailable], ReportQ3.[Q3 Unmailable], ReportQ0.[Q0 WEB Fak], ReportQ3.[Q3 WEB Fak], ReportQ0.[Q0 # eMailTilmeldt], ReportQ3.[Q3 # eMailTilmeldt], ReportQ0.[Q0 WEB Lin], ReportQ3.[Q3 WEB Lin], ReportQ0.[Q0 WEB Om], ReportQ3.[Q3 WEB Om], ReportQ0.[Aktiv i Q0 (web)], ReportQ3.[Aktiv i Q3 (web)]
FROM ReportQ0 INNER JOIN ReportQ3 ON ReportQ0.Segment = ReportQ3.Segment;

 

[VicRauch]

Bokazoit,

Welcome to the board. Glad we could help Actually, thank you for posting your solution, it will help someone else down the line!