Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116

    Can you critique my database please?

    Hi,



    I created an Access database, which I use for storing musicians' details, engagements etc. I've removed all the sensitive information and attached it with this post. If anyone gets any time, would you mind checking it over for me please?

    I have a few concerns in particular. They are:

    When I use the version with all my data in, whenever I enter a new player using the F_Musicians form, it updates the MusiciansDetails table, but not the MusiciansDetails Query. However, with this test version is seems to be working fine. Do you have any idea why this is happening? Edit: As soon as I manually enter the number in the GeographicalArea field in the T_MusiciansDetails table, then the MusiciansDetails query updates. That's a little bit annoying, but at least I know the reason for it now.

    With the F_Musicians form, on the first tab. the GeographicArea is displayed from a subform. It looks ugly though! All I want ideally is to look identical to the other boxes on the screen, but I don't know how to achieve this. Also, if I add a new player, it seems that I can't enter any data into it. Maybe a drop down list would be good.

    Similarly, on the instruments tab, I can't change the data there.

    On the MusiciansDetails Query, I can't add new data. Perhaps it's read only, and related to my first concern above?

    I've probably got lot of other questions that I can't think of at the moment, but if anyone can offer any advice I'd be very grateful.

    Thanks.
    Attached Files Attached Files

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Neil,
    Please have a look at the attached. I have modified your tables (added primary key and indexed all foreign keys), and changed your F_MusiciansDetails form to address all your issues above. You may want to pay attention to the use of combos and related record source changes to the subform (based on the table now instead of the non-editable query) for the instrument selection.
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    See if this helps.

    NeilS-davegri-v01.zip

    I worked mainly on the musician form and subform.
    Eliminated the first tab, as it had same source as main form. Moved it's controls to main form.
    Reworked the instruments subform.
    The geographic didn't need to be a subform, combobox will do.
    The Financial tab has same source as main form, but maybe reduces clutter, so it's OK.

    Click image for larger version. 

