Help me

[firewall_ie]

Can some have a look at the main form,

What I am trying to do is when in the main form I click on attendance record And i want the record Id to have the same number as the clientID It is giving it a blank number I have the input the number my self can someone please assist me.

 

[Mile-O]

Always a good thing to do before posting a database is to either mention what version it is or convert it to Access '97 to increase the scope of help you can receive.

I myself use Access '97 and downloaded your attachment but can't open it due to it being a later version.

In 2000 or 2002, whichever you are using, there is an option in the menu to Convert to Access '97.

 

[firewall_ie]

verion 97

Here you go mile

 

[Mile-O]

Before we even begin worrying about your initial problem there is a lot more to be done.

Your tables are not normalised. Rather than have a table with client details, doctor details, AND counsellor details you should break these down into three separate tables as the doctor and counsellor are not dependant upon the client.

The Attendance table shouldn't have a repeating group (i.e fields for Monday through Friday)

You probably want a table for clinics, too.

Don't have a field called name as it is a reserved word (being a property of many objects in Access)

Instead, you should break this down into two fields: ClientForename & ClientSurname.

There are other tables and fields in the database of whose purpose I am unsure of.

The appointments table with it's holiday, sick leave, etc should be removed and given a table of its own where these are listed in rows rather than columns and given their own unique ID.

 

[firewall_ie]

Yes I have tables for all the information that relates to the clients. All I really want to do is sort out the attendance record on the main form so when i click on it the clientID for the client it will pass through to the record Id example aaron is clientID 1 and all his attendance records sould be saved as recordID 1.

I hope i have not lost you

 

[Mile-O]

You haven't lost me; I'm just saying your database is going to need redesigned before you are going to get any real, and unproblematic, functionality out of it.

You have every table built around the ClientID which is WRONG.

From what I can see you are going to need these tables:

tblClient - holds all the clients' personal information

tblDoctor - holds the doctors' contact/personal information

tblCounsellor - holds the Counsellors' contact/personal information

tblClinic - holds the clinic information

tblAttendance - keep track of attendance

tblSupervisors - a list of supervisors and their information

tblReasons - a list of reasons for non-attendance


There are probably more tables that can be made but, because I don't know what some of these fields are for, I can't detail for you.

The ClientID should not be the primary key in each of these tables as you have them all as autonumbers so tying them together is virtually impossible.

The ClientID should be seen as a foreign key.

Ask yourself questions.

Can one client have more than one doctor?
Can one client have more than one counsellor?

 

[firewall_ie]

Any chance that you can do a sample

 

[Mile-O]

I can't do too much as I'm busy just now but here's a quick thing thrown together - it doesn't contain every field you listed and isn't completely related but should give you an idea.

 

[Autoeng]

SuperMile to the rescue!

 

[Mile-O]

SuperMile to the rescue!

Autoeng, it is bad manners to "hi-jack" another's thread. If you wish to praise me then please start a new thread for it.

 

[Autoeng]

lol