locked
Help designing a query RRS feed

  • Question

  • Hi

    First of all let me say I'm not even sure if a query is what I need for this...

    In my Access db I have 5 tables, all with one fieldname in common - "Artist".  Any time an artist is added to any of the tables I would like their name automatically added to a further table called "ArtistList".  This table should filter out any duplicates and contain only unique artist names in its own "Artistname" field.

    Is this possible and if so, how do I do it?  I am a novice Access user with Access2013.

    Many thanks

    Ian

    Thursday, March 28, 2013 6:47 PM

Answers

All replies

  • Is there a reason why you have 5 different tables? And is there a reason why you'd store the Artist name redundantly in a sixth table?

    I can possibly understand the first situation (5 different tables), but not the second. If you do have a legitimate need for the five different tables, rather than having a sixth table 'ArtistList', create a query that UNIONs together the other five tables, and use that query wherever you would otherwise have used the 'ArtistList' table.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    Thursday, March 28, 2013 7:48 PM
  • Hi Douglas

    Thanks for replying! 

    The reason there are 5 different tables is to easily differentiate between different pages on my companies website.  There are 5 pages, each doing something slightly differently from the last.  The one thing they all have in common is the need to display an artist name and that artist could be featured on one or all of those pages.

    The reason for the 6th table is - in my mind - so I can generate a list of different artist names from the other 5 tables in order to output those names to a list on the website using classic asp.  My asp skills are limited but I do know how to display items from a specific table, hence table#6.

    I hope this explains a bit better?

    Ian

    Thursday, March 28, 2013 8:08 PM
  • Well, it makes me even more convinced that you only need one table and one query.

    Add an additional "AppliesToPage" field to your table, and have it filled with the values 1 through 5, corresponding to the web page to which the record applies.

    Have a query that returns unique values from the table.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    • Marked as answer by iandwilliams Thursday, March 28, 2013 8:24 PM
    Thursday, March 28, 2013 8:12 PM
  • Thanks Douglas

    I will try that and see how it displays on the site.

    Regards

    Ian

    Thursday, March 28, 2013 8:24 PM