Query has taken its bat home

  • Thread starter Thread starter anthony.carter
  • Start date Start date
A

anthony.carter

Hi,

The database has a series of tables each of which starts off with the
same catagories of fields - location location ID and shift. It can be
done more efficiently, I know, but I want to walk before I try to run.

A query has been set up on all but one of these tables to return all
those locations and shifts that have not been visited in the last
year. It all went swimmingly until the last query / table when the
return keeps showing a blank (even though there are plenty of entries
that satisfy the criteria). The same fields are used as is the same
formula (even the same chanting of incantations) - all to no avail.

Furthermore, if I try to create one of the queries that already works
(giving it another name) it doesn't work either, but the original
does!

Could some kind soul please help me see the wood for the trees?

Thanks,

Regards,

TC
 
When asking about a query it is often best to post the SQL. Open the query,
click View > SQL, copy what you see, and paste it into a message, along with
a brief explanation (such as you have provided), and a description of what
went wrong.
You may be able to do some of your own detective work. Copy the SQL that
works and paste it into a Word document. Create another query that you
intend to be the same as the first one. If it doesn't work, copy and paste
the SQL into the same or a different Word document. Depending on the
version of Word there are ways of comparing the two documents, or you could
just look at them side by side. If one query works, and another query that
is intended to be identical does not, the SQL will be different.
 
WOW!!
That's practically sexy!

Thanks very much. Anyone care to hazard a guess as to why I'm
generating a different code even though I went through the same
creative process? The code below is from the working query (first) and
the non-working query (second). The difference is in the type of join.

SELECT [St/W].Station, [St/W].Watch, [St/W].[St/W]
FROM [St/W] LEFT JOIN [MOC Records Audit All] ON [St/W].[St/W] = [MOC
Records Audit All].[St/W]
WHERE ((([MOC Records Audit All].[St/W]) Is Null));


SELECT [St/W].Station, [St/W].Watch, [St/W].[St/W]
FROM [St/W] INNER JOIN [Scenario Observation] ON [St/W].[St/W] =
[Scenario Observation].[St/ W]
WHERE ((([Scenario Observation].[St/ W]) Is Null));


Thanks,

Regards,

TC
 
The join type is significant. In query design view the tables are joined by
a line. In the first query the line has an arrow on the end pointing to
[MOC Records Audit All]. Right click the line, and click Join Properties.
It can be a little tricky, so if you do not see a box with Join Properties
as a choice, try again. You can also edit the SQL directly, but the method
described will show you how the interface could have led you to the
difference between the two queries.

An Inner Join returns only those records in which the joined fields in both
tables are identical. A Left Join returns all of the records from [St/W],
and only the records in [MOC Records Audit All] where the joined fields are
identical. In the Join Properties dialog an Inner Join is the first choice,
and a Left Join is the second choice (I think).

You will be doing yourself a favor in the long run if you stick to
alphanumeric characters and underscores for names. Special characters such
as slashes may have other meanings, and can confuse the code. In any case,
spaces and special characters require that the field name always be placed
in brackets. Another point is that shorter field names can be easier to
work with in general (MOC_AudAll, or whatever is enough to identify it).

WOW!!
That's practically sexy!

Thanks very much. Anyone care to hazard a guess as to why I'm
generating a different code even though I went through the same
creative process? The code below is from the working query (first) and
the non-working query (second). The difference is in the type of join.

SELECT [St/W].Station, [St/W].Watch, [St/W].[St/W]
FROM [St/W] LEFT JOIN [MOC Records Audit All] ON [St/W].[St/W] = [MOC
Records Audit All].[St/W]
WHERE ((([MOC Records Audit All].[St/W]) Is Null));


SELECT [St/W].Station, [St/W].Watch, [St/W].[St/W]
FROM [St/W] INNER JOIN [Scenario Observation] ON [St/W].[St/W] =
[Scenario Observation].[St/ W]
WHERE ((([Scenario Observation].[St/ W]) Is Null));


Thanks,

Regards,

TC


When asking about a query it is often best to post the SQL. Open the
query,
click View > SQL, copy what you see, and paste it into a message, along
with
a brief explanation (such as you have provided), and a description of
what
went wrong.
You may be able to do some of your own detective work. Copy the SQL that
works and paste it into a Word document. Create another query that you
intend to be the same as the first one. If it doesn't work, copy and
paste
the SQL into the same or a different Word document. Depending on the
version of Word there are ways of comparing the two documents, or you
could
just look at them side by side. If one query works, and another query
that
is intended to be identical does not, the SQL will be different.










- Show quoted text -
Click to expand...
Click to expand...
 
Back
Top