I am a beginner Microsoft Access (XP) user and was able to develop a simple table of customer sales data and basic forms for adding to, viewing, and finding records. I am familiar with parts of Visual Basic and MySQL, but I don't know enough to use Access to its full potential. One thing I need to be able to do as soon as I can is to allow reports to be printed every two weeks with the sales records from a specific timeframe. I also need to be able to do a simple calculation for each record (sales amount - supplies amount = job revenue) as well as total all of the 'job revenue' entries for the report period selected. Is there a simple way I can do this before completing formal training in Access databases?

Yes, there is.

What you want to do is create an unbound field (not one that has a value pulled from a table or query), and you'll be able to plug in any formula you like for it.

In this case, your job revenue field will have a formula something like =[Sales Amount] - [Supplies Amount] (by the way, this looks like gross profit, not job revenue, but if you want to call it JR, that's okay with me.)

For the totalling of the job revenue fields, you simply want to create a running total. It's part of the field properties, under the Data tab. You'll see the last box says "Running Total"...select "Over All" and well...you'll have your running total.

This is about as far as I think I can take you without seeing your database and without pushing my own boundaries as far as "giving you enough to learn from vs. telling you the answer outright which really doesn't help you."

In SQL it would be

SELECT (sales_amount_column - supplies_amount_column) as revenue, SUM(revenue) as total_revenue FROM table [WHERE criteria]

What I am primarily confused about is where to do this (table, query, form, or report) and how to enter calculations and reference existing data. My table contains 2 currency columns (sales and supplies amounts) and I need to use these to calculate the gross profit per row and sum this new column (and the supplies column) based on dates entered in fields on a form. For example:

0) Date, Sales, Supplies
1) 1, 10, 2
2) 3, 5, 0
3) 4, 15, 5

For the entered dates "2" through "4" would result in a report with...

"Date, Sales, Supplies, Profit
3, 5, 0, 5
4, 15, 5, 10

Supplies total: 5
Profit total: 15"

That depends. Do you need the calculations for more than one report? If so, use a query. If you don't (and it doesn't sound like you do), use a report.

To get you some practice, and also because it's a good idea, you should do one of your calculations ( Sales Amt - Supplies Amt ) in the query your report is based on, and the other one in the report itself. And then the other calculation you need, the subtotals and grand total, you should do on the report, because it makes sense for those to be there, especially the way Access reports do "grouping."

So when you're in "design view" for your report, go to the properties window, the data tab, and I think you want the recordsource property. That's either some SQL code, or the name of a query; in either case, if you click the "..." button on the right of the property, you'll get into the editor for the query. Then in design view, go to the first empty column, and
type Job Revenue: [Sales Amount] - [Supplies Amount].

As Adam says, this will make that calculation available to other reports if you decide to create them. ( It's a bad idea to keep writing out the same formula, because you don't have to, but also because if you ever want to change it, you should be able to in one place. ) It's also a good idea because SQL was meant to do this type of calculation, it's an inherent part of your data.

Now in the report, again in design view, you want to go into the grouping and sorting window, and then set this up. Grouping means sorting, but with a break and subtotals if you want 'em between values. You could do that by a date range, the sales person's name, or anything else that's a useful way for you to look at your data.

I am having a hard time figuring out where I am supposed to key in the formulas and how to create the reports. I do not yet fully understand how queries, reports, pages, etc. are to be used. All I am able to do successfully is create forms that interact with tables in the ways I described above. Are there any good tutorials that will teach me step by step how to accomplish these other tasks?

Not that I've ever seen.

A query is a means of being able to filter/sort/display information across one or more tables. If you only want say 3 fields from a table, for example, you may have a query for just those three fields.

A report pulls data from either a query or a table (you specify). Forms can also do the same thing if you should so choose.

Originally Posted by ForrestCroce

To get you some practice, and also because it's a good idea, you should do one of your calculations ( Sales Amt - Supplies Amt ) in the query your report is based on, and the other one in the report itself. And then the other calculation you need, the subtotals and grand total, you should do on the report, because it makes sense for those to be there, especially the way Access reports do "grouping."

So when you're in "design view" for your report, go to the properties window, the data tab, and I think you want the recordsource property. That's either some SQL code, or the name of a query; in either case, if you click the "..." button on the right of the property, you'll get into the editor for the query. Then in design view, go to the first empty column, and
type Job Revenue: [Sales Amount] - [Supplies Amount].

As Adam says, this will make that calculation available to other reports if you decide to create them. ( It's a bad idea to keep writing out the same formula, because you don't have to, but also because if you ever want to change it, you should be able to in one place. ) It's also a good idea because SQL was meant to do this type of calculation, it's an inherent part of your data.

Now in the report, again in design view, you want to go into the grouping and sorting window, and then set this up. Grouping means sorting, but with a break and subtotals if you want 'em between values. You could do that by a date range, the sales person's name, or anything else that's a useful way for you to look at your data.

hi

thanks a lot for the nice explanation.
it has been MONTHS since I'm looking for some logical explanation about math functions in access 2007-10.

I did what you mentioned and it works.

Question: how to use cross tab calculation and IF function?
Example: I calculate invoice paper. there is a "purchase amount" and "commission %" => calculated "gross € profit"

out of this amount i have to deduct work cost. here it gets complicate...
if product A = 15% work cost
if product B = 20% work cost

my goal is to see how much i paid for work, meaning "purchase amount" x eaither 15 or 20%. product type is mentioned in earlier step of filling the invoice.

pls advice how to calculate,
thanks!
Igal'

another issue - report is somehow limited in width.
large part on left side is used to input the invoice and the right to show the results.

maybe i plan it wrong?

hi

i am probably writing something wrong...

saw some videos about calc functions - basically it's possible to apply them also in tables and in queries.

after inputting the info in INVOICES table, through the INVOICES form
- where shall i calculate my results?
- what is a smart way to approach such system?

thanks,