Solved

Query Help

Posted on 2014-02-20
18
264 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

16 Experts available now in Live!

Get 1:1 Help Now