A while back I wrote about a Inner Join technique using a date range. (You can find that post here.)
Today I’m going to take it a step further and discuss using inner joins to another query and conclude with an issue in Access you may shed some light on.
Access can work great in large companies, call us to discuss
Using Queries as Inner Joins
For the longest I’ve been using make table queries to calculate totals and percentages, then linked the tables in secondary queries to perform updates. For example, take a look at the following tblWidgetImport table:
WidgetWeekID WidgetTypeID WidgetQty WeeklyWidgetPercent ------------ ------------ ---------- ------------- 1 5 570 1 6 415 1 7 878 1 8 142 2 5 45 2 6 725 2 7 951 2 8 145
The table displays the total production by WidgetTypeID per week. What I needed to do was calculate the percentage of each Widget’s total for the week, and then update the WeeklyWidgetPercent column.
I can calculate totals by week using the following query:
SELECT WidgetWeekID, Sum(WidgetQty) AS TotalWidgets FROM tblWidgetImport GROUP BY WidgetWeekID
Which produces the following results:
WidgetWeekID TotalWidgets ------------ ------------ 1 2,005 2 1,866
Now that I have my summary query figured out I used it in an Inner Join query with the original table to calculate percent of each WidgetTypeID:
SELECT W.WidgetWeekID, W.WidgetTypeID, Sum(W.WidgetQty)/T.TotalWidgets AS [WeeklyWidgetPercent] FROM tblWidgetWeekImport AS W INNER JOIN ( SELECT WidgetWeekID, Sum(WidgetQty) AS TotalWidgets FROM tblWidgetImport GROUP BY WidgetWeekID ) AS T ON W.WidgetWeekID = T.WidgetWeekID GROUP BY W.WidgetWeekID, W.WidgetTypeID, T.TotalWidgets;
The query above will produced the following result set:
WidgetWeekID WidgetTypeID WeeklyWidgetPercent ------------ ------------ ------------------- 1 5 28.43% 1 6 20.70% 1 7 43.79% 1 8 7.08% 2 5 2.41% 2 6 38.85% 2 7 50.96% 2 8 7.77%
Nice! I now have a query that will calculate percent of each widget type by week without using a temporary table! Now I just have to insert this query with the original table to update the WeeklyWidgetPercent column, what could go wrong?
Something Went Wrong
I created the following query to update the original table using the two queries above:
UPDATE tblWidgetImport AS P
INNER JOIN (
SELECT W.WidgetWeekID, W.WidgetTypeID, Sum([W].[WidgetQty])/[T].[TotalWidgets] AS WeeklyWidgetPercent
FROM tblWidgetImport AS W
INNER JOIN (
SELECT WidgetWeekID, Sum(WidgetQty) AS TotalWidgets
FROM tblWidgetImport
GROUP BY WidgetWeekID
) AS T
ON W.WidgetWeekID = T.WidgetWeekID
GROUP BY W.WidgetWeekID, W.WidgetTypeID, T.TotalWidgets
) AS PC
ON (P.WidgetTypeID = PC.WidgetTypeID) AND (P.WidgetWeekID = PC.WidgetWeekID)
SET P.WeeklyWidgetPercent = PC.WeeklyWidgetPercent;
When I try to execute this query Access returns Must use an updatable query. Ben helped me with these complex joins and says that of all RDBMS out there, Access is probably the most stingy whenever it comes to executing an update query using a summary query. As soon as a query includes a source that’s non-updatable (e.g. aggregations), the whole query is non-updatable even though we aren’t trying to update the aggregated query but a base table using values from aggregated tables. Luke Chung has a great white paper on this issue here.
In this situation, we have two choices; export the query result into temporary table as I’ve always done or wrap the query into a VBA function which Access will allow.
UPDATE tblWidgetImport AS P SET P.WeeklyWidgetPercent = GetWeeklyPercentByType(P.WidgetTypeID, P.WidgetWeekID);
Where GetWeeklyPercentByType() is a VBA function that uses a DAO.OpenRecordset. It would work but since we’d be opening recordset for each row, it would be slower than just making tables and linking them.
Taking your inner join queries to the next level will require you to invest more time, but will provide you with faster results and less clutter in your database.
Regarding using a temporary table versus a VBA function workarounds, what are the advantages to using VBA? The temp tables solution makes debugging easier, but it adds housekeeping chores.
Hi Kevin!
The temp table is almost always quicker since you can do a inner join and perform one query vs calling the function for each record in the table.
Thanks
Juan
As far as the slow recordset goes, you can create a snapshot-type recordset and store it in a module level variable. Then, you only create the recordset once. Each time the function gets called, you only have to find the appropriate record.
Great comment Zev, thanks!
Juan