Relationships and adding records

[channell]

Ok, so I have a database full of employees. Now, every day, I enter what
amployees worked where. When I first Designed the data base, I had the
option to make employees the main focus, or the date the main focus. I saw
multiple pros and cons to both, and eventually decided to make employees the
main focus of the database. Now, here is where I have a problem:

I can go into each individual employee's records, "Add New Record" and enter
the date and what position they filled for that day. And to go a step
further, I can add multiple dates for one employee. Now, if I do that for
50+ people a day, it can get monotonous. I want to do the opposite. Is
there a way I can create a form based off a query that I can just have the
date be the main focus (meaning I only enter it once), and then add multiple
employees on a continuous form? I hope I explained myself well enough. The
Primary Key is EMPLOYEE ID.

 

[Tom van Stiphout]

On Wed, 10 Dec 2008 05:29:01 -0800, channell

Before we talk about forms, let's talk about database design. I don't
understand your table layout. Can you summarize for the relevant
tables and fields? It should be something like this:

tblEmployees
EmployeeID PK
FirstName
LastName

tblWorkLocations
LocationID PK
LocationName

'This is a classic many-to-many relation:
tblWhoWorkedWhere
EmployeeID PK
LocationID PK
WorkDate PK

-Tom.
Microsoft Access MVP

 

[Golfinray]

Add a combo box to your form. Let the wizard set it up for you and let
employee id be the source. Then right click on the box to get properties, go
to events, select the AfterUpdate event. Out to the right there is a little
button, push that and start the code builder. Type:
Me.filter = "[employee Id] = """ & me.combo# & """"
Me.filteron = true
The combo number will be displayed there, like combo10 or combo4

 

[S.Clark]

I assume that you have two tables already... tblEmployee & tblEmployee_work

I would create another table tblWorkDates, that has a single field of
datatype DateTime. Create a main/sub form with tblWorkDates as the main, and
tblEmployee_Work as the sub.

 

[Maarkr]

You will need to change the relationship from one employee - many days to one
day - many employees. I think if you do this you will be starting over for
your new structure. Instead of having EmployeeID linking to your foreign key
in the WorkDate
table, you need to modify your structure to have your WorkDateID to a
foreign key in the Employee table; like adding a WD_ID field (long int) to
the employee table. Then link them up in the relationship window, and make
a new entry form with the WorkDate table as primary with an employee sub.
Depending on how the rest of the structure is built and how complex it is,
you may also be able to set it up to have a form with the day and a
multi-select box with employee names, so you go to a day and select all
employees for that day, then update it. If you have more daily employee
details, you would have to go in and update those is a separate form --
unless you're talented enough to add that info on the multi-select page.

 

[channell]

Hmmm... Ok, Let me Try: I have my "Relationships" Opened and the relevant
ones read as follows:

tblEMPLOYEES
Employee ID (PK)
FNAME
LNAME
....and so forth

tblDAILYINFO
INFO ID (PK)
EMPLOYEE ID (How this table and "tblEMPLOYEES" is Linked)
DATE (This is the important one)
POSITION
....and so forth

Do you need anything else? My Database jargon is not up to par, but I am
working on it. So if you could help me with what you mean by "many-to-many
relationship" and so forth. Thanks a ton Tom!!

-Scott Channell

 

[Tom van Stiphout]

On Wed, 10 Dec 2008 06:14:01 -0800, channell

many to many: Often written as M:M = a relationship between two tables
where you can say: each <first_table> has many <last_table> records,
and each <last_table> has many <first_table> records. In my example:
each employee works in many locations (over time), and in each
location we have many employees working.
Many to many relations require a third "junction" table to express
them, that's why I have 3 tables rather than 2, and tblWhoWorkedWhere
is that junction table. In the relationships diagram you draw a
1-to-many (1:M) link from Employees to junction, and from Locations to
junction. And enforce them (of course).

What you showed is a 1:M: each employee has many dailyinfos, and each
dailyinfo is about a single employee. That can work as well if
location (= position?) is not important. I think that if you had
PositionID rather than Position (text) you had a M:M between Employees
and Positions.

Don't use "Date" as a field name; it is a reserved word. WorkDate or
some other term is better.

Can DailyInfo only have one record per employee per date? If yes, put
a unique index on the combination of EmployeeID and WorkDate. Use the
Indexes form for that.

Does the above information trigger you to make db changes?

-Tom.
Microsoft Access MVP

 

[channell]

This does help, yes. Now, I forgot to mention a table:

tblePOSITION
POSITION ID (PK)
POSITION
EMPLOYEE ID (I am not sure I need that in this table... it doesn't seem to
do much)


Now, POSITION ID is also linked in tblDAILYINFO to POSITION. I guess I need
to make a third table (M:M) with tblEMPLOYEES, tbleDAILYINFO and tblPOSITION
?? Is that correct in that assumption?

 

[John W. Vinson]

Ok, so I have a database full of employees. Now, every day, I enter what
amployees worked where. When I first Designed the data base, I had the
option to make employees the main focus, or the date the main focus. I saw
multiple pros and cons to both, and eventually decided to make employees the
main focus of the database. Now, here is where I have a problem:

I can go into each individual employee's records, "Add New Record" and enter
the date and what position they filled for that day. And to go a step
further, I can add multiple dates for one employee. Now, if I do that for
50+ people a day, it can get monotonous. I want to do the opposite. Is
there a way I can create a form based off a query that I can just have the
date be the main focus (meaning I only enter it once), and then add multiple
employees on a continuous form? I hope I explained myself well enough. The
Primary Key is EMPLOYEE ID.

Just to add to the advice you're getting... you can actually use a Form with
an unbound textbox with a control source

=Date()

to display today's date. Let's call this txtToday.

Put a Subform on the form based on your tblDailyInfo; set the Master Link
Field to

txtToday

This won't be in the dropdown list but it will accept it and fill in today's
date for all new records in the subform. Use a combo box on the subform to
select the employee ID.

As Tom suggests... change the fieldname of DATE. Especially since you're using
=Date() in the form, Access can and will get confused!