Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    ultrarunner2017 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    44
    Thanks, Ajax;
    I seem to have been approaching this from the wrong angle;


    I had always thought the hierarchy should be Artists > Albums > Songs, and when I tried a many to many join table, I only joined artists and albums, but not songs.
    I will certainly take your advice and go with all three tables being joined by tbl_AlbumArtistSongs.

  2. #17
    ultrarunner2017 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    44
    OK. With the database set up as you recommended (still need to add details), I have one issue:
    Because the PK fields are what is linked, it looks like I will need to enter the PK number, rather than the text, such as album title or artist name.
    But I did find a method to allow entry of text data rather than the numeric PK, using the 'SELECT DISTINCTROW' function with the fields I want to use (which must include the PK or course).
    The issue with this is it requires the field to be a lookup, which I have been strongly advised against. I am well aware that queries won't function as expected when lookup fields are included.
    That said, it will make entering data so much easier if I use this method.
    However, I should be able to do what I want using forms, rather than the lookup. So I will first test the basic design I now have, and start adding queries and forms.

    With all the talk about how bad lookup fields are, there must be a purpose for them, where they do not cause problems.
    Once again, I do need to do a lot more study.
    While I have been pointed to some great resources online, I guess I'm old-school in that I prefer a book over searching multiple online sources.
    Since I already have the Access 2010 Bible, and the Access 2010 'Missing Manual' as e-books, I am going to have a look at them before purchasing any new ones. What I am doing in Access 2019 is probably no different that what I would do in Access 2010, so those old texts are probably still relevant.

    Again, I thank you for helping me out with this.

    FW

  3. #18
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Because the PK fields are what is linked, it looks like I will need to enter the PK number, rather than the text, such as album title or artist name.
    ...
    The issue with this is it requires the field to be a lookup, which I have been strongly advised against
    the whole idea is to use forms for data entry and forms/reports for data extraction. tables and queries should never be accessed directly by a user. You would use a combo box of the form to do your lookup
    With all the talk about how bad lookup fields are, there must be a purpose for them, where they do not cause problems.
    not so far as I am aware, their benefit is perceived as 'create the lookup in the table and it is automatically created in a form or report. Aside from the issues of which I am sure you are aware in interpretation (store number/see text) they don't work in queries (i.e. you still need to join the table) and do you really want to see the down arrow in reports?

  4. #19
    ultrarunner2017 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    44
    the whole idea is to use forms for data entry and forms/reports for data extraction. tables and queries should never be accessed directly by a user. You would use a combo box of the form to do your lookup
    I guess my problem with Access is that I have always been using it on a personal level, where I am the only user. I have no issues with entering data directly into tables, but I understand that is not how Access was designed to work, and it is bad practice.
    I will add the necessary forms and queries to this db before I start entering any data. I just need to be patient with learning it. I have a bad habit of reading a few paragraphs of instruction, then jumping right into designing my db.

  5. #20
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255

    Example

    This MusicAlbumsManagement.zip is an example of how the project could be developed

  6. #21
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    This MusicAlbumsManagement.zip is the modified example as there was an error when opening.

  7. #22
    ultrarunner2017 is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    44
    Quote Originally Posted by CarlettoFed View Post
    This MusicAlbumsManagement.zip is the modified example as there was an error when opening.
    I've been away from Access for a while, just downloaded the file today.
    Now, I only need to get to the point with my knowledge of Access to build the database you did.
    I'm still studying the books I have on hand. I'll get there eventually. I really do want to learn this, but sometimes I get frustrated, and walk away from it for a while.

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

Similar Threads

  1. Replies: 18
    Last Post: 08-09-2018, 06:45 AM
  2. A quick way to copy all tables from a database (data only)
    By earlcools in forum Import/Export Data
    Replies: 1
    Last Post: 10-13-2017, 02:48 AM
  3. Replies: 4
    Last Post: 05-25-2017, 06:07 AM
  4. Replies: 1
    Last Post: 05-16-2016, 05:58 AM
  5. Copy data from table in another database
    By jcc285 in forum Import/Export Data
    Replies: 8
    Last Post: 04-07-2016, 05:12 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