Results 1 to 11 of 11
  1. #1
    rik1254 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2012
    Posts
    5

    Few relational database questions

    Hello all

    Im probably being really thick as Ive been staring at the same screen for the past 6 weeks trying to sort this out.
    I will probably keep updating this with issues as I go along, but currently I have:

    1) I want to create an IF statement, so for example (Im doing a database on a bus company) a driver needs a specific licence to drive certain busses.
    How can I do it, so if I choose a driver with a normal licence, it will only show me the busses that require a standard licence?



    2) In a query, I want to search for multiple items within the same column. In this case I need to search for items 1, 6, 11, 16, 21 out of a list of 25. Ive tried "=1;6;11;16;21" and 1;6, etc...., but I think Im just being thick and cant grasp it. [Sorted....kind of]

    Any help would be great, or if someone has the time, I can brief you on everything and help me start from scratch if thats easier.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The following statement implies that you might have a structural issue with your database:
    search for multiple items within the same column
    In a relational database, you would not have multiple items within the same column (field). Each item should be a record.

    Can you provide more details on how you have structured your tables?

  3. #3
    rik1254 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2012
    Posts
    5


    The column highlighted in red is the column Im trying to query into groups 1;6;11;16;21, as in the column next to it is the charge band (cycles from A-E). Ive sorted it now as I changed the query from the groups to the charge band, just had to add an additional column to the query.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    To group the records that all have an A in the charge band field, you would just sort by that field & it sounds like you have done that, but how do does that relate to drivers, license type and the buses?

  5. #5
    rik1254 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2012
    Posts
    5
    Thats a different issue (hence the points 1) and 2) )

    With the drivers, you can have licences X Y Z and the busses require a certain licence to drive. i.e. a mini bus requires X, normal bus requires Y and a stretched bus requires Z.
    Once I choose a driver, I then want the database to only allow me to select busses within that licence group and below (so if the drive has licence Z, they can drive all busses).

    I think thats the best I can explain it without writing out my assignment brief

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Since this is an assignment, I will just give you some of my thoughts.

    First, I would associate a ranking number with the license type since you cannot do very good comparisons with text fields. I would set that up in a table

    tblLicenseTypes
    -pkLicTypeID primary key, autonumber
    -txtLicenseType (your x, y, or z)
    -longRank (a long integer number field)

    With Z having the highest rank, I would give it a 3, Y a 2 and x a 1

    Since a driver can have many license types, that is a one-to-many relationship. Further since a license type can apply to many drivers, you have another one-to-many relationship. When you have 2 one-to-many relationships between the same to entities, you have a many-to-many relationship which requires a junction table.

    tblDriverLicenseTypes
    -pkDriveLicTypeID primary key, autonumber
    -fkDriverID foreign key to the driver table
    -fkLicTypeID foreign key to tblLicenseTypes

    You will also need to reference the license type for the buses.

    From there you can use a query to get the maximum license type for each driver. Then you can create another query to compare the max license type of the driver to the license type of the buses using the ranking field I mentioned. (where license rank of the driver >= that of the bus)

  7. #7
    rik1254 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2012
    Posts
    5
    Thank you for that

    Ive currently got a friend helping me who done this assignment last year, but I will try to implement what you've said as it makes a lot more sense than what we're trying to implement.

    Ill keep you posted.

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Good luck; please post back with any questions

  9. #9
    rik1254 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2012
    Posts
    5
    We've gone and confused ourselves now, but in the process, we took a break and created the input forms.
    During this we then found we can validate certain parts, which using a part of your suggestion, can end this misery of a database.

    What Ive now done is:
    Created a form with the following fields:

    Daily ID
    Date
    Emp ID
    Emp name
    Emp licence type (in numeric values)
    Bus registration
    Bus type
    Bus licence needed (in numeric values)

    So what Im trying to do now is validate the Bus Licence needed field, so you cannot choose a bus that requires a higher licence.

    My Validation rule (is using MS access built in features)
    <=[licence type]
    So less than or equals to the value in the licence type field

    My problem now is that when I try and choose an option (valid or invalid), the error messege given is a generic messege saying that my validation rule is wrong.


    Any ideas?
    I can take a screenshot of the form and everything if that would help.

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You have to have the full expression

    [bus licence needed]<=[licence type]

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Suggestions:

    Do not use spaces in object names. Only use letters, numbers and the underscore. Examples: "Bus_licence_needed" or "BusLicenceNeeded". Spaces in names will cause you headaches!! (Being Picky: license)

    "Date" is a reserved word and shouldn't be used in object names, plus it is not very descriptive. "Date" of what? Driver DOB. When he was hired, etc.....
    Here is a list of reserved words:
    http://allenbrowne.com/AppIssueBadWord.html

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

Similar Threads

  1. Need to hire out relational database design
    By janakybrent in forum Access
    Replies: 5
    Last Post: 02-19-2016, 05:11 AM
  2. Replies: 7
    Last Post: 11-02-2012, 08:31 AM
  3. Setting up relational database
    By aceoftrades in forum Access
    Replies: 1
    Last Post: 02-22-2012, 08:42 AM
  4. Relational Database Design Questions
    By mribnik in forum Database Design
    Replies: 40
    Last Post: 08-09-2011, 02:57 PM
  5. Relational Database/query help
    By Champin4 in forum Queries
    Replies: 7
    Last Post: 07-14-2010, 08:49 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