Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Query Join? Include all records from table

Member
  
Join Date: Feb 2008
Posts: 39
#1: May 30 '08
HI

I have 2 tables ProductType and Sales.

I am trying write a query which gives me a summary of Product Types and Sales for each Product.

Product.....................Sales

A..............................100
B...............................50
C...............................250
D................................0
E................................20

However when I run my query I get

Product.....................Sales

A..............................100
B...............................50
C...............................250
E................................20

Product D is missing because it has no Sales. However I still want it included in results of Query. I have tried left and right joins but they still wont return all the products in the products table?

Can anyone help?

Thanks

pod's Avatar
pod pod is offline
Member
  
Join Date: Sep 2007
Location: Gatineau (across the river from my national capital)
Posts: 83
#2: May 30 '08

re: Query Join? Include all records from table

Quote:

Originally Posted by JC2710

HI

I have 2 tables ProductType and Sales.

I am trying write a query which gives me a summary of Product Types and Sales for each Product.

Product.....................Sales

A..............................100
B...............................50
C...............................250
D................................0
E................................20

However when I run my query I get

Product.....................Sales

A..............................100
B...............................50
C...............................250
E................................20

Product D is missing because it has no Sales. However I still want it included in results of Query. I have tried left and right joins but they still wont return all the products in the products table?

Can anyone help?

Thanks

You could use a LEFT JOIN (or RIGHT JOIN)

SELECT Product.Name, Sales.Total
FROM Product
LEFT JOIN Sales
ON Product.Product_ID=Sales.Product_ID

check the following :
W3school SQL LEFT JOIN
Member
  
Join Date: Feb 2008
Posts: 39
#3: May 30 '08

re: Query Join? Include all records from table

Quote:

Originally Posted by pod

You could use a LEFT JOIN (or RIGHT JOIN)

SELECT Product.Name, Sales.Total
FROM Product
LEFT JOIN Sales
ON Product.Product_ID=Sales.Product_ID

check the following :
W3school SQL LEFT JOIN

I am but it doesnt work. Maybe because I have a WHERE stmt included in the Query?
pod's Avatar
pod pod is offline
Member
  
Join Date: Sep 2007
Location: Gatineau (across the river from my national capital)
Posts: 83
#4: Jun 2 '08

re: Query Join? Include all records from table

Quote:

Originally Posted by JC2710

I am but it doesnt work. Maybe because I have a WHERE stmt included in the Query?

it's possible. See if you can make it work first without the WHERE statement first.
Moderator
  
Join Date: Feb 2008
Location: Beauly, near Inverness, Scotland
Posts: 1,576
#5: Jun 2 '08

re: Query Join? Include all records from table

Hi. To add to Pod's reply, if you remove the where clause and the query does return a value for the missing grouping then the reason you don't see the group when the WHERE clause is present is because your WHERE clause does not work with Null values for the compared fields.

To explain, if there are no rows to return for a particular grouping in an outer-joined query any calculated field on the 'other side' of the outer join for that grouping will be returned as a null. If your WHERE clause was, say, WHERE SALES > 0 this will exclude rows where SALES is null, because nulls are not compatible with any other type of value.

In handling nulls you either have to test for them explicitly, or convert them to typed values which the comparison can handle correctly.

It would be easier to guide you if you posted the SQL of your query, including the WHERE clause, for us to check.

-Stewart
Member
  
Join Date: Feb 2008
Posts: 39
#6: Jun 3 '08

re: Query Join? Include all records from table

Thanks for your help guys!

My query works without the WHERE
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProductType.ProductType, Sum(tblEstimates.Price) AS SumOfPrice
  2. FROM tblProductType LEFT JOIN tblEstimates ON tblProductType.ID = tblEstimates.ProductTypeID
  3. GROUP BY tblProductType.ProductType;
But doesnt include all ProductTypes with WHERE....the WHERE stmt has 2 dates.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProductType.ID, tblProductType.ProductType, Count(tblProductType.ID) AS [Count of Estimates]
  2. FROM tblProductType LEFT JOIN tblEstimates ON tblProductType.ID = tblEstimates.ProductTypeID
  3. WHERE (((tblEstimates.Deleted)=0) AND ((tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo]))
  4. GROUP BY tblProductType.ID, tblProductType.ProductType;
