Solved

Query Help

Posted on 2014-02-20
18
265 Views
Last Modified: 2014-02-21
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
Comment
Question by:lulu50
  • 11
  • 4
  • 2
  • +1
18 Comments
 
LVL 7

Expert Comment

by:Steve
ID: 39873783
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
 

Author Comment

by:lulu50
ID: 39873947
I need to do it this way so I can pass parameter later to it.
0
 
LVL 1

Assisted Solution

by:SarahDaisy8
SarahDaisy8 earned 250 total points
ID: 39874151
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
 

Author Comment

by:lulu50
ID: 39874281
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
 

Author Comment

by:lulu50
ID: 39874333
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39874542
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
 

Author Comment

by:lulu50
ID: 39874621
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
 

Author Comment

by:lulu50
ID: 39874636
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
 
LVL 1

Expert Comment

by:SarahDaisy8
ID: 39874794
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 39874824
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
 

Author Comment

by:lulu50
ID: 39874885
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39874938
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
 

Author Comment

by:lulu50
ID: 39874983
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
 

Author Comment

by:lulu50
ID: 39875008
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
 

Author Comment

by:lulu50
ID: 39875021
how can I save the query as a querydef
0
 

Author Comment

by:lulu50
ID: 39875059
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39875107
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
 

Author Closing Comment

by:lulu50
ID: 39878460
Thanks
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

895 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