Still Struggling...

[BruceM]

I tried sending Part 2, but still NG. Here is half of Part 2.

Now, as I said previously, the "many" side table holds the foreign key.
So, in this case, the junction table holds two FK's, which you have
done correctly in your junction table.

Finally! I did something right!

Where you went wrong is by putting EmpTitleID in tblEmployees as a FK.

....and then hope was dashed. :-(

EmpTitleID does not need to exist in *either* table.

Whoa;*what*?! <trying hard not to cry> I needed this explanation because
some things I was doing w/o really understanding why; simple because someone
said do this and then I would read a post that said no, do this, and the
book
would say something different. In tblKeyEmployees, I have a combo PK. That
was one of the original tables I had and yet I don't do the same thing here.

there is nothing to prevent the same title being assigned to the same
employee many times over.

There it is. What I was inadequately trying to explain earlier. I can see
this is where I'm going to get into trouble because I don't really
understand
how you would look at that and *know* it means the same title assigned to
the
same employee over and over. I'm going to have to get a handle on this; more
homework.

It is not good practice to introduce unnecessary elements into your db.

Nor do I want to; I have enough trouble already.

Still awake?..........Hello?........ Pick your head up off that desk.
Sleep
on
your own time, dammit! ;-)

....zzzz...zzzzz...huh?...what?!...I'm awake, I'm awake! (Besides I slid off

...you have two separate M:M relationships that you need to keep track of.
Don't worry, it's not as complicated as it may sound at first.

Well, maybe not for you...

tblEmployees M:M tblDepartments

[quoted text clipped - 26 lines]

EmpID (FK to tblEmployees and first part of PK - LI number)
SubjID (FK to tblSubjects and second part of PK - LI number)

I apologize to you now, Beetle, for the confusion because in my earlier post
I stated, " The other problem I had was somethng you mentioned earlier in
your sentence "..to help keep track of things." I was having trouble
tracking
what was happening; a situation that was not helped by some of the table
names I used (too many tables with the word Employee in it) and the fact
that
I kept adding notes and comments to my diagram so that I couldn't see
anything. I had spun off School Data from the employee table like we
discussed, but I had also put Emergency Info into a seperate table too.

One of those tables with the word Employee in it was tblEmpSubjects. I don't
know why I dropped it.

So in essence, your Subjects table has evolved. It is not just a way
to track subjects that are taught. Rather, it is a way to describe a
persons role within a Department.

I'm almost afraid to ask because I think I should have this down since
you've already explained but, "describe a persons role within the dept.?"
Wouldn't that info be in tblEmpDept? Let me think about this some more. If I
read it over and over; it will come.

 

[BruceM]

A bit more?

You then probably thought if you put it in tblEmployees, you would end up
with an >empty field in the Substitute teacher records, so you put it in
tblSiteEmp.

Hmm...I think you're giving me too much credit although we're all aware of
my dislike for empty fields. :-(

2) tblSiteEmp exists for one reason only. To store address information for
your >full time staff.

What?! When did this happen? I thought it was to store info that didn't
pertain to subs (addresses and emergency info). BTW Bruce, that's how we
ended up with tblSiteEmp because Subs aren't obligated to nor will they
disclose their address or any emergency info. I will break out in hives if I
have all of those empty fields (approx. 14-16) for *every* sub record. :-(
Just so you both understand, we aren't talking about 1 or 2 subs per day
here. We're a large school. We've had as many as 20 sub requests in a single
day; depending on what's going on (training, workshops, conferences,
illness,
etc.). There's always something going on.

For Preferred Subject, just add a field to tblEmployees and have the users
manually enter whatever the preferred subject is. This will add relatively
little to the data entry process, and it will prevent you from having to
add yet another relationship to your db. If you end up with a few records
where the PreferredSubject field is empty, I think that's acceptable.

At this point, so do I; done!

 

[BruceM]

A little more?

As far as the Subs credentials, that would be handled in the same manner
as the full time staff. If they have some type of credential, then there
will
be a record in tblEmpClassifications to reflect that fact. If not, there
won't
be a record. Simple.

???...not following you. I don't track site staff credentials. The district
handles that. As long as subs have a teaching credential, they can sub in
whatever subject they want. My only reason for tracking who has a particular
credential (Special Education, English, Math, etc.) is to handle emergencies
that may come up. Sp. Ed. and Math at least, are special subjects in my
book.
I know the issues Sp. Ed. faces having worked in that dept. many years. You
can't have just any one there. They won't make it. Math is another subject
that needs a specialist so to speak. Every year something happens where we
need a long-term sub. I just wanted to be ready. But I don't understand what
you are proposing; just add a field to tblEmployees?

but I think it's normalized enough that it would be completely functional.

Agreed!

 

[Beetle]

I posted this reply through Access Monster but am attempting to post
it here just to see if it works.

BTW - "Mark", who's real name is Steve, is just a troll that shows up every
now and
then to slum for work, which is against the rules of the group, but he
doesn't care.
Just ignore him.

On to the reply;

Really?! Both tables?

Yes really. Since each table is only allowed one record per employee, then
EmpID will be a unique value for each record in both tables. Therefore it
qualifies as the PK for both tables.

You're killing me here! What?! ...understanding flowing out. Is it
because in the first example it was 1:1 and in this one it's 1:M?

Yes. Different relationship, different rules. In this case EmpID (the PK
of the parent table) can be repeated many times in the child table, so
it does not qualify as the PK of the child ("many") table. The child table
must have it's own unique PK, like PhoneID.

Whoa, *what*?! <trying hard not to cry> This is hard. I needed this
explanation because some things I was doing without really understanding why;

[quoted text clipped - 3 lines]

have a combo PK. That was one of the original tables I had and yet I don't do
the same thing here.


There it is. What I was inadequately trying to explain earlier. I can see
this is where I'm going to get into trouble because I don't really understand
how you would look at that and *know* it means the same title assigned over
and over to the same employee. I'm going to have to get a handle on this;
more homework.

Perhaps it would help your understanding if we take a look at how the data
would actually appear in the table. First, let's look at the table structure
as you had it;

tblTitlesEmps M:M
*************
TitlesEmpsID (PK) Autonumber
EmpID (FK to tblEmps)
TitlesID (FK to tblTitles)

With the above structure, the only value that has uniqueness enforced is
TitlesEmpsID (the PK), unless you put a unique index on EmpID and
TitlesID (which I'm reasonably sure you didn't do in this case). Now, let's
suppose you have an employee named John Smith whose EmpID is 1.
Let's also suppose that your Titles table has a description like
"Economics Professor" and that the TitleID for this description is 5.
There is nothing preventing the data in your junction table from looking
like;

TitlesEmpsID EmpID TitlesID
1 1 5
2 1 5
3 1 5

As you can see, the same employee can have the same title assigned
multiple times, because the only "rule" is that TitlesEmpsID must be
unique for each record.

Now, if we get rid of TitlesEmpsID, and use EmpID and TitlesID as
a combined two field PK then;

EmpID TitlesID
1 5
1 5 <<<<<this would not be allowed
1 3
1 4

With this structure, the same employee can have more than one title
assigned, but never the same one twice, because the combination of
the two values must be unique.

Updated comment: I'm almost afraid to ask because I think I should have this
down since you've already explained but, "describe a persons role within a
Department"? Isn't that the same as EmpTitle?

You might be right. I would have to know a little bit more to say for sure.
From what I do know, it seems like you need;

1) A way to describe what Dept.(s) an employee works in. In this case
tblDepts holds descriptive information about the different departments
an tblEmpDepts (the junction table) controls the relationship.

2) A way to describe what an employee does within each Dept. So the
descriptive information like "Dept. Chair", "Physics Prof.", or
"Custodian"
would be held in? tblTitles? tblSubjects?. Your choice I guess. Then the
relationship would be managed by tblEmpTitles (or whatever you call it).

So maybe it will turn out that tblTitles an tblSubjects are essentially the
same thing. Maybe you can actually get rid of a couple of tables!

???...not following you. I don't track site staff credentials. The district
handles that. As long as subs have a teaching credential, they can sub in

[quoted text clipped - 7 lines]

long-term sub. I just want to be ready. But I don't understand what you are
proposing; just add a field to tblEmployees?

No, I am not proposing that you add any fields at all. Perhaps I can explain
it a little better. Your tblEmployees holds the *names* of all potential
employees, whether they are full time or subs. tblClassifications holds
the descriptions of all possible credential types. tblEmpClassifications
manages the relationship between the two. Therefore, *if* a sub
had some type of credential, and *if* you wanted to store that data in
your application, then a record would be created in tblEmpClassifications
just the same as it would for a full time employee. If you don't want
to track that info, then no record is ever created. No empty fields or
anything to worry about.

 

[Beetle284 via AccessMonster.com]

Finally, I decided to create some relationships. I could

never get the ones I needed before. Guess what? It worked....it worked...it
worked! I cried.

Hey, there's no crying in baseball or relational database design ;- )

Again, we aren't on the same page. It's more closely related to Subject
(which is in tblSiteEmps). It's like us asking them, "So, what's your
specialty?". If you have a Sp. Ed. credential, you have gone through teacher
training *plus* additional years training for Sp. Ed.

OK, I guess I'm missing something here. Hopefully you can find a way to make
it
work the way you need. If not, you can post back if you want and we can give
it
another shot.

I tried making a form. I
was able to do it. I tried making a subform for tblSiteEmps but had trouble
linking it to tblEmps. I worked from *before* sunrise to well after sunset. I
had all of my posts, book and other materials. I kept moving between the
newsgroup and my db to find answers to my problems. After working for what
seemed like hours I was finally able to link the two. When I clicked on the
arrow they moved in tandem.

Actually, since this is a 1:1 relationship, you can use a subform if you want,
but it's
not really necessary. You could do it all on one form if you wanted (there's
that damn
monkey wrench again <g>).

Now that you're ready to try creating some forms, the fun is really gonna
start :- )

 

[Aria via AccessMonster.com]

Hey, there's no crying in baseball or relational database design ;- )

Actually, since this is a 1:1 relationship, you can use a subform if you want,
but it's
not really necessary. You could do it all on one form if you wanted (there's
that damn
monkey wrench again <g>).

But the reason we did that is to get rid of tblSubs...remember? You told me
to use tblEmployees for the fields that are common to both subs and site
employees. Then you said to create a sub form for tblSiteEmps.

Now that you're ready to try creating some forms, the fun is really gonna
start :- )

Yeah...about that. Can I ask you a question? I think I made a mistake by
inputting fields for ClassDescription and TitleDescription. Naturally, there
wasn't a drop-down list. <imagine that> I remembered that we discussed a
combobox. I was having problems creating it because I can't find the answer
I'm looking for. Everyone seems to start at the same point, "Row Source". My
info states," You use unbound controls to display information." "...use bound
controls to display, enter, and update field values in your database."
Everything I have read so far says use unbound. When I did, it didn't save
the data. When I close and re-enter there's nothing there. I'm thinking
maybe it should be bound. Been searching for days... Why is it so hard to
find the info you're looking for?
Ok, I asked for an answer to one question but you ought to know better than
that by now. "...the fun is really gonna start'? What do you know that I
don't? BTW, how do you do make a bound control? I think I tried it both ways
but it wasn't quite working or I was too tired to notice what I was doing
wrong.

Finally, I decided to create some relationships. I could
never get the ones I needed before. Guess what? It worked....it worked...it
worked! I cried.

Hey, there's no crying in baseball or relational database design ;- )

Again, we aren't on the same page. It's more closely related to Subject
(which is in tblSiteEmps). It's like us asking them, "So, what's your
specialty?". If you have a Sp. Ed. credential, you have gone through teacher
training *plus* additional years training for Sp. Ed.

OK, I guess I'm missing something here. Hopefully you can find a way to make
it
work the way you need. If not, you can post back if you want and we can give
it
another shot.

I tried making a form. I
was able to do it. I tried making a subform for tblSiteEmps but had trouble

[quoted text clipped - 3 lines]

seemed like hours I was finally able to link the two. When I clicked on the
arrow they moved in tandem.

Actually, since this is a 1:1 relationship, you can use a subform if you want,
but it's
not really necessary. You could do it all on one form if you wanted (there's
that damn
monkey wrench again <g>).

Now that you're ready to try creating some forms, the fun is really gonna
start :- )

 

[Beetle284 via AccessMonster.com]

But the reason we did that is to get rid of tblSubs...remember? You told me

to use tblEmployees for the fields that are common to both subs and site
employees. Then you said to create a sub form for tblSiteEmps.

To this point we have been talking almost exclusively about tables. I don't
recall saying much about forms, but I could be wrong. Anyway, having the
tables set up like you do is correct, but that doesn't necessarily mean
that you have to use two separate *forms*. If you are interested in having
the names and addresses on one form, I can try to explain that later, but
if you are happy with how you have it now, then I'll just leave it alone.
Maybe
you don't need to think about anything more right now.

Yeah...about that. Can I ask you a question? I think I made a mistake by
inputting fields for ClassDescription and TitleDescription. Naturally, there
wasn't a drop-down list.

I'm not sure what you mean here. ClassDescription is certainly appropriate
in tblClassifications, and TitleDescription in tblTitles, so I'm not sure why
you
think this was a mistake. If you tell me what form you are working on and
which
tables are involved I can give you more specific advice, but in the meantime
here is some general information.

First, when referring to objects on forms like text boxes, combo boxes, etc.
they are called controls. Tables and queries have fields, forms and reports
have controls. Controls have a "Control Source" property. If the control
source
is a field (in a table or query), then the control is bound. If the control
source is
something other than a field (like a calculation), or if there is no control
source
(a control does not have to have a "control source") then it is unbound. So
to make
a bound control, you use a field as the control source. Whether a control
should
be bound or unbound depends on what you are doing. Right now,
it sounds like you are working with the subforms for your employees where
you need to assign the proper Classification and Title, so the control should
be bound, because that data needs to be stored in the table. Unbound controls
are typically used to perform searches, do calculations, etc.

Now, in the case of a combo box (or a list box), there will also be a "Row
Source"
property, which basically determines what data, or values, are *displayed* in
the
combo box. I use the term display loosely, because you can, an in most cases
would, hide certain values in a combo box so the users don't see them. This
can
be data from a table or query, or just a list of values that you define
yourself.

"...the fun is really gonna start'? What do you know that I
don't?

I would say you're finding out right about now.

Control Source. Row Source. Bound. Unbound. Master/Child links.
Bound Column. Column Count.

Fun stuff, don't you think? <big grin>

Hey, there's no crying in baseball or relational database design ;- )

Actually, since this is a 1:1 relationship, you can use a subform if you want,
but it's
not really necessary. You could do it all on one form if you wanted (there's
that damn
monkey wrench again <g>).

But the reason we did that is to get rid of tblSubs...remember? You told me
to use tblEmployees for the fields that are common to both subs and site
employees. Then you said to create a sub form for tblSiteEmps.

Now that you're ready to try creating some forms, the fun is really gonna
start :- )

Yeah...about that. Can I ask you a question? I think I made a mistake by
inputting fields for ClassDescription and TitleDescription. Naturally, there
wasn't a drop-down list. <imagine that> I remembered that we discussed a
combobox. I was having problems creating it because I can't find the answer
I'm looking for. Everyone seems to start at the same point, "Row Source". My
info states," You use unbound controls to display information." "...use bound
controls to display, enter, and update field values in your database."
Everything I have read so far says use unbound. When I did, it didn't save
the data. When I close and re-enter there's nothing there. I'm thinking
maybe it should be bound. Been searching for days... Why is it so hard to
find the info you're looking for?
Ok, I asked for an answer to one question but you ought to know better than
that by now. "...the fun is really gonna start'? What do you know that I
don't? BTW, how do you do make a bound control? I think I tried it both ways
but it wasn't quite working or I was too tired to notice what I was doing
wrong.
[quoted text clipped - 27 lines]

Now that you're ready to try creating some forms, the fun is really gonna
start :- )

 

[Aria via AccessMonster.com]

I would say you're finding out right about now.

Control Source. Row Source. Bound. Unbound. Master/Child links.
Bound Column. Column Count.

Fun stuff, don't you think? <big grin>

Oh yeah...*big* time fun.

I'm fighting the urge to ask you more questions because I think you really
deserve a break from me. It's a struggle, believe me. But...<come on, did you
*really* think I would leave it at that?>

Dumb posting question: I have unfinished business with this post. If I mark
questions as answered, will you still track or no? In other words, if I still
have questions for you and Bruce, will you know?

But the reason we did that is to get rid of tblSubs...remember? You told me
to use tblEmployees for the fields that are common to both subs and site
employees. Then you said to create a sub form for tblSiteEmps.

To this point we have been talking almost exclusively about tables. I don't
recall saying much about forms, but I could be wrong. Anyway, having the
tables set up like you do is correct, but that doesn't necessarily mean
that you have to use two separate *forms*. If you are interested in having
the names and addresses on one form, I can try to explain that later, but
if you are happy with how you have it now, then I'll just leave it alone.
Maybe
you don't need to think about anything more right now.

Yeah...about that. Can I ask you a question? I think I made a mistake by
inputting fields for ClassDescription and TitleDescription. Naturally, there
wasn't a drop-down list.

I'm not sure what you mean here. ClassDescription is certainly appropriate
in tblClassifications, and TitleDescription in tblTitles, so I'm not sure why
you
think this was a mistake. If you tell me what form you are working on and
which
tables are involved I can give you more specific advice, but in the meantime
here is some general information.

First, when referring to objects on forms like text boxes, combo boxes, etc.
they are called controls. Tables and queries have fields, forms and reports
have controls. Controls have a "Control Source" property. If the control
source
is a field (in a table or query), then the control is bound. If the control
source is
something other than a field (like a calculation), or if there is no control
source
(a control does not have to have a "control source") then it is unbound. So
to make
a bound control, you use a field as the control source. Whether a control
should
be bound or unbound depends on what you are doing. Right now,
it sounds like you are working with the subforms for your employees where
you need to assign the proper Classification and Title, so the control should
be bound, because that data needs to be stored in the table. Unbound controls
are typically used to perform searches, do calculations, etc.

Now, in the case of a combo box (or a list box), there will also be a "Row
Source"
property, which basically determines what data, or values, are *displayed* in
the
combo box. I use the term display loosely, because you can, an in most cases
would, hide certain values in a combo box so the users don't see them. This
can
be data from a table or query, or just a list of values that you define
yourself.

"...the fun is really gonna start'? What do you know that I
don't?

I would say you're finding out right about now.

Control Source. Row Source. Bound. Unbound. Master/Child links.
Bound Column. Column Count.

[quoted text clipped - 36 lines]

 

[Aria via AccessMonster.com]

O.k., so I lost the struggle. Well, maybe not. I won't ask a question, just
jog your memory and give background info for what I didn't answer before.

Memory jog:

I'm not sure what you mean here. ClassDescription is certainly appropriate
in tblClassifications, and TitleDescription in tblTitles, so I'm not sure why
you
think this was a mistake. If you tell me what form you are working on and
which
tables are involved I can give you more specific advice, but in the meantime
here is some general information.

Additional Info:

The reason I said I think I made a mistake there was because I created the
field but naturally, that will not give me the drop down list that I was
looking for. I should have created the combo box. I was working in
frmEmployees. If I kept it the way I had it, I would have to input the same
info over and over and you know my coaches go for that sort of thing:
There you have it. I'm going to make it out without asking a zillion and one
questions. Of course if you'd rather...

But the reason we did that is to get rid of tblSubs...remember? You told me
to use tblEmployees for the fields that are common to both subs and site
employees. Then you said to create a sub form for tblSiteEmps.

To this point we have been talking almost exclusively about tables. I don't
recall saying much about forms, but I could be wrong. Anyway, having the
tables set up like you do is correct, but that doesn't necessarily mean
that you have to use two separate *forms*. If you are interested in having
the names and addresses on one form, I can try to explain that later, but
if you are happy with how you have it now, then I'll just leave it alone.
Maybe
you don't need to think about anything more right now.

Yeah...about that. Can I ask you a question? I think I made a mistake by
inputting fields for ClassDescription and TitleDescription. Naturally, there
wasn't a drop-down list.

I'm not sure what you mean here. ClassDescription is certainly appropriate
in tblClassifications, and TitleDescription in tblTitles, so I'm not sure why
you
think this was a mistake. If you tell me what form you are working on and
which
tables are involved I can give you more specific advice, but in the meantime
here is some general information.

First, when referring to objects on forms like text boxes, combo boxes, etc.
they are called controls. Tables and queries have fields, forms and reports
have controls. Controls have a "Control Source" property. If the control
source
is a field (in a table or query), then the control is bound. If the control
source is
something other than a field (like a calculation), or if there is no control
source
(a control does not have to have a "control source") then it is unbound. So
to make
a bound control, you use a field as the control source. Whether a control
should
be bound or unbound depends on what you are doing. Right now,
it sounds like you are working with the subforms for your employees where
you need to assign the proper Classification and Title, so the control should
be bound, because that data needs to be stored in the table. Unbound controls
are typically used to perform searches, do calculations, etc.

Now, in the case of a combo box (or a list box), there will also be a "Row
Source"
property, which basically determines what data, or values, are *displayed* in
the
combo box. I use the term display loosely, because you can, an in most cases
would, hide certain values in a combo box so the users don't see them. This
can
be data from a table or query, or just a list of values that you define
yourself.

"...the fun is really gonna start'? What do you know that I
don't?

I would say you're finding out right about now.

Control Source. Row Source. Bound. Unbound. Master/Child links.
Bound Column. Column Count.

[quoted text clipped - 36 lines]

 

[Mike Barnard]

associated queries. If you want my help, contact me at (e-mail address removed).

Steve

Hmmmm, curious about the names. "rlaird" in the email. "Steve" as a
signature and ""Mark" <[email protected]>" in the from header.

Confused? You soon will be!

 

[John... Visio MVP]

Hmmmm, curious about the names. "rlaird" in the email. "Steve" as a
signature and ""Mark" <[email protected]>" in the from header.

Confused? You soon will be!

rlaird is Roberta Laird who lives with Steve. He has worn out is welcome
with PCDatasheet and Steve, so now he is using his wifes email address. This
really speaks to his credibility if he can not survive using his own name.

John...

 

[BruceM]

I've been away off and on, and haven't been following the whole discussion,
but let me mention a few things about combo boxes. A combo box, as has been
mentioned, can be bound or unbound. A search combo box (to find an employee
record, for instance) is an example of an unbound combo box.
A bound combo box need to get its information from somewhere. One
possibility is a Value List. If you have to select one of the five boroughs
of NYC you could just have a list: Brooklyn; Bronx; Manhattan; Queens;
Staten Island.
For the 50 states you may want to show either the full name or the
two-letter abbreviation, depending on the circumstances. In that case a
lookup table is the best choice. You can have the StateID, StateAbbrev,
StateName. All you would see in the combo box is either the name or the
abbreviation (or both, if you like). In any case, only one value will be
displayed after the selection has been made. Maybe you could do away with
the StateID, and store the abbreviation instead since it is unlikely to
change, but the principle remains the same.
Another option is to build a combo box row source based on values already
storred in the field. If the value you want to use isn't there, type it it.
I don't think there is a use for this approach in your database, but it
comes in handy at times. The Row Source is the field to which the combo box
is bound.
If you store the EmployeeID you can view other Employee information as
needed, at any time. Your EmployeeID is stored in each payroll record, but
no other information about you needs to be stored there. EmployeeID is the
link to the rest of your information.
This may be repetition of what you already know, but it seems you may be
dancing around the topic a bit.

O.k., so I lost the struggle. Well, maybe not. I won't ask a question,
just
jog your memory and give background info for what I didn't answer before.

Memory jog:

I'm not sure what you mean here. ClassDescription is certainly appropriate
in tblClassifications, and TitleDescription in tblTitles, so I'm not sure
why
you
think this was a mistake. If you tell me what form you are working on and
which
tables are involved I can give you more specific advice, but in the
meantime
here is some general information.

Additional Info:

The reason I said I think I made a mistake there was because I created the
field but naturally, that will not give me the drop down list that I was
looking for. I should have created the combo box. I was working in
frmEmployees. If I kept it the way I had it, I would have to input the
same
info over and over and you know my coaches go for that sort of thing:
There you have it. I'm going to make it out without asking a zillion and
one
questions. Of course if you'd rather...

But the reason we did that is to get rid of tblSubs...remember? You told
me
to use tblEmployees for the fields that are common to both subs and site
employees. Then you said to create a sub form for tblSiteEmps.

To this point we have been talking almost exclusively about tables. I
don't
recall saying much about forms, but I could be wrong. Anyway, having the
tables set up like you do is correct, but that doesn't necessarily mean
that you have to use two separate *forms*. If you are interested in having
the names and addresses on one form, I can try to explain that later, but
if you are happy with how you have it now, then I'll just leave it alone.
Maybe
you don't need to think about anything more right now.

Yeah...about that. Can I ask you a question? I think I made a mistake by
inputting fields for ClassDescription and TitleDescription. Naturally,
there
wasn't a drop-down list.

I'm not sure what you mean here. ClassDescription is certainly appropriate
in tblClassifications, and TitleDescription in tblTitles, so I'm not sure
why
you
think this was a mistake. If you tell me what form you are working on and
which
tables are involved I can give you more specific advice, but in the
meantime
here is some general information.

First, when referring to objects on forms like text boxes, combo boxes,
etc.
they are called controls. Tables and queries have fields, forms and
reports
have controls. Controls have a "Control Source" property. If the control
source
is a field (in a table or query), then the control is bound. If the
control
source is
something other than a field (like a calculation), or if there is no
control
source
(a control does not have to have a "control source") then it is unbound.
So
to make
a bound control, you use a field as the control source. Whether a control
should
be bound or unbound depends on what you are doing. Right now,
it sounds like you are working with the subforms for your employees where
you need to assign the proper Classification and Title, so the control
should
be bound, because that data needs to be stored in the table. Unbound
controls
are typically used to perform searches, do calculations, etc.

Now, in the case of a combo box (or a list box), there will also be a "Row
Source"
property, which basically determines what data, or values, are *displayed*
in
the
combo box. I use the term display loosely, because you can, an in most
cases
would, hide certain values in a combo box so the users don't see them.
This
can
be data from a table or query, or just a list of values that you define
yourself.

"...the fun is really gonna start'? What do you know that I
don't?

I would say you're finding out right about now.

Control Source. Row Source. Bound. Unbound. Master/Child links.
Bound Column. Column Count.

Hey, there's no crying in baseball or relational database design ;- )

[quoted text clipped - 36 lines]

Now that you're ready to try creating some forms, the fun is really
gonna
start :- )

 

[Aria via AccessMonster.com]

Hi Bruce,
Thanks for the information. BTW, I see what you mean about starting
additional threads. It seems I did it again...sorry about that. I'll try to
keep it straight.
I'm having a bit of a problem.This happened early last week. I don't know if
you read the post where I stated I didn't get a drop-down list for my combo
box. In the beginning it was unbound but since it didn't work as expected, I
figured it should probably be bound so I changed it. I was trying to get a
drop-down list for tblTitles and tblClassifications. Once I created the bound
box, I received the following error message, "The value you entered isn't
valid for this field. For example you may have entered text in a numeric
field or a # that is larger than the field size." It doesn't highlight any
field in particular.
Strange happenings:
1. When clicking through records, the timing is off maybe half a beat.
You'll see the previous records classification or title after I have moved to
the next record but then it changes.

2. The date created field has the date in the first record only. Subsequent
records have the time.

3. Titles aren't in alpha order. I'm sure it's because I sorted it after I
created the combo box but those changes aren't reflected in the list. I think
it would be easier to find titles if it's in alpha order.

4. I input a default value for State but again, those changes are reflected.
It still shows the original input.

I thought it may have something to do with the combo box since the message
appeared right after but not being sure, I took the following steps:

1. Checked tblEmployee and tblSiteEmp. The only # is the autonumber; all
other fields are text.

2. In tblSiteEmps the Address field is listed as 255 so I changed it to
something reasonable. I know it probably wasn't that but since I was already
there, I took care of it.

3. I deleted both combo boxes and re-created them. Same problem.

4. I checked the input mask for the date created field. As far as I can see,
it's correct. It says short date.

I read a post dated 7/2/08 that had the same error message as mine but that
person had code in the AfterUpdate event and their RowSource info was
different so I didn't think I could adapt their answer to my own problem.
It's probably similar though. What do you think?

This may be repetition of what you already know, but it seems you may be
dancing around the topic a bit.

<lol> Was I? You're probably right.

I've been away off and on, and haven't been following the whole discussion,
but let me mention a few things about combo boxes. A combo box, as has been
mentioned, can be bound or unbound. A search combo box (to find an employee
record, for instance) is an example of an unbound combo box.
A bound combo box need to get its information from somewhere. One
possibility is a Value List. If you have to select one of the five boroughs
of NYC you could just have a list: Brooklyn; Bronx; Manhattan; Queens;
Staten Island.
For the 50 states you may want to show either the full name or the
two-letter abbreviation, depending on the circumstances. In that case a
lookup table is the best choice. You can have the StateID, StateAbbrev,
StateName. All you would see in the combo box is either the name or the
abbreviation (or both, if you like). In any case, only one value will be
displayed after the selection has been made. Maybe you could do away with
the StateID, and store the abbreviation instead since it is unlikely to
change, but the principle remains the same.
Another option is to build a combo box row source based on values already
storred in the field. If the value you want to use isn't there, type it it.
I don't think there is a use for this approach in your database, but it
comes in handy at times. The Row Source is the field to which the combo box
is bound.
If you store the EmployeeID you can view other Employee information as
needed, at any time. Your EmployeeID is stored in each payroll record, but
no other information about you needs to be stored there. EmployeeID is the
link to the rest of your information.
This may be repetition of what you already know, but it seems you may be
dancing around the topic a bit.

O.k., so I lost the struggle. Well, maybe not. I won't ask a question,
just

[quoted text clipped - 123 lines]

 

[Beetle]

Just for clarification, in one of my previous posts, when I stated the
following;

I was talking specifically about tables, not forms.

Now that you have moved on to creating some forms, it will be helpful
if you can be very specific in describing what you are doing. There is a
bit more terminology when it comes to forms, and it is very easy for us
to misunderstand what you are doing since we are not there to see it.

As I said before, if you are referring to objects on forms, they are called
controls. Also, it helps if you tell us the name of the form (or forms) you
are working with as well as the name of the table(s) or query(s) that
are the recordsource of the form(s). For example, you said you are working
on frmEmployees, but does this form have a subform? If so, are the
controls (combo boxes) you are having trouble with on the main form or
the subform? What is the Control Source and the Row Source of the
combo box(s) you are having trouble with?

Let's start with that, then we can move on to your other issues.
--
_________

Sean Bailey

Hi Bruce,
Thanks for the information. BTW, I see what you mean about starting
additional threads. It seems I did it again...sorry about that. I'll try to
keep it straight.
I'm having a bit of a problem.This happened early last week. I don't know if
you read the post where I stated I didn't get a drop-down list for my combo
box. In the beginning it was unbound but since it didn't work as expected, I
figured it should probably be bound so I changed it. I was trying to get a
drop-down list for tblTitles and tblClassifications. Once I created the bound
box, I received the following error message, "The value you entered isn't
valid for this field. For example you may have entered text in a numeric
field or a # that is larger than the field size." It doesn't highlight any
field in particular.
Strange happenings:
1. When clicking through records, the timing is off maybe half a beat.
You'll see the previous records classification or title after I have moved to
the next record but then it changes.

2. The date created field has the date in the first record only. Subsequent
records have the time.

3. Titles aren't in alpha order. I'm sure it's because I sorted it after I
created the combo box but those changes aren't reflected in the list. I think
it would be easier to find titles if it's in alpha order.

4. I input a default value for State but again, those changes are reflected.
It still shows the original input.

I thought it may have something to do with the combo box since the message
appeared right after but not being sure, I took the following steps:

1. Checked tblEmployee and tblSiteEmp. The only # is the autonumber; all
other fields are text.

2. In tblSiteEmps the Address field is listed as 255 so I changed it to
something reasonable. I know it probably wasn't that but since I was already
there, I took care of it.

3. I deleted both combo boxes and re-created them. Same problem.

4. I checked the input mask for the date created field. As far as I can see,
it's correct. It says short date.

I read a post dated 7/2/08 that had the same error message as mine but that
person had code in the AfterUpdate event and their RowSource info was
different so I didn't think I could adapt their answer to my own problem.
It's probably similar though. What do you think?

This may be repetition of what you already know, but it seems you may be
dancing around the topic a bit.

<lol> Was I? You're probably right.

I've been away off and on, and haven't been following the whole discussion,
but let me mention a few things about combo boxes. A combo box, as has been
mentioned, can be bound or unbound. A search combo box (to find an employee
record, for instance) is an example of an unbound combo box.
A bound combo box need to get its information from somewhere. One
possibility is a Value List. If you have to select one of the five boroughs
of NYC you could just have a list: Brooklyn; Bronx; Manhattan; Queens;
Staten Island.
For the 50 states you may want to show either the full name or the
two-letter abbreviation, depending on the circumstances. In that case a
lookup table is the best choice. You can have the StateID, StateAbbrev,
StateName. All you would see in the combo box is either the name or the
abbreviation (or both, if you like). In any case, only one value will be
displayed after the selection has been made. Maybe you could do away with
the StateID, and store the abbreviation instead since it is unlikely to
change, but the principle remains the same.
Another option is to build a combo box row source based on values already
storred in the field. If the value you want to use isn't there, type it it.
I don't think there is a use for this approach in your database, but it
comes in handy at times. The Row Source is the field to which the combo box
is bound.
If you store the EmployeeID you can view other Employee information as
needed, at any time. Your EmployeeID is stored in each payroll record, but
no other information about you needs to be stored there. EmployeeID is the
link to the rest of your information.
This may be repetition of what you already know, but it seems you may be
dancing around the topic a bit.

O.k., so I lost the struggle. Well, maybe not. I won't ask a question,
just

[quoted text clipped - 123 lines]

gonna
start :- )

 

[Beetle284 via AccessMonster.com]

Just for clarification, in one of my previous posts, when I stated the
following;

I was talking specifically about tables, not forms.

Now that you have moved on to creating some forms, it will be helpful
if you can be very specific in describing what you are doing. There is a
bit more terminology when it comes to forms, and it is very easy for us
to misunderstand what you are doing since we are not there to see it.

As I said before, if you are referring to objects on forms, they are called
controls. Also, it helps if you tell us the name of the form (or forms) you
are working with as well as the name of the table(s) or query(s) that
are the recordsource of the form(s). For example, you said you are working
on frmEmployees, but does this form have a subform? If so, are the
controls (combo boxes) you are having trouble with on the main form or
the subform? What is the Control Source and the Row Source of the
combo box(s) you are having trouble with?

Let's start with that, then we can move on to your other issues.

Hi Bruce,
Thanks for the information. BTW, I see what you mean about starting
additional threads. It seems I did it again...sorry about that. I'll try to
keep it straight.
I'm having a bit of a problem.This happened early last week. I don't know if
you read the post where I stated I didn't get a drop-down list for my combo
box. In the beginning it was unbound but since it didn't work as expected, I
figured it should probably be bound so I changed it. I was trying to get a
drop-down list for tblTitles and tblClassifications. Once I created the bound
box, I received the following error message, "The value you entered isn't
valid for this field. For example you may have entered text in a numeric
field or a # that is larger than the field size." It doesn't highlight any
field in particular.
Strange happenings:
1. When clicking through records, the timing is off maybe half a beat.
You'll see the previous records classification or title after I have moved to
the next record but then it changes.

2. The date created field has the date in the first record only. Subsequent
records have the time.

3. Titles aren't in alpha order. I'm sure it's because I sorted it after I
created the combo box but those changes aren't reflected in the list. I think
it would be easier to find titles if it's in alpha order.

4. I input a default value for State but again, those changes are reflected.
It still shows the original input.

I thought it may have something to do with the combo box since the message
appeared right after but not being sure, I took the following steps:

1. Checked tblEmployee and tblSiteEmp. The only # is the autonumber; all
other fields are text.

2. In tblSiteEmps the Address field is listed as 255 so I changed it to
something reasonable. I know it probably wasn't that but since I was already
there, I took care of it.

3. I deleted both combo boxes and re-created them. Same problem.

4. I checked the input mask for the date created field. As far as I can see,
it's correct. It says short date.

I read a post dated 7/2/08 that had the same error message as mine but that
person had code in the AfterUpdate event and their RowSource info was
different so I didn't think I could adapt their answer to my own problem.
It's probably similar though. What do you think?

This may be repetition of what you already know, but it seems you may be
dancing around the topic a bit.

I've been away off and on, and haven't been following the whole discussion,
but let me mention a few things about combo boxes. A combo box, as has been

[quoted text clipped - 29 lines]

 

[Aria via AccessMonster.com]

I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears to be
cboTitleDescription. I deleted the combo box a couple of days ago in trying
to isolate the problem. I saved the form without cboTitleDescription. When I
opened the form today and cycled through the records, cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID], [tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated the
following;

However, there is a way to solve both problems, which I should have
mentioned in my last post, but it involves - yes, that's right - [quoted text clipped - 3 lines]
(full time and subs), then you add another table for the data that
applies to only *some* employees, and relate it back via EmployeeID.

I was talking specifically about tables, not forms.

Now that you have moved on to creating some forms, it will be helpful
if you can be very specific in describing what you are doing. There is a
bit more terminology when it comes to forms, and it is very easy for us
to misunderstand what you are doing since we are not there to see it.

As I said before, if you are referring to objects on forms, they are called
controls. Also, it helps if you tell us the name of the form (or forms) you
are working with as well as the name of the table(s) or query(s) that
are the recordsource of the form(s). For example, you said you are working
on frmEmployees, but does this form have a subform? If so, are the
controls (combo boxes) you are having trouble with on the main form or
the subform? What is the Control Source and the Row Source of the
combo box(s) you are having trouble with?

Let's start with that, then we can move on to your other issues.

Hi Bruce,
Thanks for the information. BTW, I see what you mean about starting

[quoted text clipped - 54 lines]

 

[Beetle284 via AccessMonster.com]

You may need to refresh my memory a bit here. You had m:m relationships
for Employees to Titles and Employees to Classifications, so you have
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears to be
cboTitleDescription. I deleted the combo box a couple of days ago in trying
to isolate the problem. I saved the form without cboTitleDescription. When I
opened the form today and cycled through the records, cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID], [tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated the
following;

[quoted text clipped - 28 lines]

 

[Aria]

Yes, that's correct.
--
Aria W.

You may need to refresh my memory a bit here. You had m:m relationships
for Employees to Titles and Employees to Classifications, so you have
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears to be
cboTitleDescription. I deleted the combo box a couple of days ago in trying
to isolate the problem. I saved the form without cboTitleDescription. When I
opened the form today and cycled through the records, cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID], [tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated the
following;

[quoted text clipped - 28 lines]

gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

 

[Beetle]

OK, so what you need to do is use subforms for this. I'll use Titles as an
example. The junction table (tblEmpTitles) stores the data that defines
the relationship, so you add another subform that uses the junction table
as it's recordsource. The junction table should have these fields;

EmpID
TitleID

Now, if you are using the wizard, Access will create text boxes and labels
for each of these fields. I would delete both the text box and the label
for EmpID (it's usually not a good idea to display ID numbers to users) and
the label for TitleID. Then change the TitleID text box to a combo box with
the following properties (if you're not using the wizard, just start with a
combo box to begin with);

Control Source = TitleID (in tblEmpTitles)
Row Source = Select TitleID, TitleDescription From tblTitles OrderBy
TitleDescription
Bound Column = 1
Column Count = 2
Column Widths = 0,1

Now you can manipulate the size an appearance of the subform so that
it appears to be just a group of combo boxes on your form. For example
you might set the width so that it is only as wide as your combo box, and
set the height so that the user could view maybe two or three combo
boxes at once. Other properties of the form you may want to change
might be;

Default View = Continuous
Scroll Bars = Vertical Only
Record Selectors = No
Navigation Buttons = No
Border Style = Thin

This way, when you make a selection in the combo box, the associated ID
numbers will be properly stored in tblEmpTitles, and you have the ability
to assign more than one title to an employee.

--
_________

Sean Bailey

Yes, that's correct.
--
Aria W.

You may need to refresh my memory a bit here. You had m:m relationships
for Employees to Titles and Employees to Classifications, so you have
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears to be
cboTitleDescription. I deleted the combo box a couple of days ago in trying
to isolate the problem. I saved the form without cboTitleDescription. When I
opened the form today and cycled through the records, cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID], [tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated the
following;
[quoted text clipped - 28 lines]
gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m

 

[Aria]

I didn't give you all of the information that you requested. The problem is
on the main form. The recordsource, I believe is tblTitles. I think I found
the answer to one of my problems. The drop-list isn't in alpha order because
the sort was based on qryTitles, but the combo box is based on tblTitles.

--
Aria W.

Yes, that's correct.
--
Aria W.

You may need to refresh my memory a bit here. You had m:m relationships
for Employees to Titles and Employees to Classifications, so you have
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

I'm very sorry; my mistake.

I'm working with frmEmployees with sfrmSiteEmps. The problem appears to be
cboTitleDescription. I deleted the combo box a couple of days ago in trying
to isolate the problem. I saved the form without cboTitleDescription. When I
opened the form today and cycled through the records, cboTitleClassification
appears to work as expected. I then created a new combo box for Title
Description; same error message as before. The Control Source is
TitleDescription. The Row Source is SELECT [tblTitles].[TitleID], [tblTitles].
[TitleDescription] FROM tblTitles; The Column count is 2. The Column Widths
are 0";1". The Bound Column is 1.

Just for clarification, in one of my previous posts, when I stated the
following;
[quoted text clipped - 28 lines]
gonna
start :- )

--
________________________

Sean Bailey
s b a i l e y 2 8 4 AT e c e n t r a l DOT c o m