Solved

Access VBA: Insert statement using a stored parameter query

Posted on 2013-01-16
5
855 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
ID: 38784999
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
ID: 38788016
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
ID: 38788081
in VBA,
dim strSQL as string

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

currentdb.execute strSQL, dbfailonerror  '<<<
0
 

Author Comment

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

thanks so much for your help!
0
 

Author Closing Comment

by:bishopkd
ID: 38788142
the syntax fixed my queries
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

930 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

9 Experts available now in Live!

Get 1:1 Help Now