Multiplying

[chazcoral2]

I would like to multiply a volume that I have in a table by a dollar amount that is in the same table. How can I accomplish that?

[jzwp11]

If you are doing the calculation on a form, add another textbox control and set its control source to the following:

=[volumecontrolname]*[dollaramountcontrolname]

If you are doing it in a query:

SELECT volumefield*dollaramountfield as myTotal
FROM yourtablename

[chazcoral2]

I'm sorry I don't understand your instructions for the query.

[jzwp11]

My apologies, I used the SQL text for the query which is what the query would look like in SQL view. You are probably using the design view of the query. It is hard to represent that in a post on the forum, so I will try to explain it. Create a new query and in the upper grid that is displayed, select the table that has the 2 fields that you want to multiply. In the first row/column in the lower grid, put in the expression: field1*field2 (substituting your actual field names). Add any other fields you want the query to display. If you are curious you can switch to SQL view and you will see something similar to what I posted.

[Chazcoral]

I am struggling with this a little bit. I followed your instructions how I understood them in Design view and this is the SQL that resulted. What needs to be different?
I have attached a screen shot of my existing query and what I would like for it to do.

SELECT DISTINCTROW [Imaging Log tbl].Number, [Imaging Log tbl].[Difficulty rating], [Imaging Log tbl].[Team Member], [Imaging Log tbl].Month
FROM [Imaging Log tbl]
GROUP BY [Imaging Log tbl].Number, [Imaging Log tbl].[Difficulty rating], [Imaging Log tbl].[Team Member], [Imaging Log tbl].Month
HAVING ((([Imaging Log tbl].Number) Like "Number*Difficulty rating") AND (([Imaging Log tbl].[Team Member])="Shirley Roulette") AND (([Imaging Log tbl].Month)="July"));

[jzwp11]

I assume that the field called number is a number datatype field. BTW, the word number is a reserved word in Access, so it would be best not to use it as a table or field name. The same holds for month, day, and year.

What is the datatype of the difficulty rating field?

From the query you posted in the attachment, it looks like you are trying to sum the number field. And then multiply the sum by the difficulty factor?

Perhaps you can explain in words what you are trying to do. From your original post you said the following but I don't know which field is the volumne and which is the dollar amount?

I would like to multiply a volume that I have in a table by a dollar amount that is in the same table

Is this what you are after:

SELECT [Imaging Log tbl].Number, [Imaging Log tbl].[Difficulty rating], [Imaging Log tbl].[Team Member], [Imaging Log tbl].Month, ([Imaging Log tbl].Number*[Imaging Log tbl].[Difficulty rating]) as LineTotal
FROM [Imaging Log tbl]
WHERE Imaging Log tbl].[Team Member]="Shirley Roulette" AND [Imaging Log tbl].Month="July";

[chazcoral2]

Sorry for the confusion. When I first posted this link it was hypothetical for a database that I was considering for vendors. Now I am trying to apply the same concept for an existing database.
I have several tasks that My team accomplishes each week. Example auditing. The files that they audit are inconsistant in difficulty, therefore I have developed a range of 5 difficulty ratings so if they audit 10 files that are easy they are rated at 1 so their score would be a 10. If they did 10 files that were very difficult they are rated at 5 so their score would be 50.
They enter daily totals, at month end I want to produce a report that shows an average of how many files they did, what the average difficulty rating was, providing an average score for the month.

[chazcoral2]

I was able to make your original suggestion work. Thanks for you help.

[jzwp11]

Glad you were able to work it out.

By the way, I'm not sure why you have the date broken down into separate fields (month, day, year?). Access has several built-in functions where you can extract those values from a date when needed.

[chazcoral2]

Occasionally My team has to change the date to a previous day, and then I of course perform searches by date. Would I be able to accomplish that?

[jzwp11]

Searching by date is commonly done and is generally easier if you use a single field with a date/time data type. You can search by a specific date or a date range. With the built-in functions I mentioned previously, you can search for a specific month, year, quarter, week # etc or a range there of.

This site has a listing of all the functions available in Access categorized by field data type.

[chazcoral2]

I have placed a calendar on my form how would I make it fill the date field in my table. I'm assuming through either a command button or a macro but I 'm not good at writing macro, or editing them for a command button. I tried using the example in in your link but I could get the set date commend button to work in my form.

[jzwp11]

I did not see a calendar functionality on the particular page I referenced perhaps it was somewhere else on the site. I have never used a calendar selection tool in my work, so I don't know how they work, but I have worked with code so I may be able to get it work. Could you post your database (with any sensitive data removed)?

[chazcoral2]

In "MY Name and Date Form" I have two versions one displays a calendar, when they click the date it should place that in the "date" field in the "imaging log table". The command button should open the "calendar form" and on the once the date is chosen it should fill the date field in table. Either one would work for me. Here is the link to the site for the second choice. http://www.techonthenet.com/access/forms/popup_cal.php

[jzwp11]

I had to modify the code behind the module and the Set Date button on the calendar popup to correspond with your particular field names. I did change your date field to mydate since the word date is a reserved word in Access. The revised database is attached.