forms and tables

[Robert Painter]

Good morning,
Not sure why but I am struggling with forms using 3 tables.
I have a form with all details of employees(frmEmployees) and am now adding
email address and CV (resume) to this form. In order to keep normalisation
i created 2 new tables: tblEmail and tblCV and added relationships
EmployeeID - EmployeeIDFK.
When I tried to create a form using:

SELECT tblEmployees.CustomisedID, tblEmployees.Title,
tblEmployees.FirstName, tblEmployees.EmployeeName, tblEmployees.Address,
tblEmployees.City, tblEmployees.PostalCode, tblEmployees.HomePhone,
tblEmployees.MobilePhone, tblEmployees.DrivingLicenceExpires,
tblEmployees.EmploymentStatus, tblEmployees.Notes, tblEmployees.Driver,
tblEmployees.[Full/Part Time], tblEmployees.[Temp/Permanent work],
tblEmployees.Salary, tblEmployees.[Date of Registration],
[tblEmailAddress/CV].[E-mail Address], [tblEmailAddress/CV].CVfilepath,
tblEmployees.EmployeeID, tblCVfilePath.CVfilepath FROM tblCVfilePath,
tblEmployees INNER JOIN [tblEmailAddress/CV] ON tblEmployees.EmployeeID =
[tblEmailAddress/CV].EmployeeIDFK;

I can see all in design and datasheet view but in form view the form is
blank.

Whilst I have sat and tried to solve this problem I have found all ok if I
have tblEmployees and TblEmail but when adding tblCV then the form blanks.

I thought tblCV may have been at fault because it has CVID - Autonumber;
CVfilepath - hyperlink; EmployeeIDFK so added cvfiledpath to tblEmail
thinking the hyperlink had something to do with it but that worked fine if
tblcv was removed from sql statement.

I have just deleted tblCV then re-created it and added successfully to
tblEmployees then added relationships and all still seems ok.

Is anyone able to tell me why ??

I hate it when these things happen and i cannot sort it out.

Robert

 

[John W. Vinson]

Good morning,
Not sure why but I am struggling with forms using 3 tables.
I have a form with all details of employees(frmEmployees) and am now adding
email address and CV (resume) to this form. In order to keep normalisation
i created 2 new tables: tblEmail and tblCV and added relationships
EmployeeID - EmployeeIDFK.
When I tried to create a form using:

SELECT tblEmployees.CustomisedID, tblEmployees.Title,
tblEmployees.FirstName, tblEmployees.EmployeeName, tblEmployees.Address,
tblEmployees.City, tblEmployees.PostalCode, tblEmployees.HomePhone,
tblEmployees.MobilePhone, tblEmployees.DrivingLicenceExpires,
tblEmployees.EmploymentStatus, tblEmployees.Notes, tblEmployees.Driver,
tblEmployees.[Full/Part Time], tblEmployees.[Temp/Permanent work],
tblEmployees.Salary, tblEmployees.[Date of Registration],
[tblEmailAddress/CV].[E-mail Address], [tblEmailAddress/CV].CVfilepath,
tblEmployees.EmployeeID, tblCVfilePath.CVfilepath FROM tblCVfilePath,
tblEmployees INNER JOIN [tblEmailAddress/CV] ON tblEmployees.EmployeeID =
[tblEmailAddress/CV].EmployeeIDFK;

I can see all in design and datasheet view but in form view the form is
blank.

Whilst I have sat and tried to solve this problem I have found all ok if I
have tblEmployees and TblEmail but when adding tblCV then the form blanks.

I thought tblCV may have been at fault because it has CVID - Autonumber;
CVfilepath - hyperlink; EmployeeIDFK so added cvfiledpath to tblEmail
thinking the hyperlink had something to do with it but that worked fine if
tblcv was removed from sql statement.

I have just deleted tblCV then re-created it and added successfully to
tblEmployees then added relationships and all still seems ok.

It's rarely a good idea to create One Great Master Query to do everything. You
were seeing the blank form because the query (a) had no records, so you
couldn't see the existing records and (b) was not updateable so you couldn't
see the new record either. Adding tblCVfilePath with an INNER JOIN will
restrict the query to just those records with data in both tables; you could
use LEFT JOIN instead to see all tblEmployees records with or without a
matching record in tblCVfilepath. The form was probably not updateable because
you had not created (or had inadvertantly deleted) the enforced Relationship
between the tables.

Since a given person may well have multiple email addresses (I have at least
five) and perhaps multiple CVs, I'd really suggest using a Form based on
tblEmployees with two Subforms for these related tables. If they have only one
CV then I'd just make the hyperlink (or file path) a field in tblEmployees
rather than having a separate table.

 

[Robert Painter]

Hi John,
Firstly allow me to explain. Over the past few months i have been creating
a database for my sons business which is Recruitment and ad hoc work.