So is there any way of using a left join with a WHERE??
Moderator
  
Join Date: Feb 2008
Location: Beauly, near Inverness, Scotland
Posts: 1,576
#7: Jun 3 '08

re: Query Join? Include all records from table

Hi. The WHERE clause just needs a minor alteration to include any rows where a field from the estimate table is null (which indicates that there were no estimates for that particular product over the chosen period). The OR'd IS NULL added to your WHERE clause below should do this:

Expand|Select|Wrap|Line Numbers
  1. WHERE (((tblEstimates.Deleted)=0) AND ( (tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo])) OR (tblEstimates.DateReceived IS Null)
  2. GROUP BY tblProductType.ID, tblProductType.ProductType;
-Stewart
Member
  
Join Date: Feb 2008
Posts: 39
#8: Jun 3 '08

re: Query Join? Include all records from table

Quote:

Originally Posted by Stewart Ross Inverness

Hi. The WHERE clause just needs a minor alteration to include any rows where a field from the estimate table is null (which indicates that there were no estimates for that particular product over the chosen period). The OR'd IS NULL added to your WHERE clause below should do this:

Expand|Select|Wrap|Line Numbers
  1. WHERE (((tblEstimates.Deleted)=0) AND ( (tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo])) OR (tblEstimates.DateReceived IS Null)
  2. GROUP BY tblProductType.ID, tblProductType.ProductType;
-Stewart

Thanks for all your help guys! Works great!
Member
  
Join Date: Feb 2008
Posts: 39
#9: Jun 6 '08

re: Query Join? Include all records from table

Quote:

Originally Posted by JC2710

Thanks for all your help guys! Works great!

HELP!!!!

I cant get this to work!!!

Adding WHERE tblEstimates.DateReceived Is Null works on the query I stated above but I have an almost identical query and it doesnt work!!
Expand|Select|Wrap|Line Numbers
  1. SELECT tblStatus.ID, tblStatus.Status, Count(tblEstimates.ID) AS [Count of Estimates]
  2. FROM tblStatus LEFT JOIN tblEstimates ON tblStatus.ID = tblEstimates.StatusID
  3. WHERE (((tblEstimates.Deleted)=0) AND ((tblEstimates.DateReceived) Between [Forms]![frmStatus_Summary_Report]![txtDateFrom] And [Forms]![frmStatus_Summary_Report]![txtDateTo]))
  4. GROUP BY tblStatus.ID, tblStatus.Status;
Then I add OR tblEstimates.DateReceived IS NULL

And the left join still wont work!!!

So frustrating!! Any ideas?
Member
  
Join Date: Feb 2008
Posts: 39
#10: Jun 6 '08

re: Query Join? Include all records from table

Quote:

Originally Posted by Stewart Ross Inverness

Hi. The WHERE clause just needs a minor alteration to include any rows where a field from the estimate table is null (which indicates that there were no estimates for that particular product over the chosen period). The OR'd IS NULL added to your WHERE clause below should do this:

Expand|Select|Wrap|Line Numbers
  1. WHERE (((tblEstimates.Deleted)=0) AND ( (tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo])) OR (tblEstimates.DateReceived IS Null)
  2. GROUP BY tblProductType.ID, tblProductType.ProductType;
-Stewart

Sorry Stewart

Looks like your previous suggestion doesnt work!!

If I add the OR (tblEstimates.DateReceived IS Null) it only brings back ProductTypes that have no estimates related to them at all, not no estimates related to them between the two dates!!

All i want is a simple LEFT JOIN that works!!

Has anyone ever made anything in SQL work apart from SELECT Field FROM table!!

Im starting to wonder!!

Ho hum another cpl of days wasted!!

If you can help Stewart I would be grateful!!

Thanks
Moderator
  
