WHERE statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What am I doing wrong? I have a table called 'Enrolments' which among other
columns, has a lookup column called 'Level' which looks up a table called
'NTPLevels' and the column in that called 'Level'. I want to be able to run a
query that pulls only those names within a set 'Level' (in this case Level
1-2). The 'Level' column is in 'Text' format throughout, but every time I run
it with the WHERE statement it comes up with nothing, if I take the WHERE
statement out it gives me all of the records.

This is my SQL;

SELECT LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First Name] AS
Name, Enrolments.Level
FROM Enrolments
WHERE (((LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First Name])
Is Not Null) AND ((Enrolments.Level) Is Not Null)
AND ((Enrolments.Surname) Is Not Null)) AND ((Enrolments.Level) = '1-2')
ORDER BY Enrolments.Surname;

Thanks in advance.
 
If I'm understanding correctly, you have a table that has a "lookup"
datatype field. (One of ) the problem(s) with this data type is that it
stores one value, but displays something else.

So, for example, if you are trying to do a parameter query that uses the
displayed value (rather than what Access actually stores), you won't get any
matches... oh wait?! that's what you just described, right?!

The solution you'll find recommended in the tablesdbdesign newsgroup is to
NOT USE Lookup data type fields, for just that (and other) reasons.

If you must keep the lookup field, you'll have to modify your parameter
query to be looking for the actually-stored value.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Ok, thanks Jeff. Yes, that makes sense, so is there a way of seeing what the
actual stored value is and if so, could I then Query' it to include this data
to get the results?

Jeff Boyce said:
If I'm understanding correctly, you have a table that has a "lookup"
datatype field. (One of ) the problem(s) with this data type is that it
stores one value, but displays something else.

So, for example, if you are trying to do a parameter query that uses the
displayed value (rather than what Access actually stores), you won't get any
matches... oh wait?! that's what you just described, right?!

The solution you'll find recommended in the tablesdbdesign newsgroup is to
NOT USE Lookup data type fields, for just that (and other) reasons.

If you must keep the lookup field, you'll have to modify your parameter
query to be looking for the actually-stored value.

Regards

Jeff Boyce
Microsoft Office/Access MVP


KneeDown2Up said:
What am I doing wrong? I have a table called 'Enrolments' which among
other
columns, has a lookup column called 'Level' which looks up a table called
'NTPLevels' and the column in that called 'Level'. I want to be able to
run a
query that pulls only those names within a set 'Level' (in this case Level
1-2). The 'Level' column is in 'Text' format throughout, but every time I
run
it with the WHERE statement it comes up with nothing, if I take the WHERE
statement out it gives me all of the records.

This is my SQL;

SELECT LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First Name] AS
Name, Enrolments.Level
FROM Enrolments
WHERE (((LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First Name])
Is Not Null) AND ((Enrolments.Level) Is Not Null)
AND ((Enrolments.Surname) Is Not Null)) AND ((Enrolments.Level) = '1-2')
ORDER BY Enrolments.Surname;

Thanks in advance.
Click to expand...
Click to expand...
 
(there's good news and bad news...)

The good news is that you CAN see what the stored values are. The bad news
is how many hoops you have to jump through to see them. The really bad news
is you HAVE to see them to make the query run the way you are describing.

The solution is to NOT use lookup fields in your tables. Instead, store
(and display) the foreign key value. Then, when you want to use a query,
join the parent and child tables in the query, select the child table's
"display" field (not the ID, the "value"), and do your parameter query
against THAT field.

Pay me now or pay me later...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


KneeDown2Up said:
Ok, thanks Jeff. Yes, that makes sense, so is there a way of seeing what
the
actual stored value is and if so, could I then Query' it to include this
data
to get the results?

Jeff Boyce said:
If I'm understanding correctly, you have a table that has a "lookup"
datatype field. (One of ) the problem(s) with this data type is that it
stores one value, but displays something else.

So, for example, if you are trying to do a parameter query that uses the
displayed value (rather than what Access actually stores), you won't get
any
matches... oh wait?! that's what you just described, right?!

The solution you'll find recommended in the tablesdbdesign newsgroup is
to
NOT USE Lookup data type fields, for just that (and other) reasons.

If you must keep the lookup field, you'll have to modify your parameter
query to be looking for the actually-stored value.

Regards

Jeff Boyce
Microsoft Office/Access MVP


KneeDown2Up said:
What am I doing wrong? I have a table called 'Enrolments' which among
other
columns, has a lookup column called 'Level' which looks up a table
called
'NTPLevels' and the column in that called 'Level'. I want to be able to
run a
query that pulls only those names within a set 'Level' (in this case
Level
1-2). The 'Level' column is in 'Text' format throughout, but every time
I
run
it with the WHERE statement it comes up with nothing, if I take the
WHERE
statement out it gives me all of the records.

This is my SQL;

SELECT LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First Name]
AS
Name, Enrolments.Level
FROM Enrolments
WHERE (((LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First
Name])
Is Not Null) AND ((Enrolments.Level) Is Not Null)
AND ((Enrolments.Surname) Is Not Null)) AND ((Enrolments.Level) =
'1-2')
ORDER BY Enrolments.Surname;

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Ok Thanks Jeff. Being a newbie on this, not quite sure how to do this
exactly. I know I get the ID field if I query against a 'lookup' value.
Please could you explain how I get to what you're suggesting.

Jeff Boyce said:
(there's good news and bad news...)

The good news is that you CAN see what the stored values are. The bad news
is how many hoops you have to jump through to see them. The really bad news
is you HAVE to see them to make the query run the way you are describing.

The solution is to NOT use lookup fields in your tables. Instead, store
(and display) the foreign key value. Then, when you want to use a query,
join the parent and child tables in the query, select the child table's
"display" field (not the ID, the "value"), and do your parameter query
against THAT field.

Pay me now or pay me later...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


KneeDown2Up said:
Ok, thanks Jeff. Yes, that makes sense, so is there a way of seeing what
the
actual stored value is and if so, could I then Query' it to include this
data
to get the results?

Jeff Boyce said:
If I'm understanding correctly, you have a table that has a "lookup"
datatype field. (One of ) the problem(s) with this data type is that it
stores one value, but displays something else.

So, for example, if you are trying to do a parameter query that uses the
displayed value (rather than what Access actually stores), you won't get
any
matches... oh wait?! that's what you just described, right?!

The solution you'll find recommended in the tablesdbdesign newsgroup is
to
NOT USE Lookup data type fields, for just that (and other) reasons.

If you must keep the lookup field, you'll have to modify your parameter
query to be looking for the actually-stored value.

Regards

Jeff Boyce
Microsoft Office/Access MVP


What am I doing wrong? I have a table called 'Enrolments' which among
other
columns, has a lookup column called 'Level' which looks up a table
called
'NTPLevels' and the column in that called 'Level'. I want to be able to
run a
query that pulls only those names within a set 'Level' (in this case
Level
1-2). The 'Level' column is in 'Text' format throughout, but every time
I
run
it with the WHERE statement it comes up with nothing, if I take the
WHERE
statement out it gives me all of the records.

This is my SQL;

SELECT LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First Name]
AS
Name, Enrolments.Level
FROM Enrolments
WHERE (((LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First
Name])
Is Not Null) AND ((Enrolments.Level) Is Not Null)
AND ((Enrolments.Surname) Is Not Null)) AND ((Enrolments.Level) =
'1-2')
ORDER BY Enrolments.Surname;

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
It will probably take some work, depending on how much you've already built
upon that lookup field.

You can change the table's field from a lookup type to a (?whatever the
underlying ID type is) in table design mode. Highlight the field and check
the tabs in the lower window to make the change.

If you have any forms based on the table (or on queries thereof), and if
you have any controls based on the lookup field, you'll need to convert
it/those to combo boxes and do a "lookup" against the table that really
holds the ID and value.

The same will apply if you have any reports using the field...

Regards

Jeff Boyce
Microsoft Office/Access MVP


KneeDown2Up said:
Ok Thanks Jeff. Being a newbie on this, not quite sure how to do this
exactly. I know I get the ID field if I query against a 'lookup' value.
Please could you explain how I get to what you're suggesting.

Jeff Boyce said:
(there's good news and bad news...)

The good news is that you CAN see what the stored values are. The bad
news
is how many hoops you have to jump through to see them. The really bad
news
is you HAVE to see them to make the query run the way you are describing.

The solution is to NOT use lookup fields in your tables. Instead, store
(and display) the foreign key value. Then, when you want to use a query,
join the parent and child tables in the query, select the child table's
"display" field (not the ID, the "value"), and do your parameter query
against THAT field.

Pay me now or pay me later...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


KneeDown2Up said:
Ok, thanks Jeff. Yes, that makes sense, so is there a way of seeing
what
the
actual stored value is and if so, could I then Query' it to include
this
data
to get the results?

:

If I'm understanding correctly, you have a table that has a "lookup"
datatype field. (One of ) the problem(s) with this data type is that
it
stores one value, but displays something else.

So, for example, if you are trying to do a parameter query that uses
the
displayed value (rather than what Access actually stores), you won't
get
any
matches... oh wait?! that's what you just described, right?!

The solution you'll find recommended in the tablesdbdesign newsgroup
is
to
NOT USE Lookup data type fields, for just that (and other) reasons.

If you must keep the lookup field, you'll have to modify your
parameter
query to be looking for the actually-stored value.

Regards

Jeff Boyce
Microsoft Office/Access MVP


What am I doing wrong? I have a table called 'Enrolments' which
among
other
columns, has a lookup column called 'Level' which looks up a table
called
'NTPLevels' and the column in that called 'Level'. I want to be able
to
run a
query that pulls only those names within a set 'Level' (in this case
Level
1-2). The 'Level' column is in 'Text' format throughout, but every
time
I
run
it with the WHERE statement it comes up with nothing, if I take the
WHERE
statement out it gives me all of the records.

This is my SQL;

SELECT LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First
Name]
AS
Name, Enrolments.Level
FROM Enrolments
WHERE (((LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First
Name])
Is Not Null) AND ((Enrolments.Level) Is Not Null)
AND ((Enrolments.Surname) Is Not Null)) AND ((Enrolments.Level) =
'1-2')
ORDER BY Enrolments.Surname;

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Yes, ok I think I'm with you - does this mean that I'm incorporating the
table I once looked up, into the table from where I looked? I was trying to
keep the info split as I thought it was better (for speed) to keep the data
across several tables as opposed to all in one?
Thanks for your help.

Jeff Boyce said:
It will probably take some work, depending on how much you've already built
upon that lookup field.

You can change the table's field from a lookup type to a (?whatever the
underlying ID type is) in table design mode. Highlight the field and check
the tabs in the lower window to make the change.

If you have any forms based on the table (or on queries thereof), and if
you have any controls based on the lookup field, you'll need to convert
it/those to combo boxes and do a "lookup" against the table that really
holds the ID and value.

The same will apply if you have any reports using the field...

Regards

Jeff Boyce
Microsoft Office/Access MVP


KneeDown2Up said:
Ok Thanks Jeff. Being a newbie on this, not quite sure how to do this
exactly. I know I get the ID field if I query against a 'lookup' value.
Please could you explain how I get to what you're suggesting.

Jeff Boyce said:
(there's good news and bad news...)

The good news is that you CAN see what the stored values are. The bad
news
is how many hoops you have to jump through to see them. The really bad
news
is you HAVE to see them to make the query run the way you are describing.

The solution is to NOT use lookup fields in your tables. Instead, store
(and display) the foreign key value. Then, when you want to use a query,
join the parent and child tables in the query, select the child table's
"display" field (not the ID, the "value"), and do your parameter query
against THAT field.

Pay me now or pay me later...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


Ok, thanks Jeff. Yes, that makes sense, so is there a way of seeing
what
the
actual stored value is and if so, could I then Query' it to include
this
data
to get the results?

:

If I'm understanding correctly, you have a table that has a "lookup"
datatype field. (One of ) the problem(s) with this data type is that
it
stores one value, but displays something else.

So, for example, if you are trying to do a parameter query that uses
the
displayed value (rather than what Access actually stores), you won't
get
any
matches... oh wait?! that's what you just described, right?!

The solution you'll find recommended in the tablesdbdesign newsgroup
is
to
NOT USE Lookup data type fields, for just that (and other) reasons.

If you must keep the lookup field, you'll have to modify your
parameter
query to be looking for the actually-stored value.

Regards

Jeff Boyce
Microsoft Office/Access MVP


What am I doing wrong? I have a table called 'Enrolments' which
among
other
columns, has a lookup column called 'Level' which looks up a table
called
'NTPLevels' and the column in that called 'Level'. I want to be able
to
run a
query that pulls only those names within a set 'Level' (in this case
Level
1-2). The 'Level' column is in 'Text' format throughout, but every
time
I
run
it with the WHERE statement it comes up with nothing, if I take the
WHERE
statement out it gives me all of the records.

This is my SQL;

SELECT LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First
Name]
AS
Name, Enrolments.Level
FROM Enrolments
WHERE (((LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First
Name])
Is Not Null) AND ((Enrolments.Level) Is Not Null)
AND ((Enrolments.Surname) Is Not Null)) AND ((Enrolments.Level) =
'1-2')
ORDER BY Enrolments.Surname;

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure I understood your statements, so I can't agree/disagree with
you.

To get the best of Access' features and functions, your data needs to be
well-normalized. I am suggesting that copying the same data (e.g., a
person's name) across many tables is a very bad idea in Access. In addition
to the unnecessary duplicate data storage, this approach also costs you in
all the code you have to develop to keep the values synchronized among the
many places/tables it's stored.

I am suggesting that you do NOT incorporate one table "into" another table.
Don't mess with the tables once you have your data storage defined. Work in
forms instead. They give you much better control over the interaction with
users, and you can still use a combobox to list the lookup values.

Regards

Jeff Boyce
Microsoft Office/Access MVP


KneeDown2Up said:
Yes, ok I think I'm with you - does this mean that I'm incorporating the
table I once looked up, into the table from where I looked? I was trying
to
keep the info split as I thought it was better (for speed) to keep the
data
across several tables as opposed to all in one?
Thanks for your help.

Jeff Boyce said:
It will probably take some work, depending on how much you've already
built
upon that lookup field.

You can change the table's field from a lookup type to a (?whatever the
underlying ID type is) in table design mode. Highlight the field and
check
the tabs in the lower window to make the change.

If you have any forms based on the table (or on queries thereof), and if
you have any controls based on the lookup field, you'll need to convert
it/those to combo boxes and do a "lookup" against the table that really
holds the ID and value.

The same will apply if you have any reports using the field...

Regards

Jeff Boyce
Microsoft Office/Access MVP


