Results 1 to 12 of 12
  1. #1
    Fionfion is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    25

    To get value from another table

    Hi all, I'm very new to Access and is working on a little database.

    I have 2 tables, 1 for all customers, 1 for follow-up which is linked to the first table (subform).
    In the customer record, I have a "Last updated" field, in the follow-up table, I have all the follow-up dates.
    How do I set value of "last updated" from the newest follow-up date?

    Thanks in advance!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    You don't need and should not have a date field in the customers table. You can display the latest date from the subform on the main form in a textbox control. Try the DMax() function (with criteria of your Customer Primary Field) as the Control Source property of the text box.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Ideally, you don't. There is no need to have that field in customer record. Just retrieve the value from follow-up table when needed. A DMax() domain aggregate expression in textbox will do it, like:

    =DMax("DateUpdate", "FollowUp", "CustomerID=" & [Customer])

    Don't know your exact table and field names so adjust as need. Research Access Help and web for more info on domain aggregate functions.
    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.

  4. #4
    Fionfion is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    25
    I'm so so sorry.. I meant "subdatasheet" when I said the follow-up table was subform.

    So my customer table looks like this:
    + CustomerID Customer name Last updated

    And when I expand the subdatasheet(follow up table) it will show all the follow-up dates and details for the customer, that's why I wanted a field to show the last updated date in my customer table, so I can see the last updated date without expanding the follow-up subdatasheet.

    Thanks for all your help!!

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Can't do this directly with table. Populating that field in Customer table will require code or manually entering the value. Code would have to be behind a form.

    However, a query could show a calculated field using the DMax() function to pull the 'last updated' date.
    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.

  6. #6
    Fionfion is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    25
    I was trying with the DMAX function but not sure how to make it work.. so I did some research and tried to do it with total query and it seems to work fine. Here's what I did:

    1. Create a total query with all the field in my customer table and select "Total"
    2. Add the "actiondate" from the "follow-up" table and select "Max"
    3. Link "Follow-up" table as subdatasheet

    When I run the query, it looks the same as the table, except I cannot amend the customer record in the query, which is even better for me as I actually did not want any amendment to mess up the record. But I could add follow-up entry.

    It certainly does the trick.. Now I'm just wondering, would it be ok, like it's not gonna mess the record up right?? (sorry I really know nothing about query)

  7. #7
    Fionfion is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    25
    And now the field name is "MaxOfaction".. is that possible to change the field name?

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Subdatasheets are very cumbersome and are not used by more experienced users as they cause problems down the road. Leave the tables as plain and simple as can be, use forms/queries/reports to display data to the user as they desire it.

    As everyone says, remove the date field from customer table.

    In queries, you can put any name to any field. It would look like this:
    FieldName: here goes whatever is there right now
    The characters before the ":" can be anything you choose.

  9. #9
    Fionfion is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    25
    Thanks guys I finally figured out how to use the DMAX function 😃

    I know that using subdatasheet is not recommended.. but I cannot think of a better way to list all follow up details for each customer.. 😔

    Thanks again for all your help!

    Ps. How do I mark this post as solved?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Done. Thread Tools dropdown above first post.

    A form/subform arrangement does not present the data as you want?
    Last edited by June7; 10-19-2017 at 03:35 PM.
    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.

  11. #11
    Fionfion is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    25

    Red face

    Quote Originally Posted by June7 View Post
    Done. Thread Tools dropdown above first post.

    A form/subform arrangement does not present the data as you want?
    Thanks so much for your help

    I already have a form with subform which only shows one record in one page, but I also want a list of all records with all actions done, so when I sort the last updated date I can see which record has not been updated.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    A form/subform arrangement can be set to act like Datasheet/Subdatasheet.

    And then there is this method http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp
    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.

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

Similar Threads

  1. Replies: 5
    Last Post: 04-25-2017, 01:38 AM
  2. secondary data sheet
    By Giiuseppe Gazerro in forum Access
    Replies: 4
    Last Post: 07-06-2015, 07:56 AM
  3. Replies: 7
    Last Post: 09-04-2013, 03:28 PM
  4. Data sheet forms
    By calobo in forum Forms
    Replies: 1
    Last Post: 12-07-2012, 10:12 AM
  5. Data sheet sub-form
    By cb19366 in forum Forms
    Replies: 1
    Last Post: 03-31-2010, 01:05 AM

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