Join Date: Feb 2008
Location: Beauly, near Inverness, Scotland
Posts: 1,576
#11: Jun 9 '08

re: Query Join? Include all records from table

Hi. SQL is a very powerful query language - but to use it to good effect we need to be very clear about what we are trying to achieve. I think there has been a lack of clarity, reflected in experiments with SQL which do not achieve what you want. These are not a waste, as you will always learn more when you try to resolve problems for yourself this way.

What you want to achieve:

1. You want to total sales activity for your products between two dates.
2. You want to show all products which DO NOT have any sales totals between two dates.

I did not take into account the dates for (2) when going over the left-join requirements with you, and should have recognised that to satisfy requirements 1 and 2 we need two queries, which can be combined in one SQL statement if necessary, but it is still two queries. The first to calculate the totals, and the second to left-join these back to the products table. Showing these as separate (cascaded) queries based on your earlier posts:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProductType.ID AS ProductID, Count(tblProductType.ID) AS [Count of Estimates]
  2. FROM tblProductType WHERE (((tblEstimates.Deleted)=0) AND ((tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo]))
  3. GROUP BY tblProductType.ID, tblProductType.ProductType;
I have named this one qryEstimatesCount, so it can be used in
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProductType.ID, tblProductType.ProductType, [Count of Estimates]
  2. FROM tblProductType LEFT JOIN qryEstimatesCount ON tblProductType.ID = ProductID
  3. GROUP BY tblProductType.ID, tblProductType.ProductType;
but it ought to be possible to include these in one SQL query as follows
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProductType.ID, tblProductType.ProductType, [Count of Estimates]
  2. FROM tblProductType LEFT JOIN 
  3. (SELECT tblProductType.ID AS ProductID, Count(tblProductType.ID) AS [Count of Estimates]
  4. FROM tblProductType WHERE (((tblEstimates.Deleted)=0) AND ((tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo])) 
  5. GROUP BY tblProductType.ID, tblProductType.ProductType)
  6.  
  7. ON tblProductType.ID = ProductID
  8. GROUP BY tblProductType.ID, tblProductType.ProductType;
but I haven't tried this out to check. The two-query cascade approach will certainly do the SELECT the totals you want, then LEFT JOIN this back to the products table as necessary.

-Stewart
Member
  
Join Date: Feb 2008
Posts: 39
#12: Jun 9 '08

re: Query Join? Include all records from table

Quote:

Originally Posted by Stewart Ross Inverness

Hi. SQL is a very powerful query language - but to use it to good effect we need to be very clear about what we are trying to achieve. I think there has been a lack of clarity, reflected in experiments with SQL which do not achieve what you want. These are not a waste, as you will always learn more when you try to resolve problems for yourself this way.

What you want to achieve:

1. You want to total sales activity for your products between two dates.
2. You want to show all products which DO NOT have any sales totals between two dates.

I did not take into account the dates for (2) when going over the left-join requirements with you, and should have recognised that to satisfy requirements 1 and 2 we need two queries, which can be combined in one SQL statement if necessary, but it is still two queries. The first to calculate the totals, and the second to left-join these back to the products table. Showing these as separate (cascaded) queries based on your earlier posts:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblProductType.ID AS ProductID, Count(tblProductType.ID) AS [Count of Estimates]
  2. FROM tblProductType WHERE (((tblEstimates.Deleted)=0) AND ((tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo]))
  3. GROUP BY tblProductType.ID, tblProductType.ProductType;
I have named this one qryEstimatesCount, so it can be used in
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProductType.ID, tblProductType.ProductType, [Count of Estimates]
  2. FROM tblProductType LEFT JOIN qryEstimatesCount ON tblProductType.ID = ProductID
  3. GROUP BY tblProductType.ID, tblProductType.ProductType;
but it ought to be possible to include these in one SQL query as follows
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProductType.ID, tblProductType.ProductType, [Count of Estimates]
  2. FROM tblProductType LEFT JOIN 
  3. (SELECT tblProductType.ID AS ProductID, Count(tblProductType.ID) AS [Count of Estimates]
  4. FROM tblProductType WHERE (((tblEstimates.Deleted)=0) AND ((tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo])) 
  5. GROUP BY tblProductType.ID, tblProductType.ProductType)
  6.  
  7. ON tblProductType.ID = ProductID
  8. GROUP BY tblProductType.ID, tblProductType.ProductType;
