Hi Sharon,
Sorry for the delay in responding - I've been a bit busy.
Your table structure looks fine and gives plenty of flexibility in how you
link your forms and reports.
Your next task is to really understand the concept of linking your data.
There is no need to use the NI number as your primary reference.
Think of your delegates table as the main data source in your database, with
all other tables linked to it.
Insert an autonumber field into your Delegates table and name it 'ID'
Inset a number field in all other tables and name them 'Main ID'
Now create a form with your delegates table as it's record source and insert
a 'tab control' onto it.
Modify the tab control so you have a separate page for your delegates info
and for each of your sub-tables.
The controls (text boxes etc) for your delegates can be inserted on the
'Delegates' tab.
Now create forms for all other tables.
Insert each of these forms onto the relevant tabs on your delegates form as
sub-forms.
As you insert each sub-form, you will be prompted to link 'Child and Master'
fields in the associated tables.
It'll generally give you a default link of 'ID to ID'. In all cases, choose
'Define My Own' and link the 'ID' field in the master table with the 'Main
ID' field in the child table.
Now you've got these links in place, every time you create a record in a sub-
form, the 'ID' field value of the Delegates table will automatically be
copied to the 'Main ID' field of your sub-tables.
Your reports can be constructed in a similar manner, with main reports with
record source of the delegates table and sub-reports with record source of
the relevant sub-table or query.
If you have a situation where your delegates take more than one course at a
particular college, then achivements could be recorded in a sub-sub table and
entered through a sub-sub form, inserted into the college sub-form which is
inserted in the main delegates form. (Reports, sub Reports and Sub-Sub
Reports are also feasible)
Whith this set up properly, when you select a particular delegate on the main
form or report, only data relating to the delegate will be displayed in the
sub-form or report.
Oh! and always remember a few golden rules:
Never enter data directly into a table. (Access should never be treated as a
spreadsheet)
Never print a table or form - always create a report for your final out-put.
Never manually input 'link' data - always find a way to do it automatically.
Always name all your database objects as something descriptive to their
function.
(e.g. call your delegates table 'tbl delegates' and your delegates form 'frm
delegates' whereas your employer objects could be called 'stbl employer' &
'sfrm employer') - Makes life so much easier when linking complex structures
and when you start using VBA coding.
I hope this is helpful.
I think this site is called 'Access Monster' because newcommers to access
rarely understand what a huge, complex beast it can be.
The help files will give you good leads on Tab Controls and Sub Forms &
Reports.
Regards,
Andy
Ok here goes at the moment we are putting far to much data onto excel about
young skillseeders and the courses they do along with their employer. so
they sent me on an introduction to Access course so that i could build up a
database to hold all of the information the tables i have set up are as
follows:
Delegate details (showing where they live and contact details,
delegate requirements to see if they need accommodation and what type =
start and end dates for courses.
Lodging Information for if they need accommodation detailing their contact
info
College details showing contact details
Company details showing contact info
Courses stating what colleges offer what
course level options to show what levels each delegate has achieved and
Course details showing costs course names start and end detas etc
Now i am under the impression that i have to have the national insurance
number in each of the tables (my unique identifier for each delegate) so that
i can print of reports showing how much each delegate cost, which areas they
are from, what courses they have taken and where they went to work etc etc
which is a bind because i have to enter the NI number an awful lot of times.
am i correct in thinking this because i cant think of a way of linking all of
the information together
thank you for your help its much appreciated
Sharon
[quoted text clipped - 17 lines]
