Use SELECT...INTO as the source for a report?

[Chris]

I'm working on a report that has to aggregate a lot of information. The
columns for this report are driving me bonkers. I can come up with the
columns if I use multiple queries; however, combining the queries to return
one row for each line of the report just doesn't seem to be working out.

The SELECT...INTO statement sounds promising, but the documentation seems to
indicate that this statement is only used to make new tables. In a sense, I
need to create a temporary table and then use that table in a report.

Is this the way this kind of scenario is typically handled?

 

[Van T. Dinh]

SELECT ... INTO is an Action Query (the action of creating a new Table with
data) which cannot be used as the RecordSource for a Report.

A RecordSource for a Report needs to be a Selection Query, not an Action
Query.

One possibility is to use the SELECT ... INTO ... to create a temp. Table
and then use this temp. Table as the RecordSource for your Report. Remember
to delete the temp.Table when you finish with the Report. However, the
process is certainly not as efficient as creating a right Selection Query to
be used as the RecordSource for your Report.

 

[Ken Snell [MVP]]

Creating a table and then using that table as the source of data is a
standard option for ACCESS developers. Often, though, what is done is to
keep an empty copy of the table and fill it with data via an append query.
That way, the table will have the desired data types for the fields, names,
etc. Much more predictable.

You cannot use a maketable table (or any other action query) as the
RecordSource for a form or a report.

 

[Chris]

Thanks, guys. I realized the inaccuracy of the subject line right after I
sent the message. ;^)

A temporary table seems the way to go--at least, for now.

Something I hadn't considered: Won't the use of a temporary table--whether
kept around for when needed or not--cause the size of the MDB to grow out of
control? Seems like a compact database would need to be performed more often
than normal.

 

[Chris]

How do I fill a temporary table with an append query--when the fields in the
temporary table can only be populated with multiple queries? Seems like this
will have to be done programmatically, no?

 

[david epsom dot com dot au]

will have to be done programmatically, no?

Yes, programmatically, typically in the open event
of the report, right after the statement
codedb.execute "delete * from tbl_rpt_tmp"

Or, to avoid bloat, right after the statement
application.dbengine(0).begintrans

(put the .rollback statement in the report close event)

Another way to avoid bloat is to put the tmp
table in a linked database: instead of "delete *",
use:

vba.kill "c:\tmpdatabase.mdb"
vba.filecopy "c:\template.tmp", "c:\tmpdatabase.mdb"

(david)

 

[Sophie Guo [MSFT]]

Hello,

You may refer to the information in the "CREATE TABLE Statement" topic in
Access 2003 help file:

CREATE TABLE Statement

Syntax

CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH
COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL]
[index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

Remarks

When a TEMPORARY table is created it is visible only within the session in
which it was created. It is automatically deleted when the session is
terminated. Temporary tables can be accessed by more than one user.


You can use INSERT INTO Statement to insert data. For more information,
refer to the "INSERT INTO Statement" in the Access help file.

I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.