Results 1 to 14 of 14
  1. #1
    Alx is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    5

    Unhappy attendance statistics

    Hi,



    I've recently been tasked by my superior to learn and implement Access to keep attendance records in a shelter.

    I currently have a record for each resident (resident ID and name (str) sex, age, language (ysn)). Some stay for a night, some for a month.

    I need to attach these informations to 10 available beds every day and then produce a report at the end of each month/semester/year on how many men, women,etc. have stayed with us this month/semester/year

    I tried making my first column the date/primary key and the following columns bed numbers, then entering the resident IDs where they were sleeping that night thinking I could then make a query to select the desired dates and add up the totals but I think I messed up somewhere...

    Does anyone have an idea of how I can make this happen?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Each entity should have its own table. 'Guests' is one. Beds is another. This way, you simply add/remove a bed if you get more/lose one. That brings up attributes of the entity. Is availability an attribute of a bed? Perhaps one needs repair and has to be taken out of the pool for some time? These are decisions that you have to make based on the process that the db needs to support. Do yourself a favour and read up on these topics (and others - see below), especially on db normalization. If you don't get a well designed foundation under your db, the rest of it will pose problems every step of the way. You can post back a pic of your relationship diagram (if you create relationships) for feedback.

    The short answer is that tblResidents and tblBeds along with a junction table to join the 2 is where you need to go.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names
    - http://allenbrowne.com/AppIssueBadWord.html

    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Micron beat me to it.

    Click image for larger version. 

Name:	Untitled.png 
Views:	34 
Size:	12.9 KB 
ID:	43944

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    @micron, would it be a good idea in this case to use a calendar table here? The calendar table would be pre-populated with each day for the next 100 years (whatever), day's could be marked unvailable for what ever reason, and using a Unique Index between a Calendar day and a bed you could guarantee you don't double book the bed. oh, and a unique index between the calendar day and resident ensures you don't double book a resident. I'm also thinking it might make it simpler to search against.

    Click image for larger version. 

Name:	Untitled.png 
Views:	34 
Size:	13.2 KB 
ID:	43948

  5. #5
    Alx is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    5
    Thanks for the links!

    The beds remain stable.
    The people who will be doing the data entry aren't necessarily well versed in computer use soI would like them to enter the data through forms (10 beds at a time every night) from roll up menus so I figured this would be the best but something seems off
    Attached Thumbnails Attached Thumbnails relations.png  

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Anytime you start numerating tables or fields, eg. strBed# or TblUsages_#, it's red flag that your design isn't normalized. The table structure I've posted above will accomplish what you've asked.

    You say that beds remain stable. Do you mean to say that you will always and forever have 10 beds? You'll never expand to have another one, or lose one in the future?

  7. #7
    Alx is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    5
    with field tags
    Attached Thumbnails Attached Thumbnails relations.png  

  8. #8
    Alx is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    5
    unless we move the facility, we'll always have the same amount of beds.

    I just saw your example, it looks alot better than mine, I just don't know how that will translate to a user friendly daily form?

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Can you show me form you're currently designing?

  10. #10
    Alx is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    5
    So here's what I have up to now...
    Couple quick questions...
    Is there a way to populate the calendar dates or do I have to fill them in by hand?
    When it comes time to compile my monthly report, will I have to make a totals query for every sex, language and age group (M, F, En, Fr, etc) before combining them with a query query?
    And finally, when I make my form, is there a way to lock the date and have a slot already numbered for every bed for my data entry people to just select the user name from a roll up menu? (I just want to know if it's possible)

    Thank you so much guys!
    Attached Thumbnails Attached Thumbnails relations.png  

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Been out grocery shopping so have been inactive here.
    Anything regarding functionality would be entirely based on the inputs and outputs desired. Yes, I would do unique indexes to prevent double booking. Don't know if I would have a table for dates when I can get that from a built in calendar control, but the point about not booking some days is worth considering if some days are days off - which I doubt.

    I see from post 7 that nothing I provided for education was read or absorbed or it was and is being ignored/not understood. In that case, I have nothing further to add if the chances are it will be ignored or go unquestioned anyway. The design is like that of a spreadsheet, and not just the beds - the genders in tblUsages are another example.

    EDIT - my apologies if you read/will read those links but just posted what you already have. Your current schema is missing the mark by a wide margin, which I hope you will figure out once you grasp normalization.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Alx View Post
    So here's what I have up to now...
    Couple quick questions...
    Is there a way to populate the calendar dates or do I have to fill them in by hand? See below for example code. This assumes you NEED a separate calendar table.
    When it comes time to compile my monthly report, will I have to make a totals query for every sex, language and age group (M, F, En, Fr, etc) before combining them with a query query? I would need to know more specifically what you're trying to query. Regarding the age group table, it might be advisable to calculate the age group at the time of the report based on the date of birth. This way you won't need to update an age group table in as people graduate to the next group.
    And finally, when I make my form, is there a way to lock the date You can lock the control in the properties window and have a slot already numbered for every bed for my data entry people to just select the user name from a roll up menu? (I just want to know if it's possible) Yes! Just have a command button that will query all the beds, then loop over the beds and for each one add a record to TblPresence for that day. See below for example code.

    Thank you so much guys!
    Code:
    Public Sub test()
        'PopulateCalendarTable Date, 10000
        
    End Sub
    
    Public Sub PopulateCalendarTable(StartDate As Date, NumOfDays As Long)
    On Error GoTo ErrHandler_PopulateCalendarTable
        Dim db As DAO.Database
        Dim qry As String
        Dim x As Long
        
        'parameter validation
        If NumOfDays < 1 Then
            Err.Raise 1001, , "Number of days to add must be greater than or equal to 1."
        End If
        
        Dim LastDate As Variant
        LastDate = DMax("CalendrierDate", "TblCalendrier")
        If Not IsNull(LastDate) Then
            If LastDate >= StartDate Then
                Err.Raise 1002, , "You're attempting to add calendar days that are earlier than or equal to records that already exist."
            End If
        End If
        
    
        Set db = CurrentDb
        For x = 0 To NumOfDays - 1
            qry = "INSERT INTO TblCalendrier(CalendrierDate) VALUES (#" & (StartDate + x) & "#);"
            'Debug.Print qry
            db.Execute qry, dbFailOnError
        Next x
        
    
    ExitHandler_PopulateCalendarTable:
        Set db = Nothing
        Exit Sub
    
    ErrHandler_PopulateCalendarTable:
        MsgBox Err.Description, vbInformation, "PopulateCalendarTable: Error #" & Err.Number
        Resume ExitHandler_PopulateCalendarTable
    End Sub
    Code:
    Private Sub LoadBeds(CalendarId as Variant)
    On Error GoTo ErrHandler_LoadBeds
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim rs2 As DAO.Recordset
        Dim qry As String
        
        If IsNull(CalendarId) Then Err.Raise 1001, , "Calendar ID is Null"
    
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT * FROM TblPresence WHERE DateCalendrier=" & CalendarId)
        If Not (rs.BOF And rs.EOF) Then
            'Query found some records
            Err.Raise 1002, , "Records already exist for this day."
        Else
            'Query didn't find anything so let's add the beds
            Set rs2 = db.OpenRecordset("SELECT * FROM TblLit")
            If Not (rs2.BOF And rs2.EOF) Then
                'got a list of all the beds, add records to the occupations table
                rs2.MoveFirst
                Do While Not rs2.EOF
                    
                    rs.AddNew
                    rs!DateCalendrier= CalendarId
                    rs!Lit = rs2!BedId
                    rs!Usager = Null 'tbd by user in GUI
                    rs.Update
                    
                    rs2.MoveNext
                Loop
                
            Else
                'Query didn't find any beds
                Err.Raise 1003, , "The beds table appears to be empty"
            End If
            rs2.Close
            
        End If
        rs.Close
        
        'REQUERY YOUR SUBFORM HERE TO SHOW THE BEDS
    
    ExitHandler_LoadBeds:
        Set rs = Nothing
        Set rs2 = Nothing
        Set db = Nothing
        Exit Sub
    
    ErrHandler_LoadBeds:
        MsgBox Err.Description, vbInformation, "LoadBeds: Error #" & Err.Number
        Resume ExitHandler_LoadBeds
    
    End Sub
    Last edited by kd2017; 01-19-2021 at 07:05 PM.

  13. #13
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    "Yes, I would do unique indexes to prevent double booking. Don't know if I would have a table for dates"

    I'd hate to overcomplicate your project, op. Should you find a calendar table isn't necessary you can certainly use a date field in the junction table instead. one caveat to be aware of is that date datatypes also include time. So for example you'll want to safeguard against a user accidently booking a bed on Jan 20 at midnight and again at noon.

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Alx-davegri-v01.zip

    See if this is close to your requirements:

    Click image for larger version. 

Name:	alx.png 
Views:	25 
Size:	28.6 KB 
ID:	43953

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating YTD Statistics
    By bradical987 in forum Access
    Replies: 2
    Last Post: 11-25-2015, 06:17 AM
  2. Having statistics
    By hamidchi in forum Queries
    Replies: 1
    Last Post: 09-12-2015, 05:34 PM
  3. Summary Statistics
    By carojasa in forum Queries
    Replies: 4
    Last Post: 02-21-2015, 11:56 AM
  4. Replies: 6
    Last Post: 01-16-2014, 12:41 PM
  5. library statistics
    By sonia in forum Access
    Replies: 1
    Last Post: 04-11-2010, 12:22 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