Solved

Access VBA: Insert statement using a stored parameter query

Posted on 2013-01-16
5
853 Views
Last Modified: 2013-01-17
Hi.

In Access 2007, I want to insert rows into a table based on a saved parameter query that involves a union of many SELECT statements.

The query looks like this (+  6 more SELECTs):

SELECT
"DG1" As ["ID"],
Sum(IIf(Year([OrdrDate])=([Enter Year]-2) ,HDB.[GRQty],0)) AS ["prevYR2"],
Sum(IIf(Year([OrdrDate])=([Enter Year]-1) ,HDB.[GRQty],0)) AS ["prevYR"],
Sum(IIf(Year([OrdrDate])=([Enter Year]-1) AND Month([OrdrDate])<=([Enter Month]) ,HDB.[GRQty],0)) AS ["prevYTD"],
Sum(IIf(Year([OrdrDate])=([Enter Year]) AND Month([OrdrDate])<=([Enter Month]) ,HDB.[GRQty],0)) AS ["YTD"]
FROM HDB
WHERE
HDB.[GRQty] <= [Enter Bmax1] AND fmt = "DG"
GROUP BY 1

UNION SELECT
"DG2" As ["ID"],
Sum(IIf(Year([OrdrDate])=([Enter Year]-2) ,HDB.[GRQty],0)) AS ["prevYR2"],
Sum(IIf(Year([OrdrDate])=([Enter Year]-1) ,HDB.[GRQty],0)) AS ["prevYR"],
Sum(IIf(Year([OrdrDate])=([Enter Year]-1) AND Month([OrdrDate])<=([Enter Month]) ,HDB.[GRQty],0)) AS ["prevYTD"],
Sum(IIf(Year([OrdrDate])=([Enter Year]) AND Month([OrdrDate])<=([Enter Month]) ,HDB.[GRQty],0)) AS ["YTD"]
FROM HDB
WHERE
HDB.[GRQty] BETWEEN [Enter Bmin2] And [Enter Bmax2] AND fmt = "DG"
GROUP BY 1

which produces a tidy result set that I would like to insert into a summary table.

I'm having trouble approaching the logic of this -- both what is possible syntax-wise and what is most efficient.

- Is there a way to rewrite this SELECT into an INSERT query?
- Can you execute an INSERT statement  in VBA and call a stored query for the SELECT portion?
- Should I open this query into a recordset and then loop through some insert logic?


Many thanks.
0
Comment
Question by:bishopkd
  • 3
  • 2
5 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
something like, this is just the format,

you have to fill in the field names , represented by f1,f2,..fn  in the statement below

Insert into TableX(f1,f2,...Fn)
select a.f1,a.f2, ..a.fn
from
(
SELECT
"DG1" As ["ID"],
Sum(IIf(Year([OrdrDate])=([Enter Year]-2) ,HDB.[GRQty],0)) AS ["prevYR2"],
Sum(IIf(Year([OrdrDate])=([Enter Year]-1) ,HDB.[GRQty],0)) AS ["prevYR"],
Sum(IIf(Year([OrdrDate])=([Enter Year]-1) AND Month([OrdrDate])<=([Enter Month]) ,HDB.[GRQty],0)) AS ["prevYTD"],
Sum(IIf(Year([OrdrDate])=([Enter Year]) AND Month([OrdrDate])<=([Enter Month]) ,HDB.[GRQty],0)) AS ["YTD"]
FROM HDB
WHERE
HDB.[GRQty] <= [Enter Bmax1] AND fmt = "DG"
GROUP BY 1

UNION SELECT
"DG2" As ["ID"],
Sum(IIf(Year([OrdrDate])=([Enter Year]-2) ,HDB.[GRQty],0)) AS ["prevYR2"],
Sum(IIf(Year([OrdrDate])=([Enter Year]-1) ,HDB.[GRQty],0)) AS ["prevYR"],
Sum(IIf(Year([OrdrDate])=([Enter Year]-1) AND Month([OrdrDate])<=([Enter Month]) ,HDB.[GRQty],0)) AS ["prevYTD"],
Sum(IIf(Year([OrdrDate])=([Enter Year]) AND Month([OrdrDate])<=([Enter Month]) ,HDB.[GRQty],0)) AS ["YTD"]
FROM HDB
WHERE
HDB.[GRQty] BETWEEN [Enter Bmin2] And [Enter Bmax2] AND fmt = "DG"
GROUP BY 1
) as a
0
 

Author Comment

by:bishopkd
Comment Utility
Thanks. I was trying to make that more complicated.

After I define the parameters, what method do I use to execute the query?
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 500 total points
Comment Utility
in VBA,
dim strSQL as string

strSQL="Insert into tableName(<fieldnames here>)" & _
          " select etc

currentdb.execute strSQL, dbfailonerror  '<<<
0
 

Author Comment

by:bishopkd
Comment Utility
my obj.Execute was failing for data reasons. all is well now.

thanks so much for your help!
0
 

Author Closing Comment

by:bishopkd
Comment Utility
the syntax fixed my queries
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now