Binding Two SQL Queries Together !

  • Thread starter Thread starter lovely_angel_for_you
  • Start date Start date
L

lovely_angel_for_you

Hi,

I have situation in creating a SQL query, I think what I want to
achieve can be done. I have two tables,

CaseData >> Date(Date), Case(string), Caller(String)
People >> Caller(String), City(String)

Now I want to display the data from CaseData, where Caller is From City
I select, within a select range.

I can create the list of all cases from CaseDate within a date range
using this.

Set rdset1 = conn.Execute("SELECT * FROM CaseData Where Date>=#" &
Format$(StartDate.Text, "mm/dd/yyyy") & "# AND Date<=#" &
Format$(EndDate.Text, "mm/dd/yyyy") & "# Order By Date, Time")

And I can create the list of Callers from a City using this

Set rdset2 = conn.Execute("Select * From People Where City='" &
CitySelected & "'")

Now can we join these two queries. As right now I am creating the list
of Callers, then do while till the rdset2.eof, I am then creating the
list of cases, and printing them using do while not rdset1.eof.

However as I want to show the data in order by date, I am not able to
accomplish that. And also it is causing the number of queries to
database increase.

I am pretty sure that there is a way to bind these queries together,
how I dont know. Maybe using Join or some other sql command. Please
assist me in this one.

Waiting eagerly.

Regards
Lovely
 
Hi,

I have situation in creating a SQL query, I think what I want to
achieve can be done. I have two tables,

CaseData >> Date(Date), Case(string), Caller(String)
People >> Caller(String), City(String)

Now I want to display the data from CaseData, where Caller is From City
I select, within a select range.
Click to expand...

Just use a join:

SELECT CASEDATA.*
FROM CASEDATA INNER JOIN PEOPLE
ON CASEDATA.CALLER = PEOPLE.CALLER
WHERE CITY = '<INSERT CITY HERE>'
AND [DATE] BETWEEN #DATE1# AND #DATE2#;

Note again you should bracket [DATE] (if that is in fact the name of
your field) because "date" is a reserved word.

I assume you know how to adapt the variables to your VBA code (^:

HTH
 
Got the query working.

Set rdset = conn.Execute("SELECT CaseData.* FROM CaseData INNER JOIN
People ON CaseData.Name = People.Name Where People.City='" & List_City
& "' And CaseData.CaseDate>=#" & Format$(StartDate.Text, "mm/dd/yyyy")
& "# AND CaseData.CaseDate<=#" & Format$(EndDate.Text, "mm/dd/yyyy") &
"# Order By CaseDate")

However, I was trying to get the total count of records generated from
the above query, and I am not able to do that.

I tried:

Set rdset = conn.Execute("SELECT Count(*) AS TotalCnt FROM CaseData
INNER JOIN People ON CaseData.Name = People.Name Where People.City='" &
List_City & "' And CaseData.CaseDate>=#" & Format$(StartDate.Text,
"mm/dd/yyyy") & "# AND CaseData.CaseDate<=#" & Format$(EndDate.Text,
"mm/dd/yyyy") & "# Order By CaseDate")

And I get the error :
You tried to execute a query that does not include the specified
expression 'CaseDate' as part of an aggregate function.

Any suggestion where I am going wrong again this time.

Regards
Lovely
 
Got it resolved. How stupid I get sometimes. While counting, where is
the need of ordering. So when I removed, Order By CaseDate, it works
perfectly fine.

Set rdset = conn.Execute("SELECT Count(*) AS TotalCnt FROM CaseData
INNER JOIN People ON CaseData.Name = People.Name Where People.City='" &
List_City & "' And CaseData.CaseDate>=#" & Format$(StartDate.Text,
"mm/dd/yyyy") & "# AND CaseData.CaseDate<=#" & Format$(EndDate.Text,
"mm/dd/yyyy") & "#")

Thanks for all the assistance.

Regards
Lovely
 
Back
Top