building a query using other queries

  • Thread starter Thread starter Paul James
  • Start date Start date
P

Paul James

I'm trying to build a query for the recordset of a report that will contain
three subreports. The four tables involved are related as follows:

tblLbranch <--- tblLicensee ---> tblAgent ---tblAgentBranch

So tblLicensee has a one to many relationship with both tblLbranch and
tblAgent, and tblAgent has a one to many with tblAgentBranch. The report
will be structured so that tblLicensee provides the data for the main form,
while the three other tables are contained in the three subforms.

The one field common to all four tables is LicenseeID.

The problem arises when I try to create the query for the main report. The
recordsets for each of the three subreports are of course based on criteria
applied to each table separately, and I've easily created those three
(sub)queries. But the recordsets for each of the subreports generally
involve different licensees; and I need the query for the main report to
produce a recordset that includes all of the licensees from the three
subreports. What it's doing at present, however, is displaying only those
licensees that are contained in ALL the subreports. In other words, if a
licensee exists in one subreport but not in another, that licensee doesn't
appear in the main report. I need all of them.

I know how to accomplish this using some append queries to create some
temporary records, then delete them, but this creates holes in the file that
need to be compressed, and if I could do this with a select query, I'd much
rather do that.

I'm a sql weenie, so I've tried to do this with the qbe grid. But it hasn't
worked so far. I'll try to diagram what I've done in the qbe grid, and
hopefully someone can tell me a way to modify it so it will work.

field1 field2 field3
subquery1 subquery2 subquery3

criteria1
criteria2
criteria3

I don't know how much of the foregoing layout will survive the reformatting
in the news server, but what I'm attempting to display is that I'm trying to
get the query to produce the records containing the LicenseeID from all 3
subreports by putting the criteria from each of the three (sub)queries on
separate criteria lines. The heading to the left of these criteria lines is
labeled "or," and I would have expected to thus include ALL the licensee
records from the subqueries. Instead, it's only displaying the LicenseeIDs
that are contained in all three underlying queries.

I would have expected this if I had put the three criteria on the same line
(since this would be an "AND" condition), but when I do that, I get no
records at all.

Can anyone tell me if there's a way to get the query for the main query to
produce records containing the LicenseeID fields contained in all three
subqueries?

Thanks in advance.

Paul
 
1) When using a temporary table, you can create a temporary database
and link to the temp table. This avoid most of the database bloat.

2) If you can, apply the criteria to the subqueries, before you join
them into the report query.

3) You may be able to create a complex set of criteria that works for
you:


is null is null is null
is null is null crit3
is null crit2 is null
is null crit2 crit3
crit1 is null is null
crit1 is null crit3
crit1 crit2 is null
crit1 crit2 crit3


(david)
 
david epsom dot com dot au said:
1) When using a temporary table, you can create a temporary database
and link to the temp table. This avoid most of the database bloat.

2) If you can, apply the criteria to the subqueries, before you join
them into the report query.

3) You may be able to create a complex set of criteria that works for
you:


is null is null is null
is null is null crit3
is null crit2 is null
is null crit2 crit3
crit1 is null is null
crit1 is null crit3
crit1 crit2 is null
crit1 crit2 crit3


(david)
Click to expand...
There is a new product called Super*SQL which allows you to build a
virtual clipboard of results from one query which can then be used in
another query. Don't know if it would help but it might be worth
looking into. I found it at www.sqlmagic.com.
 
Thanks, David.

Interesting idea about putting the temp table in a separate database.
You're right - it would solve the bloating problem.

Question: I know how to export an object like a table or query to an
external database, but how do you append records from a query to a table in
another database? Is there a way to write an append query that will do
this? Or is there a way to do it with VBA?
 
external database, but how do you append records from a query to a table
in
another database? Is there a way to write an append query that will do
Click to expand...

If it is just an ordinary Access database, you can simply qualify
the table name with the file name:

select * from [file].


You can do this in any kind of query: select, append, create table etc.

To manage tables in a different file, it is often easier to
create linked tables, because Access helps you handle linked
tables, but you can create linking queries (like the one shown
above) instead of linked tables.

(david)
 
David, you've turned on a light in a dark room. It never occurred to me
that you could create a separate database to contain temporary tables and
records, and control them with queries that include a file location spec.

Great information.

Thanks so much.
 
that you could create a separate database to contain temporary tables and

.... There is a performance hit, even with local tables ...

(david)
 
I know there's always a performance hit with linked tables, but in this case
it's well worth it, because we won't use it that often. But we've had
bloating problems - so the performance hit will be worth it to eliminate
those problems.

Paul
 
it's well worth it, because we won't use it that often. But we've had
Then this may be helpful:

'---------------
Option Explicit
Private Const mcModuleName = "mdlRP_FileOp"
Private Const MAX_PATH = 260

Private Declare Function GetTempFileName Lib "Kernel32" _
Alias "GetTempFileNameA" (ByVal lpszPath As String, _
ByVal lpPrefixString As String, ByVal wUnique As Long, _
ByVal lpTempFileName As String) As Long

Private Declare Function GetTempPath Lib "Kernel32" _
Alias "GetTempPathA" (ByVal nBufferLength As Long, _
ByVal lpBuffer As String) As Long

Public Function r__gfnTmpFolder() As String
' Returns the "TEMP" folder used for storing temporary files
' 2003/01/21 dlg right to attribution retained
Dim strFolder As String
Dim lngResult As Long

strFolder = String$(MAX_PATH, 0)
lngResult = GetTempPath(MAX_PATH, strFolder)

If lngResult <> 0 Then
r__gfnTmpFolder = left$(strFolder, InStr(strFolder, vbNullChar) - 1)
Else
r__gfnTmpFolder = vbNullString
End If

End Function


Public Function r__gfnTmpFile() As String
'2003/01/21 dlg right to attribution retained
Dim sfile As String
Dim sPath As String
Dim lngResult As Long

On Error GoTo standard_err

1000 sPath = String$(MAX_PATH, 0)
1010 sfile = String$(MAX_PATH, 0)

1020 lngResult = GetTempPath(MAX_PATH, sPath)
1030 If lngResult <> 0 Then
1040 sPath = left$(sPath, InStr(sPath, vbNullChar) - 1)
1050 lngResult = GetTempFileName(sPath, gsApp(), 0, sfile)
1060 If lngResult <> 0 Then
1070 sfile = left$(sfile, InStr(sfile, vbNullChar) - 1)
1080 End If
1090 End If

r__gfnTmpFile = sfile
Exit Function
standard_err:
gsbErr_Raise mcModuleName, "r__gfnTmpFile"
End Function

'-------------------

(david)
 
David - these functions you sent look very interesting, but you're a couple
of levels of expertise beyond me. It would be great if you could say a few
words about what they do, and how to use them. That is, where you'd put
them and what parameters you'd need to pass to them.

I'm not asking for complete detail, but even a few words about what they do,
along with a brief explanation about how to call them might shed enough
light so I could put them to good use.

Thanks again,

Paul
 
sorry !!! It's just to get a file name. As a curtesy to users,
I create temp files in the users temp folder. There are some
unresolved function references in there:
that's just my error handler
gsApp just returns the short name version of my application
name, used for building the temp file name (like VB temp
files were always called VBxxxx.TMP)

use like this: sFile = r__gfnTmpFile()

Nothing terribly critical - just useful utility functions
if you are using temp files.

(david)
 
Back
Top