Results 1 to 6 of 6
  1. #1
    mbjazz is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    1

    Using lookup data for table 1 mutible times in table 2

    Hi everybody.


    I'm an access beginner trying to make a simple data base to help me plan and produce concerts.

    A short description of the problem in my project is as follows.

    I have table employe_data containing info of employes data: name, mobile, email.

    Table concert_data contains details of the concerts i'm planning: eg, artist, date, time etc.

    In table concertd_data i would like to do multible lookups in different fields to table employe_data to assign employes a various task in the concert:

    concert_artist
    concert_date
    concert_time
    booking_responsible look up into table a getting name, mobile, mail off person a
    production_responsible look up into table a getting name, mobile, mail off person b

    This works great for 1 person only with a many to one relationship from table concerts (many) to table employe (on), but person b's data never arrives.

    So in short my problem is that I want many fields to look up data in the same table and return info for the various look ups.

    this might be a beginner problem, but hey... thats what i am ;-)

    Any ideas of a proper design?

    greetings

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    Since you have many tasks related to a concert, you have a one-to-many relationship which requires a separate but related task table to hold the assignments.

    employe_data
    -pkEmployeeID primary key, autonumber
    -empname (the word name is a reserved word in Access, so you should use a different field name)
    -mobile
    -email

    concert_data
    -pkConcertDataID primary key, autonumber
    -concert_artist
    -concert_datetime

    By the way can an artist have many concert dates?

    concert_dataAssignments
    -pkCDAssignID primary key, autonumber
    -fkConcertDataID foreign key to concert_data table; all foreign key fields must be a long integer number datatype field to match that of the related autonumber field
    -fkEmpID foreign key to employe_data table
    -fkAssignmentID foreign key to tblAssignments

    tblAssignments (a table that just holds a list of all possible assignments)
    -pkAssignmentID primary key, autonumber
    -txtAssignment

    The tables will only store the foreign key data, if you want to see additional details you would use a query that brings the related tables together and you would select the fields you would want to see.

  3. #3
    allanjgray is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    2

    Similar problem

    I have a similar problem, and have set up your database structure, and it all seems to work good.

    Is it possible to make records for the default tasks automatically when creating a new item(project in my case), so that when you make a new project you create one record for each possible task with an unassigned field. So that when you go to a form to edit the project you have a subform with the 3 default tasks ready to be just edited, rather than having to create records for each task.

    I hope that makes sense, I am probably missing the point entirely.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Yes, what you describe is possible. You will have to define those tasks as default tasks in some way. Assuming that you have a table that has all possible tasks, you can add a field such as a yes/no field and if checked, it would be a default task. It will take some Visual Basic for Application (VBA) code to accomplish what you want. I've attached an example database.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just thought I'd mention that there is a free data model here that might be useful -- at least some ideas. may be overkill, but some features may be worth noting.

    http://www.databaseanswers.org/data_...gigs/index.htm

  6. #6
    allanjgray is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    2
    Thanks very much for the replies, I will look into it today.

    Allan

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

Similar Threads

  1. Replies: 11
    Last Post: 07-24-2012, 07:50 PM
  2. One lookup table many relationships?
    By sparlaman in forum Database Design
    Replies: 4
    Last Post: 03-29-2011, 10:28 AM
  3. Confused about lookup in table v form
    By jray7000 in forum Access
    Replies: 3
    Last Post: 08-23-2010, 03:36 PM
  4. Replies: 1
    Last Post: 05-25-2010, 02:58 PM
  5. Replies: 0
    Last Post: 08-01-2009, 12:43 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