Field Properties > LookUp

[Programmer - wannaB]

Please Help me to understand This>> I am sure this is working, But I need
help understanding..
I created a table with 4 fields;
CCID Auto number – index field,
CCName – Simple text,
CCNum – Numeric representation of CCName,
CCDescription – Simple Text description of the record.
Then I added the CCID field to another table as a Lookup field Named
CP_CCID, using the lookup Wizard which created this query>> SELECT
Tbl_CurrencyCode.CCID, Tbl_CurrencyCode.CCName, Tbl_CurrencyCode.CCNum,
Tbl_CurrencyCode.CCDescription FROM Tbl_CurrencyCode ORDER BY [CCName]; <<
and when Prompted if I wanted to hide the index field (which was suggested) I
agreed, and clicked yes. ??This is where I get a bit confused?? I thought it
would be hidden from display, but still save the index field value in the
CP_CCID field for that record – HOWEVER when I look at the table the data in
that field is not the index number – UNLESS I modify the Column Width
Properties of the LookUp (from 0â€;1â€;1â€;4†to 1â€;1â€;1â€;4â€) Now I understand
that the 0†is what hides the first column of the lookup query fields
selected, BUT how can it not be displayed in the field after the lookup drop
down is gone, and why does it only display the next field in the dropdown
list???

Now I thought to my self what if I were to query on the CP_CCID field, and
low-n-behold the index number works, when I tell it to display all records
with index number < 5, that’s what it does even though it displays the text
characters of the CCName field.
AND if I query on the CP_CCID field for text I get “Type mismatch
criteriaâ€
SO That’s what I am looking at and I’m not even sure how to describe my
question, other then how does it do that, why does it do that???? Most
importantly Is this really a good idea to do this (I can only imagine the
confusion of the next guy who works on the database and see’s text data in a
field that he can’t query on with text)

 

[Programmer - wannaB]

SORRY >> I reread my post and noticed mistakes >> SEE in line fixes in CAPS
VERY MANY THANKS TO ANYONE WHO CAN HELP ME

Please Help me to understand This>> I am sure this is working, But I need
help understanding..
I created a table with 4 fields;
CCID Auto number – index field,
CCName – Simple text,
CCNum – Numeric representation of CCName,
CCDescription – Simple Text description of the record.
Then I added the CCID field to another table as a Lookup field Named
CP_CCID, using the lookup Wizard which created this query>> SELECT
Tbl_CurrencyCode.CCID, Tbl_CurrencyCode.CCName, Tbl_CurrencyCode.CCNum,
Tbl_CurrencyCode.CCDescription FROM Tbl_CurrencyCode ORDER BY [CCName]; <<
and when Prompted if I wanted to hide the index field (which was suggested) I
agreed, and clicked yes. ??This is where I get a bit confused?? I thought it
would be hidden from display, but still save the index field value in the
CP_CCID field for that record – HOWEVER when I look at the table the data in
that field is not the index number – UNLESS I modify the Column Width
Properties of the LookUp (from 0â€;1â€;1â€;4†to 1â€;1â€;1â€;4â€) Now I understand
that the 0†is what hides the first column of the lookup query fields
selected, BUT how can it not be displayed in the field after the lookup drop
down is gone, and why does it only display the next field in the dropdown
list???

Now I thought to my self what if I were to query on the CP_CCID field, and
low-n-behold the index number works, when I tell it to display all records
with index number < 5, that’s what it does even though it displays the text
characters of the CCName field. SHOULD BE CP_CCID WHAT i AM TRYING TO SAY HERE IS THAT EVEN THOUGH I AM QUERYING FOR NUMERIC VALUES IN THE CP_CCID FIELD THE DISPLAY IS TEXT, AND IT IS DISPLAYED AS THE CP_CCID FIELD

 

[RoLaAus]

When you create a look-up field, there is a property called "Bound
column". This is the field that is actually stored in the table that
is doing the look-ing-up.

In other words, if you have an Employee table and let's say you want
to know each time an employee swipes their badge at the front door/
security station to gain access to the building, let's call it a Entry
table.

Emplolyee table has the following fields;
Emp_ID
emp_Last_Name
emp_First_Name

and Entry table has;
entry_empID_lookup
entry_TimeStamp
entry_Doorway (just in case there are multiple entry points)

If you ran a report and wanted to see when a person comes in and out,
it would not do much good to run the report using the Emp_ID as a
manager or other person will likely not know anyone elses ID off the
top of their head.

So, your lookup might have 3 columns (ID, Last Name, First Name) you
could use the Order By clause and alphabetize them, and your Bound
Column would be 1 (the ID field)

The reason you would want to use an ID field for a bound column is
because (more than likely the ID is a number, either a social sec # or
some other numeric only identifier), it is faster to lookup a number
field than it is an alpha-numeric field. The reason you would want to
include other fields in your lookup is because you can include fields
that mean more to the end-user than some arbitrary number, and you can
have a more meaningful sort, like by last name, rather than sorting it
by the default ID sorting, which would most likely end up being by
hire-date (people hired more recently would have a higher number than
people that have been around for years).

And all of this is not even taking into consideration if the employee
is still an active employee or if they are no longer working for the
company.

Please Help me to understand This>> I am sure this is working, But I need
help understanding..
I created a table with 4 fields;
CCID Auto number - index field,
CCName - Simple text,
CCNum - Numeric representation of CCName,
CCDescription - Simple Text description of the record.
Then I added the CCID field to another table as a Lookup field Named
CP_CCID, using the lookup Wizard which created this query>> SELECT
Tbl_CurrencyCode.CCID, Tbl_CurrencyCode.CCName, Tbl_CurrencyCode.CCNum,
Tbl_CurrencyCode.CCDescription FROM Tbl_CurrencyCode ORDER BY [CCName]; <<
and when Prompted if I wanted to hide the index field (which was suggested) I
agreed, and clicked yes. ??This is where I get a bit confused?? I thought it
would be hidden from display, but still save the index field value in the
CP_CCID field for that record - HOWEVER when I look at the table the data in
that field is not the index number - UNLESS I modify the Column Width
Properties of the LookUp (from 0";1";1";4" to 1";1";1";4") Now I understand
that the 0" is what hides the first column of the lookup query fields
selected, BUT how can it not be displayed in the field after the lookup drop
down is gone, and why does it only display the next field in the dropdown
list???

Now I thought to my self what if I were to query on the CP_CCID field, and
low-n-behold the index number works, when I tell it to display all records
with index number < 5, that's what it does even though it displays the text
characters of the CCName field.
AND if I query on the CP_CCID field for text I get "Type mismatch
criteria"
SO That's what I am looking at and I'm not even sure how to describe my
question, other then how does it do that, why does it do that???? Most
importantly Is this really a good idea to do this (I can only imagine the
confusion of the next guy who works on the database and see's text data in a
field that he can't query on with text)

 

[Jeff Boyce]

You are quite correct in assuming that the use of "lookup" data type fields
will cause future programmers (?and users) headaches ... and you, too, if
you step away from it for a couple months!

Tables are great places to store data in Access, but they don't work as well
as Forms for displaying/editing/adding it.

In a form, you can use a combobox to provide a "lookup" of data from another
table, display a "looked-up" value, but store the looked-up value's rowID in
the table underlying your form. This way, the underlying table stores a
rowID in a field that's supposed to store a rowID.

You use a query to join that rowID back to the lookup table (a table with a
list of the values to be looked up) for display purposes.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Please Help me to understand This>> I am sure this is working, But I need
help understanding..
I created a table with 4 fields;
CCID Auto number - index field,
CCName - Simple text,
CCNum - Numeric representation of CCName,
CCDescription - Simple Text description of the record.
Then I added the CCID field to another table as a Lookup field Named
CP_CCID, using the lookup Wizard which created this query>> SELECT
Tbl_CurrencyCode.CCID, Tbl_CurrencyCode.CCName, Tbl_CurrencyCode.CCNum,
Tbl_CurrencyCode.CCDescription FROM Tbl_CurrencyCode ORDER BY [CCName]; <<
and when Prompted if I wanted to hide the index field (which was
suggested) I
agreed, and clicked yes. ??This is where I get a bit confused?? I thought
it
would be hidden from display, but still save the index field value in the
CP_CCID field for that record - HOWEVER when I look at the table the data
in
that field is not the index number - UNLESS I modify the Column Width
Properties of the LookUp (from 0";1";1";4" to 1";1";1";4") Now I
understand
that the 0" is what hides the first column of the lookup query fields
selected, BUT how can it not be displayed in the field after the lookup
drop
down is gone, and why does it only display the next field in the dropdown
list???

Now I thought to my self what if I were to query on the CP_CCID field, and
low-n-behold the index number works, when I tell it to display all records
with index number < 5, that's what it does even though it displays the
text
characters of the CCName field.
AND if I query on the CP_CCID field for text I get "Type mismatch
criteria"
SO That's what I am looking at and I'm not even sure how to describe my
question, other then how does it do that, why does it do that???? Most
importantly Is this really a good idea to do this (I can only imagine the
confusion of the next guy who works on the database and see's text data in
a
field that he can't query on with text)

 

[Programmer - wannaB]

Thanks Guys, my head hurts even more after reading both of those very
wonderfull responses - But seriously thank you very much, both of those
responses are great. I'm thinking now that there may not be a straight
forward/easy answer here (I must have been intoxicated to think that was a
possibility, after all this is a MicroSoft Product) .....

Am I correct, that some would believe in building the structure into the
core, and others believe that it's best to build an unstructured core which
provides more versitility in building pieces as part of the whole ???

AND If that is a valid thought, What are the Pro's and Con's of each method?

Thank you very much!!!!!! for all replies.

You are quite correct in assuming that the use of "lookup" data type fields
will cause future programmers (?and users) headaches ... and you, too, if
you step away from it for a couple months!

Tables are great places to store data in Access, but they don't work as well
as Forms for displaying/editing/adding it.

In a form, you can use a combobox to provide a "lookup" of data from another
table, display a "looked-up" value, but store the looked-up value's rowID in
the table underlying your form. This way, the underlying table stores a
rowID in a field that's supposed to store a rowID.

You use a query to join that rowID back to the lookup table (a table with a
list of the values to be looked up) for display purposes.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Please Help me to understand This>> I am sure this is working, But I need
help understanding..
I created a table with 4 fields;
CCID Auto number - index field,
CCName - Simple text,
CCNum - Numeric representation of CCName,
CCDescription - Simple Text description of the record.
Then I added the CCID field to another table as a Lookup field Named
CP_CCID, using the lookup Wizard which created this query>> SELECT
Tbl_CurrencyCode.CCID, Tbl_CurrencyCode.CCName, Tbl_CurrencyCode.CCNum,
Tbl_CurrencyCode.CCDescription FROM Tbl_CurrencyCode ORDER BY [CCName]; <<
and when Prompted if I wanted to hide the index field (which was
suggested) I
agreed, and clicked yes. ??This is where I get a bit confused?? I thought
it
would be hidden from display, but still save the index field value in the
CP_CCID field for that record - HOWEVER when I look at the table the data
in
that field is not the index number - UNLESS I modify the Column Width
Properties of the LookUp (from 0";1";1";4" to 1";1";1";4") Now I
understand
that the 0" is what hides the first column of the lookup query fields
selected, BUT how can it not be displayed in the field after the lookup
drop
down is gone, and why does it only display the next field in the dropdown
list???

Now I thought to my self what if I were to query on the CP_CCID field, and
low-n-behold the index number works, when I tell it to display all records
with index number < 5, that's what it does even though it displays the
text
characters of the CCName field.
AND if I query on the CP_CCID field for text I get "Type mismatch
criteria"
SO That's what I am looking at and I'm not even sure how to describe my
question, other then how does it do that, why does it do that???? Most
importantly Is this really a good idea to do this (I can only imagine the
confusion of the next guy who works on the database and see's text data in
a
field that he can't query on with text)

 

[Jeff Boyce]

Gosh, wouldn't that depend on our respective definitions of "unstructured"?

I think you'll find a lot of the folks who volunteer their time and ideas
here would be against the notion of an "unstructured" database. If
anything, a lot of us will be recommending that you turn off your computer
and spend time with paper and pencil drawing out the entities and
relationships you'll be using in your system.

"Unstructured" may imply that anything that comes up will find a home ... a
little to risky!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks Guys, my head hurts even more after reading both of those very
wonderfull responses - But seriously thank you very much, both of those
responses are great. I'm thinking now that there may not be a straight
forward/easy answer here (I must have been intoxicated to think that was a
possibility, after all this is a MicroSoft Product) .....

Am I correct, that some would believe in building the structure into the
core, and others believe that it's best to build an unstructured core
which
provides more versitility in building pieces as part of the whole ???

AND If that is a valid thought, What are the Pro's and Con's of each
method?

Thank you very much!!!!!! for all replies.

You are quite correct in assuming that the use of "lookup" data type
fields
will cause future programmers (?and users) headaches ... and you, too, if
you step away from it for a couple months!

Tables are great places to store data in Access, but they don't work as
well
as Forms for displaying/editing/adding it.

In a form, you can use a combobox to provide a "lookup" of data from
another
table, display a "looked-up" value, but store the looked-up value's rowID
in
the table underlying your form. This way, the underlying table stores a
rowID in a field that's supposed to store a rowID.

You use a query to join that rowID back to the lookup table (a table with
a
list of the values to be looked up) for display purposes.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

in
message news:[email protected]...

Please Help me to understand This>> I am sure this is working, But I
need
help understanding..
I created a table with 4 fields;
CCID Auto number - index field,
CCName - Simple text,
CCNum - Numeric representation of CCName,
CCDescription - Simple Text description of the record.
Then I added the CCID field to another table as a Lookup field Named
CP_CCID, using the lookup Wizard which created this query>> SELECT
Tbl_CurrencyCode.CCID, Tbl_CurrencyCode.CCName, Tbl_CurrencyCode.CCNum,
Tbl_CurrencyCode.CCDescription FROM Tbl_CurrencyCode ORDER BY [CCName];
<<
and when Prompted if I wanted to hide the index field (which was
suggested) I
agreed, and clicked yes. ??This is where I get a bit confused?? I
thought
it
would be hidden from display, but still save the index field value in
the
CP_CCID field for that record - HOWEVER when I look at the table the
data
in
that field is not the index number - UNLESS I modify the Column Width
Properties of the LookUp (from 0";1";1";4" to 1";1";1";4") Now I
understand
that the 0" is what hides the first column of the lookup query fields
selected, BUT how can it not be displayed in the field after the lookup
drop
down is gone, and why does it only display the next field in the
dropdown
list???

Now I thought to my self what if I were to query on the CP_CCID field,
and
low-n-behold the index number works, when I tell it to display all
records
with index number < 5, that's what it does even though it displays the
text
characters of the CCName field.
AND if I query on the CP_CCID field for text I get "Type mismatch
criteria"
SO That's what I am looking at and I'm not even sure how to describe my
question, other then how does it do that, why does it do that???? Most
importantly Is this really a good idea to do this (I can only imagine
the
confusion of the next guy who works on the database and see's text data
in
a
field that he can't query on with text)