Counting Unique IDs per Date

[MatthewH]

Hi everyone,
I have a set of data in Access that looks similar to the Excel file I've attached.
I am hoping someone can help me use an Access Query (or maybe VBA is the way to go?)

I need to create a count of the following:
1) # of Primary Keys per Company
2) # of Unique Packages per Company
3) # of Statuses that say Case/Closed/Escalated per Company (Each would be their own output)
4) THIS IS THE HARD ONE: On a Company level (per company), how many Statuses said Case/Closed/Escalated per Package. This is different than the one above it, as it would count two identical package #s as one under the same company when counting. As the one above, each would have their own output (Case/Closed/Escalated)

I was hoping to get this sort of output in a table where the left-most column just included each Company ID and then going to the right.

Any help as to how this could be done in queries would be helpful, I keep seeing that people are using Crosstab Queries for similar stuff but wasn't able to get a grasp on it myself.

 

[CJ_London]

as it would count two identical package #s as one under the same company when counting

for this one, use SELECT DISTINCT

 

[plog]

All four of your queries are Totals Queries (https://support.office.com/en-us/ar...-a-query-430A669B-E7FD-4C4B-B154-8C8DBBE41C8A), give that link a read then tackle #1.

#2 will require a sub-query to get all your unique packages per company. Create a query with just those 2 fields, make it a totals query and leave 'Group By' underneath each field. Then build your final query upon that query.

#3 is a simple totals query like #1, however you will be applying criteria.

#4 is essentially a combination of techniques for #2 & #3. It will require you apply criteria to your sub-query, then build a totals query upon it.

Give them all a shot and see if you can make them work. Post back here your issues--demonstrating it with data will help a lot.

 

[MatthewH]

Thank you both for your help! I'll start tackling these now with your guidance. Quick question, is there any way to put this data all into a singular table once it's finished? Just by Company#?
They all are related to company #? Columns would be as such:

Company# | 1 | 2 | 3a | 3b | 3c | 4a | 4b | 4c |

 

[plog]

Yes, you could put all of the queries (1 - 4c) into a UNION then build a cross-tab upon that UNION. Let's cross that bridge when we get there though, that's more of an advanced level class.

 

[MatthewH]

I know I haven't posted in this thread in forever but I am still working on it and have come back to try and work on the next step.

I've completed the four queries I need but now I'm attempting to put them all together into a singular table.

Each query outputs a different number of rows (because if the count is zero it isn't being output).

The first query contains all the companies that I would want in Column A.
How do I get the other queries to be added as new columns and then (as a final step), fill in whatever is blank as zero? (So that we would have the count as zero instead of just blank.)

Any help is super appreciated, I didn't fully understand the UNION / cross-tab upon the union thing but I hope someone can explain it better!

 

[plog]

Different number of rows isn't a problem with UNION queries, different number of columns is. That means when you create the UNION you must ensure that each individual SELECT has the same number of fields in it--and its good practice to make sure they have the same names as well.

Looking back on your initial post, let's work with the first 2 queries. Let's say you did #1 and named the resulting query 'A', it has a field called [Company] and a field called [TotalPrimaryKeys]. Also, you did #2 and named the query 'B, it has a field called [Company] and [UniquePackages]. To put them in a UNION you would use this code:

SELECT Company, "Total Primary Keys" AS TotalField, [TotalPrimaryKeys] AS TotalValue
FROM A
UNION ALL
SELECT Company, "Unique Packages" AS TotalField, [UniquePackages] AS TotalValue
FROM B

Run that and you will get a dataset like this:

Company, TotalField, TotalValue
XYZ Co., Total Primary Keys, 17
ZZZ Inc., Total Primary Keys, 2
XYZ Co., Unique Packages, 14
ZZZ Inc., Unique Packages, 3

That's just combining the first 2 queries. Make sure you do all 4--and here's the best part--you must write SQL for a UNION in Access, you can't use the drag and drop interface. Use my SQL as a guide and make yours work. Then post back here and we can do the cross-tab.

 

[MatthewH]

Different number of rows isn't a problem with UNION queries, different number of columns is. That means when you create the UNION you must ensure that each individual SELECT has the same number of fields in it--and its good practice to make sure they have the same names as well.

Looking back on your initial post, let's work with the first 2 queries. Let's say you did #1 and named the resulting query 'A', it has a field called [Company] and a field called [TotalPrimaryKeys]. Also, you did #2 and named the query 'B, it has a field called [Company] and [UniquePackages]. To put them in a UNION you would use this code:

SELECT Company, "Total Primary Keys" AS TotalField, [TotalPrimaryKeys] AS TotalValue
FROM A
UNION ALL
SELECT Company, "Unique Packages" AS TotalField, [UniquePackages] AS TotalValue
FROM B

Run that and you will get a dataset like this:

Company, TotalField, TotalValue
XYZ Co., Total Primary Keys, 17
ZZZ Inc., Total Primary Keys, 2
XYZ Co., Unique Packages, 14
ZZZ Inc., Unique Packages, 3

That's just combining the first 2 queries. Make sure you do all 4--and here's the best part--you must write SQL for a UNION in Access, you can't use the drag and drop interface. Use my SQL as a guide and make yours work. Then post back here and we can do the cross-tab.

I'm starting to work on it and I'll get back to you in 5 minutes or so once I figure out the entirety of the code and UNION them all together. One question: I know you stated it must have the same number of columns. The OUTPUT is the same number of columns but for the second one I use a filter so it has 3 columns in the design view (Company, Count, Where statement). How do I work around this since both columns used are the same for the Count and Where statement? Or does it not matter?

EDIT: I tried the code and it seemed something went wrong when it asked me for parameter values for two things. I just clicked OK on them to see what would happen and I got blanks for all the TotalValue cells but everything else seemed to be fine.
The parameter values it asked for where the last column (as I'm sure you could piece together.) Not sure at this point how to change it, going to fiddle around with it a bit.


EDIT2: I just solved it, I got them all together! What's the next step?

 

[plog]

I just solved it, I got them all together! What's the next step?

Can you post your SQL? Just want to see what we are working with.

 

[MatthewH]

Can you post your SQL? Just want to see what we are working with.

SELECT [Company Id], "Alert Count" As TotalField, [CountOfCompany Id] as TotalValue
FROM AlertCountPerCompany
UNION ALL
SELECT [Company Id], "Approved X Per Company" As TotalField, [CountOfCompany Status] as TotalValue
FROM CountApproveX
UNION ALL
SELECT [Company Id], "Approved Y Per Company" As TotalField, [CountOfCompany Status] as TotalValue
FROM CountApproveY
UNION ALL
SELECT [Company Id], "Approved Z Per Company" As TotalField, [CountOfCompany Status] as TotalValue
FROM CountApproveZ

 

[plog]

Looks good. Now, to get the results you want, you need a crosstab query (https://support.office.com/en-us/ar...ab-query-8465B89C-2FF2-4CC8-BA60-2CD8484667E8). It turns the values in one of your columns ([Company Id]) into column headings. For this we can use the drag and drop interface.

1. Make a new query and bring in your UNION query.
2. Click the 'Crosstab' option in the ribbon.
3. Bring down all 3 fields.
4. Change the 'Group By' under TotalValue to 'Sum'.
5. Underneath that, in the Crosstab row use these:

Company Id -> Column Heading
TotalField -> Row Heading
TotalValue -> Value

Run that and you have your data. The bad news is that this could take a while to run depending on how much data you have. A cross-tab is bad enough, but its built on a UNION which itself is built on 4 other queries. But to get the layout you want, you need a crosstab.

 

[MatthewH]

Looks good. Now, to get the results you want, you need a crosstab query (https://support.office.com/en-us/ar...ab-query-8465B89C-2FF2-4CC8-BA60-2CD8484667E8). It turns the values in one of your columns ([Company Id]) into column headings. For this we can use the drag and drop interface.

1. Make a new query and bring in your UNION query.
2. Click the 'Crosstab' option in the ribbon.
3. Bring down all 3 fields.
4. Change the 'Group By' under TotalValue to 'Sum'.
5. Underneath that, in the Crosstab row use these:

Company Id -> Column Heading
TotalField -> Row Heading
TotalValue -> Value

Run that and you have your data. The bad news is that this could take a while to run depending on how much data you have. A cross-tab is bad enough, but its built on a UNION which itself is built on 4 other queries. But to get the layout you want, you need a crosstab.

I posted this elsewhere and got a suggestion for nulling the other values:

"1. Add your Master table and all 4 queries
2. Create Left Joins from your Master table to each of your 4 queries
3. Return column A from your Master table
4. Return each column B from each of your 4 queries for the other four columns in a calculated field, using the Nz function to return a 0 for any unmatched records. i.e.

Code:
Query1ColumnB: Nz([Query1].[ColumnB],0)+0Query2ColumnB: Nz([Query2].[ColumnB],0)+0Query3ColumnB: Nz([Query3].[ColumnB],0)+0Query4ColumnB: Nz([Query4].[ColumnB],0)+0
"
Would this at all do what's needed?

BACK ON TOPIC:
I'm trying to do this Crosstab and it's telling me I have too many column headers (34000 or so). Company ID is the Column Heading and there are 34000 company IDs, should they be the Row Heading instead and TotalField be the Column Heading? I think you had that backwards, I could be very mistaken though.

Edit: It now worked when I switched it! One last important question: How can I get the blank values to become 0? I'm sure this is a quick answer but I'm so lost once we enter these kinds of jobs, it's way beyond my scope of knowledge haha.

 

[plog]

According to your initial post, no:

I was hoping to get this sort of output in a table where the left-most column just included each Company ID and then going to the right.

If you want company names as column headers then a cross-tab is the way to go. But if you have over 34000 companies, I question why you would want to do this in the first place.

 

[MatthewH]

According to your initial post, no:



If you want company names as column headers then a cross-tab is the way to go. But if you have over 34000 companies, I question why you would want to do this in the first place.

ColumnA contains all of my Company IDs. Column B is the count of filter X, C is the count of filter Y, D if the count of filter Z. It works perfect! My last question (in red above) is just how to get zeros for the results that don't have values. Is that possible?

 

[plog]

Yes, you need to manually edit the SQL of your cross-tab. The first line should be like this:

TRANSFORM Nz(Sum([TotalValue]),0) AS ValueField

 

[MatthewH]

Yes, you need to manually edit the SQL of your cross-tab. The first line should be like this:

TRANSFORM Nz(Sum([TotalValue]),0) AS ValueField

I don't think you recognize the extent to which you've saved my butt.
Thank you so much for all of your help. You were about as good as it can get when it came to teaching me and helping me, as I was able to do some of the coding on my own to learn it for the next time I have to make a sheet like this. Thank you!!!!!!!

 

[plog]

No problem, glad we got there.