Comparing actual sales versus forecast

[lilminx]

Hi there,

Please could someone offer me a little pointer.

I am trying to create a query for a report or form. The idea behind the query is to compare the actual sales in a month to the forecast for that month.

There are a number of things that I'm trying to take into account.

• I need to be able to break the list down either to a report per sales person or have the sales person as selectable.
• I would also need the month being looked at to be selectable.
• It should also show only those customers and products that are over a certain percentage different from the forecast data - again based on a keyed in selection.

I know the above should be possible but I just can't seem to find the simple solution for it. Please find attached a very small example of the information in each of the tables I am comparing. I have limited it to one example customer with 2 products.

I hope someone can help me with this one.

Thanks Jodie

 

[PNGBill]

Haven't looked at your attachment but I would start with a simple query that collected the Sales Data and Budget Data for all sales persons.
Repeat this for Products and again for Regions.

If you can then redo the query to combine all of this into one query, then better still but I would get the basics first.

You then add a field to your query to calculate the difference between Actual and Budget. Easy to search for help on this when you are at that stage.

The create a form and use sub forms if you still have 3 queries.

The form is where you can select the criteria of your data by way of list box or combo box.
Get help on this when you are at that stage.

Your report should then replicate the work done on your form or even be run off the form.

First step, collect all data for all sales persons, all products, all dates and all regions.

Then restrict the data by way of list box and or combo box in your form.

Another side story here is you could even have forms where you click on a region and from there click on the report and this will automatically only show the restricted data - when set up. Your wizard can often do this for you when creating the command button.
So you can click on any sale person and see the relevant form or report by the click of a button.