Solved

Count number of Widgets accross 4 tables

Posted on 2016-09-26
30
44 Views
Last Modified: 2016-09-28
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
0
Comment
Question by:bnemmers
  • 11
  • 7
  • 5
  • +2
30 Comments
 
LVL 18
ID: 41816908
make queries to count (would this be SUM or does each have a value of 1?) each widget grouped by BuildingID. In each query, label the field to count with something different so that when tables are joined, the fieldnames are different.

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
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41816912
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41816934
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.
0
 
LVL 18
ID: 41816973
... and now you have 3 ways to do the same thing :)
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41817023
@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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41817272
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
0
 
LVL 18
ID: 41817279
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.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41817757
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
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41817764
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41817793
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;

Open in new window

/gustav
1
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41817827
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
0
 
LVL 1

Author Comment

by:bnemmers
ID: 41818370
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..
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
ID: 41818408
bnemmers ... did you try the DCount() solution???  What you are getting is exactly why I proposed the DCount() method shown below ...

This is the complete SQL for all 4 tables ... just change the table & field names to your actual names.  I adjusted it to not include the Nz() function as DCount() will already return 0 for no records as pointed out by Gustav.

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;

ET
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41818494
As I said in the earlier post, you need to create a crosstab of the union query to pivot the counts.
0
 
LVL 1

Author Closing Comment

by:bnemmers
ID: 41818500
That worked just like I wanted.

Thanks everyone for your help. You guys are awesome!
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41818564
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41818662
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.
UnionVSDLookup.JPG
1
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41818921
@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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41819208
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41819996
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.
0
 
LVL 1

Author Comment

by:bnemmers
ID: 41820179
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
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41820255
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
0
 
LVL 1

Author Comment

by:bnemmers
ID: 41820283
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41820287
You probably miss an index.

See my note above. I tested with 10^4 records.

/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41820348
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.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41820368
<<<<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
0
 
LVL 1

Author Comment

by:bnemmers
ID: 41820411
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41820424
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.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41820498
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
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41820541
Please start a new question since this one is closed and we'll take the process to the next step.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now