Results 1 to 8 of 8
  1. #1
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17

    Table and SubTable??

    Hello again Access Wizards,



    I need some guidance on how to setup my database/table/queries.

    My ClaimsForm writes to my ClaimTBL. In the ClaimForm there is a SubForm for ClaimantsTBL. There can be multiple Claimants per ClaimForm. Right now, for every Claimant an identical claim is created for each Claimant in the ClaimantTBL. This lets me get every Claimant info (Address, Name, ect.) for each Claim. I want to merge this into a word document, but I want to have all the Claimant information in one Claim record. Right now I can tab through the mail merge records and get each one separately but not together. This way also creates additional claim records, so two claimants creates two identical claims except the Claimants are different, not really what I wanted.

    Should I create an Expression in the query to "merge" all the claimants into one field "AllClaimants"? I'm not sure if that would be correct or how to do that.


    I first had it setup so there was one "Lead Claimant" and that wrote directly to the ClaimTBL and the AdditionalClaimants wrote to the AdditionalClaimantTBL. This would give me the Lead Claimant's info but no others and only one Claim record was created. I'm thinking this is the better way but I'm lost as to how to pull the data for each additional Claimant if the number of claimants vary from claim to claim to make an effective mail merge.

    I'm not even at Novice level, so there may be a better way to setup my query so all this information is in one row, but I've been unsuccessful thus far.

    Thanks in advance!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Do you really need mail merge? Access reports will not satisfy this data output?
    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
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    Yes, unfortunately each letter will need to be manually tweaked. The problem now is, once the Merge is completed, I only have either the Lead Claimant's name, address, ect available in the merge fields, or I have all claimant's information available, but under different records. So I can't have the letter pull Mr. Evans ID 338890, Mr. Jones ID 78773 and Mr. Smith ID 123456 in one paragraph without knowing how many claimants are in the claim prior to the merge. and setting the merge to go that many records ahead.

    What I need is a way to add each persons info to the single row of the claimTBL or a query I create.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It is possible to concatenate multiple records to a single string with VBA http://allenbrowne.com/func-concat.html but I don't think Mail Merge likes calculated fields. So would probably have to save the result to a 'temp table'.

    Can also use Word automation code to build table in document.
    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
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It seems that your design doesn't meet your requirement. You may have to sort out what you actually need as a final letter; then break that down into components; then build a query or even a temp table to gather the data you need for that final letter.

  6. #6
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    It is possible to concatenate multiple records to a single string with VBA http://allenbrowne.com/func-concat.html but I don't think Mail Merge likes calculated fields.
    This may work, thank you, I'll see if I can get it working.

    It seems that your design doesn't meet your requirement.
    There should be a way do do what I am attempting, but I'm pretty sure I'm doing something fundamentally incorrect. I'll see if I can toy around with a bit and if I still can't get it I'll post a copy here. The main problem I'm having is that the name/addresses are pulling from a mastermemberTBL and the same TBL is used for the additional claimants. So the row in the ClaimsTBL pulls the name/address/ect of just one of the IDs and I need to pull the name/address data for each one, all in one row.

  7. #7
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I use a temp table (actually a temp database with a temp table) with one of my db word merge routines.
    Create a temp table with one field for each of your merge fields. The table need not be normalized, it just needs all your merge fields.
    Use a series of append and update procedures to add your data to the temp table.

  8. #8
    myusersname is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    17
    I wussed out and just changed it back to lead Claimant and a txtBox for the Additional Claimants. I really only need the lead guys Address, but it would have been nice to put each one on there in case I wanted to update all the claimants with a letter.

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

Similar Threads

  1. Replies: 2
    Last Post: 08-22-2014, 05:49 AM
  2. How to highlight cell in subtable
    By togo in forum Access
    Replies: 16
    Last Post: 10-02-2012, 02:46 PM
  3. Replies: 8
    Last Post: 09-06-2012, 09:53 AM
  4. How do I subtable?
    By blippy in forum Database Design
    Replies: 7
    Last Post: 07-21-2011, 10:49 AM
  5. Subtable of Two Tables
    By swalsh84 in forum Queries
    Replies: 3
    Last Post: 04-30-2010, 10:41 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