Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Lostandconfused is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    8

    Question Help me understand relationships!! please!

    Hi guys,

    I am new to this forum and using Microsoft Access, it seems like a great piece of software. Long story short I am taking an online class and there is a requirement to design a table for an assignment, I am not looking for someone to do it for me! But for guidance as there is no instructions apart from some you tube links to follow, I have done so and understand how to create tables and fields. I also understand how to link tables, but I am not sure if I am linking them correctly based on the instructions.



    Using Microsoft Access, create the following traffic ticket database with its associated tables, fields (including appropriate data type), primary keys and foreign keys. A traffic ticket is a notice issued by a law enforcement official to a motorist accusing violation of traffic laws.

    This are the instructions:

    Note:
    make sure to create the relationships between your tables before adding the records in your tables. (MS ACCESS)


    • The motorist information includes driver license number, name, zipcode, state. The motorist is uniquely identified by his/her license number. Create at least 5 unique motorist records.
    • The traffic offense type includes the offense description (i.e. red light) and its designed fine amount (i.e. $158). Each traffic offense type is uniquely identified by its offense description. Create at least 3 unique traffic offense type records.
    • The law enforcement official information includes badge number, name, and year in service (i.e. 2). The law enforcement is uniquely identified by his/her badge number. Create at least 3 unique law enforcement official records.
    • The traffic ticket includes the ticket number, date issued, the motorist’s driver license number, the traffic offense type’s description, and the law enforcement official’s badge number. The traffic ticket is uniquely identified by its ticket number. Create at least 8 traffic ticket records. One motorist may have more than one traffic ticket.

    A traffic ticket is issued by a specific law enforcement official to one and only motorist. A law enforcement official can issue many traffic tickets and a motorist can received many traffic tickets.

    • Create one query named "Invoices" using "Query Design" that outputs the total (aka SUM function) amount of the traffic tickets due and total number (aka COUNT function) of traffic tickets received by each individual motorist's license number. The outputs should also display the name of the motorists.

    ​Thank you!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How can we know if you linked them correctly according to instructions if you don't show what you did? Provide image of Relationships window or attach db.
    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.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would you show a picture of the relationship window or attach your dB so we can see what you are doing/have done?

  4. #4
    Lostandconfused is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    8
    Quote Originally Posted by ssanfu View Post
    Would you show a picture of the relationship window or attach your dB so we can see what you are doing/have done?
    Yes I am sorry, see image attached.
    Attached Thumbnails Attached Thumbnails Relationships.jpg  

  5. #5
    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
    Here's a link to lots of info on database planning and design.

    Some of the videos may be helpful to you since they discuss concepts and examples.
    The tutorials from RogersAccessLibrary are great but you have to work through them --you will learn.

    FYI --Access does not treat names with embedded spaces very well. Suggest you create names without embedded spaces

    Relationships are how you implement business rules in the database.
    eg - A Driver may incur many Fines. That is a Driver may have 0,1 or Many Fines. (1 to Many)

    Each table should be about a single subject (Person, Concept, Event...)
    Every table in a relational database should have a unique identifier (Primary Key) which uniquely identifies it from every other record in that table.

    For OffenseType consider:

    OffenseID (PK)
    OffenseName
    OffenseDescription
    OffenseFineAmount


    Good luck with your course.
    Last edited by orange; 06-27-2018 at 05:33 PM.

  6. #6
    Lostandconfused is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    8
    Thank you for the answer and links Orange, as it relates to the work I have done am I completely wrong in relation to the instructions?

    When it comes to the relationships linked and databases created.

    Thank you,

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You are not linking on autonumber which is fine by me but many would argue you should.

    I do advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

    Otherwise, links look valid to me. Did you set links to enforce referential integrity?
    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.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    With the exceptions that orange and Jun noted, looks good to me.
    Also note that "State" and "Description" are reserved words and shouldn't be used as object names.


    Microsoft Access Tables: Primary Key Tips and Techniques (Read this several times)
    Autonumbers--What they are NOT and What They Are



    Here is how I would design the dB (might not meed your requirements for the class because of the PK/FK fields)
    Click image for larger version. 

Name:	Relationship3.png 
Views:	40 
Size:	118.2 KB 
ID:	34565

  9. #9
    Lostandconfused is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    8
    Quote Originally Posted by June7 View Post
    You are not linking on autonumber which is fine by me but many would argue you should.

    I do advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

    Otherwise, links look valid to me. Did you set links to enforce referential integrity?
    Hey June,

    Thanks for taking the time to answer, I will remove any spaces as noted by you and Orange.

    Yes I linked and enforced referential integrity on all of them.

    Thank you,

  10. #10
    Lostandconfused is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    8
    Quote Originally Posted by ssanfu View Post
    With the exceptions that orange and Jun noted, looks good to me.
    Also note that "State" and "Description" are reserved words and shouldn't be used as object names.


    Microsoft Access Tables: Primary Key Tips and Techniques (Read this several times)
    Autonumbers--What they are NOT and What They Are



    Here is how I would design the dB (might not meed your requirements for the class because of the PK/FK fields)
    Click image for larger version. 

