Results 1 to 10 of 10
  1. #1
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38

    select with calculations on two table

    I have the following (weird) situation, with two tables:

    operator(id, name, cost)
    forecast(id, date, operator1_hrs, operator2_hrs)

    (I actually have 5 but 2 is ok for the sake of this example)


    Now I have to calculate the cost of each operator (and the total). Ideally, it should be something like this:
    Code:
    select date, operator1_hrs*operator.cost where operator.id=1, operator2_hrs*operator.cost where operator.id=2
    from forecast, operator
    Is there anyway to do something like this? Thanks

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Your design does not follow good relational database design,what will you do if your business grows and you'll have 6 operators? How do you know who is operator1 in forecast operator1_hrs? If you really only have 5 records in operator (ID 1 through 5) and operator1_hrs refers to ID=1 in operator then you can use dlookup to get the cost: operator1_hrs*dlookup("[Cost]","[Operator]","[ID] = 1").

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    would help to see some example data and the result required from that data.


    suggest this might work, but no guarantees, it is extremely inefficient and will need updating every time you have a new operator

    Code:
    select date, operator1_hrs*operator1.cost, operator2_hrs*operator2.cost
    from forecast, operator as operator1, operator as operator2
    where operator1.ID=1 and operator2.ID=2
    At the moment, looks like your second table is not constructed correctly - I would expect to see

    ID
    Date
    operatorID
    operatorHours

    and your query would be

    Code:
    select date, operatorID, operatorHours*operator.cost
    from forecast INNER JOIN operator ON operator.ID=forecast.operatorID
    you can turn this into a crosstab if you want to see operators side by side

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would recommend you develop a better naming convention. Imagine having a dB with 20 tables - "ID" as the PK field name for every table would be/get very confusing.
    "Date" is a reserved word in Access and shouldn't be used for object names. For a list of reserved words see Problem names and reserved words in Access



    Consider

    tblOperators

    OperatorID_PK Autonumber
    OperatorFName Text
    OperatorLName Text
    Cost Number-Double or Currency


    tblForecasts

    ForecastID_PK Autonumber
    OperatorID_FK Number-Long / link to tblOperators
    WorkDate Date/Time
    WorkHours Number-Double





    Good luck with your project......

  5. #5
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Thanks to all,
    I have decided to normalize (at least partially) the database design.
    Now I have a cross-reference table OreOperatore which joins operators with their hourly cost and the forecast, since each forecast may have multiple operators. In the cross table I add hours per operator and calculate (with an update query) the total cost = hours * hourly_cost per forecast.
    I also created a historical table with all forecasts and the calculated fields (including total), which I update with an insert query.Click image for larger version. 

Name:	forecast.png 
Views:	37 
Size:	29.2 KB 
ID:	42337
    I add the schema: sorry but field names are in italian. I also created a form by means of which I can enter the required data: worked hours etc.
    Now, I would like to run the update query and the insert only on the selected forecast ID. How can I do this? thanks.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't even see a 'forecast ID' in that schema. Do you mean the Preventivo table?

    Isn't this insert/update issue resolved in your other thread?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Quote Originally Posted by June7 View Post
    I don't even see a 'forecast ID' in that schema. Do you mean the Preventivo table?

    Isn't this insert/update issue resolved in your other thread?
    It is the Preventivo table, but the issue has not been solved.
    I have a form from which I insert the data needed for the calculations. Of course, it points to a single record of that table.
    Now I would like to run the update and insert on that record only. Is it possible?
    Thanks

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    data is Italian for Date? As already pointed out, Date is a reserved word. I don't know whether or not data will be interpreted as the Date() function. Be safe and don't use data as field name.

    If form is open to record you want to edit, just set value of field.

    Me!fieldname = Me.textboxname
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Quote Originally Posted by June7 View Post
    data is Italian for Date? As already pointed out, Date is a reserved word. I don't know whether or not data will be interpreted as the Date() function. Be safe and don't use data as field name.

    If form is open to record you want to edit, just set value of field.

    Me!fieldname = Me.textboxname
    I am actually tryong this but Access does not execute the code in either txtbox_afterupdate nor txtbox_change
    Still, it is in a trusted location

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    Can you upload a zipped copy of the database?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 30
    Last Post: 09-30-2015, 10:58 AM
  2. How to select a single record to do calculations
    By ManuelLavesa in forum Access
    Replies: 8
    Last Post: 09-29-2015, 04:39 PM
  3. Replies: 32
    Last Post: 03-01-2015, 12:01 PM
  4. Inter Table calculations
    By michaelhare in forum Access
    Replies: 4
    Last Post: 12-01-2014, 11:04 AM
  5. Replies: 1
    Last Post: 09-20-2011, 07:28 PM

Tags for this Thread

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