but I haven't tried this out to check. The two-query cascade approach will certainly do the SELECT the totals you want, then LEFT JOIN this back to the products table as necessary.

-Stewart

Thnaks for your help Stewart!

Appreciate it!

Sorry if i sound frustrated!!!

I will tr this out when I get the chance later today!!

Thanks again
Moderator
  
Join Date: Feb 2008
Location: Beauly, near Inverness, Scotland
Posts: 1,576
#13: Jun 9 '08

re: Query Join? Include all records from table

Hi. I saw that you had posted a response which may have been edited to be just the letters 'dgfgddfgdfgdfgdfgdfgdfgdfgdfgdf' so I deleted it in my Moderator capacity.

I tried out the one-query Left-join approach myself without success this afternoon, so I may be leading you astray thinking that the two could be combined. I will check this out further and let you know.

If the two-query version is working for you then you do indeed have a viable solution. This is the approach I use myself for all such two-stage queries (do some grouping then left-join results). I think it is easier to follow, and allows for re-use of the first stage totalling query to do other jobs if necessary without rewriting.

-Stewart
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#14: Jun 10 '08

re: Query Join? Include all records from table

Interesting.

Let me know if this works for you. If not, then your first step is to specify as clearly and logically as you can exactly what you require from the SQL. This will help you as well as anyone trying to assist you. It's almost impossible to work logically without a clear and defined understanding of what's required.

As a last point, please remember to post all your code (including SQL) in the code tags provided. This is a requirement on this site, as not doing so wastes so much time and effort for people trying to assist.
Member
  
Join Date: Feb 2008
Posts: 39
#15: Jun 11 '08

re: Query Join? Include all records from table

Hi Stewart

Thanks again for all your help.

Yes the "dfgfdfgdfdfdfgdfdfg" was me editing my post as I couldnt work out how to delete it! Sorry!

I think Ive solved my problem! Have used your theory, which just needed tweaking slightly.

Anyway this is the final SQL which seems to work! Which combines the 2 queries!

Expand|Select|Wrap|Line Numbers
  1. SELECT tblProductType.ID, tblProductType.ProductType, T2.[Count of Estimates]
  2. FROM tblProductType LEFT JOIN 
  3.  
  4. (SELECT tblProductType.ID AS ProductID, Count(tblEstimates.ID) AS [Count of Estimates]
  5. FROM tblProductType LEFT JOIN tblEstimates ON tblProductType.ID = tblEstimates.ProductTypeID
  6. WHERE (((tblEstimates.Deleted)=0) AND ((tblEstimates.DateReceived) Between [Forms]![frmProductType_Summary_Report]![txtDateFrom] And [Forms]![frmProductType_Summary_Report]![txtDateTo]))
  7. GROUP BY tblProductType.ID, tblProductType.ProductType) AS T2 
  8.  
  9. ON tblProductType.ID = T2.ProductID
  10. GROUP BY tblProductType.ID, tblProductType.ProductType, T2.[Count of Estimates];
I can now move on to other problems!

Thanks again for all your help. Im sure Ill be needing it again soon!

Thanks

James
Moderator
  
Join Date: Feb 2008
Location: Beauly, near Inverness, Scotland
Posts: 1,576
#16: Jun 11 '08

re: Query Join? Include all records from table

Well done James, particularly for sorting out the syntax of the single-query version. Excellent!

-Stewart
Member
  
Join Date: Feb 2008
Posts: 39
#17: Jun 24 '08

re: Query Join? Include all records from table

Hi Stewart

Afraid Im stuck again!

I have more or less the same problem as above.

I have tblEstimates holding estimates, linked to tblEstimators. Each Estimate has a status ie Work In Progress, Won , Lost. This is linked to tblStatus. Simple so far. So when I do a query to see the status of Estimates for each estimator. I do a simple GROUP query. However I get the same problem as before where it only includes status's for each estimator that exist.

