Results 1 to 5 of 5
  1. #1
    Skywalk669 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    2

    Question Understanding relationships

    I'm fairly new to Access. However, I was able to design a table and several forms, queries, and reports based on that table. Now I want to improve it by breaking the one table up into several tables, as there are now close to 100 fields and will probably be adding more each year. I would prefer to do this without losing the data that is already in there. I just am not getting the relationship thing? Every time that I feel like I think I am about to get it, it doesn't work and I get a headache.



    What type of field should be a primary key in a new table? Can a lookup field be a primary key?

    I work for a non profit youth agency. We have multiple programs and each program takes place in multiple locations. The way that I currently have it set up is to primarily track the youth and not necessarily the other way around. For example each record has all the youth's contact information and then I have separate fields for each program with lookup fields for locations. For example one field is labeled Program A and has a drop down menu of Location 1, Location 2, etc. I think that I need to have a separate table for each program, but do I need one for each location as well?

    I know that this is probably not the ideal way to set it up, but please advise. My goal is to eventually be able to print out reports for each program at each location with a list of each child that attended on any particular day, as well as reports that show every program that an individual child attended within a certain time period.

    Please help.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is a good site to start with: http://www.accessmvp.com/Strive4Peace/

  3. #3
    Skywalk669 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    2
    I'm still very confused. The closest thing that comes close to what I am trying to create is the student database template. However, that template only has the youth being assigned to one classroom. We might have a youth in 6 or 7 different programs. From the website you just listed, I noticed that lookup fields are not the ideal way to go. So for one program that takes place in multiple locations, I have it currently as a lookup field with the field labeled ProgramA and the drop down menu has the locations. Should I break that up into a separate field for each location? And if I do that, would I be able to use the Program A field as the Primary Key for a new table and the foreign key for the initial table?

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is another link. And there is a link at the end of Commandment 2 about look up fields you should read.

    What type of field should be a primary key in a new table?
    I use an autonumber as the primary key for tables. (Lots of opinions on this subject)

    ....... as there are now close to 100 fields and will probably be adding more each year.
    100 fields in one table seems excessive. Are there repeating field names? ie Program_A, Program_B or Jan2010, Feb2010, Mar2010,...
    This is an indication that your structure is not normalized.

    Creating a normalized table structure is a little science and a little art form.

    The first thing you need to do is get the tables structure correct. Maybe you could make a copy of your database with only the tables, delete the data, do a "compile and repair", and post it here so other people can see the field. (I don't have A2K10).

  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
    Further to Steve's comments:

    Getting your tables and relationships set up correctly is key to successful database.

    see post #2 at https://www.accessforums.net/showthr...lp-with-design

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

Similar Threads

  1. trouble understanding expression
    By mejia.j88 in forum Queries
    Replies: 4
    Last Post: 02-01-2012, 03:00 PM
  2. understanding access queries
    By scamper in forum Queries
    Replies: 2
    Last Post: 01-03-2012, 09:20 AM
  3. Understanding afterupdate
    By MAM8433 in forum Access
    Replies: 8
    Last Post: 05-23-2011, 02:05 PM
  4. Not understanding Running Sum Query
    By dynamictiger in forum Queries
    Replies: 4
    Last Post: 08-30-2010, 11:50 AM
  5. Understanding SQL Querys
    By jacobbiljo in forum Access
    Replies: 8
    Last Post: 11-17-2009, 05:17 PM

Tags for this Thread

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