Results 1 to 9 of 9
  1. #1
    batchjb69 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    4

    Quering two fields in a table to populate one field in another table

    I want to create a table known as output table that gets instrument type from the classification table in the attached sample of a much larger database.

    To do this the query will have to use the security name field to populate the cash instrument type and the ISIN field for the stock instrument type as the names of the stocks can be slightly different to what's in the classification table.



    How can this be done in access?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm confused (not unusual). Sometimes it's supposed to use the name, and sometimes the ISIN? What is the output you expect to see? Normally one field would relate the tables.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    batchjb69 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    4
    That's right id expect to see the ISIN, Instrument Name which are already in the tblinput table and the and Instrument type which comes from the tblclassicications table. to do this sometimes the name and sometimes the ISIN is used

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What determines "sometimes"? Again, what output do you expect based on the data in the sample?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And I'll add I suspect I know what result you want and how to get it, but it would be nice to have the goal specified.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    batchjb69 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    4
    The goal is to create a new table "tbloutput" which is as per input sheet but with the instrument type added using classificationtbl. To do this query ISIN column. If no ISIN query name column instead.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is this what you want?

    ID ISIN InstrumentName InstrumentType
    1
    Cash Cash
    2 xs1000023 Barclays PLC Stock
    3 xs1000078 BP PLC Stock
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    batchjb69 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    4
    Yes that's it

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    SELECT tblInput.ID, tblInput.ISIN, tblInput.InstrumentName, Nz([tblclassifications_1].[Instrument Type],[tblclassifications].[Instrument Type]) AS InstrumentType
    FROM (tblInput LEFT JOIN tblclassifications ON tblInput.InstrumentName = tblclassifications.[Instrument Name]) LEFT JOIN tblclassifications AS tblclassifications_1 ON tblInput.ISIN = tblclassifications_1.ISIN;
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 09-05-2016, 10:56 AM
  2. Replies: 4
    Last Post: 05-15-2016, 03:26 PM
  3. Replies: 1
    Last Post: 06-11-2012, 02:34 PM
  4. Replies: 2
    Last Post: 06-09-2012, 03:16 PM
  5. Replies: 3
    Last Post: 10-06-2010, 06:33 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