Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    chazcoral2 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    52

    Multiplying


    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?

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    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

  3. #3
    chazcoral2 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    52
    I'm sorry I don't understand your instructions for the query.

  4. #4
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    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.

  5. #5
    Chazcoral is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    9
    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"));

  6. #6
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    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";

  7. #7
    chazcoral2 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    52
    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.

  8. #8
    chazcoral2 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    52
    I was able to make your original suggestion work. Thanks for you help.

  9. #9
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    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.

  10. #10
    chazcoral2 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    52
    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?

  11. #11
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    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.

  12. #12
    chazcoral2 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    52
    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.

  13. #13
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    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)?

  14. #14
    chazcoral2 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    52
    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

  15. #15
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums