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.
11 1504
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 3,080
Recognized Expert Specialist
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)
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 14,534
Recognized Expert Moderator MVP
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
thanks mccarthy... here i have attached the doc file and please assist me in defining the tables
i tried 2 compress the doc file but its in rar format which is not supported here... need help
MMcCarthy 14,534
Recognized Expert Moderator MVP
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.
i hav attached the txt file
MMcCarthy 14,534
Recognized Expert Moderator MVP
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: James Fortune |
last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't
understand why people who know how to write code to completely replace
a front end do not write something that will automate...
|
by: Susan Bricker |
last post by:
Greetings. Before I begin, I have been stuck on this problem for about a
5 days, now. I have tried and just seem to be not getting anywhere. I
know that the explanation is lengthy, but I am a...
|
by: MFC |
last post by:
Ok, after three C# books, (C# How to Program, Programming in the Key
of C#, and C# Weekend Crash Course) and three weeks, I believe I have
tried everything to make a certain form function...
|
by: pv |
last post by:
Hi everyone,
I need help with following scenario, please:
Users are accessing same web server from intranet (users previously
authenticated in Active Dir) and from extranet (common public...
|
by: chris |
last post by:
Hallo,
I am in need of a replacement for the Microsoft Visual Studio .NET.
The reason is quiet simple. I develop forms which are used on
different
microsoft windows platform, and one...
|
by: Noremac |
last post by:
My google skills must be dwindling. I am trying to determine how in ASP.NET
2.0 I can get the ReturnUrl querystring variable in Forms Authentication to
contain the absolute url.
Just like others...
|
by: Terry Olsen |
last post by:
I'm trying to add a domain user to a local group using the code below:
Dim LCL As New DirectoryEntry("WinNT://" + Environment.MachineName +
",computer")
Dim DOM As New...
|
by: Peted |
last post by:
Im using c# 2005 express edition
Ive pretty much finished an winforms application and i need to
significantly improve the visual appeal of the interface.
Im totaly stuck on this and cant seem...
|
by: active |
last post by:
Below is a small but complete program that appears to show you
can't retrive a Palette from the clipboard.
This is true whether the palette is placed on the clipboard by Photoshop or
Photoshop...
|
by: Thelma Roslyn Lubkin |
last post by:
I am still having trouble trying to use a popup form to allow user to
set filters for the main form.
The main form is based on a single table.
The popup contains 5 listboxes, so the user can...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |