3 queries into one

[desmur]

I have created three queries.
1 make table query
1 delete query and then another make table query. I have then created a macro do just do all the queries one after the other. The only problem is that I have parameters in each query to filter out certain dates. It is the same date for each query. Is there a way that I can have it ask for the date once and then it knows it for all three queries?

 

[ssecca]

ABSOLUTLY; Only don't use the macros use the modules and
VBA

Public function RunMyQueries()
Dim strMSG1 As String
Dim strMSG2 as string
Dim strInput1 As String
Dim strInput2 As String

strMSG1 = "Enter start date"
strMSG2 = "Enter end date"

strInput1 = InputBox(Prompt:=strMSG1, Title:="Start Date&quot
strInput2 = InputBox(Prompt:=strMSG2, Title:="End Date&quot

currentdb.execute("Create Table SQL statement from your first make table query goes here&quot

currentdb.execute("Delete From SQL statement from your delete query goes here&quot

currentdb.execute("Create Table SQL statement from your second make table query goes here&quot

End Function


Modify the queries to use strInput1 and strInput 2 in place of your current criteria.

Good Luck

 

[desmur]

Thanks for the hlep, but I'm still not catching on. Here is my code

Public Function RunMyQueries()
Dim strMSG1 As String
Dim strMSG2 As String
Dim strInput1 As String
Dim strInput2 As String

strMSG1 = "Enter start date"
strMSG2 = "Enter end date"

strInput1 = InputBox(Prompt:=strMSG1, Title:="Start Date&quot
strInput2 = InputBox(Prompt:=strMSG2, Title:="End Date&quot

CurrentDb.Execute (INSERT INTO tblcummhours ( emp_id, SumOfts_hrs, ts_pay_dte ) IN 'E:\cts 1-31\cochodat.mdb'
SELECT tblConsultant.emp_id, Sum(tblTimeSheet.ts_hrs) AS SumOfts_hrs, tblTimeSheet.ts_pay_dte AS [ending date]
FROM tblConsultant INNER JOIN tblTimeSheet ON tblConsultant.emp_id = tblTimeSheet.ts_emp_id
GROUP BY tblConsultant.emp_id, tblTimeSheet.ts_pay_dte HAVING (((tblTimeSheet.ts_pay_dte) = [enter ending date]))
ORDER BY Sum(tblTimeSheet.ts_hrs) DESC


CurrentDb.Execute ("DELETE cummtotalhrs.[ending Date]
FROM cummtotalhrs
WHERE (((cummtotalhrs.[ending Date])=[enter ending date])))
)

CurrentDb.Execute ("INSERT INTO cummtotalhrs ( emp_id, sumofts_hrs, [ending Date] ) IN 'e:/cts 1-31/cochodat.mdb'
SELECT tblcummhours.emp_id, Sum(tblcummhours.SumOfts_hrs) AS sumofts_hrs, [EndingDate] AS [ending Date]
FROM tblcummhours
GROUP BY tblcummhours.emp_id, [EndingDate];
&quot

End Function

Also, since I am now doing in in vba, how do I execute it?

 

[ssecca]

Your doing just fine. A couple of things need to be adjusted however.

The SQL string in the execute statement needs to be a string. That means it needs to be enclosed in quotes.

something like this...

"select * from tbl_Mytable"

Noew the problem is if you extend beyond a line you need to tell the procedure that the next line is part of this line you do that by doing the following...


"Select Field1, field2,field3" _
" From Tbl_MyTable"

The space followed by the "_" tells acces to concatinate the two lines in to one line of code. You can do this multiple lines but there is a finite number of lines around 30 if I remember correctly.


Now this is only part of the problem. The rest of the problem is getting your variables into to SQL string. Bacause the variable names are refering to a variable in the VBA procedure they can not be included inside the quotes so the way to do this is...


"Select Field1, field2,field3" _
" From Tbl_MyTable" _
" Where field1 =" & strInput1

Alright now this works great for one variable is numeric but not for a string. For a string use this syntax

"Select Field1, field2,field3" _
" From Tbl_MyTable" _
" Where field1 ='" & strInput1 & "'"

Alright don't give up you are almost there.

As to executing the procedure in the click event of a command button just enter the name of the procedure you want to run.


In your case ...>

runMyQueries

That is all it takes

Good Luck
ssecca

 

[desmur]

Thanks for your help, but I am now very confused.
What I did was make the sql on one line so I would not have to use the _ and ", but now it seems like you are saying that I have to get the strings inside the sql but not in the quotes?????? Here is my query again but even though it is show here on separate lines, it is not really on the separate lines.

Public Function RunMyQueries()
Dim strMSG1 As String
Dim strMSG2 As String
Dim strInput1 As String
Dim strInput2 As String

strMSG1 = "Enter pay date"
strMSG2 = "Enter end date"

strInput1 = InputBox(Prompt:=strMSG1, Title:="Start Date&quot
strInput2 = InputBox(Prompt:=strMSG2, Title:="End Date&quot

CurrentDb.Execute ("INSERT INTO tblcummhours ( emp_id, SumOfts_hrs, ts_pay_dte ) IN 'E:\cts 1-31\cochodat.mdb' SELECT tblConsultant.emp_id, Sum(tblTimeSheet.ts_hrs) AS SumOfts_hrs, tblTimeSheet.ts_pay_dte AS [ending date] FROM tblConsultant INNER JOIN tblTimeSheet ON tblConsultant.emp_id = tblTimeSheet.ts_emp_id GROUP BY tblConsultant.emp_id, tblTimeSheet.ts_pay_dte HAVING (((tblTimeSheet.ts_pay_dte) = [enter ending date])) ORDER BY Sum(tblTimeSheet.ts_hrs) DESC;&quot


CurrentDb.Execute ("DELETE cummtotalhrs.[ending Date]FROM cummtotalhrs WHERE (((cummtotalhrs.[ending Date])=[enter ending date])))&quot

CurrentDb.Execute ("INSERT INTO cummtotalhrs ( emp_id, sumofts_hrs, [ending Date] ) IN 'e:/cts 1-31/cochodat.mdb' SELECT tblcummhours.emp_id, Sum(tblcummhours.SumOfts_hrs) AS sumofts_hrs, [EndingDate] AS [ending Date] FROM tblcummhours GROUP BY tblcummhours.emp_id, [EndingDate]; &quot

End Function

 

[ssecca]

Quote 1

CurrentDb.Execute ("INSERT INTO tblcummhours ( emp_id, SumOfts_hrs, ts_pay_dte ) IN 'E:\cts 1-31\cochodat.mdb' SELECT tblConsultant.emp_id, Sum(tblTimeSheet.ts_hrs) AS SumOfts_hrs, tblTimeSheet.ts_pay_dte AS [ending date] FROM tblConsultant INNER JOIN tblTimeSheet ON tblConsultant.emp_id = tblTimeSheet.ts_emp_id GROUP BY tblConsultant.emp_id, tblTimeSheet.ts_pay_dte HAVING (((tblTimeSheet.ts_pay_dte) =#" & [enter ending date] "#)) ORDER BY Sum(tblTimeSheet.ts_hrs) DESC;&quot

QUOTE 2

CurrentDb.Execute ("DELETE cummtotalhrs.[ending Date]FROM cummtotalhrs WHERE (((cummtotalhrs.[ending Date])=#" & enter ending date] & "#)))&quot


Try these the third one looks OK. I've made the assumption that the date are data type date if that is not the case if they are strings then replace the "#" with a single quote.

Sorry to take so long to get back, Been sick.
ssecca