KneeDown2Up said:
Ok Thanks Jeff. Being a newbie on this, not quite sure how to do this
exactly. I know I get the ID field if I query against a 'lookup' value.
Please could you explain how I get to what you're suggesting.

:

(there's good news and bad news...)

The good news is that you CAN see what the stored values are. The bad
news
is how many hoops you have to jump through to see them. The really
bad
news
is you HAVE to see them to make the query run the way you are
describing.

The solution is to NOT use lookup fields in your tables. Instead,
store
(and display) the foreign key value. Then, when you want to use a
query,
join the parent and child tables in the query, select the child
table's
"display" field (not the ID, the "value"), and do your parameter query
against THAT field.

Pay me now or pay me later...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


Ok, thanks Jeff. Yes, that makes sense, so is there a way of seeing
what
the
actual stored value is and if so, could I then Query' it to include
this
data
to get the results?

:

If I'm understanding correctly, you have a table that has a
"lookup"
datatype field. (One of ) the problem(s) with this data type is
that
it
stores one value, but displays something else.

So, for example, if you are trying to do a parameter query that
uses
the
displayed value (rather than what Access actually stores), you
won't
get
any
matches... oh wait?! that's what you just described, right?!

The solution you'll find recommended in the tablesdbdesign
newsgroup
is
to
NOT USE Lookup data type fields, for just that (and other) reasons.

If you must keep the lookup field, you'll have to modify your
parameter
query to be looking for the actually-stored value.

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
What am I doing wrong? I have a table called 'Enrolments' which
among
other
columns, has a lookup column called 'Level' which looks up a
table
called
'NTPLevels' and the column in that called 'Level'. I want to be
able
to
run a
query that pulls only those names within a set 'Level' (in this
case
Level
1-2). The 'Level' column is in 'Text' format throughout, but
every
time
I
run
it with the WHERE statement it comes up with nothing, if I take
the
WHERE
statement out it gives me all of the records.

This is my SQL;

SELECT LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First
Name]
AS
Name, Enrolments.Level
FROM Enrolments
WHERE (((LTrim(UCase(Enrolments.Surname)) & " " &
Enrolments.[First
Name])
Is Not Null) AND ((Enrolments.Level) Is Not Null)
AND ((Enrolments.Surname) Is Not Null)) AND ((Enrolments.Level) =
'1-2')
ORDER BY Enrolments.Surname;

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Yes, that makes perfect sense, I understand where I was going wrong now - I
knew I had to have the 'spread' of tables but I had the 'suppliers' being
looked up from the main one - I see now that there is no need to, they are
linked via the relationships anyway. Jeff, thanks for your patience, it must
be hard work for someone with your knowledge trying to get through to people
like me! I got there in the end, thanks again.

Jeff Boyce said:
I'm not sure I understood your statements, so I can't agree/disagree with
you.

To get the best of Access' features and functions, your data needs to be
well-normalized. I am suggesting that copying the same data (e.g., a
person's name) across many tables is a very bad idea in Access. In addition
to the unnecessary duplicate data storage, this approach also costs you in
all the code you have to develop to keep the values synchronized among the
many places/tables it's stored.

I am suggesting that you do NOT incorporate one table "into" another table.
Don't mess with the tables once you have your data storage defined. Work in
forms instead. They give you much better control over the interaction with
users, and you can still use a combobox to list the lookup values.

Regards

Jeff Boyce
Microsoft Office/Access MVP


KneeDown2Up said:
Yes, ok I think I'm with you - does this mean that I'm incorporating the
table I once looked up, into the table from where I looked? I was trying
to
keep the info split as I thought it was better (for speed) to keep the
data
across several tables as opposed to all in one?
Thanks for your help.

Jeff Boyce said:
It will probably take some work, depending on how much you've already
built
upon that lookup field.

You can change the table's field from a lookup type to a (?whatever the
underlying ID type is) in table design mode. Highlight the field and
check
the tabs in the lower window to make the change.

If you have any forms based on the table (or on queries thereof), and if
you have any controls based on the lookup field, you'll need to convert
it/those to combo boxes and do a "lookup" against the table that really
holds the ID and value.

The same will apply if you have any reports using the field...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Ok Thanks Jeff. Being a newbie on this, not quite sure how to do this
exactly. I know I get the ID field if I query against a 'lookup' value.
Please could you explain how I get to what you're suggesting.

:

