Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004

    Unanswered: Help needed with table relationships in Access

    Hi, I have not designed a DB in Access for around 5 years now, and I just need a little reminder help with what I think will be a 3 table database.

    I want to record visitor numbers to 5 libraries in one town. I want to be able to count how many visits each person makes to a library, and how many visits each library gets.

    I think I need 3 tables:
    userage etc etc

    libraryaddress etc


    So one user can visit many libraries. One library can have many visits.
    Have I got it right? I have attached a screenshot of my table relastionships in Access.

    Attached Thumbnails Attached Thumbnails relationships.jpg  
    Last edited by moss2076; 03-23-10 at 05:10.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    can a user visit the same library more than once in any one day?
    presumably a user cannot visit the same library more than once in any time slice..

    what you have in tblvisits is an intersection table (where an entity represents the intersection of two other tables)

    often intersection tables are used where two things overlap eg when placing and order you will have what is in effect an intersection table storing the orderno and the product number + any other associated elements which are relevant to that intersection .. for an order that would be say quantity ordered, agreed price any discounts and so on). usually the priamry key of an intersection table are the foreign keys that comprise the primary key other tables for our order thats usually the order no + product no

    if it was me then I'd model the priamry key of the sub table to be the library + user + dateofvisit

    bear in mind that Access/JET stores date time values so you don't need visit_date and satrt tiem and end time. you need two elements, which coudl be

    date/time of arrival and date/time of departure
    date/time of arrival and duration of visit

    userage ignores one of the fundamentals of db design as the age is only relevant for that day or fro the remainder of that (birth)year. what you shoudl be recording is DateOfBirth and calculate the age from that.
    address arguably should be in a separate table as a person may have more than one address, although it may be that your business rules allow a single address. if that is so then you need to qualify that when you document/comment your project. a library by definition may have only one address (at least I hope there isn't a library out there with two different addresses.

    Im not to certain whether username is a user name such as "moss2076" or a persons name such as "Mr Stirling Moss", if the latter then it would be better to store all elements as separate columns
    qualifications (optional but some people insist that their qualifications are registered)

    and optionally known as.. some people may prefer to be called, say Billy Bragg, rather than William Bragg.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2004
    Hello, thankyou for taking the time to reply, I appreciate it.

    In answer to a few of your questions, Yes a person can visit a library as many times as they want in a day, and no, a user cannot visit the same library more than once in any time slice.

    The age, and address of a user isnt too important right now, the main thing is capturing their name.

    Could you possibly show me how you would construct your tables with keys and their relationships?

Posting Permissions

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