Relationship and namings

[chewy]

Attached is a copy of all my tables and queries in my DB. How would I relate these better or does the naming cause any problems in relating fields together? Any suggestions or anything I am missing?

Thanks

 

[Mile-O]

Firstly, rather than having your primary key in each table called ID, give it a name that reflects the table: DepartmentID, EmployeeID, etc.

Secondly, the department field in your tblEmployee could be a number field and linked to tblDepartment's DepartmentID making a one-to-many relationship as one department can have many employees.

Again, the tblVacation can have a VacationID rather than ID and this can have the number field EmployeeID linked this time to the primary key in tblEmployee as one employee can have many vacations.

 

[chewy]

Are there conventions of naming fields in table other that no spaces? Like should the fields other than primary key have ID after it. ie. VacationID?

 

[Mile-O]

I'll make a quick model of what you've given.

Other things to think about - maybe just niggles on my part but...as one table holds department details, don't call it tblDepartment, call it tblDepartments - always use plurals. tblEmployees, tblVacations, etc.

What's the TotalDays field in tblVacation? Is that calculated or calculable in any way?

 

[chewy]

TotalDays is how many days off the user enters. If they take off more than one day at a time. You could enter 4 or 5 days or whatever.

 

[Mile-O]

This is what I would do with your structure - look through it and play spot the difference.

 

[chewy]

I changed those things now the hard part is to update all subsequent changes in the forms. Is there an easy way to do this?

 

[Mile-O]

To be honest, I don't know.

 

[chewy]

Now that I changed the EmployeeID on the Vacation table. I am having troubleconnecting the query to the form. Is there anything special I have to look for if using this method?

 

[mission2java_78]

Huh...
you changed all the names and you expect it to work?
Im lost...in your query change the fields selected to your new fields.

Jon

 

[mission2java_78]

You could loop through every control on each form...
and change the control source to the new name.

Something to this effect

For each f in Forms
For each ctl in Forms.Controls
'check the ctl
'change it
next ctl 'grab next ctl
next f 'grab next form

Jon

 

[chewy]

since I am using the first and last name seperatly How is this changed
SELECT [tblEmployees].[EmployeeID], [EmployeeForename]+" "+[EmployeeSurname] AS [Employee] FROM [tblEmployees];

It is giving me tblEmployee.Employee no found. How do I use the concatanated full name in the form and in the query?

 

[mission2java_78]

Why are you adding strings up?

Use & no +

Jon

 

[Only1Abhi]

To Chewy, Dear Friend

I like to Chew It Chew It,
I like to Chew It Chew It,

I like to Chew It Chew It,
I like to Chew It Chew It,

Sorry Just felt like saying that!
Hope you don't mind!

 

[Mile-O]

Why are you adding strings up?

Use & no +

Jon

I split the forename and surname in the database example I posted on this thread and used [Forename] + " " + [Surname] as it is the same as & in concatenating strings - as long as he isn't trying to multiply or diveide strings there's no problems with using + to concatenate strings.