So if the estimator has Won 3 estimates it shows and if they have Lost 2 it shows. But if they have Won 3 and Lost 0, it only shows them as Won 3, and Lost doesnt appear! However the solution above that we worked out wont work because its broken down by each Estimator and you end up with NULL in estimator field. Dont know if this makes sense.

Have included the query below:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblEstimates.EstimatorID, tblEstimators.EstimatorName, Sum(tblEstimates.Price) AS SumOfPrice, Sum(tblEstimates.ActualPrice) AS SumOfActualPrice, tblEstimates.StatusID, tblStatus.Status, Count(tblStatus.Status) AS CountOfStatus
  2. FROM (tblEstimates LEFT JOIN tblStatus ON tblEstimates.StatusID = tblStatus.ID) RIGHT JOIN tblEstimators ON tblEstimates.EstimatorID = tblEstimators.ID
  3. WHERE (((tblEstimates.DateReceived) Between [Forms]![frmEstimator_Summary_Report]![txtDateFrom] And [Forms]![frmEstimator_Summary_Report]![txtDateTo]) AND ((tblEstimates.Deleted)=0))
  4. GROUP BY tblEstimates.EstimatorID, tblEstimators.EstimatorName, tblEstimates.StatusID, tblStatus.Status
  5. HAVING (((tblEstimates.EstimatorID)=[Forms]![frmEstimator_Summary_Report]![cboEstimator])) OR ((([tblEstimates]![EstimatorID] Like [Forms]![frmEstimator_Summary_Report]![cboEstimator]) Is Null));
So basically I get:

Estimator Name: Smith

Won: 3

Whereas I want:

Estimator Name: Smith

Won: 3

Lost: 0



Any ideas?

Thanks
Moderator
  
Join Date: Feb 2008
Location: Beauly, near Inverness, Scotland
Posts: 1,576
#18: Jun 24 '08

re: Query Join? Include all records from table

Solution as before is to separate the two issues - the counting of the number of estimates and the outer-joining of the estimators.

Devise an outer-joined summary query which provides the counts for each estimator by joining the estimates and status tables as in your example SQL, but without including the estimators table in the query. Save this estimate count query. Left-join the count query to the estimators to get your final results, using Nz or similar to convert null values for the counts to zeros.

You will find it much easier to break the problem down into steps which lead on from one to the next than to try to solve this all-in-one, particularly when using outer joins. An advantage is that you can check that the count query is returning correct results before you try outer joining the results back to the estimators.

-Stewart
Member
  
Join Date: Feb 2008
Posts: 39
#19: Jun 26 '08

re: Query Join? Include all records from table

Hi Stewart

Thanks for the advice! Sorry, I was in one of my SQL moods!!!

I think ive solved it! I did 3 querys. The first to Select All Estimators and All Status's, which returned each status with each estimator. Then I did a group by query to get all estimates and then finally a query that joined the two using NZ as before for the NULLS.

Seems to work. And I figured it out before you posted yr advice!! So im obviously learning from yr excellent teaching!! :o)

Thanks again!

Im sure ill be back!!

James
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#20: Jun 27 '08

re: Query Join? Include all records from table

Good for you James :)

It's always nice to see members visibly progressing and learning as time goes on.
Newbie
  
Join Date: Jul 2008
Posts: 9
#21: Jul 25 '08

re: Query Join? Include all records from table

Everyone,

I have found this discussion very helpful in solving essentially the same problem and I wish I had come to this realization a long time ago. I was able to follow the comments left by JC2710 in post 15 of this thread and create a query that gives me the results the way I want them.

However I am running into a problem when I save the query in MS Access. What I input as the SQL code is as follows:

