none
MS Access DB RRS feed

  • Question

  • I have a training tracker, I am developing in Access 2010. I have 2 tables, one table to hold the Class information, and the second is a list of the employee. I created a query that tied the two fields together, but I cant add a completed date. Below is the SQL i am using. Can someone assist in being able tro a a completed date for the course?

    SELECT Classes.[Class Name], Classes.[Expiration in Days], Classes.CLPs, Classes.[Cert Required], Classes.[Class Priority], Classes.[Required Groups].Value, [USAMRAA Users].SAMAccountName, [USAMRAA Users].[Last Name], [USAMRAA Users].[First Name], [USAMRAA Users].Branch, [USAMRAA Users].Division, [USAMRAA Users].Group, [USAMRAA Users].[Branch Chief], Classes.[Date Completed]
    FROM Classes INNER JOIN [USAMRAA Users] ON Classes.[Required Groups].Value = [USAMRAA Users].Group
    WHERE ((([USAMRAA Users].SAMAccountName)=GetUserName()))
    ORDER BY Classes.[Class Priority];


    Lee Mossolle

    Thursday, November 2, 2017 3:30 PM

Answers

  • How did you set up the classuser tbl?
    I simply built it in table design view.  Then, having amended the names of the keys of the other two tables I created the relationships.

    As regards groups I would not use multi-valued fields for these (or for anything else, so I'd do the same for the certificates required), but model the two many-to-many relationship types between classes and groups in the conventional way by means of tables (ClassGroups and ClassGroupsRequired in the image below).  The interface would again be by means of subforms within a classes parent form.  To save space each subform within the parent form can be on a separate page of a tab control.  The FormsDemo file in my same OneDrive folder includes an example of this.


    Ken Sheridan, Stafford, England

    • Marked as answer by lmossolle Wednesday, November 15, 2017 7:13 PM
    Wednesday, November 8, 2017 4:36 PM

All replies

  • Hi Lee,

    Welcome to Office 2010 forum.

    I notice that the issue is related to some codes in Access 2010. To better resolve the issue, I will move the thread to Access for Developers forum for more help.

    Thanks for your understanding.

    Best Regards,
    Winnie Liang


    Please remember to mark the replies as answers if they help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, November 3, 2017 6:58 AM
  • Please post a relationship diagram of the relevant tables.

    2 tables seems too few.


    -Tom. Microsoft Access MVP

    Sunday, November 5, 2017 2:17 AM
  • As Tom says, two tables are likely to be insufficient.  There is almost certainly a many-to-many relationship type between employees and classes, in which case, at the very least you will need to model this by a third table which resolves the relationship type into two one-to-many relationship types.  The completed date is an attribute of the relationship type, so would be a column in this third table.

    You'll find an example of a basic many-to-many relationship type like this, and how it can be represented in the user interface as StudentCourses.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates a number of possible user interfaces, but you would use a conventional form/subform as, unlike the other options, this allows you to include non-key attributes in the subform, status in my case, date completed in yours, by binding a control in the subform to the relevant column in the table which models the relationship type (StudentCourses in my demo).

    Ken Sheridan, Stafford, England

    Sunday, November 5, 2017 11:34 PM
  • Hi Imossolle,

    What's your current issue? What do you mean "I cant add a completed date"? Will the SQL get error if it contains  the Classes.[Date Completed]? I would suggest you provide details about your current issue so we could try to reproduce your issue. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 6, 2017 8:40 AM
  • Can you take a look at the DB? Not sure why I am not able to add a completed date

    https://1drv.ms/u/s!AsO0YaRZhzJMgQXQ9RMWvp9zRXyQ


    Lee

    Wednesday, November 8, 2017 12:38 PM
  • Can you take a look at the DB? Not sure why I am not able to add a completed date

    https://1drv.ms/u/s!AsO0YaRZhzJMgQXQ9RMWvp9zRXyQ


    Lee

    Wednesday, November 8, 2017 12:38 PM
  • Can you take a look at the DB? Not sure why I am not able to add a completed date

    https://1drv.ms/u/s!AsO0YaRZhzJMgQXQ9RMWvp9zRXyQ


    Lee

    Wednesday, November 8, 2017 12:38 PM
  • As I said previously, you need to model the many-to-many relationship type between classes and users by a table which resolves it into two one-to-many relationship types.  See the image below from an amended copy of your file:

    1.  Note that I have changed the names of the keys to ClassID and UserID to avoid ambiguity.  The generic 'ID' should never be used as a column name.

    2.  DateCompleted is an attribute of the relationship type, so is a column in the ClassUsers table which models the relationship type.

    3.  Note that both relationships are enforced.  An unenforced relationship serves no useful purpose whatsoever.

    4.  The user interface for a model like this would usually be a classes form within which is a class users subform, in continuous forms view and linked to the parent form on ClassID.  You'll find an example in StudentCourses.zip in my public databases folder at:

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly. 

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates a number of possible interfaces for a basic many-to-many relationship type like this, but you will need to use a standard form/subform as the other methods do not allow for a non-key attribute such as your DateCompleted.

    5.  I've assumed that each user can take each class once only, hence the two-column primary key of ClassUsers, but if they can take a class more than once the DateCompleted column should also be included in the primary key.

    Ken Sheridan, Stafford, England

    Wednesday, November 8, 2017 1:38 PM
  • Can you upload the amended copy? Thanks!

    Lee

    Wednesday, November 8, 2017 3:01 PM
  • Can you upload the amended copy?

    I don't see any necessity for that.  You merely have to create the additional table, and make the few small amendments to the existing two.  You can then create the relationships, ensuring that referential integrity is enforced, and build the form/subform, using my StudentCourses demo as a guide.  You'll learn more from doing it yourself than you would from my doing the work for you.



    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Wednesday, November 8, 2017 3:43 PM Typo corrected
    Wednesday, November 8, 2017 3:39 PM
  • How did you set up the classuser tbl? With pulling the ID's? I currently have a class assignment by a selected group. it shows data if you are currently assigned to that group

    Lee

    Wednesday, November 8, 2017 3:46 PM
  • How did you set up the classuser tbl?
    I simply built it in table design view.  Then, having amended the names of the keys of the other two tables I created the relationships.

    As regards groups I would not use multi-valued fields for these (or for anything else, so I'd do the same for the certificates required), but model the two many-to-many relationship types between classes and groups in the conventional way by means of tables (ClassGroups and ClassGroupsRequired in the image below).  The interface would again be by means of subforms within a classes parent form.  To save space each subform within the parent form can be on a separate page of a tab control.  The FormsDemo file in my same OneDrive folder includes an example of this.


    Ken Sheridan, Stafford, England

    • Marked as answer by lmossolle Wednesday, November 15, 2017 7:13 PM
    Wednesday, November 8, 2017 4:36 PM