Re: Query with lookup table

Tech-Archive recommends: Fix windows errors by optimizing your registry

I am still not sure what fields are in the Location_lookup
table. I think you said that there is an ID field and if
that's correct, then the ON clause should be:
ON Location_lookup.ID = [Drill information].Location
To confirm this, open the Location_lookup table in design
view and check the field names. Then switch the table to
*** view to see the values in the ID(?) field.

You should be using a WHERE clause not a HAVING clause.
Since you are not using an aggregate function, there is no
real need for the GROUP BY clause.

You probably want to use AND instead of OR, but I can't tell
without knowing exactly what you want the query do.

Putting this together with my suggestion, the query would
be:

SELECT [Drill information].DrillID,
[Drill information].Location,
[Drill information].Date
FROM Location_lookup INNER JOIN [Drill information]
ON Location_lookup.ID = [Drill information].Location
WHERE ([Drill information].Date
Between #1/1/2006# And #12/31/2006#)
AND (Location_lookup.Location="cvg")

Note that Date is a reserved word in Access and you may
eventually run into trouble with that name. Maybe
ReadingDate would be more appropriate?
--
Marsh
MVP [MS Access]

JudyT wrote:
My apologies...here is my query in sql view.

SELECT [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
FROM Location_lookup INNER JOIN [Drill information] ON
Location_lookup.Location = [Drill information].Location
GROUP BY [Drill information].DrillID, [Drill information].Location, [Drill
information].Date
HAVING ((([Drill information].Date) Between #1/1/2006# And #12/31/2006#)) OR
((([Drill information].Location)="cvg"));


"Marshall Barton" wrote:

JudyT wrote:
I am very new to this ACCESS database stuff. So I needs some help
understanding what you wrote.


Either change your criteria to use the location ID value

(Where do I change this in the query?

In the Location field's Criteria (i.e. the same place where
you are specifying the location's name).

When asking a question about a query, you should post a
Copy/Paste of your query's SQL view. Lacking that, I will
guess that your query looks something like:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location
FROM atable
WHERE atable.Location = "West Texas"

Assuming that the West Texas location has an ID of 123, the
change would be to use this instead:
WHERE atable.Location = 123


or change the query to join to the location table and add the
location name field to the query so you can use the name as
the criteria.

(I am not sure what is you mean with this part).

In this case the query would be similar to this:

SELECT atable.DrillID, atable.Shift, atable.Drill_Date,
atable.Drill_Identifiers, atable.Location,
[Location Table].LocationName
FROM atable INNER JOIN [Location Table]
ON atable.Location = [Location Table].[ID Location]
WHERE [Location Table].LocationName = "West Texas"

Because you probably have no idea what all that SQL means, I
suggest that you create a new query. Don't bother selecting
a table. As soon as you see the query design grid, use the
View menu to switch to SQL view. Then Copy/Paste the SQL
statement above over the top of whatever Access had inserted
automatically. Check each of my guesses at the table and
field names and correct them to the names you are actually
using. You can try to switch the query to data*** view to
see if it runs and, it it does, if it's returning the
correct data. Once the query is performing correctly,
switch to design view to see how it could have been
specified using the query design grid.

All this confusion is caused by your use of the lookup field
in the table. It probably would have been better if you had
left it as (or change it now) a simple text box display so
you could see what's actually in the field. Then it would
be more obvious how to set up your query.


"Marshall Barton" wrote:

JudyT wrote:

I have a table which has a field(column) as a lookup field from the lookup
table. I am trying to do a query on that field and for some odd reason it is
returning no records, not a null but an error like return. I can see the
data in the field, it is there, can someone help me?

Here is the table
DrillID Shift Drill_Date Drill_Identifiers Location-lookup field from

Location Table
ID Location-this field
I want to do a query that pulls specific data...ie. whether it was at one
buidlding or another.


With a look up field, what you see is **not** what you get.
Either change your criteria to use the location ID value or
change the query to join to the location table and add the
location name field to the query so you can use the name as
the criteria.


.

Quantcast