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.