Crosstab and Union Queries MS Access

I don't think you want to use a union.  I think you need to use a left join to connect the two recordsets.  I'm assuming that ytd is year to date but I don't know what lytd is.  If it turns out that you do need a union, you need a column in the query that specifies which row is ytd and which is lytd.

Whether you use a union or a left join, you will need a final query that joins the query you are now building back to  the customer table using a left join.  This outer query will let you bring back in customers that had no data for the period.  You can't do this as part of the crosstabs because the crosstab specifically eliminates any rows that don't contain data for the crosstab.

Tell us what lytd is so we can determine if you need a union or a left join.

Regarding the variance.  Are you looking for a variance per category?  If so, I wouldn't do it in the crosstab.  You will end up with a lot of columns depending on how many categories you have because you will have a set of columns for ytd, another for lytd, and a third for the variance.

Many Thanks PAT, LYTD for me is Last year to date so for todays date it would be 20/07/2016 -20/07/2017 transactions (UK dates) or 07/20/2016 etc.

As for variance that would need to be on Grand Total and all category totals.


Many thanks again for your prompt reply and knowledge.

Cheers
Andy

You missed to specifiy the source:

SELECT 'YTD' AS Source * FROM [qry_S_anal_ytd_crosstab]
UNION 
SELECT 'LYTD', * FROM [qry_S_anal_lytd_crosstab]

Select allOpen in new window

And for the variance as it is a comparision, I would use a JOIN instead of a UNION. E.g.

SELECT  C.ACNO,
        C.NAME,
        YTD.*,
        LYTD.*,
        V.*
FROM    Customers C
    LEFT JOIN [qry_S_anal_ytd_crosstab] YTD ON YTD.ACNO = C.ACNO
    LEFT JOIN [qry_S_anal_lytd_crosstab] LYTD ON LYTD.ACNO = C.ACNO
    LEFT JOIN [qry_S_anal_var_crosstab] V ON V.ACNO = C.ACNO
WHERE NOT YTD.ACNO IS NULL
    OR NOT LYTD.ACNO IS NULL;

Select allOpen in new window

The variance itself requires a seprate query plus crosstab query.

You need to summarize the lytd dataset and summarize the ytd dataset and union those two recordsets including the hardcoded source field so you can identify which is which.  The recordset will contain:

ACNO TRD_DATE TR_ID TR_VALUESum TR_CAT  Source

You can then crosstab that union

A query of that crosstab can find the difference between the ytd and lytd values (one may be null so use Nz() to get rid of nulls so the arithmetic will work.

Then you can union the difference query.  That's a start but far from the whole process.

----  There are lots of moving parts here so rather than try to identify them all and produce air code which I have no way of testing, I  will try to identify where the problem is since the posted code example doesn't solve it..
1. You have two sets of data that you have to join thre times and then union.  You need an inner join to match records in both sets of data.  A left join to get records that are in lytd but NOT in ytd and you need a right join to get records that are NOT in lytd but that ARE in ytd.  In reality, you can do this with two queries because either the left or the right join can also pull in the inner join records.
2.  You are asking to find a difference that ignores category so that means working with two different crosstabs and joining them using a FULL OUTER JOIN (left, right, inner).
3. And finally, you want customers that might have no activity during the period which involves a final step that uses a left join with the customer table to pull in null values for customers that do not have activity.  This has to be a separate query as the final step because the unions will simply eliminate rows that have no values in any column so you can't do this earlier.