Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    3

    Unanswered: Dates in an event database

    Hello

    I have created an Access 2007 database to record University American Fair details, name, address, date etc.

    Many of the Fairs are on twice a year so how do I add the new date for a fair without overwriting the previous fair date and adding the same info again, name, address, etc

    Thanks

    Ross

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You need 2 tables: one for the Events Data: ID, details, name, address, etc. and a second for the Events Dates: ID (From the first table), Start date, End date. You can then create a query establishing a One (Events data) to Many (Dates) relationship between both tables. The ID in the first table is the Primary key while it's a Foreing key in the second. e.g.
    Code:
    SELECT Tbl_Events_Data.*, Tbl_Events_Dates.*
    FROM Tbl_Events_Data INNER JOIN Tbl_Events_Dates ON 
         Tbl_Events_Data.ID = Tbl_Events_Dates.ID;
    Have a nice day!

  3. #3
    Join Date
    May 2011
    Posts
    3
    Hi Sinndho

    Thanks for your response.

    What is it you mean by ID form the first table "second for the Events Dates: ID ([B]From the first table)"

    If I am in the the relationship function and have the Event details on the left and the Event dates table on the right, if I drag the ID on the Events details to the ID on the Dates table I dont get a one to many. Should I be dragging ID to start date, I was getting a one to many this way but I am confused how this records in the table afterwards

    Ross

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    From the moment you want to create a relationship between two tables you need at least one column in both tables with the same value. That's the role of the ID columns. In the first table (One side of the relationship) it's the Identity column for the table (Primary key), while in the second table (Many side of the relationship) each row pertaining to one row of the first table has a copy of the Identity value.

    Example:
    Code:
    Table1                       Table2
    ==================+=========================
    ID   Event Name        ID     Start Date   End Date
    ------------------+--------------------------------
     1   Event One          1      01/1/2011   01/14/2011
     2   Second Event       2      02/19/2011  03/4/2011
                            1      01/4/2011   01/18/2011
    In table2 the first and third rows pertain to the first row of Table1 while the second row pertains to the second row of Table1.
    Have a nice day!

Posting Permissions

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