Name:	Relationship3.png 
Views:	40 
Size:	118.2 KB 
ID:	34565
    Thanks so much Steve, that is above and beyond I will borrow some of your design and apply it best I can. And change the reserved key words for State and Description.

  11. #11
    Lostandconfused is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2018
    Posts
    8
    Hey guys,

    So finally I got the records in and thanks to the help I figured out the relationships, i'm on the last step, trying to create a query, but I keep getting an error, does anyone know why?


    • Create one query named "Invoices" using "Query Design" that outputs the total (aka SUM function) amount of the traffic tickets due and total number (aka COUNT function) of traffic tickets received by each individual motorist's license number. The outputs should also display the name of the motorists.

    * Do not use "large number" as a data type for any of your fields *

    Click image for larger version. 

Name:	Query Design.jpg 
Views:	33 
Size:	145.3 KB 
ID:	34571

    Thank you,

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Under OffenseFineAmount, change Group By to Sum. Then add TicketNumberID field and use Count beneath it.

    You might want to change design of TrafficOffenseType to include ID field.
    Last edited by June7; 06-27-2018 at 02:10 PM.
    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.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    LostAndConfused: you seem to be in good hands and I am late to the party (and uninvited I might add) but I can't resist commenting that there are at least 2 things in the requirements that I would probably never do. So this is just for your future consideration.

    1) Each traffic offense type is uniquely identified by its offense description. No way, and ssanfu has "corrected" the flaw in his proposed design, but I don't see a reason provided. A bit simplified, but a descriptive field does not a good relationship make. RAN RED LIGHT is as valid as RAN RED LITE since they are unique, yet they mean the same thing. This situation can arise with simple spelling variances, and when queried, you won't get LITE records when the input for a query is LIGHT.

    2)
    year in service (i.e. 2) Again, no way. This is like having an Age field, which is never static from day to day. Neither is YearsOfService. The correct approach would be a StartDate and an expression in a query that subtracts StartDate from Date (the current date, which is another reserved name). Here is a good source for this info, as I don't see where it was provided.

    Speaking of names, if you adopt a convention (especially for forms, reports, code variables, etc) you will seldom run in to this problem. Of course, name prefixes don't really apply to table fields, but something like camel case will ensure you avoid bad design through use of spaces or reserved names (e.g. StartDate and not Date and not Start Date).
    See http://access.mvps.org/access/general/gen0012.htm and https://access-programmers.co.uk/for...d.php?t=225837

    As for the query, it looks to me as if the problem is the outer join between 2 left tables and an inner join on the right. Change the right side join to show all from the center and those from the right that match, or make both equal. Not sure why you'd want all motorist info plus tickets where there is a match, assuming there's no motorist info in your db if there's no ticket related to them.
    As always, my peers will comment on items 1 and 2 if they don't agree (or anything else, for that matter).

    Last edited by Micron; 06-27-2018 at 03:10 PM. Reason: clarification & added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    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
    Just looking at the thread and saw this

    • Create one query named "Invoices" using "Query Design" that outputs the total (aka SUM function) amount of the traffic tickets due and total number (aka COUNT function) of traffic tickets received by each individual motorist's license number. The outputs should also display the name of the motorists.


    I interpret the last line to be restricted to those motorists who have received tickets. If this is correct, I would use an Inner (not Left) join between Motorists and Tickets. But, I may be misinterpreting the instructions you were given.

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @LostAndConfused: I just tried to make a query to see if I get the same error. I just realized I missed adding the license number to the motorist table. Sorry.

    So I created forms and added data for the officer, offense type, motorist info and added some tickets. I then created the query; I didn't get an error.
    It seems you still have (IMO) design errors relating to the PK fields.



    @micron; I agree with you on the points you raised. Happy to have you jump in any time.


    @orange: I was going to point out that "Name" and "Date" are reserved words, but I thought I had seen that in a previous post. Apparently I am able now to read things that are not there.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help a noob understand IIF
    By rbennion in forum Queries
    Replies: 2
    Last Post: 08-30-2017, 12:46 PM
  2. Trying To Understand Table Relationships
    By UserX in forum Database Design
    Replies: 3
    Last Post: 08-13-2015, 05:14 PM
  3. Trying to Understand Relationships
    By Bkper087 in forum Access
    Replies: 3
    Last Post: 10-10-2014, 12:42 AM
  4. Help me understand normalization and what it looks like
    By njnewuser in forum Database Design
    Replies: 2
    Last Post: 04-01-2014, 12:33 PM
  5. Someone to help me understand a guide
    By carrybag93 in forum Access
    Replies: 4
    Last Post: 06-04-2012, 12:20 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