Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Sheffield, South Yorkshire, England
    Posts
    51
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Problems using results from lookup fields (Access2000 V 9.0.2719)

    Can anyone give a quick fishing lesson to point me in the direction to solve what is probably a very trivial problem?

    I have a database that includes at its centre a table tblSONGS of religious songs including such fields as TITLE, AUTHOR1, AUTHOR2, AUTHOR3 and ARRANGER. The latter 4 fields are lookup fields all pointing to the same table called tblNAMES. This consists of fields NAME_ID, FIRSTNAMES, and SECONDNAME.

    I have a simple query qryNAMES that is based on the tblNAMES with fields NAME_ID, FIRSTNAMES, SECONDNAME, and FULLNAME the latter field being a simple concatenation of the previous 2 fields.

    I had planned to create queries from these and other tables with the names of the authors (where present) and arranger are concatenated together to give (for example)

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Problems using results from lookup fields (Access2000 V 9.0.2719)

    Such problems often appear to be trivial, but when you try to solve them it turns out not to be the case. One initial observation abut your tblNAMES: Why not simply concatenate the FIRSTNAMES and SECONDNAME in your query with an expression something like
    FULLNAME: FIRSTNAMES & " " & SECONDNAME
    That way you don't have to edit the name in two places, type it twice etc.
    The problem with your query undoubtedly has to do with joins - and I think what you want from a query perspective is to place 4 copies of tblNAMES in your query grid, joining one to each of the fields AUTHOR1, AUTHOR2, AUTHOR3 and ARRANGER. When you do that the first table will be shown as tblNAMES, the second as tblNAMES1, the third as tblNAMES2, and the fourth as tblNAMES3. The joins should each be set as RIGHT JOINS, so that the query gives you all records from tblSONGS, and returning a Null if there is no AUTHOR2 or AUTHOR3 for example. You then return the CONCATNAMES (or the expression above) for each of the tblNAMES instances to get the desired result.

    As to combo boxes, they are actually somewhat simpler. You set the RowSource property for the combo box to be tblNames (you may want to create a query using the builder and sort it by name). Set the data source to the numeric value for AUTHOR1 or whatever, make the number of columns 2, and hide the first column by making it's width 0. The wizard for building combo boxes will do this for you if you want to experiment, and you should see the same result. Good luck - those of us interested in music have to stick together. Post if you encounter further problems. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Wendell

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Sheffield, South Yorkshire, England
    Posts
    51
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Problems using results from lookup fields (Access2000 V 9.0.2719)

    Thanks very much for your prompt & helpful reply. I think that I should be able to struggle through the rest now.

    1) I have concatinated in query as suggested.

    2) I had already tried to put in the joins but they were rejected. After your telling me to do so I realised that my mistake had been to set all the relationships at the beginning. Once I removed the joins from tblNAMES from the main relationship screen and put them in as you suggested it worked well.
    I am still running into dead ends at times with other queries/forms in getting the ID numbers appearing instead of real names but so far have got it right eventually. The wizards have bailed me out a couple of times. I must explore and see why their solution worked.

    After initially trying your solution and realising that it was working, even my wife commented on the satisfied expression on my face commenting 'its looks like you have just solved a problem'. I had - thanks to you!!

    Thanks again,

    John

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Problems using results from lookup fields (Access2000 V 9.0.2719)

    Glad to be of help - now if you have any hints about memorizing "Eugene Onegin" when a person doesn't know Russian, I'm all ears <img src=/S/question.gif border=0 alt=question width=15 height=15> <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •