Results 1 to 5 of 5
  1. #1
    drrob1983 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    4

    Calculated fields across tables and forms

    Dear all,

    I have a single [date of membership] in one table, and multiple [visit date]'s in another table. I have a form in which I want to display the difference between the two dates. I also want the record for that visit in the table to retain that calculated time elapse.

    I have tried using a query but don't seem to be able to get it to work. I suspect the one -to-many relationship is a factor.



    Any ideas?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    It is possible to do that calculation in a query if the tables are joined correctly. The jointype should be 'Show all records from Visits...' Saving the value will require running an SQL UPDATE. However, there really is no reason to save the value in table as it is available by the calculation in query.
    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.

  3. #3
    drrob1983 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    4
    unfortunately my experience is limited in this area. there are more than one members details and so because members make more than one visit, there will be many results. each result has an autonumber [ID] which is carried over into the query.

    my hope was that, in the backround, this would allow me to search the query by the [ID] field based upon the [ID] field for the record for that particular visit. Once the [ID] is found, i anticipated that the time elapsed would be displayed in the text box on the form, with no need to display the [ID] at all.

    I have tried a DLookUp function, but only ever get one result, even when the form displays different records.

    does this clarify my situation?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did you try a query with the jointype as I described? Show query SQL statement for analysis. Where did you use the DLookup? Show your DLookup expression.
    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.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Here's an example of what June's talking about.

    I gave you two queries. One that lists all visits for every member (note I took out spaces in the field names, spaces will only cause you grief as you program so I'd recommend taking them out if you can)

    The second query shows how to get only the most recent visit information.

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

Similar Threads

  1. Calculated fields
    By Madmax in forum Forms
    Replies: 1
    Last Post: 06-17-2011, 08:36 AM
  2. Replies: 6
    Last Post: 06-08-2011, 05:00 PM
  3. Calculated fields
    By graciemora in forum Forms
    Replies: 1
    Last Post: 02-04-2011, 06:07 AM
  4. calculated fields
    By nashr1928 in forum Forms
    Replies: 1
    Last Post: 07-21-2010, 04:49 PM
  5. Sum of calculated fields
    By nkuebelbeck in forum Forms
    Replies: 9
    Last Post: 03-12-2010, 01:32 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