Expand|Select|Wrap|Line Numbers
  1. SELECT [tblItems].[Team], [tblItems].[Group], [T2].[SumofPicks]
  2. FROM tblItems LEFT JOIN (SELECT [tblItems].[Group], Nz(Sum([tblDataHistory].[Picks]),0) AS [SumofPicks]
  3. FROM tblItems LEFT JOIN tblDataHistory ON [tblItems].[Item] = [tblDataHistory].[Item]
  4. WHERE (((Format([tblDataHistory].[Date],"ww")) Between Format(Date(),"ww")-2 And Format(Date(),"ww")-1))
  5. GROUP BY [tblItems].[Group]
  6. ) AS T2 ON [tblItems].[Group] = [T2].[Group]
  7. GROUP BY [tblItems].[Team], [tblItems].[Group], [T2].[SumofPicks];
  8.  
When I reopen the query and view the SQL Code again MS Access has changed it to:

Expand|Select|Wrap|Line Numbers
  1. SELECT [tblItems].[Team], [tblItems].[Group], [T2].[SumofPicks]
  2. FROM tblItems LEFT JOIN [SELECT [tblItems].[Group], Nz(Sum([tblDataHistory].[Picks]),0) AS [SumofPicks]
  3. FROM tblItems LEFT JOIN tblDataHistory ON [tblItems].[Item] = [tblDataHistory].[Item]
  4. WHERE (((Format([tblDataHistory].[Date],"ww")) Between Format(Date(),"ww")-2 And Format(Date(),"ww")-1))
  5. GROUP BY [tblItems].[Group]
  6. ]. AS T2 ON [tblItems].[Group] = [T2].[Group]
  7. GROUP BY [tblItems].[Team], [tblItems].[Group], [T2].[SumofPicks];
  8.  
For some reason Access is changing the parentheses () that open and close the nested query T2 into brackets [] and adding a period at the end.

Anyone have a clue why this might be happening?

Any help here is appreciated!
Newbie
  
Join Date: Jul 2008
Posts: 9
#22: Jul 25 '08

re: Query Join? Include all records from table

I didn't get it in before the edit timer on the last post but the reason this change is a problem is because it will not allow me to come in and edit the SQL and save it again because it has a syntax error in the FROM clause.
Moderator
  
Join Date: Feb 2008
Location: Beauly, near Inverness, Scotland
Posts: 1,576
#23: Jul 25 '08

re: Query Join? Include all records from table

Hi jghouse. The problem you describe is unfortunately an Access bug. it seems to arise with valid SQL statements which Access is unable to show graphically in design view. I know of no fix for it, I'm sorry to say.

On the occasions I edit such statements I have to change the incorrect rectangular bracketing manually.

-Stewart
Newbie
  
Join Date: Jul 2008
Posts: 9
#24: Jul 25 '08

re: Query Join? Include all records from table

Quote:

Originally Posted by Stewart Ross Inverness

Hi jghouse. The problem you describe is unfortunately an Access bug. it seems to arise with valid SQL statements which Access is unable to show graphically in design view. I know of no fix for it, I'm sorry to say.

On the occasions I edit such statements I have to change the incorrect rectangular bracketing manually.

-Stewart

Stewart,

Thank you for the response. Upon working on this more today I have found that I am able to save it with the rectangular bracketing and period in there now.

The only change I made to get this to work was to remove all other bracketing in the SQL statement. Specifically aroud the portion where I rename the Nz(Sum(...)) As [x] portion. I have removed the brackets around x and now the query will save and can be edited even with the square brackets. I have no clue why this works but it seems to repeat well.

I really wish I could explain how Access reads that SQL with the extra [] and period but it does work, saves, and is editable with no problems.

I have pasted the working code below for reference:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblItems.Team, tblItems.Group, T2.SumofPicks
  2. FROM tblItems LEFT JOIN [SELECT tblItems.Team, tblItems.Group, Nz(Sum(tblDataHistory.Picks),0) AS SumofPicks
  3. FROM tblItems INNER JOIN tblDataHistory ON tblItems.Item = tblDataHistory.Item
  4. WHERE ((Format(tblDataHistory.Date,"ww")) Between Format(Date(),"ww")-2 And Format(Date(),"ww")-1)
  5. GROUP BY tblItems.Team, tblItems.Group
  6. ]. AS T2 ON (tblItems.Team = T2.Team) AND (tblItems.Group = T2.Group)
  7. GROUP BY tblItems.Team, tblItems.Group, T2.SumofPicks
  8. ORDER BY tblItems.Team, tblItems.Group;
  9.  
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#25: Jul 28 '08

