• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

Query Help

Hi,

I have a query with an output.  

I want to insert the result of the query into a table.

here is my code

//Here I run the query

DoCmd.OpenQuery "Report2_MOS_CB_Count", acViewNormal, acEdit

//Here I want the result from the above query to be inserted into this table.

CurrentDb.Execute ("INSERT INTO Report_2_MTD_MOS_Containment( Dialed_Number, Call_Count, CreatedDate )" _
& "SELECT DATA_1, Counts, CreatedDate" _
 & " FROM Report_2_MOS_CB_Count ")

Thanks,
Lulu
0
lulu50
Asked:
lulu50
  • 11
  • 4
  • 2
  • +1
2 Solutions
 
SteveCommented:
If you have access to the Access database, the easiest way would be to open the "Report2_MOS_CB_Count" query and change it's type to "Append". It will ask you what table and let you enter the fields you want to append.
0
 
lulu50Author Commented:
I need to do it this way so I can pass parameter later to it.
0
 
SarahDaisy8Commented:
What is the purpose of opening the query before executing?  I see you have it opened in Edit mode.  I'm used to using the RunSQL command, so I'm not familiar with the execute command, though I know of it.  

When I run the Insert SQL command I just use the name of the query I've created much like you have up there.  I have never needed to open the query.  I'm assuming the base query is just a select query?  If you need to pass parameters you can do so within the select statement of the Insert Into.

StrSQL= "INSERT INTO Report_2_MTD_MOS_Containment( Dialed_Number, Call_Count, CreatedDate )" _
& "SELECT DATA_1, Counts, CreatedDate" _
 & " FROM Report_2_MOS_CB_Count "
Docmd.runsql strSQL 

Open in new window



-Sarah
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
lulu50Author Commented:
strSQL = "INSERT INTO Report_2_MTD_MOS_Containment( Dialed_Number, Call_Count, CreatedDate )" _
& "SELECT DATA_1, Counts, CreatedDate" _
 & " FROM Report2_MOS_CB_Count "
DoCmd.RunSQL strSQL


I have two date fields in the query Report2_MOS_CB_Count that user will get prompted for. [Enter Start Date] and [Enter End Date]

how can I pass them into my query?

strSQL = "INSERT INTO Report_2_MTD_MOS_Containment( Dialed_Number, Call_Count, CreatedDate )" _
& "SELECT DATA_1, Counts, CreatedDate" _
 & " FROM Report2_MOS_CB_Count  where [Enter Start Date] = #FromDate# and
[Enter End Date] = #ToDate#"
DoCmd.RunSQL strSQL
0
 
lulu50Author Commented:
like something like this


strSQL = "INSERT INTO Report_2_MTD_MOS_Containment( Dialed_Number, Call_Count, CreatedDate )" _
& "SELECT DATA_1, Counts, CreatedDate" _
 & " FROM Report2_MOS_CB_Count where [Enter start date] ='" & FromDate & "' and [Enter end date] = '" & ToDate & "'"
DoCmd.RunSQL strSQL

but I am still getting the prompt from my select query for the date range.
0
 
PatHartmanCommented:
There you are prompting again.

Why are you expecting the append query to not ask for the dates?  Unless you use the form field technique I have mentioned in many other responses to you, EVERY time you run the query, you will be prompted for the values.  That's what a prompt is.  It asks for a value whenever it needs it.  It doesn't store it for reference so it can use it later.  It asks every single time.  Opening the query first doesn't do anything for you except to cause you to be prompted twice.  Once when you open the query (which is totally unnecessary) and a second time when you run the append.

I will stop responding to your questions since apparently I don't have the answer you want.
0
 
lulu50Author Commented:
Hi,

I am not prompting!!!!

this is my code


Dim ToDate As Date
Dim FromDate As Date
Dim strSQL As String
FromDate = Me.FromDatetxt
ToDate = Me.ToDatetxt

 Dim MOS_ContainmentCount As Long: MOS_ContainmentCount = DCount("ID", "Report_2_MTD_MOS_Containment", _
         "((Report_2_MTD_MOS_Containment.CreatedDate)=#" & FromDate & "#)")

If [MOS_ContainmentCount] = 0 Then


strSQL = "INSERT INTO Report_2_MTD_MOS_Containment( Dialed_Number, Call_Count, CreatedDate )" _
& "SELECT DATA_1, Counts, CreatedDate" _
 & " FROM Report2_MOS_CB_Count where [Enter start date] ='" & FromDate & "' and [Enter end date] = '" & ToDate & "'"
DoCmd.RunSQL strSQL

the query has a date range
0
 
lulu50Author Commented:
Well Pat,

I understand what you are saying but I need examples.

Is there a way to pass my form date range to the query ?

I don't know how to do this?
0
 
SarahDaisy8Commented:
Hi Lulu,
Let's start with where this query is being opened.  Are you opening this query with a command button on a form?  Are there text boxes that capture the date the user wants?  If you must prompt, perhaps the Inputbox command might work.  You will need two.

-sarah
0
 
PatHartmanCommented:
You are not using pound signs to delimit your dates. Unless the select clause refers to Forms!xsss!ssss which points to a specific form control, you will be prompted.

You would have less trouble with syntax if you would use the QBE to build the queries.  Using QBE and referencing form controls, you will not have to worry about which data types need to be enclosed in quotes and which in pound signs and which don't require delimiters at all.

You are being prompted for [Enter start date] and [Enter end date] - what are they?  They should probably be the names of columns in your table.

Valid syntax is:
strSQL = "INSERT INTO Report_2_MTD_MOS_Containment( Dialed_Number, Call_Count, CreatedDate )" _
& "SELECT DATA_1, Counts, CreatedDate" _
 & " FROM Report2_MOS_CB_Count where [Enter start date] = #" & FromDate & "# and [Enter end date] = #" & ToDate & "#"

To put the form fields into the querydef (NOT into an SQL String in VBA)

FROM Report2_MOS_CB_Count where YourStartDate = Forms!YourForm! FromDate and YourEndDate =  Forms!YourForm!ToDate

Inside a querydef, you don't need the date delimiters because you are referencing controls on a form which Access knows are dates and will treat as dates.  When you create the SQL in VBA, you are creating a string and everything needs to be a string inside the string if that makes any sense to you.  This is a hard concept to grasp so I do understand the confusion is causes.

Aside from the incorrect delimiters, I think the problem with your query is the [Enter ...] and [Enter ...] they are not the names of columns in the recordsource and so Access assumes they are prompts and prompts you for them.

For future reference, "Prompt" has a specific meaning.  It means that something pops up and asks you to enter something in a little dialog box.  What you are attempting to do is to pass in a parameter value.  In a query, a parameter is like a prompt in that you are using it to communicate a variable but the difference is that it is silent because it is inside the query and the query engine doesn't have to ask for it.  It knows how to get it for itself.
0
 
lulu50Author Commented:
pat


this is the query that has [Enter start Date]  and [Enter end date]

how can I pass my form ?Startdate   ? EndDate

instead of prompting in this query

can I do here this

AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=#StartDate#




