Count number of Widgets accross 4 tables
Hello Experts,
I have 5 tables, table 1 is a list of building numbers, about 1000 records and the other 4 tables are list widget types in the buildings. I'm trying to create a query that will list by row the; building IDs, widget_1_count, widget_2_count, widget_3_count, widget_4_count. If the Building_ID count is zero I'd still like to list it in the results as zeros across all four fields
I’m using MS Access 2013
Thanks in advance
Bill
I have 5 tables, table 1 is a list of building numbers, about 1000 records and the other 4 tables are list widget types in the buildings. I'm trying to create a query that will list by row the; building IDs, widget_1_count, widget_2_count, widget_3_count, widget_4_count. If the Building_ID count is zero I'd still like to list it in the results as zeros across all four fields
I’m using MS Access 2013
Thanks in advance
Bill
Use the DCount() Function in your Query as shown below:
Select Building_ID, NZ(DCount("Widget_ID", "WidgetTable1", "[Building_ID = '" & [Building_ID] & "'"),0) As Widget_1_Count;
Just modify the code to include the DCount() for Widget 2, 3, 4, etc.
ET
Select Building_ID, NZ(DCount("Widget_ID", "WidgetTable1", "[Building_ID = '" & [Building_ID] & "'"),0) As Widget_1_Count;
Just modify the code to include the DCount() for Widget 2, 3, 4, etc.
ET
domain functions should not be used in queries when there is another alternative and there almost always is.
Create a Union of four totals queries.
Select BuildingID, "A", as WidgetGroup, Count(*) As WidgetCount from tblA Group By BuildingID
Union Select BuildingID, "B", as WidgetGroup, Count(*) As WidgetCount from tblB Group By BuildingID
Union Select BuildingID, "C", as WidgetGroup, Count(*) As WidgetCount from tblC Group By BuildingID
Union Select BuildingID, "D", as WidgetGroup, Count(*) As WidgetCount from tblD Group By BuildingID
Use the Crosstab wizard to pivot the union to get all the counts into a single row.
Join the Buildings table to the Crosstab using a Left Join. Select BuildingID and any other info from the building table and select the count fields from the crosstab. This final query is the one you use as the RecordSource for a form or report or as the query to export if this is destined to be exported to Excel.
Create a Union of four totals queries.
Select BuildingID, "A", as WidgetGroup, Count(*) As WidgetCount from tblA Group By BuildingID
Union Select BuildingID, "B", as WidgetGroup, Count(*) As WidgetCount from tblB Group By BuildingID
Union Select BuildingID, "C", as WidgetGroup, Count(*) As WidgetCount from tblC Group By BuildingID
Union Select BuildingID, "D", as WidgetGroup, Count(*) As WidgetCount from tblD Group By BuildingID
Use the Crosstab wizard to pivot the union to get all the counts into a single row.
Join the Buildings table to the Crosstab using a Left Join. Select BuildingID and any other info from the building table and select the count fields from the crosstab. This final query is the one you use as the RecordSource for a form or report or as the query to export if this is destined to be exported to Excel.
... and now you have 3 ways to do the same thing :)
@PatHartman ... You said ...
<<<<domain functions should not be used in queries when there is another alternative and there almost always is. >>>>
Please post the documentation preferably from Microsoft to support your statement above. As mentioned in a previous post about this subject ... because you had a bad experience using domain functions doesn't mean they should not be used. I feel that is your opinion and I respect that but it is not the bible. Your experience with domain functions had a lot of other variables they could have played a part in the performance issues you had. Personally, domain functions have always worked very well in various queries for me and I develop a lot of applications in Access. IJS!!!
ET
<<<<domain functions should not be used in queries when there is another alternative and there almost always is. >>>>
Please post the documentation preferably from Microsoft to support your statement above. As mentioned in a previous post about this subject ... because you had a bad experience using domain functions doesn't mean they should not be used. I feel that is your opinion and I respect that but it is not the bible. Your experience with domain functions had a lot of other variables they could have played a part in the performance issues you had. Personally, domain functions have always worked very well in various queries for me and I develop a lot of applications in Access. IJS!!!
ET
Pat is right, sort of - as exactly this example is a poor example for advocating a non-use of domain functions, because the union query will do exactly the same as the three calls to the domain functions or running the three subqueries:
Pick up the count from each table and present them in a simple select query with no further processing at all.
There will be no time difference between the options.
/gustav
Pick up the count from each table and present them in a simple select query with no further processing at all.
There will be no time difference between the options.
/gustav
devil's advocate here -- I generally prefer using domain functions to a subquery or union query -- especially if there in an index that can be used. It is an easy way to see things fast. I also have not seen issues with performance, in most cases.
As for creating a union query instead of joining 4 queries to a main table, I realize that a union is less queries to pollute the list of queries (which can get pretty long!). However, it is much easier to understand what is happening with separate queries. Once a union query is created, SQL is all one can see and the designer view with the grid is not available.
As for creating a union query instead of joining 4 queries to a main table, I realize that a union is less queries to pollute the list of queries (which can get pretty long!). However, it is much easier to understand what is happening with separate queries. Once a union query is created, SQL is all one can see and the designer view with the grid is not available.
I generally test several methods depending on the table size and environment ... 99% of the time there is no difference in performance to be honest.
ET
ET
The easy way to add to this discussion about Domain functions is to allow the user to test all three solutions in his/her environment then report back if there was a significant difference in performance between them.
This is the complete SQL for all 4 tables ... just change the table & field names to your actual names.
Select Building_ID, NZ(DCount("Widget_ID", "WidgetTable1", "[Building_ID = '" & [Building_ID] & "'"),0) As Widget_1_Count, NZ(DCount("Widget_ID", "WidgetTable2", "[Building_ID = '" & [Building_ID] & "'"),0) As Widget_2_Count, NZ(DCount("Widget_ID", "WidgetTable3", "[Building_ID = '" & [Building_ID] & "'"),0) As Widget_3_Count, NZ(DCount("Widget_ID", "WidgetTable4", "[Building_ID = '" & [Building_ID] & "'"),0) As Widget_4_Count;
ET
This is the complete SQL for all 4 tables ... just change the table & field names to your actual names.
Select Building_ID, NZ(DCount("Widget_ID", "WidgetTable1", "[Building_ID = '" & [Building_ID] & "'"),0) As Widget_1_Count, NZ(DCount("Widget_ID", "WidgetTable2", "[Building_ID = '" & [Building_ID] & "'"),0) As Widget_2_Count, NZ(DCount("Widget_ID", "WidgetTable3", "[Building_ID = '" & [Building_ID] & "'"),0) As Widget_3_Count, NZ(DCount("Widget_ID", "WidgetTable4", "[Building_ID = '" & [Building_ID] & "'"),0) As Widget_4_Count;
ET
DCount should return 0 (zero) for no records, and no fieldname is required, so I believe you could reduce it to:
Select
Building_ID,
DCount("*", "WidgetTable1", "[Building_ID = '" & [Building_ID] & "'") As Widget_1_Count,
DCount("*", "WidgetTable2", "[Building_ID = '" & [Building_ID] & "'") As Widget_2_Count,
DCount("*", "WidgetTable3", "[Building_ID = '" & [Building_ID] & "'") As Widget_3_Count,
DCount("*", "WidgetTable4", "[Building_ID = '" & [Building_ID] & "'") As Widget_4_Count
From
BuildingTable;
Yes, DCount will automatically return 0 for no recrods ... therefore no need to include the Nz() function as it would just be redundant. Won't hurt but not needed in this case.
ET
ET
ASKER
I’ve tried the Union that PatHartman suggested and I'm getting closer.
The CrossTab didn't work, it only gave me the count of Build_IDs per Widget types.
My first query with the union gave me a result table list below
Select [Widget_A].Building_ID, "Widget_A" as Widget_Types, Count(*) As Num_Of_Widgets from [Widget_A] Group By [Widget_A].Building_ID
Union Select [Widget_B].Building_ID, "Widget_B" as Widget_Types, Count(*) As Num_Of_Widgets from [Widget_B] Group By [Widget_B].Building_ID
Union Select [Widget_C].Building_ID, "Widget_C" as Widget_Types, Count(*) As Num_Of_Widgets from [Widget_C] Group By [Widget_C].Building_ID
Union Select [Widget_D].Building_ID, "Widget_D" as Widget_Types, Count(*) As Num_Of_Widgets from [Widget_D] Group By [Widget_D].Building_ID
Building_ID Widget_Types Num_Of-Widgets
----------- ------------ --------------
001 Widget_A 3
001 Widget_B 1
002 Widget_B 10
002 Widget_C 5
002 Widget_D 1
003 Widget_A 1
004 Widget_B 12
etc...
What I trying to do
Building _ID Widget_A Widget_B Widget_C Widget_D
---------------- ------------- ------------- ------------- -------------
001 3 0 0 0
002 0 10 5 1
003 1 0 0 0
004 0 12 0 0
Etc..
The CrossTab didn't work, it only gave me the count of Build_IDs per Widget types.
My first query with the union gave me a result table list below
Select [Widget_A].Building_ID, "Widget_A" as Widget_Types, Count(*) As Num_Of_Widgets from [Widget_A] Group By [Widget_A].Building_ID
Union Select [Widget_B].Building_ID, "Widget_B" as Widget_Types, Count(*) As Num_Of_Widgets from [Widget_B] Group By [Widget_B].Building_ID
Union Select [Widget_C].Building_ID, "Widget_C" as Widget_Types, Count(*) As Num_Of_Widgets from [Widget_C] Group By [Widget_C].Building_ID
Union Select [Widget_D].Building_ID, "Widget_D" as Widget_Types, Count(*) As Num_Of_Widgets from [Widget_D] Group By [Widget_D].Building_ID
Building_ID Widget_Types Num_Of-Widgets
----------- ------------ --------------
001 Widget_A 3
001 Widget_B 1
002 Widget_B 10
002 Widget_C 5
002 Widget_D 1
003 Widget_A 1
004 Widget_B 12
etc...
What I trying to do
Building _ID Widget_A Widget_B Widget_C Widget_D
---------------- ------------- ------------- ------------- -------------
001 3 0 0 0
002 0 10 5 1
003 1 0 0 0
004 0 12 0 0
Etc..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As I said in the earlier post, you need to create a crosstab of the union query to pivot the counts.
ASKER
That worked just like I wanted.
Thanks everyone for your help. You guys are awesome!
Thanks everyone for your help. You guys are awesome!
Thanks for the points and glad to help. As you can see ... DCount() is an exact fit for what you are trying to accomplish.
ET
ET
etsherman asked for a justification so I built one. Unfortunately, it ran so long I ended up cancelling it after 15.75 minutes.
Based on the data I used, there were 40 utilities so the queries returned 40 rows. The counts represented counting 1,180,156 detail records. Running the union and crosstab took only a second but the Dlookup ran for over 15 minutes before I finally cancelled it. The DLookup() runs four queries per row of the Utility table so 4 * 40 = 160 queries against over a million rows.
The bottom line is - if your recordsets are small enough, you can get away with anything. If you are using larger recordsets, you have to choose the most efficient method. Rather than have to always decide, I choose to use the efficient method all the time.
Interestingly, when I changed the table to my SQL Server database, the union took about the same amount of time but the DLookup() method was significantly faster and finished in under a minute. That tells me that the problem is with Jet/ACE not being able to optimize the DLookups but SQL Server can.
The data is propritary so I can't upload the database.
Based on the data I used, there were 40 utilities so the queries returned 40 rows. The counts represented counting 1,180,156 detail records. Running the union and crosstab took only a second but the Dlookup ran for over 15 minutes before I finally cancelled it. The DLookup() runs four queries per row of the Utility table so 4 * 40 = 160 queries against over a million rows.
The bottom line is - if your recordsets are small enough, you can get away with anything. If you are using larger recordsets, you have to choose the most efficient method. Rather than have to always decide, I choose to use the efficient method all the time.
Interestingly, when I changed the table to my SQL Server database, the union took about the same amount of time but the DLookup() method was significantly faster and finished in under a minute. That tells me that the problem is with Jet/ACE not being able to optimize the DLookups but SQL Server can.
The data is propritary so I can't upload the database.
@PatHartman ....
<<<The bottom line is - if your recordsets are small enough, you can get away with anything. If you are using larger recordsets, you have to choose the most efficient method. Rather than have to always decide, I choose to use the efficient method all the time.>>>
I agree with your comment and there are some variables that can come into play which can make any method inefficient. As mentioned earlier, when working with large data tables you have to consider the request at hand. I have data tables with over 10+ years of history in them and no I would not use a DCount() method straight on a table of that size. I would simply create a dynamic query to grab the sub-set of records from the history table the use the DCount() method there since the Domain can be a Table Object or a Query Object.
ET
<<<The bottom line is - if your recordsets are small enough, you can get away with anything. If you are using larger recordsets, you have to choose the most efficient method. Rather than have to always decide, I choose to use the efficient method all the time.>>>
I agree with your comment and there are some variables that can come into play which can make any method inefficient. As mentioned earlier, when working with large data tables you have to consider the request at hand. I have data tables with over 10+ years of history in them and no I would not use a DCount() method straight on a table of that size. I would simply create a dynamic query to grab the sub-set of records from the history table the use the DCount() method there since the Domain can be a Table Object or a Query Object.
ET
Pat, this is East and West. Or apples and potatoes if you like.
The question was about 10^3 records and one criteria, while you test on 10^6 records and two criteria and - on top of that - add a killing Group By.
I ran a test using about 10^4 records, also linked from SQL Server, using DCount. Time: 0,025 s
Of course - as for your data - you could have to apply a Group By. But then you would create a query to pull those grouped or distinct records and then, in a new query, use that query to retrieve the DCount values.
/gustav
The question was about 10^3 records and one criteria, while you test on 10^6 records and two criteria and - on top of that - add a killing Group By.
I ran a test using about 10^4 records, also linked from SQL Server, using DCount. Time: 0,025 s
Of course - as for your data - you could have to apply a Group By. But then you would create a query to pull those grouped or distinct records and then, in a new query, use that query to retrieve the DCount values.
/gustav
The Group By wasn't necessary. The "left" table only contains 40 unique rows. I started building the query against the big table and then changed it and forgot to remove the group by. It shouldn't impact the timing but I'll run it again to check.
Except for the size of the recordset, the example should mimic what the OP was doing. The point of the exercise is that you should know the relative efficiency of various methods and choose the efficient method unless you have a specific reason for choosing the less efficient method. Since I know that domain functions are less efficient than joins, I automatically use joins. I would never even consider using a domain function unless the join was to an aggregate query and I needed the final product to be an updateable query.
Except for the size of the recordset, the example should mimic what the OP was doing. The point of the exercise is that you should know the relative efficiency of various methods and choose the efficient method unless you have a specific reason for choosing the less efficient method. Since I know that domain functions are less efficient than joins, I automatically use joins. I would never even consider using a domain function unless the join was to an aggregate query and I needed the final product to be an updateable query.
ASKER
I may have spoken too soon. When I added my 5th table using DCount, it brought Access to its knees.
I now trying other solution and may try moving over to MS SQL.
I’ll keep updating my progress
Thanks
I now trying other solution and may try moving over to MS SQL.
I’ll keep updating my progress
Thanks
bnemmers ... Your original question ...
<<<I have 5 tables, table 1 is a list of building numbers, about 1000 records and the other 4 tables are list widget types in the buildings. I'm trying to create a query that will list by row the; building IDs, widget_1_count, widget_2_count, widget_3_count, widget_4_count. If the Building_ID count is zero I'd still like to list it in the results as zeros across all four fields>>>
1.) Original question said you had 4 widget tables ... now you are saying you are adding more tables?
2.) How many records are in the widget tables??
3.) Make sure you have an Index on the Building_ID field in each of the widget tables.
ET
<<<I have 5 tables, table 1 is a list of building numbers, about 1000 records and the other 4 tables are list widget types in the buildings. I'm trying to create a query that will list by row the; building IDs, widget_1_count, widget_2_count, widget_3_count, widget_4_count. If the Building_ID count is zero I'd still like to list it in the results as zeros across all four fields>>>
1.) Original question said you had 4 widget tables ... now you are saying you are adding more tables?
2.) How many records are in the widget tables??
3.) Make sure you have an Index on the Building_ID field in each of the widget tables.
ET
ASKER
I was trying to keep my question simple, and I thought I could scale it up to solve my problem. I really have 13 type of widgets and my last column I need to the total number of widgets per building.
Building, Widget1, Widget2, …… Widget13, Total Widget count
001 2 0 26 28
When I add the 5th widget type, which in only 11,000 records, Access slow to a crawl.
Then I tried only using the 5th widget and removed the first 4 and Access still crawled.
The result where perfect but the speed is a big issue
Is there something I’m missing?
Thanks
Building, Widget1, Widget2, …… Widget13, Total Widget count
001 2 0 26 28
When I add the 5th widget type, which in only 11,000 records, Access slow to a crawl.
Then I tried only using the 5th widget and removed the first 4 and Access still crawled.
The result where perfect but the speed is a big issue
Is there something I’m missing?
Thanks
You probably miss an index.
See my note above. I tested with 10^4 records.
/gustav
See my note above. I tested with 10^4 records.
/gustav
Sounds like you have attempted to implement a set of spreadsheets as a database. You probably should not have 13 tables to hold the same type of data. Use one table with a type code. If that were the situation, all you would need would be a crosstab query to pivot the data.
<<<<Then I tried only using the 5th widget and removed the first 4 and Access still crawled.>>>>
If all tables are indexed properly and you are are still having issues then I would create separate Count queries on each of the thirteen tables that Grouped by Building_ID and Count Widgets within each. Then use your Select Query and the DLookUp() Function instead of the DCount() Function as shown below.
Select Building_ID, DLookUp("Widget_1_Count", "QryWidgetTable1", "[Building_ID = '" & [Building_ID] & "'") As Widget_1_Count, DLookUp("Widget_2_Count", "QryWidgetTable2", "[Building_ID = '" & [Building_ID] & "'") As Widget_2_Count, DLookUp("Widget_3_Count", "QryWidgetTable3", "[Building_ID = '" & [Building_ID] & "'") As Widget_3_Count, DLookUP("Widget_4_Count", "QryWidgetTable4", "[Building_ID = '" & [Building_ID] & "'") As Widget_4_Count FROM BuildingTable;
ET
If all tables are indexed properly and you are are still having issues then I would create separate Count queries on each of the thirteen tables that Grouped by Building_ID and Count Widgets within each. Then use your Select Query and the DLookUp() Function instead of the DCount() Function as shown below.
Select Building_ID, DLookUp("Widget_1_Count", "QryWidgetTable1", "[Building_ID = '" & [Building_ID] & "'") As Widget_1_Count, DLookUp("Widget_2_Count", "QryWidgetTable2", "[Building_ID = '" & [Building_ID] & "'") As Widget_2_Count, DLookUp("Widget_3_Count", "QryWidgetTable3", "[Building_ID = '" & [Building_ID] & "'") As Widget_3_Count, DLookUP("Widget_4_Count", "QryWidgetTable4", "[Building_ID = '" & [Building_ID] & "'") As Widget_4_Count FROM BuildingTable;
ET
ASKER
Yes, I have a spreadsheets but my formulas where taking about 5 to 10 min to complete. The problem I have is my raw data source in very, very cryptic, it’s an export into a one large spreadsheet with 70,000+ rows and 32 columns. It changes weekly.
So I created spreadsheets to extract each of the widget types on their own spreadsheet then I have a main spreadsheet that collects and sums the total widgets pivoting on the Building ID. All works but the time to refresh the spreadsheet was very long.
I was hoping moving this into a DB would speed-up the process.
I’m not using table(s) for my DCount query, I’m using queries from the raw data to isolate each of the widget types. To isolate each of the widget types I need to use 3 or 4 of the raw data fields with sub-parsing of the field contents to determine which widget type it is. And the field counts depends on which widget type I’m looking for, it sucks..
I did create a queries to separate each of the widget, which I’m using to do the final DCount.
All maybe lost and I’ll have to stick with my spreadsheet solution.
I'll keep trying
Thanks
So I created spreadsheets to extract each of the widget types on their own spreadsheet then I have a main spreadsheet that collects and sums the total widgets pivoting on the Building ID. All works but the time to refresh the spreadsheet was very long.
I was hoping moving this into a DB would speed-up the process.
I’m not using table(s) for my DCount query, I’m using queries from the raw data to isolate each of the widget types. To isolate each of the widget types I need to use 3 or 4 of the raw data fields with sub-parsing of the field contents to determine which widget type it is. And the field counts depends on which widget type I’m looking for, it sucks..
I did create a queries to separate each of the widget, which I’m using to do the final DCount.
All maybe lost and I’ll have to stick with my spreadsheet solution.
I'll keep trying
Thanks
Rather than exporting the types to separate spreadsheets, try normalizing the data so you end up with a single table with many fewer columns. Then you can use crosstabs to do the counting.
Seeing a sample of the starting data might be helpful if it isn't proprietary.
Seeing a sample of the starting data might be helpful if it isn't proprietary.
benmmers ... just looking at your previous comment ... Sounds like you need to move that entire process into a Access database. You got a mix of different applications (spreadsheets & databases) going and seems like the process has become extremely difficult and hard to follow. Experts here can answer questions about the functionality of Access but the try and build a complete functioning solution without having all the data components and the logic regarding the objective is kind of impossible.
IMO there's a time for a spreadsheet and there is a time to drop the spreadsheet approach and just let a database do the job. Access is fully capable of handling this for you.
ET
IMO there's a time for a spreadsheet and there is a time to drop the spreadsheet approach and just let a database do the job. Access is fully capable of handling this for you.
ET
Please start a new question since this one is closed and we'll take the process to the next step.
create a query with Buildings and each of the queries to count. Link on BuildingID to each query. For each of the link lines, edit the properties to show all records in the Buildings table and only those records in the related table where they exist.
then put what you want to see on the grid