Slightly more complex SELECT query syntax in A97

[MLH]

I have a table (tblCorresponde nce) 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
tblCorresponden ce WHERE [OutType]="00";

[MLH]

Re: Slightly more complex SELECT query syntax in A97

BTW, my first stab at it...

SELECT CorrespID, VehicleJobID, tblOutboundType s.Title, OutType,
OutDate, DLookUp("[OutDate]","tblCorrespon dence","[OutType]='00'") AS
260OutDate
FROM tblOutboundType s INNER JOIN tblCorresponden ce ON
tblOutboundType s.OutType = OutType
WHERE OutType="01";

fell miserably short of being anywhere near right!

[Salad]

Re: Slightly more complex SELECT query syntax in A97

MLH wrote:[color=blue]
> I have a table (tblCorresponde nce) 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
> tblCorresponden ce WHERE [OutType]="00";[/color]

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 tblCorresponden ce 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]

Re: Slightly more complex SELECT query syntax in A97

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

SELECT tblCorresponden ce.CorrespID, tblCorresponden ce.OutType,
tblCorresponden ce.OutDate, Query00.Corresp ID, Query00.OutDate
FROM tblCorresponden ce INNER JOIN Query00 ON
tblCorresponden ce.VehicleJobID = Query00.Vehicle JobID
WHERE (((tblCorrespon dence.OutType)= "01"));

[color=blue]
>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 tblCorresponden ce 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.[/color]
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]

Re: Slightly more complex SELECT query syntax in A97

Well, the parameter query was a cinch...
SELECT tblCorresponden ce.CorrespID, tblCorresponden ce.OutType,
tblCorresponden ce.OutDate, Query00.Corresp ID, Query00.OutDate
FROM tblCorresponden ce INNER JOIN Query00 ON
tblCorresponden ce.VehicleJobID = Query00.Vehicle JobID
WHERE (((tblCorrespon dence.OutType)=[Which OutType?]) AND
((tblCorrespond ence.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.[color=blue]
>
>You can even make the type a parameter for added flexibility.[/color]

[MLH]

Re: Slightly more complex SELECT query syntax in A97

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]

Re: Slightly more complex SELECT query syntax in A97

http://groups.google.ca/group/comp.databases.ms-access/msg/86ce8b7335c07ff4

[Salad]

Re: Slightly more complex SELECT query syntax in A97

MLH wrote:
[color=blue]
> Well, the parameter query was a cinch...
> SELECT tblCorresponden ce.CorrespID, tblCorresponden ce.OutType,
> tblCorresponden ce.OutDate, Query00.Corresp ID, Query00.OutDate
> FROM tblCorresponden ce INNER JOIN Query00 ON
> tblCorresponden ce.VehicleJobID = Query00.Vehicle JobID
> WHERE (((tblCorrespon dence.OutType)=[Which OutType?]) AND
> ((tblCorrespond ence.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.
>[color=green]
>>You can even make the type a parameter for added flexibility.[/color][/color]

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]

Re: Slightly more complex SELECT query syntax in A97

Salad <oil@vinegar.co m> wrote in
news:SVWcf.377$ s14.307@newsrea d2.news.pas.ear thlink.net:
[color=blue]
> Although you are using 2 queries it will be much faster that using
> Dlookup or subquery.[/color]

Er, what subqueries will it be slower than?

If you use:

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

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]

Re: Slightly more complex SELECT query syntax in A97

MLH <CRCI@NorthStat e.net> wrote in
news:uv89n11ope sshrvoqne9re6v0 4mpp37a4v@4ax.c om:
[color=blue]
> Well, the parameter query was a cinch...
> SELECT tblCorresponden ce.CorrespID, tblCorresponden ce.OutType,
> tblCorresponden ce.OutDate, Query00.Corresp ID, Query00.OutDate
> FROM tblCorresponden ce INNER JOIN Query00 ON
> tblCorresponden ce.VehicleJobID = Query00.Vehicle JobID
> WHERE (((tblCorrespon dence.OutType)=[Which OutType?]) AND
> ((tblCorrespond ence.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.[/color]

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 tblCorresponden ce.CorrespID, tblCorresponden ce.OutType,
tblCorresponden ce.OutDate, Query00.Corresp ID, Query00.OutDate
FROM tblCorresponden ce INNER JOIN [SQL from QueryOO]. As Query00 ON
tblCorresponden ce.VehicleJobID = Query00.Vehicle JobID
WHERE (((tblCorrespon dence.OutType)= "00") AND
((tblCorrespond ence.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