SELECT DATA_1.DATA_1, Sum(DATA_1.Count) AS Counts, [Enter start date] AS CreatedDate
FROM (SELECT Switch(AUD_MOS_IVR_LOG.DATA_1 In ('1718','8882881718'),'8882881718',AUD_MOS_IVR_LOG.DATA_1 In ('4876','4877','8008434876'),'8008434876',AUD_MOS_IVR_LOG.DATA_1 In ('7765','7766','8005437765'),'8005437765',AUD_MOS_IVR_LOG.DATA_1 In ('6583','6584','8773542583'),'8773542583',AUD_MOS_IVR_LOG.DATA_1 In ('3583','3584','8776712583','4583','4584','8774572583'),'8776712583',AUD_MOS_IVR_LOG.DATA_1 In ('5583','5584','8777902583'),'8777902583',AUD_MOS_IVR_LOG.DATA_1 In ('1530','1531','1532','8004229146'),'8004229146',True,'Error') AS DATA_1, Count(*) AS [Count] FROM AUD_MOS_IVR_LOG WHERE (((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=[Enter start date] And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<=[Enter end date]) AND ((AUD_MOS_IVR_LOG.RECORD_TYPE)='CB')) GROUP BY AUD_MOS_IVR_LOG.DATA_1)  AS DATA_1
GROUP BY DATA_1.DATA_1;
0
 
PatHartmanCommented:
You need to replace [Enter start date]  with Forms!yourForm!yourStartDate

The Forms! reference needs to the name of a control on an open form.  Once you do that, you can save the query as a querydef and run it by name rather than by executing an embedded SQL String.  I'm not sure why you are using a select in your From clause but if it works for you, then it's fine.  It will be hard to work with it in QBE view but you can use SQL View.
0
 
lulu50Author Commented:
I did

SELECT DATA_1.DATA_1, Sum(DATA_1.Count) AS Counts, [Forms]![FrmDailyExonyRpt]![FromDatetxt] AS CreatedDate
FROM (SELECT Switch(AUD_MOS_IVR_LOG.DATA_1 In ('1718','8882881718'),'8882881718',AUD_MOS_IVR_LOG.DATA_1 In ('4876','4877','8008434876'),'8008434876',AUD_MOS_IVR_LOG.DATA_1 In ('7765','7766','8005437765'),'8005437765',AUD_MOS_IVR_LOG.DATA_1 In ('6583','6584','8773542583'),'8773542583',AUD_MOS_IVR_LOG.DATA_1 In ('3583','3584','8776712583','4583','4584','8774572583'),'8776712583',AUD_MOS_IVR_LOG.DATA_1 In ('5583','5584','8777902583'),'8777902583',AUD_MOS_IVR_LOG.DATA_1 In ('1530','1531','1532','8004229146'),'8004229146',True,'Error') AS DATA_1, Count(*) AS [Count] FROM AUD_MOS_IVR_LOG WHERE (((AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)>=Forms!FrmDailyExonyRpt!FromDatetxt And (AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP)<=Forms!FrmDailyExonyRpt!ToDatetxt) And ((AUD_MOS_IVR_LOG.RECORD_TYPE)='CB')) GROUP BY AUD_MOS_IVR_LOG.DATA_1)  AS DATA_1
GROUP BY DATA_1.DATA_1;
0
 
lulu50Author Commented:
I changed my code now I am not getting any result

strSQL = "INSERT INTO Report_2_MTD_MOS_Containment( Dialed_Number, Call_Count, CreatedDate )" _
& "SELECT DATA_1, Counts, CreatedDate" _
& " FROM Report2_MOS_CB_Count where AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP = Forms!FrmDailyExonyRpt!FromDatetxt and AUD_MOS_IVR_LOG.DATE_AND_TIME_STAMP =  Forms!FrmDailyExonyRpt!ToDatetxt "
DoCmd.RunSQL strSQL

because my query is not reading the date range!
0
 
lulu50Author Commented:
how can I save the query as a querydef
0
 
lulu50Author Commented:
Dim qd As QueryDef
Set qd = CurrentDb.QueryDefs("Report2_MOS_CB_Count")

strSQL = "INSERT INTO Report_2_MTD_MOS_Containment( Dialed_Number, Call_Count, CreatedDate )" _
& "SELECT DATA_1, Counts, CreatedDate" _
& " FROM Report2_MOS_CB_Count "
DoCmd.RunSQL strSQL
0
 
PatHartmanCommented:
No.  Don't do it in code.  Use the query builder.  Open it up.  Switch to SQL view and paste in your SQL string without all the quotes and ampersands.
0
 
lulu50Author Commented:
Thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 11
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now