Creating SQL string

[ds2728]

Hello,

I have designed a query (qry2) in query design that uses both a table and another predefined query (qry1) which was also designed in query view.

Problem:

I am tring to convert the query into code. I do not know how to reference the predefined query object (qry1) in my final query (qry2).

When I set up my sql string and reference the predined query object using "[qry1].fieldname" in the sql string it works. But I would like to define qry1 in code and reference it instead of the predined object.

thanks

dave

 

[MichaelRed]

You need to just create a variable and reference the variable instead of the litteral.

instead of:

"[qry1].fieldname"

Try

... & MyQry & "." & MyFieldName & ...

Where MyQry is the variable which holds the name of the desired query and MyFieldName is the variable which holds the name of the desired field.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ds2728]

Michael,

I tried that and error message says "can not find input table or query in 'QRY2' "


I have tried "... INNER JOIN qry1 ON Donor.[Donar ID] = qry1.[Donar ID];"

I have tried " ...INNER JOIN " & qry1 & " ON Donor.[Donar ID] = " & qry1 & ".[Donar ID];"

where qry1 is the sql string that replaces the predined query object.

I have tried "dim qry1 as string"
I have tried "dim qry1 as querydef"

I hope I have described it clearly.

thanks,

dave

 

[MichaelRed]

It is NOT clear to me. Please post the SQL for the three queries invloved - and identify the "Stored" name of each.

A snippet of code where you are attempting to "Dynamically" set the queries would also be useful.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ds2728]

Michael,

My test sql strings are listed below.

My final resul is to be:

I have a donor database which we track all donations and receipt the donors every month. We want to compare a previous time period to a current time period of all donors to find who gave donations previously and have not given during the current time period. I am doing this by comparing total donations for each donor for the time periods. The resultant should be anyone that has given x dollars during the first time period and has not given at least the same amount during the second time period.

I have 4 queries that I have set up where I will substitute variables for the dates and dollar amounts later.

query 1: to find total sum of donations > 300 by date range
time period one

qry1 = &quot;SELECT Donor.[Donar ID], Sum(receipts.Donation) AS SumOfDonation FROM Donor INNER JOIN receipts ON Donor.[Donar ID] = receipts.[Donar id] WHERE (((receipts.Date) >= #1/1/2000# And (receipts.Date) <= #12/30/2000#)) GROUP BY Donor.[Donar ID] HAVING (((Sum(receipts.Donation))>300));&quot;

query 2: to find total sum of donations > 300 by date range
current time period

qry2 = &quot;SELECT Donor.[Donar ID], Sum(receipts.Donation) AS SumOfDonation FROM Donor INNER JOIN receipts ON Donor.[Donar ID] = receipts.[Donar id] WHERE (((receipts.Date) >= #1/1/2001# And (receipts.Date) <= #12/30/2001#)) GROUP BY Donor.[Donar ID] HAVING (((Sum(receipts.Donation))>300));&quot;

query 3: find donors in time period one not found in
the current time period

qry3 = &quot;SELECT qry1.[Donar ID], qry1.SumOfDonation FROM qry1 LEFT JOIN qry2 ON qry1.[Donar ID] = qry2.[Donar ID] WHERE (((qry2.[Donar ID]) Is Null));&quot;


query 4: use qry3 to pick up donor information matching on [donor id]

qry4 = &quot;SELECT Donor.[last name], Donor.[first name], Donor.address1, Donor.address2, Donor.city, Donor.state, Donor.zip, Donor.[Dear Name], qry3.SumOfDonation FROM Donor INNER JOIN qry3 ON Donor.[Donar ID] = qry3.[Donar ID];&quot;


QRY4 is the final query that I would execute. I have this setup in query objects where I reference the predefined queries instead of qry(x) as shown in the sql strings above. When I run the qry4 as the query I receive the correct answer.


I know there has to be a way to simplify this. I started by building query objects and then built the next query to reference a query instead of a table. I was tring to replicate this in code, could be a bad idea....

I would really like to convert it all to VBA so I can control user input for all the dates and $ amounts used in qry1 and qry2.

I hope this is clearer, it's hard to tranlate your thoughts through typed words.

Thanks,

dave

 

[MichaelRed]

Clear? Well at least a LOT clearer. It may take me a while to wend through it, bu I see NO reason the schema cannot be set up as a group of Parameter queries with the Date(s) and Amount(s) simply selected or entered on a SIMPLISTIC form, producing an arbitraary output (Report?).


I had a 'sick puppy' computer to fix the last couple of days, and need to catch up on a few things, but will try to get through this tomorrow or Friday.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over