Slightly more complex SELECT query syntax in A97

[MLH]

I have a table (tblCorrespondence) holding records with fields like
[CorrespID], [VehicleJobID], [UserID], [OutType], [OutDate], [InType],
[InDate], etc...

About a dozen [OutType]'s are defined and I often use queries to
extract records of a given [OutType]. That's pretty easy.

But, recently, I wanted to list all [OutType] "15" letters, a few of
the above mentioned fields and one additional field: [OutDate] of
nother letter [OutType] "00" in the same row. That's the tough part.
It can't be simply built in the QBE grid - that much is for sure. The
[OutType] "00" correspondence record would have been created about 15
weeks earlier than the [OutType] "15" record. Listing it in the same
row is essential to my objective.

So, I'm hoping to extract a dynaset that looks something like this...
Select records where [OutType] = "15"
[CorrespID] [VehicleJobID] [UserID] [OutDate] [Type00OutDate]
159 195 17 10/15/05 7/1/05
164 200 17 10/17/05 7/3/05
168 210 17 10/22/05 7/6/05
and so on...

For each vehicle in vehicles table, there is only one correspondence
record for any given outbound letter type. This is quite difficult. I
would like to learn how this type of SQL is built. Am hoping that
someone in the NG is an SQL master and could point out some good
on-line reference material for building this kind of query. I don't
know what you call it other than a SELECT query. But it is definitely
in a different category that a simple

SELECT [CorrespID], [VehicleJobID], [OutDate], [OutType] FROM
tblCorrespondence WHERE [OutType]="00";

[MLH]

BTW, my first stab at it...

SELECT CorrespID, VehicleJobID, tblOutboundTypes.Title, OutType,
OutDate, DLookUp("[OutDate]","tblCorrespondence","[OutType]='00'") AS
260OutDate
FROM tblOutboundTypes INNER JOIN tblCorrespondence ON
tblOutboundTypes.OutType = OutType
WHERE OutType="01";

fell miserably short of being anywhere near right!

[Salad]

MLH wrote:

I have a table (tblCorrespondence) holding records with fields like
[CorrespID], [VehicleJobID], [UserID], [OutType], [OutDate], [InType],
[InDate], etc...

About a dozen [OutType]'s are defined and I often use queries to
extract records of a given [OutType]. That's pretty easy.

But, recently, I wanted to list all [OutType] "15" letters, a few of
the above mentioned fields and one additional field: [OutDate] of
nother letter [OutType] "00" in the same row. That's the tough part.
It can't be simply built in the QBE grid - that much is for sure. The
[OutType] "00" correspondence record would have been created about 15
weeks earlier than the [OutType] "15" record. Listing it in the same
row is essential to my objective.

So, I'm hoping to extract a dynaset that looks something like this...
Select records where [OutType] = "15"
[CorrespID] [VehicleJobID] [UserID] [OutDate] [Type00OutDate]
159 195 17 10/15/05 7/1/05
164 200 17 10/17/05 7/3/05
168 210 17 10/22/05 7/6/05
and so on...

For each vehicle in vehicles table, there is only one correspondence
record for any given outbound letter type. This is quite difficult. I
would like to learn how this type of SQL is built. Am hoping that
someone in the NG is an SQL master and could point out some good
on-line reference material for building this kind of query. I don't
know what you call it other than a SELECT query. But it is definitely
in a different category that a simple

SELECT [CorrespID], [VehicleJobID], [OutDate], [OutType] FROM
tblCorrespondence WHERE [OutType]="00";

If I wanted it to be fast, I would create two queries. The first query
would be Query00. It would select the CorrespID and whatever additional
fields required for "00" records and filter for type "00".

I would then create a new query and add tblCorrespondence and Query00.
Set a relationship between the two...perhaps make it a left join if some
records may not have a "00" record.

Although you are using 2 queries it will be much faster that using
Dlookup or subquery.

You can even make the type a parameter for added flexibility.

In your other post, you said Dlookup wasn't working right. You probably
needed to select the record where the type was "00" and the ID equal to
the current records ID. I think you left that out.

[MLH]

Thx, Salad. That was perfect. Here's the resultant SQL...

SELECT tblCorrespondence.CorrespID, tblCorrespondence.OutType,
tblCorrespondence.OutDate, Query00.CorrespID, Query00.OutDate
FROM tblCorrespondence INNER JOIN Query00 ON
tblCorrespondence.VehicleJobID = Query00.VehicleJobID
WHERE (((tblCorrespondence.OutType)="01"));

If I wanted it to be fast, I would create two queries. The first query
would be Query00. It would select the CorrespID and whatever additional
fields required for "00" records and filter for type "00".

I would then create a new query and add tblCorrespondence and Query00.
Set a relationship between the two...perhaps make it a left join if some
records may not have a "00" record.

Although you are using 2 queries it will be much faster that using
Dlookup or subquery.

You can even make the type a parameter for added flexibility.

