Budget v Actual

[Luigi1209]

Help - I'm fairly new to Access in the grand scheme of thigs, and totally
self-taught!

How can i set up a budget info Sales report that compares monthly actuals
against monthy budgets

If there is a sample of one somewhere i might be able to learn from that

 

[Michel Walsh]

Hi,


If you start with a Total query, you can GROUP BY on expressions. Open a
new query, bring your "actual" data table


DateTimeStamp : date_time at which the event occurred
Amount : the amount of money involved


then, click on the summation button on the toolbar to get a new line, in the
grid (Total). In the first line, type:

DateSerial( YEAR(DateTimeStamp), MONTH(DateTimeStamp), 1)

and have GROUP BY under it, at the line Total.


Bring the field Amount in the grid, this time, change the proposed GROUP BY
to SUM.


If you run that query, you will get, per month, the total (sum or) amounts
involved.





Hoping it may help,
Vanderghast, Access MVP

 

[Luigi1209]

Thanks for that - I have produced a report with groups on it ! -

My problem is (and I am probably approaching this the wrong way) I have 2
seperate tables for a) Budgets and b) Actual sales and i need to bring the 2
together - i have a common field called [CustID] in both tables -

If I could just see a copy of one that has already been produced i would be
able to learn from that - I have not had any formal training on Access at all
and I am sure I am going about things incorrectly!!! Any help would be
greatly appreciated

Luisa

 

[Michel Walsh]

Hi,


If you have two tables, you can do one total query per table, then, use a
third query that join these two previous queries (indeed, we can use
queries, rather than tables, inside other queries).



Hoping it may help,
Vanderghast, Access MVP

Thanks for that - I have produced a report with groups on it ! -

My problem is (and I am probably approaching this the wrong way) I have 2
seperate tables for a) Budgets and b) Actual sales and i need to bring the
2
together - i have a common field called [CustID] in both tables -

If I could just see a copy of one that has already been produced i would
be
able to learn from that - I have not had any formal training on Access at
all
and I am sure I am going about things incorrectly!!! Any help would be
greatly appreciated

Luisa

Hi,


If you start with a Total query, you can GROUP BY on expressions. Open a
new query, bring your "actual" data table


DateTimeStamp : date_time at which the event occurred
Amount : the amount of money involved


then, click on the summation button on the toolbar to get a new line, in
the
grid (Total). In the first line, type:

DateSerial( YEAR(DateTimeStamp), MONTH(DateTimeStamp), 1)

and have GROUP BY under it, at the line Total.


Bring the field Amount in the grid, this time, change the proposed GROUP
BY
to SUM.


If you run that query, you will get, per month, the total (sum or)
amounts
involved.





Hoping it may help,
Vanderghast, Access MVP