Results 1 to 14 of 14
  1. #1
    LeoJJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Singapore
    Posts
    4

    Question please help me! trying to build a database similar to a public library database


    Hi everyone!


    I'm trying to come up with a database for work. You can think of where I work as a library. New books (devices) will be bought occasionally and put into a common pool after some time users will borrow the books (on loan) and they will return it after they are done (back to common pool). Each user can borrow more than 1 device but each device can only be loaned to 1 user.

    Click image for larger version. 

Name:	Relationships.jpg 
Views:	47 
Size:	64.7 KB 
ID:	37947

    I have 5 devices that I have to keep track of. Each has a unique Serial Number and AFM_Tag. On the other side is User information and NRIC is a unique code for each user.

    At any given time all the device can be on 1 of the 5 status.
    1. On Loan
    2. Common Pool
    3. KIV
    4. Repair
    5. Transferred Out


    I need to track

    • which device is currently being loaned out to which user
    • status of each device


    I'll also need to create

    • loan form that user's fill in to indicate they have taken a device. (this is the part that I can't solve, how do you create a link between 2 different tables on demand?)
    • a return form when the user returns the device( this means that the on-loan status of the device will change to common pool status)


    I've tried a few different ways to create tables and relationship but can't seem to create a table that's dynamic. Is what I describe even possible on access? I'm hoping that I'm the problem not access XD

    Please help me!! thanks





  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont need a table for every device. 1 table for ALL devices, then the DEVICETYPE field (printer, scanner, PC, etc)
    (at least I do)

    the tDevices table should also have a OWNER field that has the ID of who currently has it. Null = available.
    but also a tLoanHistory table to keep the dates of loans and returns.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Might look at the MS LendingLibrary database template or the AssetTracking template.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    there are 16 library schemas here http://www.databaseanswers.org/data_models/
    Maybe they will provide some insight...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    LeoJJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Singapore
    Posts
    4
    Right now I've changed my table to look like this:
    Click image for larger version. 

Name:	Annotation 2019-03-31 162616.png 
Views:	42 
Size:	30.8 KB 
ID:	37973
    Click image for larger version. 

Name:	Annotation 2019-03-31 163018.png 
Views:	42 
Size:	54.5 KB 
ID:	37974
    But where do I place the owner field so that the device cannot be loan when already on loan?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Did you look at the LendingLibrary database?

    A device is available if there is no 'open' record in Loan table. Suggest you have date fields for date loaned and returned.

    Linking DeviceInfo and DeviceStatus on primary keys results in 1 to 1 relationship - might as well be 1 table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to the comments you have received so far, I'm suggesting you play "stump the model" with your current set up.
    See the link in my signature for Database Planning and Design --work through 1 of the tutorials from RogersAccessLibrary to experience the design process. Don't jump to physical Access database too quickly.
    Good luck with your project.

  8. #8
    LeoJJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Singapore
    Posts
    4
    I studying the lending library template. But can't seem to understand much of the inner workings. Is there somewhere I can refer to learn more about the template?

    I've played around with the database and it's exactly what I want to do. But I want to learn how to create it on my own and not just edit the names and start using it.

    What I don't understand is how simple the relationship is but yet it can check if the asset is available for check out.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I've not explored the db in depth. Keep in mind these templates often do things many of advise against. Such as:

    1. spaces in naming convention

    2. lookups in tables

    3. macros instead of VBA

    4. multi-value fields

    5. embedded attachments

    6. user interaction with tables/queries

    However, I hope that it will still help you learn concepts and techniques.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    I designed an IT Devices Registry app, which works without any main problems about 10 years no, and which does the same you described.

    Tables:
    tblDevices: DeviceID, DevGroup, Various fields describing device, CurrUserID, CurrUserLevel;
    tblUsers: UserID, UserLevel, Various fields describing user;
    tblTransactions: TransactionID, TransactionDate, DeviceID, UserID.

    In tblDevices, DevGroup is used to group devices of different types, like desktop computers, laptops, servers, displays, routers, etc.

    In tblUsers, along with real users are ones with names like "free device", "broken device", "archived device".

    In tblUsers the field UserLevel (values between 0 and 99) is used to group users. I use this to group users from different firms. And users like "free device" or "broken device" have a separate group. User "archived device" has his own separate group - 99. Also UserLevel can be used to keep real people and made-up ones like departments, workcentres, registering terminals etc. apart.

    The main form is unbound. There the user can select device group (DevGroup) and determine the filter condition for CurrUserLevel from combo boxes.

    On main form is single subform for devices, which is linked to DevGroup combo box on Main form. By default the filter condition for devices for is set to "CurrUserLevel < 99", i.e. all devices of this group which are not scrapped are enabled. When the user selects e.g. "Show archived devices" in combo in main form, the filter for devices subform is set to "CurrUserlevel = 99", and in subform only archived devices are available.

    The Devices form has a continuous transactions subform, linked to devices form with DeviceID. Here user can assign the active device in devices form to specific user. The combo box to select user is ordered by UserLevel, and by user name(s). Whenever the record is updated in this subform, CurrUserID and CurrUserLevel in tblDevices is overwritten with UserID and UserLevel of user in last transaction before current date. On certain date, only one user can be applied to certain device (there is an unique index combining TransactionDate, DeviceID and UserID).

    Of-course there is more, but this is enough to get the idea.
    Last edited by ArviLaanemets; 04-02-2019 at 06:21 AM.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may get some ideas from this free IT Assets data model from Barry Williams.
    See this link for various reference articles on Database Planning and Design
    I recommend that you get your design mapped out and tested with pencil and paper before getting too deeply involved in physical database. It is much easier to alter design in a paper-based model than a physical Access database.
    Good luck with your project.

  12. #12
    LeoJJ is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Singapore
    Posts
    4
    Thanks for helping! But there are still some areas that I don't understand. (Sorry still trying to learn how to use access)

    This is how I set up my relationships
    Click image for larger version. 

