Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    3

    Question Unanswered: Working With Dates

    Hi,
    I'm a newbie - but moving along with learning Access. I am looking to pull report/query of all due assessments between two dates as follows:
    *LName *FName *Date of Assessment Due *Assessment Type
    Table is built with Assessment Type as the field label and dates populating each assessment type for a specific record. i.e.
    Lname Fname Assessment1 Assessment2 Assessment3
    Doe John 3/1/2013 3/11/2013 3/28/2013
    One person can have more than one assessment type due in a given time frame.
    What would be the best way to approach this?
    Any and all help is appreciated!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Hello and welcome!

    You've fallen at the first hurdle, due to the design of your table. Have a read up on normal forms. Your design here violates first normal form, by having numbered assessment due date fields. If a candidate has only one assessment due, two fields will be empty. If a candidate has more than three assessments due, how will you record this?

    To get around this, you need three tables:
    • A table of candidates (with a primary key field defined)
    • A table of assessment types (with a primary key field defined)
    • An intersection table that holds the key to the candidate with one or more assessments due, the key or keys to the assessment types and the due date for each one

    It could like something like:
    Code:
    tblCandidates
    C_ID      Forename       Surname
    1         John           Doe
    2         Jane           Smith
    
    tblAssessments
    A_ID      Name
    1         Health & Safety
    2         Site Induction
    3         Basic COSHH Knowledge
    4         Database Design
    
    tblAssementsDue
    C_ID      A_ID      DateDue
    1         1         2013-03-01
    1         2         2013-04-01
    2         2         2013-03-01
    2         3         2013-05-01
    2         4         2013-06-01
    You can then query the dates in the third table to find out which assessments are due, and link this table back to the candidate and assessment tables to find out the who and the what.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Mar 2013
    Posts
    3

    Thanks...

    Thanks for that!
    I'm so used to working with Excel that it's taking me time to adjust to the relational database concept.
    If I can ask what is probably another basic question to follow up on your answer, I had inputted code for the AssmtDate on a form (combo box) so that a drop down list is populated with the acceptable dates for that assessment. The date would then populate the field titled with that assessment name - which I now see was the wrong way to go about it. However, other than that it worked beautifully! I rearranged my tables as you suggested and my question is, how do I now associate the selected date on the form with that AssmtID/Type?
    i.e. (to use your examples)
    Type Select From DateRange ComboBox
    Health & Safety Date
    Site Induction Date
    Basic COSHH Knowledge Date

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Glad to be of use!

    There are two ways that you can set up the links, depending on whether it's easier for you to treat the candidates or the assessments as more important. For this example, I'm assuming that candidates are more important.

    Create a form for candidates and leave a large space at the bottom. This form should be set to show a single record at a time. It should include a field for C_ID.

    Create another form for the assessments. Set this one to display as a datasheet (not compulsory, but easiest for this purpose). The data source for the second form should be tblAssessmentsDue. Include a text box for C_ID, and another control that's a combo box, sourced from tblAssessments. The combo box's row source should be something like "SELECT A_ID, [Name] FROM tblAssessments;"Set the column count to 2 (because you're selecting two columns for it, and in the column widths box, set the sizes to hide the ID column (e.g. "0cm;5cm"). Set the bound column to 1. Add a final field that is the due date, again from the intersection table. In the form view, make sure that the fields are wide enough to show everything that you need to see.

    Now drag the second form onto the first. This will create a subform control, with the second form being the source. If the parent/child wizard pops up, it will probably make a guess as to the relationship between the tables. If not, open the subform's properties and make sure that the parent and child fields make sense. In this example, the parent field will be the field on the main form that holds the candidate ID, and the child field will be the field from the subform with the candidate ID.

    You can use this form to add new candidates or amend existing ones. Once you have added a new candidate, you can then select assessments for them from the combo box. It will display the name but store the ID in the table, and because you have set up a parent/child relationship, the C_ID field on the subform will always contain the ID of the candidate being displayed.

    If you get stuck, attach a copy of what you've got and I'll have another look.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Mar 2013
    Posts
    3
    Useful indeed!
    Thank you very much!
    I attached the database with bogus info to populate the main tables. The form named "MCR Tracker" is closest to what I am hoping the end result to look like - functionality-wise. (It is a long way from being polished up aesthetically.) I put this together before instituting your advice on splitting up the main table so behind the scenes it is a mess. This is the most integral form. I am looking to generate a report with assessments due between two dates and this will be the most integral report.
    I'd love to hear what you think.
    Thank you so much for your help!
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    I will take a look and see what I can do. Might not be for a few days, though.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •