need help in forms

[ontherun]

hi,
i am new to access and have little knowledge in database. i have been given a task to create a database involving client (a company request for a job) and job .

Primary key for Client is ABN and for Job, its Job number. My question is, the Job table consists of more than 70 fields and i want to split the table with one-to-one relationship. i dunno how to do this. when i split the table into 30 fields each with Job number in every table as the foreign key, it ends up with one-to-many relationship.

Also, in the Client form, i need to place a button called "Add more address" where i can add more than one address information for the same client. when i click this button, only the address fields like Street, post code, country must be listed in the same page of the form. i dunno how to do this.

i would appreciate your help.

[brynne]

Hi

It doesn't really matter how many fields your Job table has, as long as all of the information contained in them relates directly to the Job. Likewise for client. However, it seems to me that you might need an intermediate table linking Client to Job, as (one would hope) that you will have many clients and many jobs. The intermediate table is the way one creates the necessary many to many relationship.

To give a simplified example, my recruitment database has many tables, but the three most essential are Applicant, Vacancy and JobApplication. In the applicant table I record only those details that are unique to the applicant. In the vacancy table, I record only those details that relate directly to the post being advertised, e.g. post title, recruiting manager, salary scale, closing/interview date etc. The JobApplication table brings the two together, eliminating the need to re-enter personal details for those applicants who apply for more than one job or record interview dates separately for each applicant. But in that table I am able to record whether or not an applicant has been shortlisted and enter an interview time.

When designing your tables, think about data redundancy, i.e. data that you would have to keep entering for multiple records. I suggest you check out the advice given on this site around normalisation.

B

[nico5038]

I guess that the present table holds both Client and Job information.
You'll need to normalize this, as described above, into two tables:
tblClient with the client data and tblJob with the job data and the Client ID.
Finally as you need multiple addresses a tblAddress will be needed with as the unique key ClientID and AddressID.

To create the Client table you can use a GroupBy query like:

select client, address, street, town from tblOld Group By client, address, street, town

When you have this query in the graphical query editor, change the type into a maketable query (use the Query menu) and give the new table the name tblClient.

Next create a SELECT query for the Job data and change that also in a maketable query.

Finally create also with a groupby query from the tblClient the needed tblAddress.

Getting the idea ?

Nic;o)

[MMcCarthy]

It may help to check out this tutorial as well.

Normalisation and Table structures

Mary

[ontherun]

thanks for your reply. i really appreciate everyone's assitance.
I've read about normalisation posted by Admin and workout the tables which i have created.
if possible can anyone of your give me your email id?
i just need some assistance in seperating the tables because it is really confusing for me to split the tables that has massive data altogether. please suggest me after seeing my doc file. Will attach the doc to your email id...

cheers,
Kathy
[email removed]

[MMcCarthy]

thanks for your reply. i really appreciate everyone's assitance.
I've read about normalisation posted by Admin and workout the tables which i have created.
if possible can anyone of your give me your email id?
i just need some assistance in seperating the tables because it is really confusing for me to split the tables that has massive data altogether. please suggest me after seeing my doc file. Will attach the doc to your email id...

cheers,
Kathy
[email removed]

Sorry Kathy

I had to remove the email address as it's against site rules. You can upload the doc file on here if you zip it first. Just post a replay and then edit it within the 5 mins window and you will see the option to add an attachment.

Mary

[ontherun]

thanks mccarthy... here i have attached the doc file and please assist me in defining the tables

[ontherun]

i tried 2 compress the doc file but its in rar format which is not supported here... need help

[MMcCarthy]

i tried 2 compress the doc file but its in rar format which is not supported here... need help

Try attaching the .doc file without zipping it.

If that doesn't work try saving it as a text file and attaching that.

[ontherun]

Please hav a look

[ontherun]

i hav attached the txt file

[MMcCarthy]

i hav attached the txt file

Have a look at the attached outline of tables. It's not complete but should be enough to get your started.

Mary