I have built a sql string within access query design tool as it has been quite an effort to try and write this off the bat.
I have transfered this string into my code as DoCmd.RunSql. What I have done is used a combo box to make sure specific records are selected. Whilst doing this in the query designer I noticed it used the HAVING clause in order to select the right criteria.
I havent really come across it much since I'm pretty new to queries but to me surely HAVING and WHERE do pretty much the same job?
When and where should I be using each and what limitations do they have. I will hopefully be looking further into Allen Brownes form filtering script where he dynamically builds the string for filtering to possibly use this to create my sql string.
Thanks,
Chris
Ps. The string I've been talking about (looks so messy when using the query tool) - sqlstring = "SELECT tbldept.Department, tbltypes.NCType, Sum(tblCosts.CostFig) AS SumOfCostFig INTO tblParetoRaising FROM (tbldept INNER JOIN (tbltypes INNER JOIN tbllog ON tbltypes.NCtypeID = tbllog.NCType) ON tbldept.DeptID = tbllog.DeptRaisedBy) INNER JOIN tblCosts ON tbllog.NCC_ID = tblCosts.NCC_ID GROUP BY tbldept.Department, tbltypes.NCType HAVING (((tbldept.Department)=[Forms]![frmParetoRaising]![cmoDeptRaisedBy]))ORDER BY Sum(tblCosts.CostFig) DESC "
-
DoCmd.RunSQL sqlstring
They are similar, but not the same and not interchangeable. The WHERE clause is used prior to the GROUP BY being performed. The HAVING clause is used on the set of records that results after the GROUP BY.
So, this works: - SELECT {...}
-
FROM {...}
-
WHERE {condition1}
-
GROUP BY {...}
-
HAVING {condition2};
But this does not: - SELECT {...}
-
FROM {...}
-
WHERE {condition1}
-
GROUP BY {...}
-
WHERE {condition2};
Pat
12 1987
They are similar, but not the same and not interchangeable. The WHERE clause is used prior to the GROUP BY being performed. The HAVING clause is used on the set of records that results after the GROUP BY.
So, this works: - SELECT {...}
-
FROM {...}
-
WHERE {condition1}
-
GROUP BY {...}
-
HAVING {condition2};
But this does not: - SELECT {...}
-
FROM {...}
-
WHERE {condition1}
-
GROUP BY {...}
-
WHERE {condition2};
Pat
Pretty much a repeat of what Zepphead 80 said
but the essential point to understand is
the where conditions are applied to each record prior to the result being grouped.
Having is applied to the recordset after the grouping has been applied. step 1 the records are selected according to the where conditions step 2 the selected records are grouped according to the group conditions step 3 the grouped records are then selected according to the having conditions step 4 the resulting recordset is returned
Thank you both for the replies. I have created something I'm now quite proud of once I got my understanding of HAVING and WHERE. I now have code which selects value from my main table based on form selections, creates a new table which then has values taken from that table and used within the record source of a pareto analysis graph using more select statements. A where clause has been built to allow data to be filtered within the first query using Allen Brownes technique of creating a dynamic where string.
NeoPa 32,556
Expert Mod 16PB
Another point to bear in mind is that when you are in Design View of a query in Access and select the type of the query as GROUP BY (Click the button that looks like a Sigma), any existing fields that have criteria will be changed to HAVING, instead of the more sensible default of WHERE. It's worth looking out for this, understanding exactly what you really want, and changing to WHERE where appropriate.
Thats the problem with developing with wizards...they don't always make the best choices.
@Delerna
I think that is the major pitfall I have found Delerna. I am alright at producing very very basic sql statements but just for ease I end up going in to the wizard but it doesn't take any time at all for the statements to look a real mess.
Just to clarify my comment, I'm not against using wizards.
One good thing about them is that they provide a great tool to learn SQL from.
If youre not sure how to write a query to do a particular task then the wizard can provide a good place to start from and even many times a good place to end at, ie the code it produces is perfectly adequate.
Knowing they don't always make the best choices however means we can then analyse the resulting query for any improvements that can be made manually.
Obviously your ability to do that will improve as your experience grows.
NeoPa 32,556
Expert Mod 16PB
That's exactly how I got started Chris. Looking at the SQL produced by the wizards.
I became intereseted to see if there was a performance difference between the two so I wrote two queries that generate the same result one using where and having and one just using having -
select [Item Number],company,Division,Facility,sum([Invoiced Quantity]) as Inv
-
from Sales_Statistics
-
Where company=100 and Division='NSW' and Facility='FN1'
-
group by [Item Number],company,Division,Facility
-
having sum([Invoiced Quantity])>2
-
-
-
select [Item Number],company,Division,Facility,sum([Invoiced Quantity]) as Inv
-
from Sales_Statistics
-
group by [Item Number],company,Division,Facility
-
having company=100 and Division='NSW' and Facility='FN1' and sum([Invoiced Quantity])>2
-
I did this on MS SQL Server and the table has 3,144,135 records in it.
I then checked the execution plan for the two queries and they were exactly Identical (See attached) and when compared together query analyser reports that they will each take 50% of the time to execute them both.
So it seems there is no difference to the performance.....in SQL server anyway!
NeoPa 32,556
Expert Mod 16PB
SQL Server is pretty clever Delerna. It is well within its capabilities to analyse the SQL strings and create identical execution plans for both.
This may not be true for all SQL servers (it would be for most mind you). That said, I find it is more about human understanding than about execution. When people better understand what the code means, they tend to make fewer errors.
Yes I agree.
Its why I added the bolded
So it seems there is no difference to the performance.....in SQL server anyway! NeoPa 32,556
Expert Mod 16PB
I didn't miss that :)
The point of my post was to draw attention away from performance as the defining issue here. I wasn't trying to argue with you on the point you made (in fact I supported it essentially).
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Graham Simms |
last post by:
I have tried to run a query where the reuslts are restricted by a
HAVING clause with 2 conditions, but the second condition seems to be
ignored.
for example
SELECT userid, AVG(position) FROM...
|
by: Rachel Weeden |
last post by:
I'm working on an ASP Web application, and am having syntax issues in
a WHERE statement I'm trying to write that uses the CInt Function on a
field.
Basically, I want to select records using...
|
by: scottelkin |
last post by:
I am trying to find all affiliates that have more commissions from this
week to the prior week. The problem is in the having part where
"aff2.affiliateid = aff.affiliateid". SQL Server just...
|
by: Henry |
last post by:
Hello,
I have a question about ASP.NET process. In this case:
Try
'SOME CODE
Response.Redirect("some_asp_page.aspx")
Catch obj_Exception as Exception
Throw obj_Exception
|
by: pwiegers |
last post by:
Hi,
I'm trying to use the result of a conditional statement in a where
clause, but i'm getting 1)nowhere 2) desperate :-)
The query is simple:
--------
SELECT
idUser,...
|
by: Miroslaw Makowiecki |
last post by:
Where can I download Comeau compiler as a trial version?
Thanks in advice.
|
by: Roopanwita |
last post by:
Hi,
I am asking a very basic question.
Can anyone explain me the difference between Having & Where Clause.
Thanks in advance,
Roopanwita
|
by: Seguros Catatumbo |
last post by:
Hello guys, i have this query:
select c8.cerveh, sum(c8.monto1) monto1,
(select prima from arysauto a where a.cerveh=c8.cerveh)
priari,
(sum(c8.monto1)-(select prima from arysauto a
where...
|
by: Jack |
last post by:
Hi,
Performance-wise, is there a difference between these 2 clauses?
I assume a large database...
1) With the WHERE clause:
Select FLD1, FLD2, FLD3
From myFile
Where FLD1 = 1 And FLD2 = 1
|
by: Crombam |
last post by:
Hi to all,
After searching the net for a couple of hours I just have to ask the question. How can I close a report with a subform (Pivot Chart) when this Pivot Chart is having no data?
...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
| |