Re: WHERE statement
- From: "Jeff Boyce" <nonsense@xxxxxxxxxxxx>
- Date: Mon, 6 Nov 2006 08:34:31 -0800
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" <KneeDown2Up@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:007CE138-8F09-467D-A80D-ECF25859FB73@xxxxxxxxxxxxxxxx
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" wrote:
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" wrote:
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" <KneeDown2Up@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B3735D6F-960C-4BBE-A802-67A4B3487920@xxxxxxxxxxxxxxxx
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" wrote:
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" <KneeDown2Up@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:6849EC91-1FF3-4700-B089-56983176DDDF@xxxxxxxxxxxxxxxx
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" wrote:
(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" <KneeDown2Up@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:A330451E-2677-4F2E-B8CF-0C344F7AED59@xxxxxxxxxxxxxxxx
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" wrote:
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" <KneeDown2Up@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:712C55AB-CA9B-4135-89C2-62E91A1CA61D@xxxxxxxxxxxxxxxx
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.
.
- References:
- Re: WHERE statement
- From: KneeDown2Up
- Re: WHERE statement
- From: Jeff Boyce
- Re: WHERE statement
- From: KneeDown2Up
- Re: WHERE statement
- From: KneeDown2Up
- Re: WHERE statement
- Prev by Date: Re: WHERE statement
- Next by Date: Doing a Word 2003 Mail Merge from Access 2003
- Previous by thread: Re: WHERE statement
- Next by thread: Re: WHERE statement
- Index(es):
Relevant Pages
|
Loading