The first instruction I received was to enable the office to be able to
function if a member was absent. The core business was initially devoted to
being an agency for drivers but had escalated to becoming a recruitment
agency over a diverse marketplace. If the member of staff for
secretarial/admin staff was absent due to holiday or sickness then the rest
of the office didn't know very well "who" did "what". I was ask initially
to design a db for searching who was capable of doing secretarial work from
a pool of maybe 500 on the books.
I did a quickie and took it in an was then confronted with " Well really we
have over 70 job skills and would like to find who is capable of say:
Switchboard, Typing, Filing, Word and payroll.
After many months of trial, error, success and failure (I personally drive
trucks for a living) I have managed to have 3 forms:
frm Employee for entering employee data ie name, address, phone
etc.
frm Skills for entering as many skills as employees has (list
now up to 100).
frm FindEmployee to go straight to employee and shows both
personel details and skills.
I seem to have overcome cascading combo boxes, the need to use count in the
search (planes and hangers springs to mind) and many other obstacles in
utilising msAccess but have struggled with the marvelous help from people
who obviously do this for a living and have the goodness in them to enjoy
helping others like myself who for whatever reason are struggling with this
infernal system. Whilst it probably seems easy and even obvious to those
who know (I myself do not struggle finding the correct gear out of 16 in my
44 tonne truck and manage to keep it between the white lines) for others
maybe newcomers or old hands it sometimes seems difficult.

I had a problem with your answer because all the posts i have read the thing
that stands out in table structure is the word normalise. Now the way I
think of this is to keep redundancy to a minimum by using (it seems) as many
tables as possible. If there are 5000 records in your db and only half have
an email address then you have 2500 blank spaces. Combine this with CV's and
the number grows.

I thought I was doing the correct thing by having tblEmployee, tbl skill,
tblCategory, tblEmployeeSkill, tblE-mailaddress, tblCV even though it may be
that everyone has an email address and Cv because there will be no
redundancy if some do not, and now you tell me different. ??

The reason I ask the question is that whilst I seem to have overcome the
problem I had; I could not understand how I had overcome it. What I do not
want is for this to come back and bite me later.

Good morning,
Not sure why but I am struggling with forms using 3 tables.
I have a form with all details of employees(frmEmployees) and am now
adding
email address and CV (resume) to this form. In order to keep
normalisation
i created 2 new tables: tblEmail and tblCV and added relationships
EmployeeID - EmployeeIDFK.
When I tried to create a form using:

SELECT tblEmployees.CustomisedID, tblEmployees.Title,
tblEmployees.FirstName, tblEmployees.EmployeeName, tblEmployees.Address,
tblEmployees.City, tblEmployees.PostalCode, tblEmployees.HomePhone,
tblEmployees.MobilePhone, tblEmployees.DrivingLicenceExpires,
tblEmployees.EmploymentStatus, tblEmployees.Notes, tblEmployees.Driver,
tblEmployees.[Full/Part Time], tblEmployees.[Temp/Permanent work],
tblEmployees.Salary, tblEmployees.[Date of Registration],
[tblEmailAddress/CV].[E-mail Address], [tblEmailAddress/CV].CVfilepath,
tblEmployees.EmployeeID, tblCVfilePath.CVfilepath FROM tblCVfilePath,
tblEmployees INNER JOIN [tblEmailAddress/CV] ON tblEmployees.EmployeeID =
[tblEmailAddress/CV].EmployeeIDFK;

I can see all in design and datasheet view but in form view the form is
blank.

Whilst I have sat and tried to solve this problem I have found all ok if I
have tblEmployees and TblEmail but when adding tblCV then the form blanks.

I thought tblCV may have been at fault because it has CVID - Autonumber;
CVfilepath - hyperlink; EmployeeIDFK so added cvfiledpath to tblEmail
thinking the hyperlink had something to do with it but that worked fine if
tblcv was removed from sql statement.

I have just deleted tblCV then re-created it and added successfully to
tblEmployees then added relationships and all still seems ok.

It's rarely a good idea to create One Great Master Query to do everything.
You
were seeing the blank form because the query (a) had no records, so you
couldn't see the existing records and (b) was not updateable so you
couldn't
see the new record either. Adding tblCVfilePath with an INNER JOIN will
restrict the query to just those records with data in both tables; you
could
use LEFT JOIN instead to see all tblEmployees records with or without a
matching record in tblCVfilepath. The form was probably not updateable
because
you had not created (or had inadvertantly deleted) the enforced
Relationship
between the tables.

Since a given person may well have multiple email addresses (I have at
least
five) and perhaps multiple CVs, I'd really suggest using a Form based on
tblEmployees with two Subforms for these related tables. If they have only
one
CV then I'd just make the hyperlink (or file path) a field in tblEmployees
rather than having a separate table.