Name:	Neil.png 
Views:	40 
Size:	25.1 KB 
ID:	44290

  4. #4
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks a lot to both of you, that's amazing. Unfortunately I'm not at home right now, but I'll have a look when I get home tonight.

  5. #5
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    I've just tried them both out now, there are really great. Thanks a lot for your time and effort.

    I notice with both, in order for me to enter the instruments to the instruments tab of the musicians form, I need to enter the unique player code first. Normally, I let my VBA code generate a unique code when I click on save, but I guess I need to also get the VBA route to initiate if I click on the instruments tab (or after I've entered the surname, I'm not sure yet).

    Logistically, in order for me to incorporate your improvements, should I try to somehow copy my data from my original version to the one you created (I'm not sure exactly how to go about this yet)? I don't know how else to go about it though.

    Thanks again.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I notice with both, in order for me to enter the instruments to the instruments tab of the musicians form, I need to enter the unique player code first. Normally, I let my VBA code generate a unique code when I click on save, but I guess I need to also get the VBA route to initiate if I click on the instruments tab (or after I've entered the surname, I'm not sure yet).
    And that is why most developers would use an autonumber primary key in that table. You can create a fancy unique ID for your musicians like you have it now, but shouldn't be used in the database relationships.

    I am not sure if Dave did anything with your tables. I have updated quite a few of them (added indexes to foregin keys, added primary keys, removed @ from format property in a large number of Short Text fields, etc.) so I suggest you try to keep my set and populate it with your old data.
    To do that:
    1.Empty the tables
    2.Run a Compact and Repair to reset the autonumbers
    3.In the External Data tab of the ribbon clcik on Access and link your old tables that have the data you want to import.
    4. Create a series of Append queries to copy the data from the link tables (they will have a 1 suffix at the end of their name) to the now empty tables.
    5.Remove (delete) the linked tables and do another Compact and Repair.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Many thanks Vlad. I'll give that a go tomorrow.

    Also, maybe I should let the MusiciansDetails table generate an auto number, which would solve that problem. I'll see if I can make that work, without messing everything up!

    I'll let you know how I get on.

  8. #8
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Morning Vlad,

    I've reattached the database you edited before, but this time I've created an autonumber in the MusiciansDetails table, and a corresponding foreign key in the InstrumentsPlayers and Payments tables. I've adjusted the relationships to reflect this.

    The only thing I'm not sure how to do yet, is to edit the Musicians form. I'm hoping to replace the PlayerCode with the autonumber, so that I'm able to add new instruments for a new player. Could you describe how I go about this please?

    Many thanks.
    Attached Files Attached Files

  9. #9
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    I've been working on it a little more, so this version is more updated. I'm just stuck on the Musicians form now. I don't know how to get the Instruments tab to work properly.

    I've got an auto numbering system for the MusiciansDetails table, so I guess the Musicians form can latch on to this instead?

    Many thanks in advance for any help.
    Attached Files Attached Files

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi, I think you attached the wrong file, this is the original test db from yesterday that doesn't have the autonumber and any of my changes.
    Are you keeping the PlayerCode as an extra id along with the autonumber?

    I know you say you already added the autonumber but it can be done in a way that minimizes the follow up work needed, specifically ensuring the queries are still working:
    1.Delete the two relationships for the T_MusicianDetails table.
    2.Instead of adding a new field and setting that as autonumber rename PlayerCode_PK (the table must be empty) to Musician_PK and change the data type from Short Text to Autonumber.
    3.Open the two related tables and change the name of the foreign key to Musician_FK and the data type to Number (Long). Again tables must be empty.
    4.Restore the relationships and run a Compact and Repair under Database Tools.
    5.Open T_MusicianDetails in design view and add the PlayerCode (Short Text) field if needed.

    Now the queries should have been updated by the built in Name Autocorrect feature of Access to replaces the old joins between PlayerCode_PK with Musician_PK.
    On the Musician form you can leave the Save code but you need to comment out the last line that moves you to a new record. Probably would be best to rename the button "Generate Player Code" or get rid of it and move the code to a different event all together (such as the BeforeUpdate event of the form).
    On the form you would need to add the new Musician_PK field as a textbox then change the master\child linking of the instruments subform to reflect the new names and you should be done.

    Please post back with your progress or if you need extra help!

    EDIT: Just saw your new post after I posted this, I'll have a look at the latest version now.
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks Vlad,

    I'm not sure what's happening, because when I clicked on my link just now it was showing the updated version. Would you mind checking, on the MusiciansDetails table, is the first field called ID_PK? If so, that the auto numbering field I added today. If it's not showing for you I'll try uploading it again.

    Many thanks.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you done any other than adding the autonumber to MuscianDetails and the other two tables?
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    [QUOTE=

    EDIT: Just saw your new post after I posted this, I'll have a look at the latest version now.
    Cheers,
    Vlad[/QUOTE]

    Many thanks.

  14. #14
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Quote Originally Posted by Gicu View Post
    Have you done any other than adding the autonumber to MuscianDetails and the other two tables?
    Yes, I've added the foreign keys from the autonumber to the MusicianPlayers and to the Payments tables. I've edited the queries a little to make them work. And I've messed around with the form a little, and made it worse!

    I might have done something else but I've gone blank now.

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please review the latest version, I have done what I mentioned in today's previous post, all queries and reports should work OK. The Musician form is working OK now. You don't need to preserve the PlayerCode in the T_Payments and T_InstrumentPlayers as it is always available in a query from T_MusicianDetails (one of the main rules of database design is to avoid data duplication).

    If this is OK then you can go to loading your old data. I think it should be straight forward as I explained in another post. Just wanted to mention one "subtlety": when loading the old data for the payments and instrumentplayer tables you will need to bring in the append query the new (already populated) T_MusicianDetails because you need to join them by the PlayerCode and append into the new tables the MusicianPK (autonumber) to the Musician_FKs of the two tables.

    Let me know if any of this is too confusing and you need some more info.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. DB Design Critique
    By Sanguineus in forum Access
    Replies: 8
    Last Post: 12-14-2018, 10:28 AM
  2. Replies: 2
    Last Post: 03-16-2018, 10:19 AM
  3. Replies: 1
    Last Post: 03-21-2015, 11:55 AM
  4. Please critique table design
    By Jennifer Murphy in forum Access
    Replies: 2
    Last Post: 02-01-2014, 11:45 PM
  5. Request for design review / critique
    By Charles7565 in forum Database Design
    Replies: 3
    Last Post: 10-26-2011, 02:04 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