Concept help

[LMB]

I have this table structure


tblEmployee
EmployeeID
EmployeeLastName
EmployeeFirstName

tblWorkAreas
WorkAreasID
WorkArea (field names are cvicu, msicu, etc...)

tblAreaWorked
AreaWorkedID
EmployeeID
WorkAreaID
WorkAreaDate
WorkAreaHours

I don't fully understand how table structure all works yet but I think this it correct. I was expecting the tblWorkAreas to be a lookup table so I typed in the areas that the therapists work in. The WorkArea is the field I based a combo box on in my form. It works fine. There are nine work areas but when I look at the table there are 21 records and the names of the work areas are duplicates. It seems that when I enter a new record for an employee, it adds it here and to the tblAreaWorked. Is this the way it's supposed to work? This table is going to get huge since there will be 18 records added each day.

Thanks,
Linda

 

[Ken Snell [MVP]]

Post the SQL of the query that is serving as the record source for the form.
I'm guessing that you don't have the right type/number of joins between
tables.

--

Ken Snell
<MS ACCESS MVP>

I have this table structure


tblEmployee
EmployeeID
EmployeeLastName
EmployeeFirstName

tblWorkAreas
WorkAreasID
WorkArea (field names are cvicu, msicu, etc...)

tblAreaWorked
AreaWorkedID
EmployeeID
WorkAreaID
WorkAreaDate
WorkAreaHours

I don't fully understand how table structure all works yet but I think this
it correct. I was expecting the tblWorkAreas to be a lookup table so I
typed in the areas that the therapists work in. The WorkArea is the field I
based a combo box on in my form. It works fine. There are nine work areas
but when I look at the table there are 21 records and the names of the work
areas are duplicates. It seems that when I enter a new record for an
employee, it adds it here and to the tblAreaWorked. Is this the way it's
supposed to work? This table is going to get huge since there will be 18
records added each day.

Thanks,
Linda

 

[PC Datasheet]

Hi Linda,

Your tables are perfect! Over and over I see in your posts that you are
using the naming conventions and primary/foreign key conventions we
discussed during the tutoring sessions. Fantastic! One comment about
tblAreaWorked --
the data in tblAreaWorked will be in a subform with either the data from
tblEmployee or tblWorkAreas in a main form. If you want the form/subform to
show an employee in the main form and be able to enter work areas and the
employee's area worked data in each work area in the subform, tblAreaWorked
is correct as you have shown. If you want the form/subform to show a work
area in the main form and be able to enter employees and each employee's
area worked data in the subform, EmployeeID and WorkAreaID need reversed.

To answer your question ---
You should be entering data for tblAreaWorked into a subform (see above).
For WorkAreaID, you should be using a combobox that gets its rows either
directly from TblWorkArea or a indirectly from a query based on TblWorkArea.
You only need a query if you need to sort the workarea data alphabetically.
In either case TblWorkArea or the query becomes the rowsource property of
the combobox. The bound Column should be 1, Column Count should be 2 and
column width should be 0;1.5. The combobox will then always have the value
of WorkAreaID for whichever work area you choose. When you look at
TblAreaWorked you should then always see only the autonumbers for WorkAreaID
in TblWorkArea (probably 1 to 9).

Steve

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com



I have this table structure


tblEmployee
EmployeeID
EmployeeLastName
EmployeeFirstName

tblWorkAreas
WorkAreasID
WorkArea (field names are cvicu, msicu, etc...)

tblAreaWorked
AreaWorkedID
EmployeeID
WorkAreaID
WorkAreaDate
WorkAreaHours

I don't fully understand how table structure all works yet but I think this
it correct. I was expecting the tblWorkAreas to be a lookup table so I
typed in the areas that the therapists work in. The WorkArea is the field I
based a combo box on in my form. It works fine. There are nine work areas
but when I look at the table there are 21 records and the names of the work
areas are duplicates. It seems that when I enter a new record for an
employee, it adds it here and to the tblAreaWorked. Is this the way it's
supposed to work? This table is going to get huge since there will be 18
records added each day.

Thanks,
Linda