(there's good news and bad news...)

The good news is that you CAN see what the stored values are. The bad
news
is how many hoops you have to jump through to see them. The really
bad
news
is you HAVE to see them to make the query run the way you are
describing.

The solution is to NOT use lookup fields in your tables. Instead,
store
(and display) the foreign key value. Then, when you want to use a
query,
join the parent and child tables in the query, select the child
table's
"display" field (not the ID, the "value"), and do your parameter query
against THAT field.

Pay me now or pay me later...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


Ok, thanks Jeff. Yes, that makes sense, so is there a way of seeing
what
the
actual stored value is and if so, could I then Query' it to include
this
data
to get the results?

:

If I'm understanding correctly, you have a table that has a
"lookup"
datatype field. (One of ) the problem(s) with this data type is
that
it
stores one value, but displays something else.

So, for example, if you are trying to do a parameter query that
uses
the
displayed value (rather than what Access actually stores), you
won't
get
any
matches... oh wait?! that's what you just described, right?!

The solution you'll find recommended in the tablesdbdesign
newsgroup
is
to
NOT USE Lookup data type fields, for just that (and other) reasons.

If you must keep the lookup field, you'll have to modify your
parameter
query to be looking for the actually-stored value.

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
What am I doing wrong? I have a table called 'Enrolments' which
among
other
columns, has a lookup column called 'Level' which looks up a
table
called
'NTPLevels' and the column in that called 'Level'. I want to be
able
to
run a
query that pulls only those names within a set 'Level' (in this
case
Level
1-2). The 'Level' column is in 'Text' format throughout, but
every
time
I
run
it with the WHERE statement it comes up with nothing, if I take
the
WHERE
statement out it gives me all of the records.

This is my SQL;

SELECT LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First
Name]
AS
Name, Enrolments.Level
FROM Enrolments
WHERE (((LTrim(UCase(Enrolments.Surname)) & " " &
Enrolments.[First
Name])
Is Not Null) AND ((Enrolments.Level) Is Not Null)
AND ((Enrolments.Surname) Is Not Null)) AND ((Enrolments.Level) =
'1-2')
ORDER BY Enrolments.Surname;

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Right - now the next problem that that's giving me! How do I get one table
(orders) to recognise the supplier (from a seperate table)? I have a form
with basically all the info going to one table, except now the suppliers,
which derives from another table. When I want to see the subform, which
includes the supplier, it only shows all the suppliers in the order that they
are in, not with any correlation to the orders placed. I guess it's something
simple but I'm buggered if I can see how to do it!

KneeDown2Up said:
Yes, that makes perfect sense, I understand where I was going wrong now - I
knew I had to have the 'spread' of tables but I had the 'suppliers' being
looked up from the main one - I see now that there is no need to, they are
linked via the relationships anyway. Jeff, thanks for your patience, it must
be hard work for someone with your knowledge trying to get through to people
like me! I got there in the end, thanks again.

Jeff Boyce said:
I'm not sure I understood your statements, so I can't agree/disagree with
you.

To get the best of Access' features and functions, your data needs to be
well-normalized. I am suggesting that copying the same data (e.g., a
person's name) across many tables is a very bad idea in Access. In addition
to the unnecessary duplicate data storage, this approach also costs you in
all the code you have to develop to keep the values synchronized among the
many places/tables it's stored.

I am suggesting that you do NOT incorporate one table "into" another table.
Don't mess with the tables once you have your data storage defined. Work in
forms instead. They give you much better control over the interaction with
users, and you can still use a combobox to list the lookup values.

Regards

Jeff Boyce
Microsoft Office/Access MVP


KneeDown2Up said:
Yes, ok I think I'm with you - does this mean that I'm incorporating the
table I once looked up, into the table from where I looked? I was trying
to
keep the info split as I thought it was better (for speed) to keep the
data
across several tables as opposed to all in one?
Thanks for your help.

:

It will probably take some work, depending on how much you've already
built
upon that lookup field.

You can change the table's field from a lookup type to a (?whatever the
underlying ID type is) in table design mode. Highlight the field and
check
the tabs in the lower window to make the change.

If you have any forms based on the table (or on queries thereof), and if
you have any controls based on the lookup field, you'll need to convert
it/those to combo boxes and do a "lookup" against the table that really
holds the ID and value.

The same will apply if you have any reports using the field...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Ok Thanks Jeff. Being a newbie on this, not quite sure how to do this
exactly. I know I get the ID field if I query against a 'lookup' value.
Please could you explain how I get to what you're suggesting.

:

(there's good news and bad news...)

The good news is that you CAN see what the stored values are. The bad
news
is how many hoops you have to jump through to see them. The really
bad
news
is you HAVE to see them to make the query run the way you are
describing.

The solution is to NOT use lookup fields in your tables. Instead,
store
(and display) the foreign key value. Then, when you want to use a
query,
join the parent and child tables in the query, select the child
table's
"display" field (not the ID, the "value"), and do your parameter query
against THAT field.

Pay me now or pay me later...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


Ok, thanks Jeff. Yes, that makes sense, so is there a way of seeing
what
the
actual stored value is and if so, could I then Query' it to include
this
data
to get the results?

:

If I'm understanding correctly, you have a table that has a
"lookup"
datatype field. (One of ) the problem(s) with this data type is
that
it
stores one value, but displays something else.

So, for example, if you are trying to do a parameter query that
uses
the
displayed value (rather than what Access actually stores), you
won't
get
any
matches... oh wait?! that's what you just described, right?!

The solution you'll find recommended in the tablesdbdesign
newsgroup
is
to
NOT USE Lookup data type fields, for just that (and other) reasons.

If you must keep the lookup field, you'll have to modify your
parameter
query to be looking for the actually-stored value.

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
What am I doing wrong? I have a table called 'Enrolments' which
among
other
columns, has a lookup column called 'Level' which looks up a
table
called
'NTPLevels' and the column in that called 'Level'. I want to be
able
to
run a
query that pulls only those names within a set 'Level' (in this
case
Level
1-2). The 'Level' column is in 'Text' format throughout, but
every
time
I
run
it with the WHERE statement it comes up with nothing, if I take
the
WHERE
statement out it gives me all of the records.

This is my SQL;

SELECT LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First
Name]
AS
Name, Enrolments.Level
FROM Enrolments
WHERE (((LTrim(UCase(Enrolments.Surname)) & " " &
Enrolments.[First
Name])
Is Not Null) AND ((Enrolments.Level) Is Not Null)
AND ((Enrolments.Surname) Is Not Null)) AND ((Enrolments.Level) =
'1-2')
ORDER BY Enrolments.Surname;

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Not hard at all ... it's all relative. I have folks I turn to when I'm
stumped...

Regards

Jeff Boyce
Microsoft Office/Access MVP

KneeDown2Up said:
Yes, that makes perfect sense, I understand where I was going wrong now -
I
knew I had to have the 'spread' of tables but I had the 'suppliers' being
looked up from the main one - I see now that there is no need to, they are
linked via the relationships anyway. Jeff, thanks for your patience, it
must
be hard work for someone with your knowledge trying to get through to
people
like me! I got there in the end, thanks again.

Jeff Boyce said:
I'm not sure I understood your statements, so I can't agree/disagree with
you.

To get the best of Access' features and functions, your data needs to be
well-normalized. I am suggesting that copying the same data (e.g., a
person's name) across many tables is a very bad idea in Access. In
addition
to the unnecessary duplicate data storage, this approach also costs you
in
all the code you have to develop to keep the values synchronized among
the
many places/tables it's stored.

I am suggesting that you do NOT incorporate one table "into" another
table.
Don't mess with the tables once you have your data storage defined. Work
in
forms instead. They give you much better control over the interaction
with
users, and you can still use a combobox to list the lookup values.

Regards

Jeff Boyce
Microsoft Office/Access MVP


KneeDown2Up said:
Yes, ok I think I'm with you - does this mean that I'm incorporating
the
table I once looked up, into the table from where I looked? I was
trying
to
keep the info split as I thought it was better (for speed) to keep the
data
across several tables as opposed to all in one?
Thanks for your help.

:

It will probably take some work, depending on how much you've already
built
upon that lookup field.

You can change the table's field from a lookup type to a (?whatever
the
underlying ID type is) in table design mode. Highlight the field and
check
the tabs in the lower window to make the change.

If you have any forms based on the table (or on queries thereof), and
if
you have any controls based on the lookup field, you'll need to
convert
it/those to combo boxes and do a "lookup" against the table that
really
holds the ID and value.

The same will apply if you have any reports using the field...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Ok Thanks Jeff. Being a newbie on this, not quite sure how to do
this
exactly. I know I get the ID field if I query against a 'lookup'
value.
Please could you explain how I get to what you're suggesting.

:

(there's good news and bad news...)

The good news is that you CAN see what the stored values are. The
bad
news
is how many hoops you have to jump through to see them. The really
bad
news
is you HAVE to see them to make the query run the way you are
describing.

The solution is to NOT use lookup fields in your tables. Instead,
store
(and display) the foreign key value. Then, when you want to use a
query,
join the parent and child tables in the query, select the child
table's
"display" field (not the ID, the "value"), and do your parameter
query
against THAT field.

Pay me now or pay me later...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
Ok, thanks Jeff. Yes, that makes sense, so is there a way of
seeing
what
the
actual stored value is and if so, could I then Query' it to
include
this
data
to get the results?

:

If I'm understanding correctly, you have a table that has a
"lookup"
datatype field. (One of ) the problem(s) with this data type is
that
it
stores one value, but displays something else.

So, for example, if you are trying to do a parameter query that
uses
the
displayed value (rather than what Access actually stores), you
won't
get
any
matches... oh wait?! that's what you just described, right?!

The solution you'll find recommended in the tablesdbdesign
newsgroup
is
to
NOT USE Lookup data type fields, for just that (and other)
reasons.

If you must keep the lookup field, you'll have to modify your
parameter
query to be looking for the actually-stored value.

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
What am I doing wrong? I have a table called 'Enrolments'
which
among
other
columns, has a lookup column called 'Level' which looks up a
table
called
'NTPLevels' and the column in that called 'Level'. I want to
be
able
to
run a
query that pulls only those names within a set 'Level' (in
this
case
Level
1-2). The 'Level' column is in 'Text' format throughout, but
every
time
I
run
it with the WHERE statement it comes up with nothing, if I
take
the
WHERE
statement out it gives me all of the records.

This is my SQL;

SELECT LTrim(UCase(Enrolments.Surname)) & " " &
Enrolments.[First
Name]
AS
Name, Enrolments.Level
FROM Enrolments
WHERE (((LTrim(UCase(Enrolments.Surname)) & " " &
Enrolments.[First
Name])
Is Not Null) AND ((Enrolments.Level) Is Not Null)
AND ((Enrolments.Surname) Is Not Null)) AND
((Enrolments.Level) =
'1-2')
ORDER BY Enrolments.Surname;

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Consider re-posting this as a new thread, perhaps in the "forms" newsgroup.
That way, a lot more eyes (and brains) can have a go at it. Down here,
buried inside this thread, not nearly so many folks will be spotting it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

KneeDown2Up said:
Right - now the next problem that that's giving me! How do I get one table
(orders) to recognise the supplier (from a seperate table)? I have a form
with basically all the info going to one table, except now the suppliers,
which derives from another table. When I want to see the subform, which
includes the supplier, it only shows all the suppliers in the order that
they
are in, not with any correlation to the orders placed. I guess it's
something
simple but I'm buggered if I can see how to do it!

KneeDown2Up said:
Yes, that makes perfect sense, I understand where I was going wrong now -
I
knew I had to have the 'spread' of tables but I had the 'suppliers' being
looked up from the main one - I see now that there is no need to, they
are
linked via the relationships anyway. Jeff, thanks for your patience, it
must
be hard work for someone with your knowledge trying to get through to
people
like me! I got there in the end, thanks again.

Jeff Boyce said:
I'm not sure I understood your statements, so I can't agree/disagree
with
you.

To get the best of Access' features and functions, your data needs to
be
well-normalized. I am suggesting that copying the same data (e.g., a
person's name) across many tables is a very bad idea in Access. In
addition
to the unnecessary duplicate data storage, this approach also costs you
in
all the code you have to develop to keep the values synchronized among
the
many places/tables it's stored.

I am suggesting that you do NOT incorporate one table "into" another
table.
Don't mess with the tables once you have your data storage defined.
Work in
forms instead. They give you much better control over the interaction
with
users, and you can still use a combobox to list the lookup values.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Yes, ok I think I'm with you - does this mean that I'm incorporating
the
table I once looked up, into the table from where I looked? I was
trying
to
keep the info split as I thought it was better (for speed) to keep
the
data
across several tables as opposed to all in one?
Thanks for your help.

:

It will probably take some work, depending on how much you've
already
built
upon that lookup field.

You can change the table's field from a lookup type to a (?whatever
the
underlying ID type is) in table design mode. Highlight the field
and
check
the tabs in the lower window to make the change.

If you have any forms based on the table (or on queries thereof),
and if
you have any controls based on the lookup field, you'll need to
convert
it/those to combo boxes and do a "lookup" against the table that
really
holds the ID and value.

The same will apply if you have any reports using the field...

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
Ok Thanks Jeff. Being a newbie on this, not quite sure how to do
this
exactly. I know I get the ID field if I query against a 'lookup'
value.
Please could you explain how I get to what you're suggesting.

:

(there's good news and bad news...)

The good news is that you CAN see what the stored values are.
The bad
news
is how many hoops you have to jump through to see them. The
really
bad
news
is you HAVE to see them to make the query run the way you are
describing.

The solution is to NOT use lookup fields in your tables.
Instead,
store
(and display) the foreign key value. Then, when you want to use
a
query,
join the parent and child tables in the query, select the child
table's
"display" field (not the ID, the "value"), and do your parameter
query
against THAT field.

Pay me now or pay me later...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
Ok, thanks Jeff. Yes, that makes sense, so is there a way of
seeing
what
the
actual stored value is and if so, could I then Query' it to
include
this
data
to get the results?

:

If I'm understanding correctly, you have a table that has a
"lookup"
datatype field. (One of ) the problem(s) with this data type
is
that
it
stores one value, but displays something else.

So, for example, if you are trying to do a parameter query
that
uses
the
displayed value (rather than what Access actually stores), you
won't
get
any
matches... oh wait?! that's what you just described, right?!

The solution you'll find recommended in the tablesdbdesign
newsgroup
is
to
NOT USE Lookup data type fields, for just that (and other)
reasons.

If you must keep the lookup field, you'll have to modify your
parameter
query to be looking for the actually-stored value.

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
What am I doing wrong? I have a table called 'Enrolments'
which
among
other
columns, has a lookup column called 'Level' which looks up a
table
called
'NTPLevels' and the column in that called 'Level'. I want to
be
able
to
run a
query that pulls only those names within a set 'Level' (in
this
case
Level
1-2). The 'Level' column is in 'Text' format throughout, but
every
time
I
run
it with the WHERE statement it comes up with nothing, if I
take
the
WHERE
statement out it gives me all of the records.

This is my SQL;

SELECT LTrim(UCase(Enrolments.Surname)) & " " &
Enrolments.[First
Name]
AS
Name, Enrolments.Level
FROM Enrolments
WHERE (((LTrim(UCase(Enrolments.Surname)) & " " &
Enrolments.[First
Name])
Is Not Null) AND ((Enrolments.Level) Is Not Null)
AND ((Enrolments.Surname) Is Not Null)) AND
((Enrolments.Level) =
'1-2')
ORDER BY Enrolments.Surname;

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Jeff - yes thanks for that, I sussed it!!! It was down to the relationships -
(which still confuse me - so must have been pot luck) I just played around
with them and hey presto! Thanks again mate.

KneeDown2Up said:
Right - now the next problem that that's giving me! How do I get one table
(orders) to recognise the supplier (from a seperate table)? I have a form
with basically all the info going to one table, except now the suppliers,
which derives from another table. When I want to see the subform, which
includes the supplier, it only shows all the suppliers in the order that they
are in, not with any correlation to the orders placed. I guess it's something
simple but I'm buggered if I can see how to do it!

KneeDown2Up said:
Yes, that makes perfect sense, I understand where I was going wrong now - I
knew I had to have the 'spread' of tables but I had the 'suppliers' being
looked up from the main one - I see now that there is no need to, they are
linked via the relationships anyway. Jeff, thanks for your patience, it must
be hard work for someone with your knowledge trying to get through to people
like me! I got there in the end, thanks again.

Jeff Boyce said:
I'm not sure I understood your statements, so I can't agree/disagree with
you.

To get the best of Access' features and functions, your data needs to be
well-normalized. I am suggesting that copying the same data (e.g., a
person's name) across many tables is a very bad idea in Access. In addition
to the unnecessary duplicate data storage, this approach also costs you in
all the code you have to develop to keep the values synchronized among the
many places/tables it's stored.

I am suggesting that you do NOT incorporate one table "into" another table.
Don't mess with the tables once you have your data storage defined. Work in
forms instead. They give you much better control over the interaction with
users, and you can still use a combobox to list the lookup values.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Yes, ok I think I'm with you - does this mean that I'm incorporating the
table I once looked up, into the table from where I looked? I was trying
to
keep the info split as I thought it was better (for speed) to keep the
data
across several tables as opposed to all in one?
Thanks for your help.

:

It will probably take some work, depending on how much you've already
built
upon that lookup field.

You can change the table's field from a lookup type to a (?whatever the
underlying ID type is) in table design mode. Highlight the field and
check
the tabs in the lower window to make the change.

If you have any forms based on the table (or on queries thereof), and if
you have any controls based on the lookup field, you'll need to convert
it/those to combo boxes and do a "lookup" against the table that really
holds the ID and value.

The same will apply if you have any reports using the field...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Ok Thanks Jeff. Being a newbie on this, not quite sure how to do this
exactly. I know I get the ID field if I query against a 'lookup' value.
Please could you explain how I get to what you're suggesting.

:

(there's good news and bad news...)

The good news is that you CAN see what the stored values are. The bad
news
is how many hoops you have to jump through to see them. The really
bad
news
is you HAVE to see them to make the query run the way you are
describing.

The solution is to NOT use lookup fields in your tables. Instead,
store
(and display) the foreign key value. Then, when you want to use a
query,
join the parent and child tables in the query, select the child
table's
"display" field (not the ID, the "value"), and do your parameter query
against THAT field.

Pay me now or pay me later...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP


Ok, thanks Jeff. Yes, that makes sense, so is there a way of seeing
what
the
actual stored value is and if so, could I then Query' it to include
this
data
to get the results?

:

If I'm understanding correctly, you have a table that has a
"lookup"
datatype field. (One of ) the problem(s) with this data type is
that
it
stores one value, but displays something else.

So, for example, if you are trying to do a parameter query that
uses
the
displayed value (rather than what Access actually stores), you
won't
get
any
matches... oh wait?! that's what you just described, right?!

The solution you'll find recommended in the tablesdbdesign
newsgroup
is
to
NOT USE Lookup data type fields, for just that (and other) reasons.

If you must keep the lookup field, you'll have to modify your
parameter
query to be looking for the actually-stored value.

Regards

Jeff Boyce
Microsoft Office/Access MVP


message
What am I doing wrong? I have a table called 'Enrolments' which
among
other
columns, has a lookup column called 'Level' which looks up a
table
called
'NTPLevels' and the column in that called 'Level'. I want to be
able
to
run a
query that pulls only those names within a set 'Level' (in this
case
Level
1-2). The 'Level' column is in 'Text' format throughout, but
every
time
I
run
it with the WHERE statement it comes up with nothing, if I take
the
WHERE
statement out it gives me all of the records.

This is my SQL;

SELECT LTrim(UCase(Enrolments.Surname)) & " " & Enrolments.[First
Name]
AS
Name, Enrolments.Level
FROM Enrolments
WHERE (((LTrim(UCase(Enrolments.Surname)) & " " &
Enrolments.[First
Name])
Is Not Null) AND ((Enrolments.Level) Is Not Null)
AND ((Enrolments.Surname) Is Not Null)) AND ((Enrolments.Level) =
'1-2')
ORDER BY Enrolments.Surname;

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top