Now that sounds interesting. I could list the outdate of the type "00"
letters in a dynaset of records of any other outype I wanted. Haven't
fooled with parameter queries, but this is a good case to try it on.
I'll have a go at it.

[MLH]

Well, the parameter query was a cinch...
SELECT tblCorrespondence.CorrespID, tblCorrespondence.OutType,
tblCorrespondence.OutDate, Query00.CorrespID, Query00.OutDate
FROM tblCorrespondence INNER JOIN Query00 ON
tblCorrespondence.VehicleJobID = Query00.VehicleJobID
WHERE (((tblCorrespondence.OutType)=[Which OutType?]) AND
((tblCorrespondence.OutDate) Is Not Null));

Thx for the suggestion. A question comes to mind, though. If I have
a form or report feeding off the parameter query, can I slip the query
its parameter somehow, eliminating the need for user input at the
keyboard? If I can, then I can use this query with a number of forms
and reports.

You can even make the type a parameter for added flexibility.

[MLH]

I found a 4-yr old post where Larry Linson discussed a work-around.
I can barely recall having toyed with this. Anyway, I don't think its
an easy thing to do. Larry would undoubtedly have mentioned any
simple solution.

[lylefair]

http://groups.google.ca/group/comp.d...ce8b7335c07ff4

[Salad]

MLH wrote:

Well, the parameter query was a cinch...
SELECT tblCorrespondence.CorrespID, tblCorrespondence.OutType,
tblCorrespondence.OutDate, Query00.CorrespID, Query00.OutDate
FROM tblCorrespondence INNER JOIN Query00 ON
tblCorrespondence.VehicleJobID = Query00.VehicleJobID
WHERE (((tblCorrespondence.OutType)=[Which OutType?]) AND
((tblCorrespondence.OutDate) Is Not Null));

Thx for the suggestion. A question comes to mind, though. If I have
a form or report feeding off the parameter query, can I slip the query
its parameter somehow, eliminating the need for user input at the
keyboard? If I can, then I can use this query with a number of forms
and reports.

You can even make the type a parameter for added flexibility.

You could try to store the types in a Vistible=False control. Then in
Query00 and the other query remove the [Enter Type] and do something like
Forms!MainForm!HiddenType
Doing it like that is explicitly defining the form tho. You can set the
parameter values prior to opening the query. Lyle provided a good example.

You could even create a function. But this would slow things down. If
you can handle the explicit, that would be best...drag the field to a
column and enter in the criteria
Forms!MainForm!HiddenType

[David W. Fenton]

Salad <oi*@vinegar.com> wrote in
news:SV***************@newsread2.news.pas.earthlin k.net:

Although you are using 2 queries it will be much faster that using
Dlookup or subquery.

Er, what subqueries will it be slower than?

If you use:

SELECT ...
FROM table JOIN [other SQL]. As othertable ON table.field =
othertable.field;

then it should be just as fast as two saved queries joined together.

"Virtual tables" (the ones where you take the SQL for a SELECT query
and put it between []. As alias), is optimized and very fast.

Indeed, most subqueries are very fast (except for correlated
subqueries, which *can't* be fast, since the subquery has to be
executed individually for each row).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:uv********************************@4ax.com:

Well, the parameter query was a cinch...
SELECT tblCorrespondence.CorrespID, tblCorrespondence.OutType,
tblCorrespondence.OutDate, Query00.CorrespID, Query00.OutDate
FROM tblCorrespondence INNER JOIN Query00 ON
tblCorrespondence.VehicleJobID = Query00.VehicleJobID
WHERE (((tblCorrespondence.OutType)=[Which OutType?]) AND
((tblCorrespondence.OutDate) Is Not Null));

Thx for the suggestion. A question comes to mind, though. If I
have a form or report feeding off the parameter query, can I slip
the query its parameter somehow, eliminating the need for user
input at the keyboard? If I can, then I can use this query with a
number of forms and reports.

To me, parameters make saved queries *LESS FLEXIBLE*, since they
can
only be used in contexts where you can set the parameters in code,
or the user has to fill them out.

It's just much easier to write the SQL on the fly with a virtual
table. Taking your SQL as starting point:

SELECT tblCorrespondence.CorrespID, tblCorrespondence.OutType,
tblCorrespondence.OutDate, Query00.CorrespID, Query00.OutDate
FROM tblCorrespondence INNER JOIN [SQL from QueryOO]. As Query00 ON
tblCorrespondence.VehicleJobID = Query00.VehicleJobID
WHERE (((tblCorrespondence.OutType)="00") AND
((tblCorrespondence.OutDate) Is Not Null));

will allow you to write the WHERE clause on the spot, it one single
SQL statement.

Of course, I don't quite understand what the hell you're doing
here,
as I don't see any reason why you'd couldn't just join the two base
tables and put criteria on the two tables. I think this is because
your explanation of the table structure involved was too hazy so I
never quite understood what you were going for.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc