Comparing data between years 2

[errolf]

I need to compare the data between 2years. I have a query that is run from a form that uses parameters between 2 dates from the form. Ineed to compare the date from the same period in the previous year. eg Between begindate a endDate and between begindate-365 and endDate-365. This gives me the data in 2 separate queries. I need to be able to to join the data from the 2 separate queries into 1 result so that I can produce a simple report to compare sales and margins between any comparative periods.

Thanks in anticipation

Errol Featherstone

 

[mhartman1]

Hello:

What you need to do is in your query make a new column and
for the field area for that column put in the following:

Expr1: Year([Your date field here])
This will display just the year of your date field. Then for the criteria put in your desired expression.

Regards
Mark

 

[Remou]

Alternatively, you may wish to look at DateAdd and a Union query or Crosstab. For example:
[tt]SELECT tblTable.TheDate,tblTable.Field1, tblTable.Field2
FROM tblTable
WHERE (((tblTable.TheDate) Between [forms]![frmform]![dteDate1] And [forms]![frmform]![dteDate2]))
UNION SELECT tblTable.TheDate,tblTable.Field1, tblTable.Field2
FROM tblTable
WHERE (((tblTable.TheDate) Between DateAdd("yyyy",-1,[forms]![frmform]![dteDate1]) And DateAdd("yyyy",-1,[forms]![frmform]![dteDate2])));[/tt]

 

[errolf]

Thanks for the reply.Please find attached sql for the query.
What I need is the same result only for the same period -12 months.Thanks for the help and ideas thus far.
Regards and thanks
Errol.
SELECT DISTINCTROW Sum(SCEX.NETT) AS SumOfNETT, Sum(SCEX.NETT) AS [DAILY SALES], Sum(SCEX.GST) AS SumOfGST, Sum(([NETT]-[GST])-[N_COST])/Sum([NETT]-[GST]) AS GP, Sum(([NETT]-[GST])-[N_COST]) AS [$Profit], SCEX.AC_NO
FROM SCEX
WHERE (((SCEX.INV_DATE) Between [Forms]![StatsSboard]![BeginningDate] And [Forms]![StatsSboard]![EndingDate]))
GROUP BY SCEX.AC_NO;

 

[Remou]

Try:
[tt]SELECT DISTINCTROW Sum(SCEX.NETT) AS SumOfNETT, Sum(SCEX.NETT) AS [DAILY SALES], Sum(SCEX.GST) AS SumOfGST, Sum(([NETT]-[GST])-[N_COST])/Sum([NETT]-[GST]) AS GP, Sum(([NETT]-[GST])-[N_COST]) AS [$Profit], SCEX.AC_NO
FROM SCEX
WHERE (((SCEX.INV_DATE) Between DateAdd("yyyy",-1,[Forms]![StatsSboard]![BeginningDate]) And DateAdd("yyyy",-1,[Forms]![StatsSboard]![EndingDate])))
GROUP BY SCEX.AC_NO;[/tt]

 

[errolf]

Thanks for the prompt reply.This method gives me the previous year but I need to be able to combine the data from both years in a crosstab format as follows :-

MTD Actual MTD LYear MTD GP MTD GPLY YTD ACTUAL YTD LYear YTDGP YTDGPLY

Thanks and Regards
Errol

 

[Remou]

I do not think you can achieve this with a single crosstab, it would take several. I think that if you join the above queries, you will achieve something similar to what you require. For example:
[tt]SELECT ThisYear.AC_NO, ThisYear.SumOfNETT, LastYear.SumOfNETT, ThisYear.[DAILY SALES], LastYear.[DAILY SALES], ThisYear.SumOfGST, LastYear.SumOfGST, ThisYear.GP, LastYear.GP, ThisYear.[$Profit], LastYear.[$Profit]
FROM ThisYear INNER JOIN LastYear ON ThisYear.AC_NO = LastYear.AC_NO;[/tt]

Where the queries above have been names 'ThisYear' and 'LastYear'.