How to create

[Kutty]

I have lot of idea how to create database.
I need to create an address database departmentwise. Also I want
to acceess Department by toggle button or Combo Box. I dont know
How to do this way with below mentioned fields.
Fields are : Serial#, Name, Designation, Extension, Mobile#, Email.
But to access the datas departmentwise what to do? Shall I create
a table such as :
Table1 Serial#, Department
Table2 Serial#, Name, Designation, Extension, Mobile#, Email
Please give me better suggestion on this.

 

[Steve Schapel]

Kutty,

A few points:

1. It is a good idea to have a Departments table, which can be used as
a lookup for data entry etc. However, there is no need for a Serial#
field here, as each entry of the name of the department will be unique
anyway.

2. It is not a good idea to use a # as part of the name of a field.

3. 'Name' is a Reserved Word (i.e. has a special meaning) in Access,
and as such should not be used as the name of a field or control.

4. You will need a Department field in the second table, as the way of
indicating which department each person belongs to. Are they Employees?
On the Employee form (based on the second table), you will be able to
represent the Department field by a Combobox, which has the Departments
table (Table1) as its Row Source.

So, I would modify the table design like this...

Table: Departments
Department

Table: Employees
SerialNo
EmployeeName
Department
Designation
Extension
Mobile
Email

 

[Kutty]

Steve,
Shall I create just one field in Departments Table?
Is it better creating combo box for Department?
No any other better facility instead of combo box?
How to do that?
I think no need of numbering in Departments table and
Employees Table to connect. Department field is enough
in both table. Am I right?
---------------------------------------------------------

 

[Kutty]

Steve,
Shall I key primary key for Departments Table?
---------------------------------------------------------------

 

[Steve Schapel]

Kutty,

Yes. As far as I can see, there is no need to have any other field in
the Departments table aside from the Department field itself. The only
time I would consider this is if I wanted to be able to list the
departments in a specific order, other than alphabetical. And yes, I
would normally use a combobox on the form for the entry of the
Department for the Employee... there are a number of advantages to this,
but I suppose the main advantage is that you can't make a spelling
error, or enter a department that doesn't exist in the list.

 

[Steve Schapel]

Kutty,

Yes, you can make the Department field the Primary Key of the
Departments table.

 

[Kutty]

I connected in query with Department field in Departments Table and Employees
table Department field. Data type in both field of department field is Text.
But once I check in table if there is connection I found not connected.
How can I connect both table ?
I need Serial# field as Autonumber. I did so.
-------------------------------------------

 

[Steve Schapel]

Kutty,

I don't understand. You mean Serial# field in Departments table? No,
you don't need this. I am not sure what you mean about "not connected".
I can't see what is the problem.

 

[Kutty]

Steve,
I meant by "not connected" is : I went to queries design. I dragged
Department field from Departments Table to Department field of Employees
Table. This is what I meant by connection. Once we connect this way and
check in table we can see plus sign in each record. I cannot see this plus
sign. That is why I thought not connected one field to another field. Did you
understand now?

 

[Steve Schapel]

Kutty,

Thanks for the further explanation.

You will see a 'plus' sign in the table datasheet if:
- the relationship between the tables is defined under
Tools|Relationships menu from the main database window, and
- the table's 'Subdatasheet Name' property is set to an appropriate
setting.

Creating a join between the tables in a query does not have the same effect.

In the case of your example, where the Departments field only has one
field for Department, and this is matched to the data in the Department
field in the Employees table, the Departments table is very handy for
the purpose of a combobox list for the data entry of the Department for
the Employees. But there will probably never be any reason to make a
query that involves both tables, as this will not achieve any purpose.

Hope that helps to clarify the situation.

 

[Kutty]

Steve,
Now I understand the matter. Thanks a lot lot.
-------------------------------------------------------------------

 

[Kutty]

Steve,
Now I can see a plus sign in table data sheet after I have done as you told.
You mentioned "Creating a join between the tables in a query does not have
the same effect". I have misconcept about joining between the tables in
query. What is the advantage of joining tables in query? What is the
difference of joining from Tools|Relationships menu from the main database
window and Creating a join between the tables in query ? I understand from
reply from you that I can create
a combo box in Employees table. Please tell me what is your openion about
creating tab control too ? Please reply to me.
------------------------------------------------------------------------------------------

 

[Kutty]

Steve,
I am going to develop a report also for this contact database. Once my boss
ask me to give him single or multiple person's name and contact number I have
to give him the report. Depends on my boss's requirement, can I put specific
employees contact list with a one click or two click? I hope you got what I
mean
-------------------------------------------------------------------------------------------

 

[Kutty]

For report : I have got an idea. Let me put an extra colum in employee table.
For example, If I need to print 3 persons contact number I have to click in
3 new colums to print 3 records. Can I do this way? How to do? Waiting for
your reply
--------------------------------------------------------------------------------------------

 

[Steve Schapel]

Kutty,

I did not suggest to create a combobox in the Employees table. In
general, tables should not be used for data entry/manipulation, and
normally should not be seen by the user. Their purpose is data storage,
so this kind of data entry functionality is not necessary. I was
referring to using a combobox on a form.

Relationships as defined in the Relationships window, and joins in
queries, are for two different purposes. You define Relationships in
order to enforce Referential Integrity. In the example we have been
discussing, this would mean that the database will not allow you to
assign a Department to an Employee if that Department does not exist in
the Departments table. On the other hand, in a query based on more than
one table, the purpose of the Join is to cause the query to return
records where the data in the joined fields matches. Of course, it
often happens that the Join in a query is the same as the Relationship
betwen the tables. But it is not uncommon for queries to involve joins
between tables that are not involved in defined Relationships.

The main reason for using a Tab Control on a form is to make the layout
of the data more user-friendly, or because there is too much information
required on the form to be comfortably accommodated on one screen.

 

[Steve Schapel]

Kutty,

Yes, this will work, and this type of approach is often followed. You
can make it a Yes/No data type field, and have it represented on your
form by a checkbox, and then you can just select the employees that you
want included in the report.

 

[Kutty]

Steve,
Actually I have to tell you tha I have to put an extra control in employee
form, not table. Sorry for the mistake. My idea is how many records I have
to print that much records I have to select by clicking. After click that
much records have to print. Can I do this? Please tell me how to do this
---------------------------------------------------------------------------------------

 

[Steve Schapel]

Kutty,

Just tick the checkbox on the form for each employee you want included
in the report. Then, in the query that the report is based on, you
would put a Criteria of -1 in this Yes/No field, so only those Employees
whose checkbox is ticked will be shown on the report.

 

[Kutty]

Steve,
Let me know how to arrange combo box on the Employee Form.
In Department I have Sales, Contract, Marketing and so on.
I have inserted combo box of Department field. But when I
click Combo Box I can get multiple Sales Department, multiple
Marketing I get. That should be unique. When I check in Table
the Departments are there with plus sign. How can I get departments
as unique fields in my Combo Box? In the same time I find the Department
field in form without Combo Box unique. Please comment. If this
is not clear for you please inform me. I will explain again.
---------------------------------------------------------------

 

[Kutty]

Steve,
Thanks for explaining how to print report as follows. But I don't know how
to do that. Could you please inform me in details? Would appreciate your
great favor.
---------------------------------------------------------------------------------------------