Name:	Annotation 2019-04-12 103711.png 
Views:	25 
Size:	77.3 KB 
ID:	38069

    Is this how I set up the UserLevel field?
    Click image for larger version. 

Name:	Annotation 2019-04-12 104019.png 
Views:	25 
Size:	60.1 KB 
ID:	38070

    In my tblDeviceInfo I've put in CurrUserID field and CurrUserLevel, but how do I fill up the 2 fields in a form? Do I have to create 2 controls to input records on the 2 different tables?
    Click image for larger version. 

Name:	Annotation 2019-04-12 104125.png 
Views:	25 
Size:	34.1 KB 
ID:	38071

    Sorry for having so many questions help you don't mind. :P

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    CurrUserID and CurrUserLevel are read from tblUserInfo and are written into tblDeviceInfo by procedure invoked when the any record bound to this device is added/updated/deleted in tblTransactions. They are used for filtering devices form, and displaying there the name of current user for this device. Setting relations for those fields is at least useless, if not dangerous.

  14. #14
    Join Date
    Apr 2017
    Posts
    1,673
    To write data into those fields into tblDevices are several possibilities. Today I'd write a couple of events for transactions form. About decade ago I used more difficult approach:
    I have an UDF (a useful one for any approach you may use)
    Code:
    Option Compare Database
    Option Explicit
    - - - - - - - -- - - - - - - - - - -
    Public Function ValidValue(parTable As String, IdField As String, IdCond As String, IdIsString As Boolean, RetField As String, DateField As String, parDate As Date)
        Dim dbs As Database
        Dim rs As Recordset
        Dim varQstr As String
        ' The function returns the value of RetField from table parTable
        ' where IdField equals IdCond and DateField is nearest past or equal to parDate.
        ' The parameter IdIsString must be True, when IdField has Text format
        
        On Error GoTo Err_ValidValue
        Set dbs = CurrentDb
        varQstr = "SELECT [" & RetField & "] FROM " & parTable & _
                " WHERE [" & DateField & "] <= " & Format(parDate, "\#mm\/dd\/yyyy\#") & _
                " And [" & IdField & "] = " & IIf(IdIsString, "'" & IdCond & "'", IdCond) & _
                " ORDER BY [" & DateField & "] DESC"
        Set rs = dbs.OpenRecordset(varQstr)
        rs.AbsolutePosition = 0
        ValidValue = rs.Fields(0).Value
        
    Err_ValidValue:
            rs.Close
            dbs.Close
            Set rs = Nothing
            Set dbs = Nothing
    End Function
    The function returns latest value from table with entry date before or equal of date determined with parameter.

    In devices form, I have a couple of invisible unbound text boxes with formulas as control sources (UserLevel 99 is for archived devices)
    Code:
    txtCurrUserID=ValidValue("tblTransactions";"DeviceID";[txtDeviceID];True;"UserID";"TransactDate";Date())
    txtCurrUL=IIf(Nz([txtCurrUserID];"")="";99;DLookUp("UserLevel";"tblUsers";"UserID='" & [txtCurrUserID] & "'"))
    Then BeforeUpdate event of devices form controls, when to write calculated data into tblDevices (In this app, normally all controls in devices form and transactions subform are disabled, except a combo for device selecting, and a combo where user can open the device for editing, or to save the record and disable all controls and subform. So even when user only edits/adds a transaction, the entry in tblDevices is always updated)
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        Me.txtCurrUserID.Requery
        ...
        If Not IsNull(Me.txtDeviceID) Then
            If Me.CurrUserID <> Me.txtCurrUserID Then
                Me.CurrUserID = Me.txtCurrUserID
            End If
            If Me.CurrUL <> Me.txtCurrUL Then
                Me.CurrUL = Me.txtCurrUL
            End If
        End If
    End Sub
    For additional security, devices form Current event also checks for same
    Code:
    Private Sub Form_Current()
        ...
        Me.txtCurrUserID.Requery
        ...
        If Not IsNull(Me.txtDeviceID) Then
            Me.CurrUserID = Me.txtCurrUserID
            Me.CurrUL = Me.txtCurrUL
            ...
        End If
    End Sub

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

Similar Threads

  1. Database - books in library
    By merowing3 in forum Database Design
    Replies: 6
    Last Post: 03-20-2018, 08:23 AM
  2. Replies: 18
    Last Post: 12-04-2017, 05:25 PM
  3. Library Database
    By Spaceman in forum Access
    Replies: 1
    Last Post: 08-09-2017, 10:47 AM
  4. Replies: 2
    Last Post: 03-22-2016, 04:35 AM
  5. Library database
    By LDP in forum Programming
    Replies: 1
    Last Post: 10-03-2012, 06:11 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