Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115

    Adding a Concatenated field into a multiple table query.

    Hello! I am writing a query based on 3 tables. The first 2 tables provide all the information I need, except 1 field (FullName). That field is an another table. It is a Concatenated field (FullName). Running the query with the 2 tables works well but, obviously, no FullName field. If I add the 3rd table with no links, I get many identical answers If I try to link the tables, using what appears to be sensible, same name, fields - I get no answers.



    Table 1 has two fields, IDField plus one field.
    Table 2 has a number of fields including the IDField of the first table and the IDField of the 3rd table.
    Table 3 (Master1) has many fields including the IDFields of the first 2 tables and the required answer in the query "FullName"
    Tables 1 and 2 are joined by the IDField of Table 1.

    I thought I was quite good at queries but this concatenated field is proving to be difficult. Leon

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    rather than describing with generic names (and the description is confusing) provide some sample data and the outcome required from that data. Would also help if you show your relationships and explain what you mean by a 'concatenated field'

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    So why can you not join table 2 with table 3 by ID field of 3rd table?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Welshgasman - Thanks for that - I have tried joining table 2 and table 3 via the IDField of table 3. I get no results. I have tried joining table 2 to table 3 using the ISField of table 2. I get no results.
    Ajax in his message above asks for more clarity of field names - ie do not use generics. That is what I will do in my next post. Thanks, Leon

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A pic of the query design window would probably help a lot. Make sure we can see the tables and necessary field names plus the design grid criteria (if possible) if you post it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Ajax - thank you for your comments. My apologies for not using specific field names. This I will correct.

    Hello! I am writing a query based on 3 tables. The first 2 tables provide all the information I need, except 1 field (FullName). That field is in another table. It is a Concatenated field (FullName). Running the query with the 2 tables works well but, obviously, no FullName field. If I add the 3rd table with no links, I get many identical answers If I try to link the tables, using what appears to be sensible, same name, fields - I get no answers.

    Table 1 (MethodT) has two fields, IDMethod plus one field called Method.
    Table 2 (MeetingsT has a number of fields including the IDMethod of the first table and the IDMaster1 of the 3rd table.
    Table 3 (Master1) has many fields including the IDFields of the first 2 tables( IDMethod and IDMeetings) and the required answer in the query in a field name "FullName"
    Tables 1 (MethodT) and 2 (MeetingsT) are joined by the IDField (IDMethod) of Table 1 (MethodT).

    In the Master1 table, the field FullName is produced by concatenating the Salutation field and the FamilyName field. I use Salutation rather that GivenName as sometimes there is a "William" who is known as "Bill". Therefore the FullName is not William Smith but Bill Smith.

    The result of my query should produce FullName, Description*, StartTime*, EndTime*, Priority*, Method*. The fields marked * are being produced correctly, until I add in the Master1 table to provide the FullName.

    I have had an error message saying that I cannot link using a concatenated field.

    I hope this helps. Leon - Images will follow in my next post

  7. #7
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Here are the images, as requested:
    Click image for larger version. 

Name:	MeetingsQ.jpg 
Views:	20 
Size:	101.5 KB 
ID:	46236

    Click image for larger version. 

Name:	DataViewFromMeetingsQ.jpg 
Views:	20 
Size:	208.8 KB 
ID:	46237

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That is a Cartesian query so yes, you'll get too many results. Unfortunately the pic doesn't help me - can't see what to join master on. Is there an IDMaster1 field in meetings and Master?
    Or should you be joining IDMethod to IDMethod between meetings and master? I have no idea what the relationships are between those tables. If you've tried all of those options and cannot get results it means that there is no relationship between Master1 and one or both of the other tables. Since there's no criteria, I can't think of any other cause.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'm now even more confused - you said in your first post 'I need, except 1 field (FullName). That field is an another table.'. But I see Fullname in MeetingsT

  10. #10
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Ajax - my apologies. I may well have added FullName to MeetingsT in the vain hope that it would be filled and I could use it. The only place that FullName works properly is in Master1, where it is a concatenated field (from Salutation and FamilyName). But, if I try to connect to FullName in Mster1, I get an error message telling me that I cannot link to a concatenated field. I was using FullName in MeetingsT hoping that it would no longer be recognised as concatenated.

  11. #11
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Micron - Please find attached a copy of the latest table relationships. I hope this helps. LeonClick image for larger version. 

Name:	Relationships19-09-2021.png 
Views:	12 
Size:	133.3 KB 
ID:	46249

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it may be a concatenation calculation, but what you are using is a calculated field - and they have their limitations - for example you can't join on them and you can't index them.


    I have tried joining table 2 and table 3 via the IDField of table 3. I get no results.
    what does 'get no results' mean? From the image in post#7 you are getting results but all 'people', not just one, So assuming your IDField are called IDMaster1 (per your relationships) a join should result in just 1 name per meeting, not 4

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why don't you just provide the db? Follow instructions at bottom of my post.
    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.

  14. #14
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Ajax - Many thanks for that!!! You may well have cracked it! I made the bold presumption that being concatenated it was, by its very nature, calculated!! I shall work on that. Thank you, Leon

  15. #15
    LeonS is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2014
    Posts
    115
    Ajax - DONE!!! Thank you so much for that help!! I had a bit of a problem changing a Calculated Field to a ShortText field - the system said NO! But I fixed it!! Now I have a FullName field out of the Query. I think that solves the problem. Thank you so much! Leon

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 03-10-2019, 02:32 AM
  2. Replies: 7
    Last Post: 02-25-2019, 10:09 PM
  3. Replies: 3
    Last Post: 07-01-2016, 08:11 AM
  4. adding a new field to a make table query
    By slimjen in forum Queries
    Replies: 2
    Last Post: 06-30-2016, 08:44 AM
  5. Replies: 9
    Last Post: 02-14-2014, 12:53 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