re: Query Join? Include all records from table

I use the function below when getting SQL from out of a QueryDef.

It's true, there are times in later versions when the square brackets are accepted, but they are not standard SQL even then.
Expand|Select|Wrap|Line Numbers
  1. 'GetSQL gets the SQL component from a named query OR a SQL string.
  2. 'When subqueries are specified in MS Access they are changed internally
  3. 'from   "FROM (SELECT blah blah blah) AS" to
  4. 'either "FROM [SELECT blah blah blah]. AS"
  5. 'or     "FROM [SELECT blah blah blah] AS" both of which are invalid SQL.
  6. 'This code assumes any effected subquery will start with " [SELECT ".
  7. 'This reverts SQL to correct format and loses ';' at end if requested.
  8. ' 11/11/2004    Updated to detect second problem and to use InStr
  9. Public Function GetSQL(strQuery As String, _
  10.                        Optional blnLoseSC As Boolean = True) As String
  11.     Dim intDepth As Integer
  12.     Dim lngLeft As Long, lngOpen As Long, lngRight As Long, lngAdjust As Long
  13.  
  14.     'If param passed is already SQL then leave as is - Otherwise get from query
  15.     If Left(strQuery, 1) = "(" Then
  16.         GetSQL = strQuery
  17.     Else
  18.         On Error Resume Next
  19.         GetSQL = CurrentDb.QueryDefs(strQuery).SQL
  20.     End If
  21.     lngLeft = -7
  22.     Do
  23.         'Check for corrupted subquery
  24.         lngLeft = InStr(lngLeft + 8, GetSQL, " [SELECT ", vbTextCompare)
  25.         If lngLeft = 0 Then Exit Do
  26.         'To find end correctly we must treat '[' & ']' as matched pairs
  27.         intDepth = 1
  28.         lngRight = lngLeft + 8
  29.         lngOpen = -lngRight
  30.         Do
  31.             'Find next ']'
  32.             lngRight = InStr(lngRight + 1, GetSQL, "]", vbBinaryCompare)
  33.             If lngRight = 0 Then
  34.                 GetSQL = ""
  35.                 Exit Function
  36.             End If
  37.             intDepth = intDepth - 1
  38.             Do
  39.                 'For lngOpen negative numbers mean that item has been counted
  40.                 'If already counted get next one - Otherwise drop through
  41.                 If lngOpen < 0 Then _
  42.                     lngOpen = InStr(-lngOpen + 1, GetSQL, "[", vbBinaryCompare)
  43.                 'we're only interested (now) if it found one BEFORE the ']'
  44.                 If lngOpen > lngRight Or lngOpen = 0 Then Exit Do
  45.                 intDepth = intDepth + 1
  46.                 lngOpen = -lngOpen
  47.             Loop
  48.         Loop While intDepth > 0
  49.         'If '].' found then be sure to drop the '.' too
  50.         lngAdjust = IIf(Mid(GetSQL, lngRight + 1, 1) = ".", 1, 0)
  51.         GetSQL = Left(GetSQL, lngLeft) & "(" & _
  52.                  Mid(GetSQL, lngLeft + 2, lngRight - lngLeft - 2) & ")" & _
  53.                  Right(GetSQL, Len(GetSQL) - (lngRight + lngAdjust))
  54.     Loop
  55.     'Lose ";" at end if requested and it exists
  56.     If blnLoseSC And Right(GetSQL, 3) = ";" & vbCrLf Then _
  57.         GetSQL = Left(GetSQL, Len(GetSQL) - 3)
  58. End Function
Newbie
  
Join Date: Jul 2008
Posts: 9
#26: Jul 28 '08

re: Query Join? Include all records from table

Quote:

Originally Posted by NeoPa

