Multi Select List creating multiple records

[channell]

I have a Mulit-Select List Box. I can select many employees. What I need it
to do, if anyone can help me out, is have an "OK" button available that I can
click (once my employees are selected) that will create new WorkDay records
for them.

This would absolutely be the most useful thing to me.


Tables:

tblEmployees:
EmployeeID (PK)
Employee Name
....So forth...

tblDailyInfo:
DailyInfoID (PK)
EmployeeID (Linked)
WorkDate
Position (Linkted to tblPositions)
....So forth...

tblePositions
PositionID
Position

If I may, I would like to throw one more addition to this: I have a field
(Positions) that I use. I would LOVE it if I could also have a secondary
list box (non-Multi-Select) that I can select a Position from so when I hit
the "OK" Button, it would populate their Position.

I know I am asking for a lot (I think), so I want to express my gratitude
for your help. Thank you so very much!


-Scott Channell

 

[a a r o n . k e m p f]

I would do this:

a) create UDF fnSplit from www.novick-software.com
b) get the multi-select ot make a comma seperated list (in code)
c) make an append stored procedure that looks like this (this would be
easy to do in the gui)

-----------------------------------------------------------------------------------------------
create procedure spAppendDailyInfo
(
@Employees Varchar(1000),
@Position SmallInt
)
As
Insert Into tblDailyInfo(employeeid, workdate, position)
Select EmployeeID, GetDate(), @Position
From tblEmployees
Where EmployeeID IN (Select Item from dbo.FnSplit(@employees, ',') )

 

[George Hepworth]

Dude, do you ever actually READ posts before supplying your non-sequitars?

Do you NEVER get tired of embarrassing yourself publicly?

DUDE, you have posted up a stored procedure! Being a SQL Server expert,
don't you know that stored procs run in SQL Server?

I realize you think you are trying to help, but when you post irrelevant
suggestions, you help no one, especially yourself.

Don't you think you'd be better off taking a couple of weeks to get yourself
organized and rested? Why not just give this Access thing a rest? Maybe
later you'll be able to read and think more clearly.

Best of luck, Dude.




message
I would do this:

a) create UDF fnSplit from www.novick-software.com
b) get the multi-select ot make a comma seperated list (in code)
c) make an append stored procedure that looks like this (this would be
easy to do in the gui)

-----------------------------------------------------------------------------------------------
create procedure spAppendDailyInfo
(
@Employees Varchar(1000),
@Position SmallInt
)
As
Insert Into tblDailyInfo(employeeid, workdate, position)
Select EmployeeID, GetDate(), @Position
From tblEmployees
Where EmployeeID IN (Select Item from dbo.FnSplit(@employees, ',') )

 

[Ken Sheridan]

Scott:

Firstly do you also have a PositionID column in tblEmployees? Having a
PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position. In each table the column is functionally
dependent on the key of the table, so no redundancy is involved. Although
the context is different, in principle its analogous to having a UnitPrice
column in both Products and OrderDetails, as in the sample Northwind database.

With a PositionID column in tblEmployees the need for your second list box
is avoided of course. In fact I don't see how it could work as it would give
each selected employee the same position! Assuming a PositionID column in
tblEmployees therefore, you should include the PositionID column in the list
box's RowSource (it can be a hidden column) which might therefore be like
this:

SELECT EmployeeID, PositionID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).

Also assuming it’s the current date you want inserted as the work date the
code for the button would go like this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID,WorkDate,Position) " & _
"VALUES(" & ctrl.ItemData(varItem) & ",#" & _
Format(VBA.Date,"yyyy-mm-dd") & "#," & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

I've assumed that PositionID in tblEmployees and Position in tblDailyInfo
are both columns of number data type, referencing a numeric primary key of
tblPositions.

Ken Sheridan
Stafford, England

 

[channell]

Ok, I have the following in Code:

Private Sub CommandCREATE_Click()
Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.ListEmployees1

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tDAILYINFO([EMPLOYEE ID],[WORKDATE],[PRELOAD
POSITION]) " & _
"VALUES(" & ctrl.ItemData(varItem) & ",#" & _
Format(VBA.DATE, "yyyy-mm-dd") & "#," & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

End Sub


It is now saying "Run-Time error '-2147217900 (80040e14)':
Number of Query Values and Destination Fields are not the same."

Here is the thing, I have for the WorkDate, and I should have mentioned it
earlier, a default value of "=Date()" which I prefer to keep. I am sure this
changes a couple of things, but could you help me?

I very very much appreciate you helping me and supplying me with this. I am
soo close. thank you!!

-Scott Channell

Scott:

Firstly do you also have a PositionID column in tblEmployees? Having a
PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position. In each table the column is functionally
dependent on the key of the table, so no redundancy is involved. Although
the context is different, in principle its analogous to having a UnitPrice
column in both Products and OrderDetails, as in the sample Northwind database.

With a PositionID column in tblEmployees the need for your second list box
is avoided of course. In fact I don't see how it could work as it would give
each selected employee the same position! Assuming a PositionID column in
tblEmployees therefore, you should include the PositionID column in the list
box's RowSource (it can be a hidden column) which might therefore be like
this:

SELECT EmployeeID, PositionID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).

Also assuming it’s the current date you want inserted as the work date the
code for the button would go like this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID,WorkDate,Position) " & _
"VALUES(" & ctrl.ItemData(varItem) & ",#" & _
Format(VBA.Date,"yyyy-mm-dd") & "#," & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

I've assumed that PositionID in tblEmployees and Position in tblDailyInfo
are both columns of number data type, referencing a numeric primary key of
tblPositions.

Ken Sheridan
Stafford, England

 

[channell]

Ken, Let's try this...

1. Let's not focus on the WorkDate, since that is a default value and is
automatic. So I would assume we can leave it out of the VBA.

2. I just went in and created a new work date for one of my employees, and
it put their Default Work Position in the now record automatically. (I took
your advice and put the "POSITION ID" in tEMPLOYEES (tbl). That worked
fabulously.

So really, after getting some of my stuff fixed, I just need the
Multi-Select List "OK" button to create new tDAILYINFO.DAILYINFO ID numbers.
In essence, I just need a new record created for them, and my system will
take care of the rest. Hope that makes sense.

I can't thank you enough for even making some things more cleare to me! It
is amazingly wonderful now, and this is my last step before I am officially
finished! Thank you Ken!

-Scott Channell (e-mail address removed)

Scott:

Firstly do you also have a PositionID column in tblEmployees? Having a
PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position. In each table the column is functionally
dependent on the key of the table, so no redundancy is involved. Although
the context is different, in principle its analogous to having a UnitPrice
column in both Products and OrderDetails, as in the sample Northwind database.

With a PositionID column in tblEmployees the need for your second list box
is avoided of course. In fact I don't see how it could work as it would give
each selected employee the same position! Assuming a PositionID column in
tblEmployees therefore, you should include the PositionID column in the list
box's RowSource (it can be a hidden column) which might therefore be like
this:

SELECT EmployeeID, PositionID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).

Also assuming it’s the current date you want inserted as the work date the
code for the button would go like this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID,WorkDate,Position) " & _
"VALUES(" & ctrl.ItemData(varItem) & ",#" & _
Format(VBA.Date,"yyyy-mm-dd") & "#," & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

I've assumed that PositionID in tblEmployees and Position in tblDailyInfo
are both columns of number data type, referencing a numeric primary key of
tblPositions.

Ken Sheridan
Stafford, England

I have a Mulit-Select List Box. I can select many employees. What I need it
to do, if anyone can help me out, is have an "OK" button available that I can
click (once my employees are selected) that will create new WorkDay records
for them.

This would absolutely be the most useful thing to me.


Tables:

tblEmployees:
EmployeeID (PK)
Employee Name
...So forth...

tblDailyInfo:
DailyInfoID (PK)
EmployeeID (Linked)
WorkDate
Position (Linkted to tblPositions)
...So forth...

tblePositions
PositionID
Position

If I may, I would like to throw one more addition to this: I have a field
(Positions) that I use. I would LOVE it if I could also have a secondary
list box (non-Multi-Select) that I can select a Position from so when I hit
the "OK" Button, it would populate their Position.

I know I am asking for a lot (I think), so I want to express my gratitude
for your help. Thank you so very much!


-Scott Channell

 

[channell]

Ken, Let's try this...

1. Let's not focus on the WorkDate, since that is a default value and is
automatic. So I would assume we can leave it out of the VBA.

2. I just went in and created a new work date for one of my employees, and
it put their Default Work Position in the now record automatically. (I took
your advice and put the "POSITION ID" in tEMPLOYEES (tbl). That worked
fabulously.

So really, after getting some of my stuff fixed, I just need the
Multi-Select List "OK" button to create new tDAILYINFO.DAILYINFO ID numbers.
In essence, I just need a new record created for them, and my system will
take care of the rest. Hope that makes sense.

I can't thank you enough for even making some things more cleare to me! It
is amazingly wonderful now, and this is my last step before I am officially
finished! Thank you Ken!

-Scott Channell (e-mail address removed)

Scott:

Firstly do you also have a PositionID column in tblEmployees? Having a
PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position. In each table the column is functionally
dependent on the key of the table, so no redundancy is involved. Although
the context is different, in principle its analogous to having a UnitPrice
column in both Products and OrderDetails, as in the sample Northwind database.

With a PositionID column in tblEmployees the need for your second list box
is avoided of course. In fact I don't see how it could work as it would give
each selected employee the same position! Assuming a PositionID column in
tblEmployees therefore, you should include the PositionID column in the list
box's RowSource (it can be a hidden column) which might therefore be like
this:

SELECT EmployeeID, PositionID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).

Also assuming it’s the current date you want inserted as the work date the
code for the button would go like this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID,WorkDate,Position) " & _
"VALUES(" & ctrl.ItemData(varItem) & ",#" & _
Format(VBA.Date,"yyyy-mm-dd") & "#," & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

I've assumed that PositionID in tblEmployees and Position in tblDailyInfo
are both columns of number data type, referencing a numeric primary key of
tblPositions.

Ken Sheridan
Stafford, England

I have a Mulit-Select List Box. I can select many employees. What I need it
to do, if anyone can help me out, is have an "OK" button available that I can
click (once my employees are selected) that will create new WorkDay records
for them.

This would absolutely be the most useful thing to me.


Tables:

tblEmployees:
EmployeeID (PK)
Employee Name
...So forth...

tblDailyInfo:
DailyInfoID (PK)
EmployeeID (Linked)
WorkDate
Position (Linkted to tblPositions)
...So forth...

tblePositions
PositionID
Position

If I may, I would like to throw one more addition to this: I have a field
(Positions) that I use. I would LOVE it if I could also have a secondary
list box (non-Multi-Select) that I can select a Position from so when I hit
the "OK" Button, it would populate their Position.

I know I am asking for a lot (I think), so I want to express my gratitude
for your help. Thank you so very much!


-Scott Channell

 

[Ken Sheridan]

Scott:

You should just need to amend the SQL statement slightly:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID) " & _
"VALUES(" & ctrl.ItemData(varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

Note that this relies on the BoundColumn property of the list box
referencing the column in its RowSource which is the EmployeeID.
Normally this would be the first column, but hidden by virtue of the
first dimension of the ColumnWidths property being zero, i.e. the
BoundColumn property would be 1, the ColumnWidths would be something
like 0cm;8cm and the RowSource would be:

SELECT EmployeeID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Now that you are not inserting the employee's PositionID into
tblDailyInfo, but purely referencing it via the relationship with
tblEmployees this does of course mean that if an employee's position
subsequently changes, then there will be know way of knowing what
their position was at the time the row was inserted as the database
will only tell you their current position. So long as you are not
losing a significant piece of information as a result, this is fine,
but otherwise you'd need the column in both tables. In the case of
the analogous situation with unit prices in the sample Northwind
database, which I mentioned, Its obviously essential that there are
UnitPrice columns in both tables as you wouldn't want old invoices
showing the new prices whenever one changes – the auditors would soon
have something to say about it! In your case the position (no pun
intended!) is not so clear cut.

Ken Sheridan
Stafford, England

Ken, Let's try this...

1. Let's not focus on the WorkDate, since that is a default value and is
automatic.  So I would assume we can leave it out of the VBA.

2. I just went in and created a new work date for one of my employees, and
it put their Default Work Position in the now record automatically.  (Itook
your advice and put the "POSITION ID" in tEMPLOYEES (tbl).  That worked
fabulously.

So really, after getting some of my stuff fixed, I just need the
Multi-Select List "OK" button to create new tDAILYINFO.DAILYINFO ID numbers.  
In essence, I just need a new record created for them, and my system will
take care of the rest.  Hope that makes sense.  

I can't thank you enough for even making some things more cleare to me!  It
is amazingly wonderful now, and this is my last step before I am officially
finished!  Thank you Ken!

-Scott Channell  (e-mail address removed)

Scott:

Firstly do you also have a PositionID column in tblEmployees?  Havinga
PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position.  In each table the column is functionally
dependent on the key of the table, so no redundancy is involved.  Although
the context is different, in principle its analogous to having a UnitPrice
column in both Products and OrderDetails, as in the sample Northwind database.

With a PositionID column in tblEmployees the need for your second list box
is avoided of course.  In fact I don't see how it could work as it would give
each selected employee the same position!  Assuming a PositionID column in
tblEmployees therefore, you should include the PositionID column in thelist
box's RowSource (it can be a hidden column) which might therefore be like
this:

SELECT EmployeeID, PositionID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).

Also assuming it’s the current date you want inserted as the work date the
code for the button would go like this:

    Dim cmd As ADODB.Command
    Dim strSQL As String
    Dim varItem As Variant
    Dim ctrl As Control

    Set ctrl = Me.lstEmployees

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText

    If ctrl.ItemsSelected.Count > 0 Then
        For Each varItem In ctrl.ItemsSelected
        strSQL = "INSERT INTO tblDailyInfo(EmployeeID,WorkDate,Position) " & _
            "VALUES(" & ctrl.ItemData(varItem) & ",#" & _
            Format(VBA.Date,"yyyy-mm-dd") & "#," & _
            ctrl.Column(1, varItem) & ")"
        cmd.CommandText = strSQL
        cmd.Execute
        Next varItem
    End If

I've assumed that PositionID in tblEmployees and Position in tblDailyInfo
are both columns of number data type, referencing a numeric primary keyof
tblPositions.

Ken Sheridan
Stafford, England

"channell" wrote:

 

[channell]

Ken, I am so sorry, and I appreciate your patience:

When I get a chance tonight, I will most certainly check out Northwind. I
think I need to back up a few steps here. Lets just focus on the
relationships first, then we can work on that "OK" button. I did not realize
you wanted me to take the relationship of [tblPOSITIONS].[POSITION ID] away
from tblDAILYINFO. I guess I didn't understand that. I think you see where
I want to go with this though, and maybe I need to backtrack or change just a
couple of things around.

Ok, I will lay out my Positions for you. This is a database for a wearhouse
invironment.

Positions are as follows: Blue, Brown, Orange, Red, Small Sort, Unload,
Yellow. There are multiple employees on "Blue" or on "Unload" etc everyday.

Again, I will lay out the relevant tables:

tEMPLOYEES (PK)
EMPLOYEE ID
EMPLOYEE NAME
POSITION ID (Newly Added Relationship)
....so forth

tDAILYINFO
DAILYINFO ID (PK)
WorkDate
POSITION ID (FK) "number value"
....so forth

tPOSITIONS
POSITION ID
POSITION

I need the Employees to have a set position they *normally* have. Now, they
can change around periodically, but that doesn't happen all that often,
unless absent or to fill in for another absent employee. Now, the thing that
you wrote really caught my attention

"Having a

PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position>"

I need this. I need to know what their current position is at the time the
record was created. I also need that position to come up automatically when
a new record is created, and that is what I have been trying to get for a
while. I manually hit the drop-down sombo box and select each employee's
position each day.

So with this said, maybe we can take a step back, and I won't try to rush
anything.

Again, I greatly appreciate your patience Ken. It is very much appreciated.
If I could conquer these things with your help, I will be able to finish up
the database. Thank you.

-Scott Channell (e-mail address removed)

Scott:

You should just need to amend the SQL statement slightly:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID) " & _
"VALUES(" & ctrl.ItemData(varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

Note that this relies on the BoundColumn property of the list box
referencing the column in its RowSource which is the EmployeeID.
Normally this would be the first column, but hidden by virtue of the
first dimension of the ColumnWidths property being zero, i.e. the
BoundColumn property would be 1, the ColumnWidths would be something
like 0cm;8cm and the RowSource would be:

SELECT EmployeeID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Now that you are not inserting the employee's PositionID into
tblDailyInfo, but purely referencing it via the relationship with
tblEmployees this does of course mean that if an employee's position
subsequently changes, then there will be know way of knowing what
their position was at the time the row was inserted as the database
will only tell you their current position. So long as you are not
losing a significant piece of information as a result, this is fine,
but otherwise you'd need the column in both tables. In the case of
the analogous situation with unit prices in the sample Northwind
database, which I mentioned, Its obviously essential that there are
UnitPrice columns in both tables as you wouldn't want old invoices
showing the new prices whenever one changes – the auditors would soon
have something to say about it! In your case the position (no pun
intended!) is not so clear cut.

Ken Sheridan
Stafford, England

Ken, Let's try this...

1. Let's not focus on the WorkDate, since that is a default value and is
automatic. So I would assume we can leave it out of the VBA.

2. I just went in and created a new work date for one of my employees, and
it put their Default Work Position in the now record automatically. (I took
your advice and put the "POSITION ID" in tEMPLOYEES (tbl). That worked
fabulously.

So really, after getting some of my stuff fixed, I just need the
Multi-Select List "OK" button to create new tDAILYINFO.DAILYINFO ID numbers.
In essence, I just need a new record created for them, and my system will
take care of the rest. Hope that makes sense.

I can't thank you enough for even making some things more cleare to me! It
is amazingly wonderful now, and this is my last step before I am officially
finished! Thank you Ken!

-Scott Channell (e-mail address removed)

Scott:

Firstly do you also have a PositionID column in tblEmployees? Having a
PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position. In each table the column is functionally
dependent on the key of the table, so no redundancy is involved. Although
the context is different, in principle its analogous to having a UnitPrice
column in both Products and OrderDetails, as in the sample Northwind database.

With a PositionID column in tblEmployees the need for your second list box
is avoided of course. In fact I don't see how it could work as it would give
each selected employee the same position! Assuming a PositionID column in
tblEmployees therefore, you should include the PositionID column in the list
box's RowSource (it can be a hidden column) which might therefore be like
this:

SELECT EmployeeID, PositionID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).

Also assuming it’s the current date you want inserted as the work date the
code for the button would go like this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID,WorkDate,Position) " & _
"VALUES(" & ctrl.ItemData(varItem) & ",#" & _
Format(VBA.Date,"yyyy-mm-dd") & "#," & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

I've assumed that PositionID in tblEmployees and Position in tblDailyInfo
are both columns of number data type, referencing a numeric primary key of
tblPositions.

Ken Sheridan
Stafford, England

"channell" wrote:

I have a Mulit-Select List Box. I can select many employees. What I need it
to do, if anyone can help me out, is have an "OK" button available that I can
click (once my employees are selected) that will create new WorkDay records
for them.

This would absolutely be the most useful thing to me.

tblEmployees:
EmployeeID (PK)
Employee Name
...So forth...

tblDailyInfo:
DailyInfoID (PK)
EmployeeID (Linked)
WorkDate
Position (Linkted to tblPositions)
...So forth...

If I may, I would like to throw one more addition to this: I have a field
(Positions) that I use. I would LOVE it if I could also have a secondary
list box (non-Multi-Select) that I can select a Position from so when I hit
the "OK" Button, it would populate their Position.

I know I am asking for a lot (I think), so I want to express my gratitude
for your help. Thank you so very much!

-Scott Channell

 

[Ken Sheridan]

Scott:

It all comes down to what are known a 'functional dependencies'. This is
a very important concept in relational database design as it determines when
a table is 'normalized', i.e. when it is correctly structured so as to
contain no redundancies. Redundancy in this context means when the database
tells us the 'fact' twice or more, e.g. in a table of addresses, if we had
both City and State columns we could be told that SanFrancisco is in
California more than once. So what, you might say. Well, there is nothing
to stop someone entering a row where San Francisco is in Alaabama or New
Hampshire or any other state, so redundancy leaves the door open to such
inconsistencies ( a visitor from Mars wouldn't know which is the correct
state), which is really why it’s a 'bad thing'.

There are rules for ensuring normalization, which are based on the concept
of functional dependency. Functional dependency in essence means that for
any given value of column 1 the value of column 2 will always be the same;
column 2 is functionally dependent on column 1; column 1 is said to be a
'determinant' of column 2. So If EmployeeID in a table Employees is 42, and
that row is for me then FirstName is always Ken and LastName always Sheridan,
both columns being functionally dependent on EmployeeID. If we have another
table Projects with columns EmployeeID and ProjectID and also FirstName and
LastName, then I could be in one row as Ken Sheridan and another as Keith
Sheridan (I used to play rugby football for a team whose captain always
called me Keith for some reason, so these sort of mistakes are not hard to
make). This table is not normalized (or more strictly speaking its not
normalized to third normal form). The reason its not normalized is that
normalization to third normal form (3NF) requires that any non-key column
must be functionally dependent solely on the whole of the primary key. The
primary key of this Projects table is in fact a composite one made up of the
two columns EmployeeID and ProjectID as these must be unique in combination.
Now we've already seen that FirstName and LastName are functionally dependent
on EmployeeID, so in Projects FirstName and LastName are functionally
dependent on a part of the key, not on the whole of the key, which breaks the
normalization to 3NF rule.

When we apply these concepts to your tables, firstly in the case of
tEmployees then Position ID, i.e. the employee's current or default position,
is functionally dependent solely on Employee ID, which is the table's primary
key, so the table is normalized to 3NF at least (and probably beyond – it
goes up to 5NF if we disregard the more bizarre normal forms beyond that).
So the table is fine.

When it comes to tDailyInfo then this will include both Employee ID and
Position ID columns, but unlike my example of projects above Position ID is
in this case not functionally dependent solely on Employee ID, because you've
indicated that an employee's position might be different in one row in
tDailyInfo from that in another row in tDailyInfo. So in this case the
normalization rule is not broken, there is no redundancy and the table is not
at risk of inconsistent data being entered. Again the table is fine.

As far as the relationships are concerned, there is a one-to-many
relationship between tEmployees and tDailyInfo on Employee ID; there is a
one-to-many relationship between tPositions and tEmployees on Position ID;
and there is a one-to-many relationship between tDailyInfo and tDailyInfo
on PositionID.

When it comes to inserting rows into tDailyInfo for those employees selected
in the list box then you need to insert values into the Employe ID and
Position ID columns, so it should only need a small amendment to my original
reply:
The list box's RowSource property would be:
SELECT [Employee ID], [Position ID], [Employee Name] FROM tEmployees ORDER
BY [Employee Name];
Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).
The code for the button would go like this:
Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control
Set ctrl = Me.lstEmployees
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo([Employee ID], [Position ID]) " &
_
"VALUES(" & ctrl.ItemData(varItem) & ", " & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If
I've assumed that the list box is called lstEmployees and that the
EmployeeID and Position ID columns in tlEmployees and in tDailyInfo
are both of number data type.
Ken Sheridan
Stafford, England

Ken, I am so sorry, and I appreciate your patience:

When I get a chance tonight, I will most certainly check out Northwind. I
think I need to back up a few steps here. Lets just focus on the
relationships first, then we can work on that "OK" button. I did not realize
you wanted me to take the relationship of [tblPOSITIONS].[POSITION ID] away
from tblDAILYINFO. I guess I didn't understand that. I think you see where
I want to go with this though, and maybe I need to backtrack or change just a
couple of things around.

Ok, I will lay out my Positions for you. This is a database for a wearhouse
invironment.

Positions are as follows: Blue, Brown, Orange, Red, Small Sort, Unload,
Yellow. There are multiple employees on "Blue" or on "Unload" etc everyday.

Again, I will lay out the relevant tables:

tEMPLOYEES (PK)
EMPLOYEE ID
EMPLOYEE NAME
POSITION ID (Newly Added Relationship)
...so forth

tDAILYINFO
DAILYINFO ID (PK)
WorkDate
POSITION ID (FK) "number value"
...so forth

tPOSITIONS
POSITION ID
POSITION

I need the Employees to have a set position they *normally* have. Now, they
can change around periodically, but that doesn't happen all that often,
unless absent or to fill in for another absent employee. Now, the thing that
you wrote really caught my attention

"Having a

PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position>"

I need this. I need to know what their current position is at the time the
record was created. I also need that position to come up automatically when
a new record is created, and that is what I have been trying to get for a
while. I manually hit the drop-down sombo box and select each employee's
position each day.

So with this said, maybe we can take a step back, and I won't try to rush
anything.

Again, I greatly appreciate your patience Ken. It is very much appreciated.
If I could conquer these things with your help, I will be able to finish up
the database. Thank you.

-Scott Channell (e-mail address removed)

Scott:

You should just need to amend the SQL statement slightly:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID) " & _
"VALUES(" & ctrl.ItemData(varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

Note that this relies on the BoundColumn property of the list box
referencing the column in its RowSource which is the EmployeeID.
Normally this would be the first column, but hidden by virtue of the
first dimension of the ColumnWidths property being zero, i.e. the
BoundColumn property would be 1, the ColumnWidths would be something
like 0cm;8cm and the RowSource would be:

SELECT EmployeeID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Now that you are not inserting the employee's PositionID into
tblDailyInfo, but purely referencing it via the relationship with
tblEmployees this does of course mean that if an employee's position
subsequently changes, then there will be know way of knowing what
their position was at the time the row was inserted as the database
will only tell you their current position. So long as you are not
losing a significant piece of information as a result, this is fine,
but otherwise you'd need the column in both tables. In the case of
the analogous situation with unit prices in the sample Northwind
database, which I mentioned, Its obviously essential that there are
UnitPrice columns in both tables as you wouldn't want old invoices
showing the new prices whenever one changes – the auditors would soon
have something to say about it! In your case the position (no pun
intended!) is not so clear cut.

Ken Sheridan
Stafford, England

Ken, Let's try this...

1. Let's not focus on the WorkDate, since that is a default value and is
automatic. So I would assume we can leave it out of the VBA.

2. I just went in and created a new work date for one of my employees, and
it put their Default Work Position in the now record automatically. (I took
your advice and put the "POSITION ID" in tEMPLOYEES (tbl). That worked
fabulously.

So really, after getting some of my stuff fixed, I just need the
Multi-Select List "OK" button to create new tDAILYINFO.DAILYINFO ID numbers.
In essence, I just need a new record created for them, and my system will
take care of the rest. Hope that makes sense.

I can't thank you enough for even making some things more cleare to me! It
is amazingly wonderful now, and this is my last step before I am officially
finished! Thank you Ken!

-Scott Channell (e-mail address removed)

:
Scott:

Firstly do you also have a PositionID column in tblEmployees? Having a
PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position. In each table the column is functionally
dependent on the key of the table, so no redundancy is involved. Although
the context is different, in principle its analogous to having a UnitPrice
column in both Products and OrderDetails, as in the sample Northwind database.

With a PositionID column in tblEmployees the need for your second list box
is avoided of course. In fact I don't see how it could work as it would give
each selected employee the same position! Assuming a PositionID column in
tblEmployees therefore, you should include the PositionID column in the list
box's RowSource (it can be a hidden column) which might therefore be like
this:

SELECT EmployeeID, PositionID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).

Also assuming it’s the current date you want inserted as the work date the
code for the button would go like this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID,WorkDate,Position) " & _
"VALUES(" & ctrl.ItemData(varItem) & ",#" & _
Format(VBA.Date,"yyyy-mm-dd") & "#," & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

I've assumed that PositionID in tblEmployees and Position in tblDailyInfo
are both columns of number data type, referencing a numeric primary key of
tblPositions.

Ken Sheridan
Stafford, England

:

I have a Mulit-Select List Box. I can select many employees. What I need it
to do, if anyone can help me out, is have an "OK" button available that I can
click (once my employees are selected) that will create new WorkDay records
for them.

This would absolutely be the most useful thing to me.

Tables:

tblEmployees:
EmployeeID (PK)
Employee Name
...So forth...

tblDailyInfo:
DailyInfoID (PK)
EmployeeID (Linked)
WorkDate
Position (Linkted to tblPositions)
...So forth...

tblePositions
PositionID
Position

If I may, I would like to throw one more addition to this: I have a field
(Positions) that I use. I would LOVE it if I could also have a secondary
list box (non-Multi-Select) that I can select a Position from so when I hit
the "OK" Button, it would populate their Position.

I know I am asking for a lot (I think), so I want to express my gratitude
for your help. Thank you so very much!

-Scott Channell

 

[channell]

Ken, I thank you for everything! I almost have it, but it is now giving me
an error:

Complie Error: Syntax Error

It pulls me to the debugger, and this is what it highlights

strSQL = "INSERT INTO tDailyInfo([EMPLOYEE ID], [PRELOAD POSITION]) " &_
"VALUES(" & ctrl.ItemData(varItem) & ", " & _ctrl.Column(1, varItem)
& ")"

It for some reason does not like the Underscores at all right after the "&"
signs. It is highlighting this statement in RED.

Thanks again Ken.

Scott:

It all comes down to what are known a 'functional dependencies'. This is
a very important concept in relational database design as it determines when
a table is 'normalized', i.e. when it is correctly structured so as to
contain no redundancies. Redundancy in this context means when the database
tells us the 'fact' twice or more, e.g. in a table of addresses, if we had
both City and State columns we could be told that SanFrancisco is in
California more than once. So what, you might say. Well, there is nothing
to stop someone entering a row where San Francisco is in Alaabama or New
Hampshire or any other state, so redundancy leaves the door open to such
inconsistencies ( a visitor from Mars wouldn't know which is the correct
state), which is really why it’s a 'bad thing'.

There are rules for ensuring normalization, which are based on the concept
of functional dependency. Functional dependency in essence means that for
any given value of column 1 the value of column 2 will always be the same;
column 2 is functionally dependent on column 1; column 1 is said to be a
'determinant' of column 2. So If EmployeeID in a table Employees is 42, and
that row is for me then FirstName is always Ken and LastName always Sheridan,
both columns being functionally dependent on EmployeeID. If we have another
table Projects with columns EmployeeID and ProjectID and also FirstName and
LastName, then I could be in one row as Ken Sheridan and another as Keith
Sheridan (I used to play rugby football for a team whose captain always
called me Keith for some reason, so these sort of mistakes are not hard to
make). This table is not normalized (or more strictly speaking its not
normalized to third normal form). The reason its not normalized is that
normalization to third normal form (3NF) requires that any non-key column
must be functionally dependent solely on the whole of the primary key. The
primary key of this Projects table is in fact a composite one made up of the
two columns EmployeeID and ProjectID as these must be unique in combination.
Now we've already seen that FirstName and LastName are functionally dependent
on EmployeeID, so in Projects FirstName and LastName are functionally
dependent on a part of the key, not on the whole of the key, which breaks the
normalization to 3NF rule.

When we apply these concepts to your tables, firstly in the case of
tEmployees then Position ID, i.e. the employee's current or default position,
is functionally dependent solely on Employee ID, which is the table's primary
key, so the table is normalized to 3NF at least (and probably beyond – it
goes up to 5NF if we disregard the more bizarre normal forms beyond that).
So the table is fine.

When it comes to tDailyInfo then this will include both Employee ID and
Position ID columns, but unlike my example of projects above Position ID is
in this case not functionally dependent solely on Employee ID, because you've
indicated that an employee's position might be different in one row in
tDailyInfo from that in another row in tDailyInfo. So in this case the
normalization rule is not broken, there is no redundancy and the table is not
at risk of inconsistent data being entered. Again the table is fine.

As far as the relationships are concerned, there is a one-to-many
relationship between tEmployees and tDailyInfo on Employee ID; there is a
one-to-many relationship between tPositions and tEmployees on Position ID;
and there is a one-to-many relationship between tDailyInfo and tDailyInfo
on PositionID.

When it comes to inserting rows into tDailyInfo for those employees selected
in the list box then you need to insert values into the Employe ID and
Position ID columns, so it should only need a small amendment to my original
reply:
The list box's RowSource property would be:
SELECT [Employee ID], [Position ID], [Employee Name] FROM tEmployees ORDER
BY [Employee Name];
Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).
The code for the button would go like this:
Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control
Set ctrl = Me.lstEmployees
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo([Employee ID], [Position ID]) " &
_
"VALUES(" & ctrl.ItemData(varItem) & ", " & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If
I've assumed that the list box is called lstEmployees and that the
EmployeeID and Position ID columns in tlEmployees and in tDailyInfo
are both of number data type.
Ken Sheridan
Stafford, England

Ken, I am so sorry, and I appreciate your patience:

When I get a chance tonight, I will most certainly check out Northwind. I
think I need to back up a few steps here. Lets just focus on the
relationships first, then we can work on that "OK" button. I did not realize
you wanted me to take the relationship of [tblPOSITIONS].[POSITION ID] away
from tblDAILYINFO. I guess I didn't understand that. I think you see where
I want to go with this though, and maybe I need to backtrack or change just a
couple of things around.

Ok, I will lay out my Positions for you. This is a database for a wearhouse
invironment.

Positions are as follows: Blue, Brown, Orange, Red, Small Sort, Unload,
Yellow. There are multiple employees on "Blue" or on "Unload" etc everyday.

Again, I will lay out the relevant tables:

tEMPLOYEES (PK)
EMPLOYEE ID
EMPLOYEE NAME
POSITION ID (Newly Added Relationship)
...so forth

tDAILYINFO
DAILYINFO ID (PK)
WorkDate
POSITION ID (FK) "number value"
...so forth

tPOSITIONS
POSITION ID
POSITION

I need the Employees to have a set position they *normally* have. Now, they
can change around periodically, but that doesn't happen all that often,
unless absent or to fill in for another absent employee. Now, the thing that
you wrote really caught my attention

"Having a

PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position>"

I need this. I need to know what their current position is at the time the
record was created. I also need that position to come up automatically when
a new record is created, and that is what I have been trying to get for a
while. I manually hit the drop-down sombo box and select each employee's
position each day.

So with this said, maybe we can take a step back, and I won't try to rush
anything.

Again, I greatly appreciate your patience Ken. It is very much appreciated.
If I could conquer these things with your help, I will be able to finish up
the database. Thank you.

-Scott Channell (e-mail address removed)

Scott:

You should just need to amend the SQL statement slightly:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID) " & _
"VALUES(" & ctrl.ItemData(varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

Note that this relies on the BoundColumn property of the list box
referencing the column in its RowSource which is the EmployeeID.
Normally this would be the first column, but hidden by virtue of the
first dimension of the ColumnWidths property being zero, i.e. the
BoundColumn property would be 1, the ColumnWidths would be something
like 0cm;8cm and the RowSource would be:

SELECT EmployeeID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Now that you are not inserting the employee's PositionID into
tblDailyInfo, but purely referencing it via the relationship with
tblEmployees this does of course mean that if an employee's position
subsequently changes, then there will be know way of knowing what
their position was at the time the row was inserted as the database
will only tell you their current position. So long as you are not
losing a significant piece of information as a result, this is fine,
but otherwise you'd need the column in both tables. In the case of
the analogous situation with unit prices in the sample Northwind
database, which I mentioned, Its obviously essential that there are
UnitPrice columns in both tables as you wouldn't want old invoices
showing the new prices whenever one changes – the auditors would soon
have something to say about it! In your case the position (no pun
intended!) is not so clear cut.

Ken Sheridan
Stafford, England

On Jan 9, 3:20 pm, channell <[email protected]>
wrote:
Ken, Let's try this...

1. Let's not focus on the WorkDate, since that is a default value and is
automatic. So I would assume we can leave it out of the VBA.

2. I just went in and created a new work date for one of my employees, and
it put their Default Work Position in the now record automatically. (I took
your advice and put the "POSITION ID" in tEMPLOYEES (tbl). That worked
fabulously.

So really, after getting some of my stuff fixed, I just need the
Multi-Select List "OK" button to create new tDAILYINFO.DAILYINFO ID numbers.
In essence, I just need a new record created for them, and my system will
take care of the rest. Hope that makes sense.

I can't thank you enough for even making some things more cleare to me! It
is amazingly wonderful now, and this is my last step before I am officially
finished! Thank you Ken!

-Scott Channell (e-mail address removed)

:
Scott:

Firstly do you also have a PositionID column in tblEmployees? Having a
PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position. In each table the column is functionally
dependent on the key of the table, so no redundancy is involved. Although
the context is different, in principle its analogous to having a UnitPrice
column in both Products and OrderDetails, as in the sample Northwind database.

With a PositionID column in tblEmployees the need for your second list box
is avoided of course. In fact I don't see how it could work as it would give
each selected employee the same position! Assuming a PositionID column in
tblEmployees therefore, you should include the PositionID column in the list
box's RowSource (it can be a hidden column) which might therefore be like
this:

SELECT EmployeeID, PositionID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).

Also assuming it’s the current date you want inserted as the work date the
code for the button would go like this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID,WorkDate,Position) " & _
"VALUES(" & ctrl.ItemData(varItem) & ",#" & _
Format(VBA.Date,"yyyy-mm-dd") & "#," & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

I've assumed that PositionID in tblEmployees and Position in tblDailyInfo
are both columns of number data type, referencing a numeric primary key of
tblPositions.

Ken Sheridan
Stafford, England

:

I have a Mulit-Select List Box. I can select many employees. What I need it
to do, if anyone can help me out, is have an "OK" button available that I can
click (once my employees are selected) that will create new WorkDay records
for them.

This would absolutely be the most useful thing to me.

 

[Douglas J. Steele]

Space-underscore is the line continuation character. What's after the
underscore should be on a new line.

Either that, or remove the underscore.

Many of the regular responders here are in the habit of using line
continuation characters to prevent problems with word wrap.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ken, I thank you for everything! I almost have it, but it is now giving
me
an error:

Complie Error: Syntax Error

It pulls me to the debugger, and this is what it highlights

strSQL = "INSERT INTO tDailyInfo([EMPLOYEE ID], [PRELOAD POSITION]) " &_
"VALUES(" & ctrl.ItemData(varItem) & ", " & _ctrl.Column(1,
varItem)
& ")"

It for some reason does not like the Underscores at all right after the
"&"
signs. It is highlighting this statement in RED.

Thanks again Ken.

Scott:

It all comes down to what are known a 'functional dependencies'. This
is
a very important concept in relational database design as it determines
when
a table is 'normalized', i.e. when it is correctly structured so as to
contain no redundancies. Redundancy in this context means when the
database
tells us the 'fact' twice or more, e.g. in a table of addresses, if we
had
both City and State columns we could be told that SanFrancisco is in
California more than once. So what, you might say. Well, there is
nothing
to stop someone entering a row where San Francisco is in Alaabama or New
Hampshire or any other state, so redundancy leaves the door open to such
inconsistencies ( a visitor from Mars wouldn't know which is the correct
state), which is really why it's a 'bad thing'.

There are rules for ensuring normalization, which are based on the
concept
of functional dependency. Functional dependency in essence means that
for
any given value of column 1 the value of column 2 will always be the
same;
column 2 is functionally dependent on column 1; column 1 is said to be a
'determinant' of column 2. So If EmployeeID in a table Employees is 42,
and
that row is for me then FirstName is always Ken and LastName always
Sheridan,
both columns being functionally dependent on EmployeeID. If we have
another
table Projects with columns EmployeeID and ProjectID and also FirstName
and
LastName, then I could be in one row as Ken Sheridan and another as Keith
Sheridan (I used to play rugby football for a team whose captain always
called me Keith for some reason, so these sort of mistakes are not hard
to
make). This table is not normalized (or more strictly speaking its not
normalized to third normal form). The reason its not normalized is that
normalization to third normal form (3NF) requires that any non-key column
must be functionally dependent solely on the whole of the primary key.
The
primary key of this Projects table is in fact a composite one made up of
the
two columns EmployeeID and ProjectID as these must be unique in
combination.
Now we've already seen that FirstName and LastName are functionally
dependent
on EmployeeID, so in Projects FirstName and LastName are functionally
dependent on a part of the key, not on the whole of the key, which breaks
the
normalization to 3NF rule.

When we apply these concepts to your tables, firstly in the case of
tEmployees then Position ID, i.e. the employee's current or default
position,
is functionally dependent solely on Employee ID, which is the table's
primary
key, so the table is normalized to 3NF at least (and probably beyond - it
goes up to 5NF if we disregard the more bizarre normal forms beyond
that).
So the table is fine.

When it comes to tDailyInfo then this will include both Employee ID and
Position ID columns, but unlike my example of projects above Position ID
is
in this case not functionally dependent solely on Employee ID, because
you've
indicated that an employee's position might be different in one row in
tDailyInfo from that in another row in tDailyInfo. So in this case the
normalization rule is not broken, there is no redundancy and the table is
not
at risk of inconsistent data being entered. Again the table is fine.

As far as the relationships are concerned, there is a one-to-many
relationship between tEmployees and tDailyInfo on Employee ID; there is a
one-to-many relationship between tPositions and tEmployees on Position
ID;
and there is a one-to-many relationship between tDailyInfo and
tDailyInfo
on PositionID.

When it comes to inserting rows into tDailyInfo for those employees
selected
in the list box then you need to insert values into the Employe ID and
Position ID columns, so it should only need a small amendment to my
original
reply:
The list box's RowSource property would be:
SELECT [Employee ID], [Position ID], [Employee Name] FROM tEmployees
ORDER
BY [Employee Name];
Its column Count property would be 3 and its ColumnWidths property
something
like 0cm;0cm;8cm (or equivalent in inches).
The code for the button would go like this:
Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control
Set ctrl = Me.lstEmployees
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo([Employee ID], [Position ID])
" &
_
"VALUES(" & ctrl.ItemData(varItem) & ", " & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If
I've assumed that the list box is called lstEmployees and that the
EmployeeID and Position ID columns in tlEmployees and in tDailyInfo
are both of number data type.
Ken Sheridan
Stafford, England

Ken, I am so sorry, and I appreciate your patience:

When I get a chance tonight, I will most certainly check out Northwind.
I
think I need to back up a few steps here. Lets just focus on the
relationships first, then we can work on that "OK" button. I did not
realize
you wanted me to take the relationship of [tblPOSITIONS].[POSITION ID]
away
from tblDAILYINFO. I guess I didn't understand that. I think you see
where
I want to go with this though, and maybe I need to backtrack or change
just a
couple of things around.

Ok, I will lay out my Positions for you. This is a database for a
wearhouse
invironment.

Positions are as follows: Blue, Brown, Orange, Red, Small Sort, Unload,
Yellow. There are multiple employees on "Blue" or on "Unload" etc
everyday.

Again, I will lay out the relevant tables:

tEMPLOYEES (PK)
EMPLOYEE ID
EMPLOYEE NAME
POSITION ID (Newly Added Relationship)
...so forth

tDAILYINFO
DAILYINFO ID (PK)
WorkDate
POSITION ID (FK) "number value"
...so forth

tPOSITIONS
POSITION ID
POSITION

I need the Employees to have a set position they *normally* have. Now,
they
can change around periodically, but that doesn't happen all that often,
unless absent or to fill in for another absent employee. Now, the
thing that
you wrote really caught my attention

"Having a
PositionID column in tblDailyInfo is fine as you'd presumably want
the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it
changes,
but in situations like this its normal to also have a column in
tblEmployees
to hold their current position>"

I need this. I need to know what their current position is at the time
the
record was created. I also need that position to come up automatically
when
a new record is created, and that is what I have been trying to get for
a
while. I manually hit the drop-down sombo box and select each
employee's
position each day.

So with this said, maybe we can take a step back, and I won't try to
rush
anything.

Again, I greatly appreciate your patience Ken. It is very much
appreciated.
If I could conquer these things with your help, I will be able to
finish up
the database. Thank you.

-Scott Channell (e-mail address removed)




:

Scott:

You should just need to amend the SQL statement slightly:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID) " & _
"VALUES(" & ctrl.ItemData(varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

Note that this relies on the BoundColumn property of the list box
referencing the column in its RowSource which is the EmployeeID.
Normally this would be the first column, but hidden by virtue of the
first dimension of the ColumnWidths property being zero, i.e. the
BoundColumn property would be 1, the ColumnWidths would be something
like 0cm;8cm and the RowSource would be:

SELECT EmployeeID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Now that you are not inserting the employee's PositionID into
tblDailyInfo, but purely referencing it via the relationship with
tblEmployees this does of course mean that if an employee's position
subsequently changes, then there will be know way of knowing what
their position was at the time the row was inserted as the database
will only tell you their current position. So long as you are not
losing a significant piece of information as a result, this is fine,
but otherwise you'd need the column in both tables. In the case of
the analogous situation with unit prices in the sample Northwind
database, which I mentioned, Its obviously essential that there are
UnitPrice columns in both tables as you wouldn't want old invoices
showing the new prices whenever one changes - the auditors would soon
have something to say about it! In your case the position (no pun
intended!) is not so clear cut.

Ken Sheridan
Stafford, England

On Jan 9, 3:20 pm, channell <[email protected]>
wrote:
Ken, Let's try this...

1. Let's not focus on the WorkDate, since that is a default value
and is
automatic. So I would assume we can leave it out of the VBA.

2. I just went in and created a new work date for one of my
employees, and
it put their Default Work Position in the now record automatically.
(I took
your advice and put the "POSITION ID" in tEMPLOYEES (tbl). That
worked
fabulously.

So really, after getting some of my stuff fixed, I just need the
Multi-Select List "OK" button to create new tDAILYINFO.DAILYINFO ID
numbers.
In essence, I just need a new record created for them, and my
system will
take care of the rest. Hope that makes sense.

I can't thank you enough for even making some things more cleare to
me! It
is amazingly wonderful now, and this is my last step before I am
officially
finished! Thank you Ken!

-Scott Channell (e-mail address removed)

:
Scott:

Firstly do you also have a PositionID column in tblEmployees?
Having a
PositionID column in tblDailyInfo is fine as you'd presumably
want the rows
in this table to hold the employee's position at the time the row
was
inserted rather than to be updated with their current position if
it changes,
but in situations like this its normal to also have a column in
tblEmployees
to hold their current position. In each table the column is
functionally
dependent on the key of the table, so no redundancy is involved.
Although
the context is different, in principle its analogous to having a
UnitPrice
column in both Products and OrderDetails, as in the sample
Northwind database.

With a PositionID column in tblEmployees the need for your second
list box
is avoided of course. In fact I don't see how it could work as
it would give
each selected employee the same position! Assuming a PositionID
column in
tblEmployees therefore, you should include the PositionID column
in the list
box's RowSource (it can be a hidden column) which might therefore
be like
this:

SELECT EmployeeID, PositionID, [Employee Name] FROM tblEmployees
ORDER BY
[Employee Name];

Its column Count property would be 3 and its ColumnWidths
property something
like 0cm;0cm;8cm (or equivalent in inches).

Also assuming it's the current date you want inserted as the work
date the
code for the button would go like this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO
tblDailyInfo(EmployeeID,WorkDate,Position) " & _
"VALUES(" & ctrl.ItemData(varItem) & ",#" & _
Format(VBA.Date,"yyyy-mm-dd") & "#," & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

I've assumed that PositionID in tblEmployees and Position in
tblDailyInfo
are both columns of number data type, referencing a numeric
primary key of
tblPositions.

Ken Sheridan
Stafford, England

:

I have a Mulit-Select List Box. I can select many employees.
What I need it
to do, if anyone can help me out, is have an "OK" button
available that I can
click (once my employees are selected) that will create new
WorkDay records
for them.

This would absolutely be the most useful thing to me.

 

[channell]

Ken, it works Beautifully!!! Thank you so very much for your help!!! You
are a genius! One last little thing... if you don't mind I need a little
help with the error handling. I notice that if I select employees without an
actual assigned "POSITION" (kind of like an extra or a floater) then it will
say syntax error because it is looking for a value for POSITION ID. So, what
I am asking, is how can I do some error handiling on the VBA side of things
so if there is a null value under "POSITION ID" on the tblEmployees, then
there will not be a syntax error. Thank you Ken.

You have been the greatest help. Thank you Ken.

-Scott Channell

Scott:

It all comes down to what are known a 'functional dependencies'. This is
a very important concept in relational database design as it determines when
a table is 'normalized', i.e. when it is correctly structured so as to
contain no redundancies. Redundancy in this context means when the database
tells us the 'fact' twice or more, e.g. in a table of addresses, if we had
both City and State columns we could be told that SanFrancisco is in
California more than once. So what, you might say. Well, there is nothing
to stop someone entering a row where San Francisco is in Alaabama or New
Hampshire or any other state, so redundancy leaves the door open to such
inconsistencies ( a visitor from Mars wouldn't know which is the correct
state), which is really why it’s a 'bad thing'.

There are rules for ensuring normalization, which are based on the concept
of functional dependency. Functional dependency in essence means that for
any given value of column 1 the value of column 2 will always be the same;
column 2 is functionally dependent on column 1; column 1 is said to be a
'determinant' of column 2. So If EmployeeID in a table Employees is 42, and
that row is for me then FirstName is always Ken and LastName always Sheridan,
both columns being functionally dependent on EmployeeID. If we have another
table Projects with columns EmployeeID and ProjectID and also FirstName and
LastName, then I could be in one row as Ken Sheridan and another as Keith
Sheridan (I used to play rugby football for a team whose captain always
called me Keith for some reason, so these sort of mistakes are not hard to
make). This table is not normalized (or more strictly speaking its not
normalized to third normal form). The reason its not normalized is that
normalization to third normal form (3NF) requires that any non-key column
must be functionally dependent solely on the whole of the primary key. The
primary key of this Projects table is in fact a composite one made up of the
two columns EmployeeID and ProjectID as these must be unique in combination.
Now we've already seen that FirstName and LastName are functionally dependent
on EmployeeID, so in Projects FirstName and LastName are functionally
dependent on a part of the key, not on the whole of the key, which breaks the
normalization to 3NF rule.

When we apply these concepts to your tables, firstly in the case of
tEmployees then Position ID, i.e. the employee's current or default position,
is functionally dependent solely on Employee ID, which is the table's primary
key, so the table is normalized to 3NF at least (and probably beyond – it
goes up to 5NF if we disregard the more bizarre normal forms beyond that).
So the table is fine.

When it comes to tDailyInfo then this will include both Employee ID and
Position ID columns, but unlike my example of projects above Position ID is
in this case not functionally dependent solely on Employee ID, because you've
indicated that an employee's position might be different in one row in
tDailyInfo from that in another row in tDailyInfo. So in this case the
normalization rule is not broken, there is no redundancy and the table is not
at risk of inconsistent data being entered. Again the table is fine.

As far as the relationships are concerned, there is a one-to-many
relationship between tEmployees and tDailyInfo on Employee ID; there is a
one-to-many relationship between tPositions and tEmployees on Position ID;
and there is a one-to-many relationship between tDailyInfo and tDailyInfo
on PositionID.

When it comes to inserting rows into tDailyInfo for those employees selected
in the list box then you need to insert values into the Employe ID and
Position ID columns, so it should only need a small amendment to my original
reply:
The list box's RowSource property would be:
SELECT [Employee ID], [Position ID], [Employee Name] FROM tEmployees ORDER
BY [Employee Name];
Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).
The code for the button would go like this:
Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control
Set ctrl = Me.lstEmployees
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo([Employee ID], [Position ID]) " &
_
"VALUES(" & ctrl.ItemData(varItem) & ", " & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If
I've assumed that the list box is called lstEmployees and that the
EmployeeID and Position ID columns in tlEmployees and in tDailyInfo
are both of number data type.
Ken Sheridan
Stafford, England

Ken, I am so sorry, and I appreciate your patience:

When I get a chance tonight, I will most certainly check out Northwind. I
think I need to back up a few steps here. Lets just focus on the
relationships first, then we can work on that "OK" button. I did not realize
you wanted me to take the relationship of [tblPOSITIONS].[POSITION ID] away
from tblDAILYINFO. I guess I didn't understand that. I think you see where
I want to go with this though, and maybe I need to backtrack or change just a
couple of things around.

Ok, I will lay out my Positions for you. This is a database for a wearhouse
invironment.

Positions are as follows: Blue, Brown, Orange, Red, Small Sort, Unload,
Yellow. There are multiple employees on "Blue" or on "Unload" etc everyday.

Again, I will lay out the relevant tables:

tEMPLOYEES (PK)
EMPLOYEE ID
EMPLOYEE NAME
POSITION ID (Newly Added Relationship)
...so forth

tDAILYINFO
DAILYINFO ID (PK)
WorkDate
POSITION ID (FK) "number value"
...so forth

tPOSITIONS
POSITION ID
POSITION

I need the Employees to have a set position they *normally* have. Now, they
can change around periodically, but that doesn't happen all that often,
unless absent or to fill in for another absent employee. Now, the thing that
you wrote really caught my attention

"Having a

PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position>"

I need this. I need to know what their current position is at the time the
record was created. I also need that position to come up automatically when
a new record is created, and that is what I have been trying to get for a
while. I manually hit the drop-down sombo box and select each employee's
position each day.

So with this said, maybe we can take a step back, and I won't try to rush
anything.

Again, I greatly appreciate your patience Ken. It is very much appreciated.
If I could conquer these things with your help, I will be able to finish up
the database. Thank you.

-Scott Channell (e-mail address removed)

Scott:

You should just need to amend the SQL statement slightly:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID) " & _
"VALUES(" & ctrl.ItemData(varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

Note that this relies on the BoundColumn property of the list box
referencing the column in its RowSource which is the EmployeeID.
Normally this would be the first column, but hidden by virtue of the
first dimension of the ColumnWidths property being zero, i.e. the
BoundColumn property would be 1, the ColumnWidths would be something
like 0cm;8cm and the RowSource would be:

SELECT EmployeeID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Now that you are not inserting the employee's PositionID into
tblDailyInfo, but purely referencing it via the relationship with
tblEmployees this does of course mean that if an employee's position
subsequently changes, then there will be know way of knowing what
their position was at the time the row was inserted as the database
will only tell you their current position. So long as you are not
losing a significant piece of information as a result, this is fine,
but otherwise you'd need the column in both tables. In the case of
the analogous situation with unit prices in the sample Northwind
database, which I mentioned, Its obviously essential that there are
UnitPrice columns in both tables as you wouldn't want old invoices
showing the new prices whenever one changes – the auditors would soon
have something to say about it! In your case the position (no pun
intended!) is not so clear cut.

Ken Sheridan
Stafford, England

On Jan 9, 3:20 pm, channell <[email protected]>
wrote:
Ken, Let's try this...

1. Let's not focus on the WorkDate, since that is a default value and is
automatic. So I would assume we can leave it out of the VBA.

2. I just went in and created a new work date for one of my employees, and
it put their Default Work Position in the now record automatically. (I took
your advice and put the "POSITION ID" in tEMPLOYEES (tbl). That worked
fabulously.

So really, after getting some of my stuff fixed, I just need the
Multi-Select List "OK" button to create new tDAILYINFO.DAILYINFO ID numbers.
In essence, I just need a new record created for them, and my system will
take care of the rest. Hope that makes sense.

I can't thank you enough for even making some things more cleare to me! It
is amazingly wonderful now, and this is my last step before I am officially
finished! Thank you Ken!

-Scott Channell (e-mail address removed)

:
Scott:

Firstly do you also have a PositionID column in tblEmployees? Having a
PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position. In each table the column is functionally
dependent on the key of the table, so no redundancy is involved. Although
the context is different, in principle its analogous to having a UnitPrice
column in both Products and OrderDetails, as in the sample Northwind database.

With a PositionID column in tblEmployees the need for your second list box
is avoided of course. In fact I don't see how it could work as it would give
each selected employee the same position! Assuming a PositionID column in
tblEmployees therefore, you should include the PositionID column in the list
box's RowSource (it can be a hidden column) which might therefore be like
this:

SELECT EmployeeID, PositionID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).

Also assuming it’s the current date you want inserted as the work date the
code for the button would go like this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID,WorkDate,Position) " & _
"VALUES(" & ctrl.ItemData(varItem) & ",#" & _
Format(VBA.Date,"yyyy-mm-dd") & "#," & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

I've assumed that PositionID in tblEmployees and Position in tblDailyInfo
are both columns of number data type, referencing a numeric primary key of
tblPositions.

Ken Sheridan
Stafford, England

:

I have a Mulit-Select List Box. I can select many employees. What I need it
to do, if anyone can help me out, is have an "OK" button available that I can
click (once my employees are selected) that will create new WorkDay records
for them.

This would absolutely be the most useful thing to me.

 

[Ken Sheridan]

Scott:

Also (my mistake) there should be a space between the ampersands and the
underscore characters:

strSQL = _
"INSERT INTO tDailyInfo([EMPLOYEE ID], [PRELOAD POSITION]) " & _
"VALUES(" & ctrl.ItemData(varItem) & ", " & _
ctrl.Column(1, varItem) & ")"

The above should be entered as four lines with carriage returns after the
underscore characters at the end of the first, second and third lines as well
as after the fourth line. The line is then executed as a single line of
code. However, you may find that your newsreader has also inserted other
line breaks, which you'll need to remove.

Ken Sheridan
Stafford, England

Ken, I thank you for everything! I almost have it, but it is now giving me
an error:

Complie Error: Syntax Error

It pulls me to the debugger, and this is what it highlights

strSQL = "INSERT INTO tDailyInfo([EMPLOYEE ID], [PRELOAD POSITION]) " &_
"VALUES(" & ctrl.ItemData(varItem) & ", " & _ctrl.Column(1, varItem)
& ")"

It for some reason does not like the Underscores at all right after the "&"
signs. It is highlighting this statement in RED.

Thanks again Ken.

Scott:

It all comes down to what are known a 'functional dependencies'. This is
a very important concept in relational database design as it determines when
a table is 'normalized', i.e. when it is correctly structured so as to
contain no redundancies. Redundancy in this context means when the database
tells us the 'fact' twice or more, e.g. in a table of addresses, if we had
both City and State columns we could be told that SanFrancisco is in
California more than once. So what, you might say. Well, there is nothing
to stop someone entering a row where San Francisco is in Alaabama or New
Hampshire or any other state, so redundancy leaves the door open to such
inconsistencies ( a visitor from Mars wouldn't know which is the correct
state), which is really why it’s a 'bad thing'.

