A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Challenge: One table associated with several fields - but avoidin



 
 
Thread Tools Display Modes
  #1  
Old December 5th, 2009, 09:06 PM posted to microsoft.public.access.tablesdbdesign
Thanks, Buddy
external usenet poster
 
Posts: 1
Default Challenge: One table associated with several fields - but avoidin

I'm using the newest version of ACCESS. I have a (table) list of employees.
I have a form with 4 positions (clerk, door, clean-up, charge) that I want to
schedule using the same list of employees. How do I use this same list so
that if I choose an employee for clerk, that when I fill in the employee for
door - that employee can not be chosen again (i.e. I want to avoid the error
of scheduling the same employee for two or more positions).

Thanks! hope that made sense.
  #2  
Old December 5th, 2009, 10:04 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Challenge: One table associated with several fields - but avoidin

You need to use a form for choosing employees and their positions. On your
form use a combobox to select an employee and setup the combobox so that
when an employee us selected and assigned a position, that employee is
removed from the rowsource of the combobox.

Steve



"Thanks, Buddy" Thanks,
wrote in message
...
I'm using the newest version of ACCESS. I have a (table) list of
employees.
I have a form with 4 positions (clerk, door, clean-up, charge) that I want
to
schedule using the same list of employees. How do I use this same list so
that if I choose an employee for clerk, that when I fill in the employee
for
door - that employee can not be chosen again (i.e. I want to avoid the
error
of scheduling the same employee for two or more positions).

Thanks! hope that made sense.



  #3  
Old December 6th, 2009, 12:00 AM posted to microsoft.public.access.tablesdbdesign
Thanks, Buddy[_2_]
external usenet poster
 
Posts: 10
Default Challenge: One table associated with several fields - but avo

Thanks, Steve. Let me clarify to make sure. I have one field for clerk,
door, clean-up, and charge ( a total of 4 fields) that I want to share the
same list (my list of employees). So if I choose an employee for field one
(clerk), when I go to field two - door, that employee's name won't appear on
the list any longer? Wow? I looked up combobox in my access book and it
doesn't tell me how to remove that employee from the row source...this is
great news if I can really do this, but can you explain a little bit more?
Is there an option on combobox that I choose to make this happen?

Thanks, Buddy

"Steve" wrote:

You need to use a form for choosing employees and their positions. On your
form use a combobox to select an employee and setup the combobox so that
when an employee us selected and assigned a position, that employee is
removed from the rowsource of the combobox.

Steve



"Thanks, Buddy" Thanks,
wrote in message
...
I'm using the newest version of ACCESS. I have a (table) list of
employees.
I have a form with 4 positions (clerk, door, clean-up, charge) that I want
to
schedule using the same list of employees. How do I use this same list so
that if I choose an employee for clerk, that when I fill in the employee
for
door - that employee can not be chosen again (i.e. I want to avoid the
error
of scheduling the same employee for two or more positions).

Thanks! hope that made sense.



.

  #4  
Old December 6th, 2009, 04:33 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Challenge: One table associated with several fields - but avo

It sounds like your tables are not correct. They should be something like:
TblEmployee
EmployeeID
FirstName
LastName
etc

TblPosition
PositionID
Position

TblSchedule
ScheduleID
ScheduleDate

TblScheduleEmployeePosition
ScheduleEmployeePositionID
ScheduleID
EmployeeID
PositionID

There is no built-in way in a combobox to remove that employee from the row
source. You need to make the row source a query and then design the query to
remove all employees previously selected from the row source.

Steve




"Thanks, Buddy" wrote in message
...
Thanks, Steve. Let me clarify to make sure. I have one field for clerk,
door, clean-up, and charge ( a total of 4 fields) that I want to share the
same list (my list of employees). So if I choose an employee for field
one
(clerk), when I go to field two - door, that employee's name won't appear
on
the list any longer? Wow? I looked up combobox in my access book and it
doesn't tell me how to remove that employee from the row source...this is
great news if I can really do this, but can you explain a little bit more?
Is there an option on combobox that I choose to make this happen?

Thanks, Buddy

"Steve" wrote:

You need to use a form for choosing employees and their positions. On
your
form use a combobox to select an employee and setup the combobox so that
when an employee us selected and assigned a position, that employee is
removed from the rowsource of the combobox.

Steve



"Thanks, Buddy" Thanks,
wrote in
message
...
I'm using the newest version of ACCESS. I have a (table) list of
employees.
I have a form with 4 positions (clerk, door, clean-up, charge) that I
want
to
schedule using the same list of employees. How do I use this same list
so
that if I choose an employee for clerk, that when I fill in the
employee
for
door - that employee can not be chosen again (i.e. I want to avoid the
error
of scheduling the same employee for two or more positions).

Thanks! hope that made sense.



.



  #5  
Old December 6th, 2009, 06:22 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Challenge: One table associated with several fields - but avo

Buudy,

Please note... *Position* is a Reserved Word and will cause you problems
because it is a problem for Access. For a complete list of Reserved Words
see... http://allenbrowne.com/Ap****ueBadWord.html To avoide such problem
you can prefix the field names with the table names, ie...

tblPosition
pPositionID
pPosition

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Steve" wrote in message
...
It sounds like your tables are not correct. They should be something like:
TblEmployee
EmployeeID
FirstName
LastName
etc

TblPosition
PositionID
Position

TblSchedule
ScheduleID
ScheduleDate

TblScheduleEmployeePosition
ScheduleEmployeePositionID
ScheduleID
EmployeeID
PositionID

There is no built-in way in a combobox to remove that employee from the
row source. You need to make the row source a query and then design the
query to remove all employees previously selected from the row source.

Steve




"Thanks, Buddy" wrote in message
...
Thanks, Steve. Let me clarify to make sure. I have one field for clerk,
door, clean-up, and charge ( a total of 4 fields) that I want to share
the
same list (my list of employees). So if I choose an employee for field
one
(clerk), when I go to field two - door, that employee's name won't appear
on
the list any longer? Wow? I looked up combobox in my access book and it
doesn't tell me how to remove that employee from the row source...this is
great news if I can really do this, but can you explain a little bit
more?
Is there an option on combobox that I choose to make this happen?

Thanks, Buddy

"Steve" wrote:

You need to use a form for choosing employees and their positions. On
your
form use a combobox to select an employee and setup the combobox so that
when an employee us selected and assigned a position, that employee is
removed from the rowsource of the combobox.

Steve



"Thanks, Buddy" Thanks,
wrote in
message
...
I'm using the newest version of ACCESS. I have a (table) list of
employees.
I have a form with 4 positions (clerk, door, clean-up, charge) that I
want
to
schedule using the same list of employees. How do I use this same list
so
that if I choose an employee for clerk, that when I fill in the
employee
for
door - that employee can not be chosen again (i.e. I want to avoid the
error
of scheduling the same employee for two or more positions).

Thanks! hope that made sense.


.





  #6  
Old December 7th, 2009, 05:17 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Challenge: One table associated with several fields - but avo

You can prevent the same employee from being chosen twice by applying a
unique index to each of the fields Clerk, Door, Clean-up, and Charge. The
employee's name would still appear in the combo box, but you wouldn't be
allowed to select the name. You don't say (that I can see) if you are
storing an EmployeeID number rather than the name, but you should be storing
an unchanging number in Clerk, Door, etc., using something like the table
structure for tblEmployee that Steve suggested. The following assumes you
are storing an employeeID number in the four fields.

One possibility for a new record is to enable only one combo box (e.g. Clerk)
so that it must be selected first. To do this, in the form's Current event:

Me.cboDoor.Enabled = Not Me.NewRecord
Me.cboCleanUp.Enabled = Not Me.NewRecord
Me.cboCharge.Enabled = Not Me.NewRecord

If it is a new record, NewRecord is True, so Not Me.NewRecord is the same as
False.

The combo box cboClerk is always enabled. Its Row Source is whatever you are
using now. In its After Update event:

Dim strSQL as String
Dim lngClerk as Long

lngClerk = Me.cboClerk
strSQL = "SELECT [EmployeeID], [FirstName],[LastName] " & _
"FROM tblEmployee " & _
"WHERE [EmployeeID] " & lngClerk & _
" ORDER BY [LastName], [FirstName]"
Me.cboDoor.RowSource = strSQL
Me.cboDoor.Enabled = True

In the After Update for cboDoor you need to add another variable:

Dim strSQL as String
Dim lngClerk as Long, lngDoor as Long

lngClerk = Me.cboClerk
lngDoor = Me.cboDoor

strSQL = "SELECT [EmployeeID], [FirstName],[LastName] " & _
"FROM tblEmployee " & _
"WHERE [EmployeeID] " & lngClerk & _
" AND [EmployeeID] " & lngDoor & _
" ORDER BY [LastName], [FirstName]"
Me.cboCleanUp.RowSource = strSQL
Me.cboCleanUp.Enabled = True

This would not change the Row Source for an existing record, so if you want
to go back to a record and change a name it would be different coding. It
seems to me it would be more difficult then, in that if you want to swap
names (Clerk and Door, for instance) you would first need to clear cboClerk,
then change the name in cboDoor, then update cboClerk.

Also, if you change the RowSource you need to change it to something else
when you go to another record, or the list will remain as you set it. It
depends on what you want to see in the list when you go to an existing record.
If you want the list exclude the selected people in that record you would
need to set the Row Source in the form's Current event. However, then you
would not be able to swap two of the names as described above unless you make
allowance for that in the coding. It is manageable enough for a new record,
but for an existing record you need to make a number of choices about how to
proceed.

There may be a cleaner approach than this, but this is what I came up with.

Thanks, Buddy wrote:
Thanks, Steve. Let me clarify to make sure. I have one field for clerk,
door, clean-up, and charge ( a total of 4 fields) that I want to share the
same list (my list of employees). So if I choose an employee for field one
(clerk), when I go to field two - door, that employee's name won't appear on
the list any longer? Wow? I looked up combobox in my access book and it
doesn't tell me how to remove that employee from the row source...this is
great news if I can really do this, but can you explain a little bit more?
Is there an option on combobox that I choose to make this happen?

Thanks, Buddy

You need to use a form for choosing employees and their positions. On your
form use a combobox to select an employee and setup the combobox so that

[quoted text clipped - 18 lines]

.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200912/1

  #7  
Old December 7th, 2009, 05:48 PM posted to microsoft.public.access.tablesdbdesign
Thanks, Buddy[_2_]
external usenet poster
 
Posts: 10
Default Challenge: One table associated with several fields - but avo

Thanks Steve. I'm beginning to think this project is above my ability, but
I'm willing to challenge myself. I just don't get how to 'make the row
source a query and then design the query to remove all employees previously
selected from the row source'. I'll play around with it. I'm also looking
for a template out there for someone else who may have run into the same
problem.

Thanks,
Buddy
"Steve" wrote:

It sounds like your tables are not correct. They should be something like:
TblEmployee
EmployeeID
FirstName
LastName
etc

TblPosition
PositionID
Position

TblSchedule
ScheduleID
ScheduleDate

TblScheduleEmployeePosition
ScheduleEmployeePositionID
ScheduleID
EmployeeID
PositionID

There is no built-in way in a combobox to remove that employee from the row
source. You need to make the row source a query and then design the query to
remove all employees previously selected from the row source.

Steve




"Thanks, Buddy" wrote in message
...
Thanks, Steve. Let me clarify to make sure. I have one field for clerk,
door, clean-up, and charge ( a total of 4 fields) that I want to share the
same list (my list of employees). So if I choose an employee for field
one
(clerk), when I go to field two - door, that employee's name won't appear
on
the list any longer? Wow? I looked up combobox in my access book and it
doesn't tell me how to remove that employee from the row source...this is
great news if I can really do this, but can you explain a little bit more?
Is there an option on combobox that I choose to make this happen?

Thanks, Buddy

"Steve" wrote:

You need to use a form for choosing employees and their positions. On
your
form use a combobox to select an employee and setup the combobox so that
when an employee us selected and assigned a position, that employee is
removed from the rowsource of the combobox.

Steve



"Thanks, Buddy" Thanks,
wrote in
message
...
I'm using the newest version of ACCESS. I have a (table) list of
employees.
I have a form with 4 positions (clerk, door, clean-up, charge) that I
want
to
schedule using the same list of employees. How do I use this same list
so
that if I choose an employee for clerk, that when I fill in the
employee
for
door - that employee can not be chosen again (i.e. I want to avoid the
error
of scheduling the same employee for two or more positions).

Thanks! hope that made sense.


.



.

  #8  
Old December 7th, 2009, 07:27 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Challenge: One table associated with several fields - but avo

You would use a combobox to enter EmployeeID in the form based on
TblScheduleEmployeePosition. The rowsource of the combobox would be a query.
To create the query, go to the list of queries and select new. One of the
options would be the Unmatched Query Wizard. Use the wizard and select
TblEmployee then in the next step select TblScheduleEmployeePosition. You
want the query to return all EmployeeIDs in TblEmployee not found in
TblScheduleEmployeePosition. In your query, make the first column EmployeeID
and the second column the following expression:
Employee = [LastName] & ", " & [FirstName]
Set the following properties in the combobox:
RowSource NameOfTheQuery
Bound Column 1
Column Count 2
Column Width 0,2

If this project is above your ability, I can help you. I provide fee-based
help with Access, Excel and Word applications. My fee to help you would be
very reasonable. If you want my help, contact me.

Steve


"Thanks, Buddy" wrote in message
...
Thanks Steve. I'm beginning to think this project is above my ability, but
I'm willing to challenge myself. I just don't get how to 'make the row
source a query and then design the query to remove all employees
previously
selected from the row source'. I'll play around with it. I'm also
looking
for a template out there for someone else who may have run into the same
problem.

Thanks,
Buddy
"Steve" wrote:

It sounds like your tables are not correct. They should be something
like:
TblEmployee
EmployeeID
FirstName
LastName
etc

TblPosition
PositionID
Position

TblSchedule
ScheduleID
ScheduleDate

TblScheduleEmployeePosition
ScheduleEmployeePositionID
ScheduleID
EmployeeID
PositionID

There is no built-in way in a combobox to remove that employee from the
row
source. You need to make the row source a query and then design the query
to
remove all employees previously selected from the row source.

Steve




"Thanks, Buddy" wrote in message
...
Thanks, Steve. Let me clarify to make sure. I have one field for
clerk,
door, clean-up, and charge ( a total of 4 fields) that I want to share
the
same list (my list of employees). So if I choose an employee for field
one
(clerk), when I go to field two - door, that employee's name won't
appear
on
the list any longer? Wow? I looked up combobox in my access book and
it
doesn't tell me how to remove that employee from the row source...this
is
great news if I can really do this, but can you explain a little bit
more?
Is there an option on combobox that I choose to make this happen?

Thanks, Buddy

"Steve" wrote:

You need to use a form for choosing employees and their positions. On
your
form use a combobox to select an employee and setup the combobox so
that
when an employee us selected and assigned a position, that employee is
removed from the rowsource of the combobox.

Steve



"Thanks, Buddy" Thanks,
wrote in
message
...
I'm using the newest version of ACCESS. I have a (table) list of
employees.
I have a form with 4 positions (clerk, door, clean-up, charge) that
I
want
to
schedule using the same list of employees. How do I use this same
list
so
that if I choose an employee for clerk, that when I fill in the
employee
for
door - that employee can not be chosen again (i.e. I want to avoid
the
error
of scheduling the same employee for two or more positions).

Thanks! hope that made sense.


.



.



  #9  
Old December 7th, 2009, 07:41 PM posted to microsoft.public.access.tablesdbdesign
Gina Whipp
external usenet poster
 
Posts: 3,500
Default Challenge: One table associated with several fields - but avo

Buddy,

See Bruce's reply he gives you a way to do what you want... for FREE.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Thanks, Buddy" wrote in message
...
Thanks Steve. I'm beginning to think this project is above my ability, but
I'm willing to challenge myself. I just don't get how to 'make the row
source a query and then design the query to remove all employees
previously
selected from the row source'. I'll play around with it. I'm also
looking
for a template out there for someone else who may have run into the same
problem.

Thanks,
Buddy
"Steve" wrote:

It sounds like your tables are not correct. They should be something
like:
TblEmployee
EmployeeID
FirstName
LastName
etc

TblPosition
PositionID
Position

TblSchedule
ScheduleID
ScheduleDate

TblScheduleEmployeePosition
ScheduleEmployeePositionID
ScheduleID
EmployeeID
PositionID

There is no built-in way in a combobox to remove that employee from the
row
source. You need to make the row source a query and then design the query
to
remove all employees previously selected from the row source.

Steve




"Thanks, Buddy" wrote in message
...
Thanks, Steve. Let me clarify to make sure. I have one field for
clerk,
door, clean-up, and charge ( a total of 4 fields) that I want to share
the
same list (my list of employees). So if I choose an employee for field
one
(clerk), when I go to field two - door, that employee's name won't
appear
on
the list any longer? Wow? I looked up combobox in my access book and
it
doesn't tell me how to remove that employee from the row source...this
is
great news if I can really do this, but can you explain a little bit
more?
Is there an option on combobox that I choose to make this happen?

Thanks, Buddy

"Steve" wrote:

You need to use a form for choosing employees and their positions. On
your
form use a combobox to select an employee and setup the combobox so
that
when an employee us selected and assigned a position, that employee is
removed from the rowsource of the combobox.

Steve



"Thanks, Buddy" Thanks,
wrote in
message
...
I'm using the newest version of ACCESS. I have a (table) list of
employees.
I have a form with 4 positions (clerk, door, clean-up, charge) that
I
want
to
schedule using the same list of employees. How do I use this same
list
so
that if I choose an employee for clerk, that when I fill in the
employee
for
door - that employee can not be chosen again (i.e. I want to avoid
the
error
of scheduling the same employee for two or more positions).

Thanks! hope that made sense.


.



.



  #10  
Old December 7th, 2009, 08:16 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Challenge: One table associated with several fields - but avo

The OP is not clear about what type of record he is creating in which the
four positions are filled. I see now that I made an assumption that it was
some sort of scheduling thing, and that a new record would be created
periodically. That assumption may not be warranted, but neither is your
assumption about the structure, as the database purpose is not clearly
defined.

What is most definitely not warranted is your continually mistaken belief
that these newsgroups are for anything other than free peer-to-peer support.
When you told the employee the design was wrong, then posted a sketchy table
structure, I wondered if you were heading in the direction of a solicitiation.


I will repeat: these newsgroups are not your personal advertising forum.
Commercial solicitations are inappropriate. If you haven't learned that by
now there is no reason to think you can handle Access.

Steve wrote:
You would use a combobox to enter EmployeeID in the form based on
TblScheduleEmployeePosition. The rowsource of the combobox would be a query.
To create the query, go to the list of queries and select new. One of the
options would be the Unmatched Query Wizard. Use the wizard and select
TblEmployee then in the next step select TblScheduleEmployeePosition. You
want the query to return all EmployeeIDs in TblEmployee not found in
TblScheduleEmployeePosition. In your query, make the first column EmployeeID
and the second column the following expression:
Employee = [LastName] & ", " & [FirstName]
Set the following properties in the combobox:
RowSource NameOfTheQuery
Bound Column 1
Column Count 2
Column Width 0,2

If this project is above your ability, I can help you. I provide fee-based
help with Access, Excel and Word applications. My fee to help you would be
very reasonable. If you want my help, contact me.

Steve


Thanks Steve. I'm beginning to think this project is above my ability, but
I'm willing to challenge myself. I just don't get how to 'make the row

[quoted text clipped - 89 lines]

.


--
Message posted via http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:56 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.