Calculating Year on Year Variance

[Tomreis]

Hi guys,

This is my first post and contribution to the Forum so please forgive any lack of, incorrect info (if any).

I've just picked up Access again after 2 years off touching it so I'm VERY rusty.

I have a data set spanning 5 years and need to run some variances to see how we are performing. Below is a snippet. I'm trying to figure out how to run a query that will lookup a reference from one field (Tag LY), find it in another (TAG TY) then perform a calculation from another field between those lookups (Revenue from lookup 1 minus Revenue from lookup 2).

Tag TY	Tag LY	Date	Source	Sub Source	Rm Nights	Revenue	ADR	Expr1
Jan-15CROGeneral Reservations	Jan-14CROGeneral Reservations	01/01/2015	CRO	General Reservations	138	£5,122.00	£37.12	CRO01/01/2015
Jan-15GDSGalileo	Jan-14GDSGalileo	01/01/2015	GDS	Galileo	128	£7,457.00	£58.26	GDS01/01/2015
Jan-15GDSSabre	Jan-14GDSSabre	01/01/2015	GDS	Sabre	86	£4,439.00	£51.62	GDS01/01/2015
Jan-15GDSWorldspan	Jan-14GDSWorldspan	01/01/2015	GDS	Worldspan	133	£7,784.00	£58.53	GDS01/01/2015
Jan-15GDSAmadeus	Jan-14GDSAmadeus	01/01/2015	GDS	Amadeus	113	£5,593.00	£49.50	GDS01/01/2015
Jan-15GDSAgencies Via Pegasus	Jan-14GDSAgencies Via Pegasus	01/01/2015	GDS	Agencies Via Pegasus	144	£4,877.00	£33.87	GDS01/01/2015
Jan-15WEBIndependent	Jan-14WEBIndependent	01/01/2015	WEB	Independent	96	£4,517.00	£47.05	WEB01/01/2015
Jan-15WEBBrand	Jan-14WEBBrand	01/01/2015	WEB	Brand	102	£7,543.00	£73.95	WEB01/01/2015

<tbody>
</tbody>

I'm on Access 2016, but please let me know if you have any questions.

Really appreciate the help

Kind regards

Tom

 

[sheetspread]

If you're in the same table just create another calculated field with the expression. If you're looking up from a different table, then inner join which is most easily added via design view.

 

[Tomreis]

hi Sheetspread, would it be possible to provide an example of such an expression?

 

[sheetspread]

Is the query only calculating within 1 table, or looking up between 2?

 

[Tomreis]

apologies just this one, the tag fields are unique references for a point in time. TY represents the date stamp of the line and LY being the relative code as if it were last year.

 

[sheetspread]

Ok you already have an Expr1 I see, you can, in design view, copy and paste that into another column and change the field names/operators to suit, or start from scratch by right clicking, build, then enter the formula.


edit: Oh wait, if the numbers appear in different rows and you wish to match them by a common label before subtracting, then you have to join the table to itself. In design view, under Table you add the same table to the query, then drag the common field from the second to the first.

 

[Tomreis]

You absolute legend! worked a treat thank you - Been on that for hours