Results 1 to 8 of 8
  1. #1
    Burnsie is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    16

    Calculations

    Hi all.

    I have a form with a subform.

    The form is client details.



    The subform is appointment details (in data view...like a spreadsheet).

    They are linked together by client number (but that doesn't show in the subform)

    One of the columns I have in my subform is duration (of the appointment) called "Duration (mins)"

    What id like to do ideally is, somewhere on the main form, have box that totals up the duration of the appointments that the client has.

    I have followed the below YouTube (see 6:30 onwards ). However, where he uses two fields to calculate (ie field A * field B), id be wanting to sum one field.

    Is this possible?

    thanks y'all

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in the query you can add calculations

    Sum: field1+field2
    or
    Duration (mins): DateDiff("n",[startTime],[EndTime])

  3. #3
    Burnsie is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    16
    Quote Originally Posted by ranman256 View Post
    in the query you can add calculations

    Sum: field1+field2
    or
    Duration (mins): DateDiff("n",[startTime],[EndTime])
    Stupid question....what query?

    Do I create a new one??

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    With client details in table tblClientDetails, appointment duration saved in tblClientDetails.AppointmentDuration, PK of clients table and FK of client details table both named as ClientNo (replace all with real names from your DB), and ClientNo being numeric:

    Into main form add an unbound text box with formula
    Code:
    =DSum("AppointmentDuration","tblClientDetails","ClientNo = " & Me.ClientNo)

  5. #5
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    Assuming I understand you're question. What you want to do is Total the Durations on the Subform. On the main form.

    For Example

    Client A has 3 Appointments on the Subform The Durations are 10 minutes, 5 Minutes and 20 Minutes. On the Main form you want to show the Total duration of 35 Minutes.

    If I'm correct then you want an unbound Textbox where the Source of the Text Box Is something like

    Select Sum(Duration) From SubformRecordsource where ClientID = Forms!MainForm!Clientid

    Be Advised that Depending on the subformrecordsource there could be a small delay everytime you change records on the main form so test it thoroughly.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Burnsie View Post
    Stupid question....what query?

    Do I create a new one??
    My forms/sub forms and reports all use queries, NOT tables.
    You can decide which fields you want to display, add columns to do calculations, do filtering and sorting.
    Tables... not so much.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ssanfu View Post
    You can decide which fields you want to display, add columns to do calculations, do filtering and sorting. Tables... not so much.
    Do you use datasheet type forms? For single/continuous forms there are lot of reasons to use query as form source, but there is nothing in your list you can't do with table as source (you have to use unbound controls instead of calculated columns of course).

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by ArviLaanemets View Post
    Do you use datasheet type forms?
    Never.

    It is a little more work to create a continuous form that looks like a datasheet, but I have more control over the display. And (supposedly) there is less data moved over the network.

    But I am one of those people that use queries as record source for forms/reports and always set up the relationship window relationships. I sometimes use unbound controls on forms and very, very rarely use unbound forms.

    I once created unbound forms for a winery in Wisconsin (I think it was in Wisconsin) that had around 150 unbound controls per form. They wanted 3 forms - one for adds, one for edits and one for deletes. LOTS and LOTS of code! But it worked for them.

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

Similar Threads

  1. Calculations
    By carlislewwtp in forum Access
    Replies: 3
    Last Post: 01-29-2018, 02:32 PM
  2. Replies: 30
    Last Post: 09-30-2015, 10:58 AM
  3. calculations
    By frustratedwithaccess in forum Queries
    Replies: 5
    Last Post: 01-08-2015, 01:56 PM
  4. calculations
    By DariusD in forum Access
    Replies: 3
    Last Post: 01-01-2013, 07:22 PM
  5. calculations??
    By richrit in forum Access
    Replies: 1
    Last Post: 04-11-2012, 05:06 PM

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