Query on table with a lookup

From: Jen (jmiller_at_wbhq.com)
Date: 04/27/04 

Date: Tue, 27 Apr 2004 10:14:22 -0700

I created a table with a lookup field to another table.
I've got a module in which I am using a SQL statement
based on the table with the lookup. For example:

sSQL = "Select * From CorporateExtVMX where Dept = '" &
Dpt & "'"

Dept is the field that is the lookup.

When running this code, I get the error message that a
parameter is blank. So I took this bit of code and
created a Query with it. I got a popup box to fill in
Dept.

Now, using the the Query wizard, I was able to create a
Query and the where clause in the SQL ended up
being "where Departmet = 'ACCOUNTING'" Note that this
time Department is spelled out. Department is the field
that is being looked up in the Dept table. But in my
CorporateVMXExt table, the field is Dept.

I apologize if I'm confusing anyone. :)

Okay, so I copied the Query SQL and posted it in the
Module SQL, so now it is this:
sSQL = "Select * From CorporateExtVMX where Department
= '" & Dpt & "'"

And after that I was getting the error message that
Department is not a field in the table. And it's not!
It's a field in the lookup table.

I guess my question is, how am I supposed to write a SQL
query on a table in which one field is looking up a table
from another table? This is driving me nuts!

Thanks,
Jennifer

Relevant Pages

  • Re: Mutivalued datatypes considered harmful
    ... query return if you brought back the "hobbies" field in an sql query - say ... sql does not - so the mysql/oracle feature was never consided multi-vlaued. ... The extensions to ms-access are real and signcivnetly in this matter. ... transloates to a lookup table, the same would be done for ms-access. ...
    (comp.databases.pick)
  • Re: Mutivalued datatypes considered harmful
    ... query return if you brought back the "hobbies" field in an sql query - say ... sql does not - so the mysql/oracle feature was never consider multi-valued. ... The extensions to ms-access are real and significant in this matter. ... translates to a lookup table, the same would be done for ms-access. ...
    (comp.databases.pick)
  • Re: Using criteria in select query for recoding of variables
    ... find a way without the lookup table. ... The greatest book is probably the query designer, at least, for start. ... You can take a look at the generated SQL. ... or Skill, is a field, and if a table is a junction table, it is often made ...
    (microsoft.public.access.queries)
  • Re: Using criteria in select query for recoding of variables
    ... "Michel Walsh" wrote: ... I will try it as a means of learning about lookup applications. ... It will be my first time using a sql ... In other words your Tobe field is exactly ...
    (microsoft.public.access.queries)
  • Re: milter requirement [socket map for FEATURE(`ldap_routing)]
    ... I would suggest you customizing MIMEDefang.org milter. ... Mimedefang filtering script is written in perl so adding SQL queries via ... "TEMP" temporary problem with lookup ...
    (comp.mail.sendmail)