Results 1 to 8 of 8
  1. #1
    Crowley is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    4

    Copy data from a query into the relevant fields in a specific table


    Hi All..

    A novice to both Access and the forum, so my apologies if this is a daft question.

    I am wishing to update/populate a table from data pulled together and calculated in a query.

    Information for a club membership is stored in a number of tables. A table for Member Details (Name, address etc.)[tblMemberDetails], a table for membership details (Date Joined, full member, Probationary Member etc.)[tblMembershipDetails] and License Details (Yes/No etc.)[tblLicenseDetails].

    A query is used to pull the relevant information together, ProbEndDate is calculated from the Date joined and only if marked as a probationary member... +3 months with a License, +6 months without. My query gets the relevant information together and calculates the ProbEndDate based on an IIF() statement.

    Click image for larger version. 

Name:	CurrentMember Query_1.JPG 
Views:	20 
Size:	41.6 KB 
ID:	44462

    I want to update the [tblMembershipDetails] table, [ProbationaryEndDate] field from the [ProbEndDate] field in Query.

    Click image for larger version. 

Name:	MembershipDetails Table_1.JPG 
Views:	20 
Size:	79.7 KB 
ID:	44464

    I've tried Using an Append Query, but don't really want to append the data, rather update the existing data. I've tried, therefore, with an update query but still can't populate the Relevant field in the [tblMembershipDetails] table.

    Or..

    Generate the Probationary End Date in the table [tblmembershipDetails] direct (Calculated field) using the relevant conditions. I've tried this but can't get iif() statement to work from the required conditions.

    I'm happy to use the Query option as the probationary end date would remain in the table until the Probation Member tick box was unticked. The query would then not generate an end date and effectively clear the probationary end date from the [tblmembershipDetails] table.

    So...please forgive my lack of knowledge and understanding, but can someone point me in the right direction and explain a straight forward way of doing it...

    Thanks, in advance, for your help....

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I don't think you should be saving the "End Date" data to a table. It's usually considered to be best practice NOT to save calculated data. Usually better to recalculate it whenever and wherever it is required, as you are doing in your existing query.
    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
    Crowley is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    4
    Quote Originally Posted by Bob Fitz View Post
    I don't think you should be saving the "End Date" data to a table. It's usually considered to be best practice NOT to save calculated data. Usually better to recalculate it whenever and wherever it is required, as you are doing in your existing query.
    Many thanks for your reply. Fair enough...

    So my problem is still that I can't get the relevant ProbEndDate to transfer to the relevant Member Details form...

    Click image for larger version. 

Name:	Membership Details Addin 1.JPG 
Views:	18 
Size:	24.5 KB 
ID:	44467

    All of the textboxes (Probation End Date excepted) are linked to a table field and display the correct data for the current member on display. The ProbEndDate comes from a query. It's getting the correct End Date to show in the Probationary End date box that is causing me problems. It's why I was looking at updating the MembershipDetails table... I can easily then show the correct date on the Membership Form....

    I can't work out how to link the ProbEndDate from the query to the required Membership number on the Member Details form, so that the correct date shows for the correct member.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    You could add the query that has the calculation to the query used for the membership form so that you could use the calculated field as the Control Source for a textbox on the form.

    Alternatively, you could create a textbox on the form that used the DLookup() function to return the appropriate value from the query.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You might want to consider researching db normalization as I think you have repeating attribute fields. Member details are likely repeating fields where this attribute of a member should probably only be one field and the value should come from tblMemberStatus or whatever you'd call it. This might not be related to your current issue, but repeating fields often becomes an issue later on. One dangerous aspect is that you can end up with more than one field in a record with a value, which would usually be impossible under many circumstances (e.g. a member cannot be both current and inactive in practical terms, but can be because of the design). FWIW, I also advise against storing any calculations.
    EDIT - for got to say that based on what I've said re: normalization, I also don't see the need for member details table if the records have a 1 to 1 relationship, which is what I think I'm seeing.
    Last edited by Micron; 03-04-2021 at 10:58 AM. Reason: chgd "active" to inactive
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Crowley is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    4
    Quote Originally Posted by Bob Fitz View Post
    You could add the query that has the calculation to the query used for the membership form so that you could use the calculated field as the Control Source for a textbox on the form.
    Told you I was stupid...

    That is exactly the answer. Your comment made me re-look at one particular Form... It was already created from a query, but I kept missing that bit... Once I figured that out, I added a new column into the query to make the calculation... works a treat.

    Can't thank you enough. Learnt more, faster, trying to figure this out than I would have done normally...

  7. #7
    Crowley is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    4
    Quote Originally Posted by Micron View Post
    You might want to consider researching db normalization as I think you have repeating attribute fields. Member details are likely repeating fields where this attribute of a member should probably only be one field and the value should come from tblMemberStatus or whatever you'd call it. This might not be related to your current issue, but repeating fields often becomes an issue later on. One dangerous aspect is that you can end up with more than one field in a record with a value, which would usually be impossible under many circumstances (e.g. a member cannot be both current and active in practical terms, but can be because of the design). FWIW, I also advise against storing any calculations.
    EDIT - for got to say that based on what I've said re: normalization, I also don't see the need for member details table if the records have a 1 to 1 relationship, which is what I think I'm seeing.
    Advice noted and I'll go through it all again to make sure all is well. Thanks for your comments.

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Crowley View Post
    Told you I was stupid...

    That is exactly the answer. Your comment made me re-look at one particular Form... It was already created from a query, but I kept missing that bit... Once I figured that out, I added a new column into the query to make the calculation... works a treat.

    Can't thank you enough. Learnt more, faster, trying to figure this out than I would have done normally...
    You're Welcome. Always pleased to help if I can
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Replies: 2
    Last Post: 09-29-2019, 07:26 PM
  2. Replies: 9
    Last Post: 07-11-2017, 07:51 AM
  3. Replies: 5
    Last Post: 12-01-2016, 11:23 AM
  4. Replies: 17
    Last Post: 06-25-2013, 05:22 PM
  5. Replies: 9
    Last Post: 06-21-2013, 08:22 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