Help!! Very Easy Database Design Question!!

[IDOL]

I am currently disigning a database.

I have the following tables:

tblManager:-

ManagerID
CompanyID
First Name
Surname
Email

tblCompany:-

CompanyID
Company
Region
Address_1
Address_2
Address_3
Address_4
Postal Code
Tel No.
Fax No.
Email
ManagerID
Extensions
Ad Tel No_1
Ad Tel No_2
Ad Tel No_3
Ad Tel No_4
Web
Speed Dial

tblEmployees:-

EmployeesID
CompanyID
ManagerID
Employee_1
Employee_2
Employee_3
Employee_4
Employee_5
Employee_6

I'm not sure exactly what route I go about sorting out a query. The relationships are obvious (I think).

Basically, I just want to be-able to create a database where it allows me to enter the relevent information regarding the company, management employees etc..

Preferably via using a form.

Can anyone give me any tips. I would be very greatful!!

 

[Jack Cowley]

This is how I would do it based on your information...

 

[IDOL]

That’s absolutely brilliant!!

Cheers mate, well appreciated.

 

[neileg]

Pleased it worked for you.

I can't help noticing you have too many relationships. For example, you have the manager ID in your company table. Why do you also need the company ID in your manager table? Similarly in your employee table you don't need both manager and company.

What does this mean?
Employee_1
Employee_2
Employee_3
Employee_4
Employee_5
Employee_6
Are you going to have a daisy chain of employees in your records, or are you just showing that you will have multiple records in this table?

I think you could merge the managers and employees table. After all, managers are employees, too. Just have an extra field that indicates that this employee is a manager.

Just trying to help!

 

[Jack Cowley]

I am glad that it worked for you. You should also consider Neil's suggestion about putting the manager into the Employee table as well....

Jack

 

[IDOL]

Pleased it worked for you.

I can't help noticing you have too many relationships. For example, you have the manager ID in your company table. Why do you also need the company ID in your manager table? Similarly in your employee table you don't need both manager and company.

What does this mean?
Employee_1
Employee_2
Employee_3
Employee_4
Employee_5
Employee_6
Are you going to have a daisy chain of employees in your records, or are you just showing that you will have multiple records in this table?

I think you could merge the managers and employees table. After all, managers are employees, too. Just have an extra field that indicates that this employee is a manager.

Just trying to help!

I see what you mean.

I just didn't want to confuse things.

Having a separate table for employees would clearly distinguish between the Management.

How were you thinking exactly?

 

[Jack Cowley]

Add a Yes/No field to the Employee table and call it Manager. Check the field if the employee is a manager. You can filter the records based on that field to show managers or employees.

hth,
Jack

 

[IDOL]

Add a Yes/No field to the Employee table and call it Manager. Check the field if the employee is a manager. You can filter the records based on that field to show managers or employees.

hth,
Jack

hhmmmm... I'll have a go. Not done much of Access since college :S

Cheers for the advice.

 

[Jack Cowley]

It will work...Good luck.

Jack

 

[IDOL]

If I wanted to run a search for a particular branch (bring up all the information - including.. Manager.. Employee etc) how would I go about it?

As the information will bein different tables.


The code:----------------------------------------------------------

Private Sub cmdSearch_Click()

If IsNull(Me.txtFilter) Then
MsgBox "You have not entered any filter criteria.", vbExclamation, "Filter Example"
Exit Sub
End If
With lstResults
.RowSource = "SELECT * FROM frmCompany WHERE [Surname] Like '" & _
IIf(chkExactMatch = True, Me.txtFilter & "';", "*" & Me.txtFilter & "*';")
.Requery
End With

lblResults.Caption = "Filter Results: " & IIf(lstResults.ListCount - 1 = -1, 0, lstResults.ListCount - 1)


End Sub

------------------------------------------------------------

Will bring up results everything to do with the branch.. but not the employees at that branch.

What do i add to the code for it to bring up everything to do with that branch including Manager etc???

Help?

 

[mission2java_78]

Hi,

DOnt use dynamic code to generate a query...you're doing way too much coding to get some expected output. Use queries and base your queries on solid criteria. All your forms should be based on some type of queries with strict SQL stmnts so that performance isnt an issue. Remember access is slow returning recordsets to the client and processing. If this was SQL you'd want to use stored procedures with views...but since this is access just create a query and base your reports / forms on this query.

Jon

 

[Jack Cowley]

If you want to search for branch (company?) you can use the form I gave you and create a combo box to find the branch you want. To do this create a combo box using the Wizard and on the first screen of the Wizard select the 3rd item, "Find a record..." Finish the Wizard. Now selecting a branch from the combo box will show you all the information including the manager and employees.

If this is not what you are after then let us know...

Jack