There are rules for ensuring normalization, which are based on the concept
of functional dependency. Functional dependency in essence means that for
any given value of column 1 the value of column 2 will always be the same;
column 2 is functionally dependent on column 1; column 1 is said to be a
'determinant' of column 2. So If EmployeeID in a table Employees is 42, and
that row is for me then FirstName is always Ken and LastName always Sheridan,
both columns being functionally dependent on EmployeeID. If we have another
table Projects with columns EmployeeID and ProjectID and also FirstName and
LastName, then I could be in one row as Ken Sheridan and another as Keith
Sheridan (I used to play rugby football for a team whose captain always
called me Keith for some reason, so these sort of mistakes are not hard to
make). This table is not normalized (or more strictly speaking its not
normalized to third normal form). The reason its not normalized is that
normalization to third normal form (3NF) requires that any non-key column
must be functionally dependent solely on the whole of the primary key. The
primary key of this Projects table is in fact a composite one made up of the
two columns EmployeeID and ProjectID as these must be unique in combination.
Now we've already seen that FirstName and LastName are functionally dependent
on EmployeeID, so in Projects FirstName and LastName are functionally
dependent on a part of the key, not on the whole of the key, which breaks the
normalization to 3NF rule.

When we apply these concepts to your tables, firstly in the case of
tEmployees then Position ID, i.e. the employee's current or default position,
is functionally dependent solely on Employee ID, which is the table's primary
key, so the table is normalized to 3NF at least (and probably beyond – it
goes up to 5NF if we disregard the more bizarre normal forms beyond that).
So the table is fine.

When it comes to tDailyInfo then this will include both Employee ID and
Position ID columns, but unlike my example of projects above Position ID is
in this case not functionally dependent solely on Employee ID, because you've
indicated that an employee's position might be different in one row in
tDailyInfo from that in another row in tDailyInfo. So in this case the
normalization rule is not broken, there is no redundancy and the table is not
at risk of inconsistent data being entered. Again the table is fine.

As far as the relationships are concerned, there is a one-to-many
relationship between tEmployees and tDailyInfo on Employee ID; there is a
one-to-many relationship between tPositions and tEmployees on Position ID;
and there is a one-to-many relationship between tDailyInfo and tDailyInfo
on PositionID.

When it comes to inserting rows into tDailyInfo for those employees selected
in the list box then you need to insert values into the Employe ID and
Position ID columns, so it should only need a small amendment to my original
reply:
The list box's RowSource property would be:
SELECT [Employee ID], [Position ID], [Employee Name] FROM tEmployees ORDER
BY [Employee Name];
Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).
The code for the button would go like this:
Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control
Set ctrl = Me.lstEmployees
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo([Employee ID], [Position ID]) " &
_
"VALUES(" & ctrl.ItemData(varItem) & ", " & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If
I've assumed that the list box is called lstEmployees and that the
EmployeeID and Position ID columns in tlEmployees and in tDailyInfo
are both of number data type.
Ken Sheridan
Stafford, England

Ken, I am so sorry, and I appreciate your patience:

When I get a chance tonight, I will most certainly check out Northwind. I
think I need to back up a few steps here. Lets just focus on the
relationships first, then we can work on that "OK" button. I did not realize
you wanted me to take the relationship of [tblPOSITIONS].[POSITION ID] away
from tblDAILYINFO. I guess I didn't understand that. I think you see where
I want to go with this though, and maybe I need to backtrack or change just a
couple of things around.

Ok, I will lay out my Positions for you. This is a database for a wearhouse
invironment.

Positions are as follows: Blue, Brown, Orange, Red, Small Sort, Unload,
Yellow. There are multiple employees on "Blue" or on "Unload" etc everyday.

Again, I will lay out the relevant tables:

tEMPLOYEES (PK)
EMPLOYEE ID
EMPLOYEE NAME
POSITION ID (Newly Added Relationship)
...so forth

tDAILYINFO
DAILYINFO ID (PK)
WorkDate
POSITION ID (FK) "number value"
...so forth

tPOSITIONS
POSITION ID
POSITION

I need the Employees to have a set position they *normally* have. Now, they
can change around periodically, but that doesn't happen all that often,
unless absent or to fill in for another absent employee. Now, the thing that
you wrote really caught my attention

"Having a
PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position>"

I need this. I need to know what their current position is at the time the
record was created. I also need that position to come up automatically when
a new record is created, and that is what I have been trying to get for a
while. I manually hit the drop-down sombo box and select each employee's
position each day.

So with this said, maybe we can take a step back, and I won't try to rush
anything.

Again, I greatly appreciate your patience Ken. It is very much appreciated.
If I could conquer these things with your help, I will be able to finish up
the database. Thank you.

-Scott Channell (e-mail address removed)




:

Scott:

You should just need to amend the SQL statement slightly:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID) " & _
"VALUES(" & ctrl.ItemData(varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem
End If

Note that this relies on the BoundColumn property of the list box
referencing the column in its RowSource which is the EmployeeID.
Normally this would be the first column, but hidden by virtue of the
first dimension of the ColumnWidths property being zero, i.e. the
BoundColumn property would be 1, the ColumnWidths would be something
like 0cm;8cm and the RowSource would be:

SELECT EmployeeID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Now that you are not inserting the employee's PositionID into
tblDailyInfo, but purely referencing it via the relationship with
tblEmployees this does of course mean that if an employee's position
subsequently changes, then there will be know way of knowing what
their position was at the time the row was inserted as the database
will only tell you their current position. So long as you are not
losing a significant piece of information as a result, this is fine,
but otherwise you'd need the column in both tables. In the case of
the analogous situation with unit prices in the sample Northwind
database, which I mentioned, Its obviously essential that there are
UnitPrice columns in both tables as you wouldn't want old invoices
showing the new prices whenever one changes – the auditors would soon
have something to say about it! In your case the position (no pun
intended!) is not so clear cut.

Ken Sheridan
Stafford, England

On Jan 9, 3:20 pm, channell <[email protected]>
wrote:
Ken, Let's try this...

1. Let's not focus on the WorkDate, since that is a default value and is
automatic. So I would assume we can leave it out of the VBA.

2. I just went in and created a new work date for one of my employees, and
it put their Default Work Position in the now record automatically. (I took
your advice and put the "POSITION ID" in tEMPLOYEES (tbl). That worked
fabulously.

So really, after getting some of my stuff fixed, I just need the
Multi-Select List "OK" button to create new tDAILYINFO.DAILYINFO ID numbers.
In essence, I just need a new record created for them, and my system will
take care of the rest. Hope that makes sense.

I can't thank you enough for even making some things more cleare to me! It
is amazingly wonderful now, and this is my last step before I am officially
finished! Thank you Ken!

-Scott Channell (e-mail address removed)

:
Scott:

Firstly do you also have a PositionID column in tblEmployees? Having a
PositionID column in tblDailyInfo is fine as you'd presumably want the rows
in this table to hold the employee's position at the time the row was
inserted rather than to be updated with their current position if it changes,
but in situations like this its normal to also have a column in tblEmployees
to hold their current position. In each table the column is functionally
dependent on the key of the table, so no redundancy is involved. Although
the context is different, in principle its analogous to having a UnitPrice
column in both Products and OrderDetails, as in the sample Northwind database.

With a PositionID column in tblEmployees the need for your second list box
is avoided of course. In fact I don't see how it could work as it would give
each selected employee the same position! Assuming a PositionID column in
tblEmployees therefore, you should include the PositionID column in the list
box's RowSource (it can be a hidden column) which might therefore be like
this:

SELECT EmployeeID, PositionID, [Employee Name] FROM tblEmployees ORDER BY
[Employee Name];

Its column Count property would be 3 and its ColumnWidths property something
like 0cm;0cm;8cm (or equivalent in inches).

Also assuming it’s the current date you want inserted as the work date the
code for the button would go like this:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim varItem As Variant
Dim ctrl As Control

Set ctrl = Me.lstEmployees

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tblDailyInfo(EmployeeID,WorkDate,Position) " & _
"VALUES(" & ctrl.ItemData(varItem) & ",#" & _
Format(VBA.Date,"yyyy-mm-dd") & "#," & _
ctrl.Column(1, varItem) & ")"
cmd.CommandText = strSQL
cmd.Execute
Next varItem