I use the function below when getting SQL from out of a QueryDef.

It's true, there are times in later versions when the square brackets are accepted, but they are not standard SQL even then.

Expand|Select|Wrap|Line Numbers
  1. 'GetSQL gets the SQL component from a named query OR a SQL string.
  2. 'When subqueries are specified in MS Access they are changed internally
  3. 'from   "FROM (SELECT blah blah blah) AS" to
  4. 'either "FROM [SELECT blah blah blah]. AS"
  5. 'or     "FROM [SELECT blah blah blah] AS" both of which are invalid SQL.
  6. 'This code assumes any effected subquery will start with " [SELECT ".
  7. 'This reverts SQL to correct format and loses ';' at end if requested.
  8. ' 11/11/2004    Updated to detect second problem and to use InStr
  9. Public Function GetSQL(strQuery As String, _
  10.                        Optional blnLoseSC As Boolean = True) As String
  11.     Dim intDepth As Integer
  12.     Dim lngLeft As Long, lngOpen As Long, lngRight As Long, lngAdjust As Long
  13.  
  14.     'If param passed is already SQL then leave as is - Otherwise get from query
  15.     If Left(strQuery, 1) = "(" Then
  16.         GetSQL = strQuery
  17.     Else
  18.         On Error Resume Next
  19.         GetSQL = CurrentDb.QueryDefs(strQuery).SQL
  20.     End If
  21.     lngLeft = -7
  22.     Do
  23.         'Check for corrupted subquery
  24.         lngLeft = InStr(lngLeft + 8, GetSQL, " [SELECT ", vbTextCompare)
  25.         If lngLeft = 0 Then Exit Do
  26.         'To find end correctly we must treat '[' & ']' as matched pairs
  27.         intDepth = 1
  28.         lngRight = lngLeft + 8
  29.         lngOpen = -lngRight
  30.         Do
  31.             'Find next ']'
  32.             lngRight = InStr(lngRight + 1, GetSQL, "]", vbBinaryCompare)
  33.             If lngRight = 0 Then
  34.                 GetSQL = ""
  35.                 Exit Function
  36.             End If
  37.             intDepth = intDepth - 1
  38.             Do
  39.                 'For lngOpen negative numbers mean that item has been counted
  40.                 'If already counted get next one - Otherwise drop through
  41.                 If lngOpen < 0 Then _
  42.                     lngOpen = InStr(-lngOpen + 1, GetSQL, "[", vbBinaryCompare)
  43.                 'we're only interested (now) if it found one BEFORE the ']'
  44.                 If lngOpen > lngRight Or lngOpen = 0 Then Exit Do
  45.                 intDepth = intDepth + 1
  46.                 lngOpen = -lngOpen
  47.             Loop
  48.         Loop While intDepth > 0
  49.         'If '].' found then be sure to drop the '.' too
  50.         lngAdjust = IIf(Mid(GetSQL, lngRight + 1, 1) = ".", 1, 0)
  51.         GetSQL = Left(GetSQL, lngLeft) & "(" & _
  52.                  Mid(GetSQL, lngLeft + 2, lngRight - lngLeft - 2) & ")" & _
  53.                  Right(GetSQL, Len(GetSQL) - (lngRight + lngAdjust))
  54.     Loop
  55.     'Lose ";" at end if requested and it exists
  56.     If blnLoseSC And Right(GetSQL, 3) = ";" & vbCrLf Then _
  57.         GetSQL = Left(GetSQL, Len(GetSQL) - 3)
  58. End Function


NeoPa,

That is a pretty good routine you built. I had thought about a similar approach to correct things but I realized I wouldn't really have a big use for it. These queries will only run in MS Access so as long as the SQL is working there I am ok. I will save this bit of code in my reference file though as you never know when I might need it.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#27: Jul 29 '08

re: Query Join? Include all records from table

Exactly as it should be.

I found I was getting the SQL out of QueryDefs a lot, so when I came across the problem (a few years ago now) I knocked up the routine otherwise my code couldn't have worked :(
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,471 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved