Pivot Table with Conditions Format

Hello,

I have an extract that I put into excel and did a pivot table on. It is basically Customer, Insurance Carrier, and Policy Type. There are 12,000 lines. I am trying to find only those customers that have a Policy Type = Umbrella(C). HOWEVER, I want to show all of there policies and Insurance Carriers.

I attached a file. There are many other field, but there are the important one. I tried in Excel, If access or SQL is better then I'll take that.

Also, just manually collapsing the pivot table manually is not an option due to the number of lines.
Umbrella-Collapse.xlsx
Michael FranzCFOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FlysterCommented:
If you're willing to use Access, it's pretty simple. You make one query with just Umbrella accounts and add that to a query with all accounts and join on the company name. See attached

Flyster
Insurance.accdb
0
Michael FranzCFOAuthor Commented:
I am getting double results. I have attached a word docx with the query build outs and a results example.
Insurance-Query-Results.docx
0
FlysterCommented:
Sorry for the delay. Had to go to work. Try changing qryUmbrella to this:

SELECT tblCustomers.[Customer – Firm Name], tblCustomers.Company, tblCustomers.[Policy Number], tblCustomers.[Policy Type]
FROM tblCustomers INNER JOIN qryUmbrella ON tblCustomers.[Customer – Firm Name] = qryUmbrella.[Customer – Firm Name];

Basically, you're just grouping those records. That should get rid of the duplicates.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Michael FranzCFOAuthor Commented:
Not sure how I understand the diff from the query you gave me above vs the query created in access. They appear to be the same.

I did this

SELECT ReportExpRenew.[Customer – Firm Name], ReportExpRenew.Company, ReportExpRenew.[Policy Number], ReportExpRenew.[Effective Date], ReportExpRenew.[Expiration Date], ReportExpRenew.[Policy Executive], ReportExpRenew.[Policy Representative], ReportExpRenew.[Policy Type], ReportExpRenew.[Total Cost]
FROM ReportExpRenew INNER JOIN [ReportExpRenew Query] ON ReportExpRenew.[Customer – Firm Name] = [ReportExpRenew Query].[Customer – Firm Name]
GROUP BY ReportExpRenew.[Customer – Firm Name], ReportExpRenew.Company, ReportExpRenew.[Policy Number], ReportExpRenew.[Effective Date], ReportExpRenew.[Expiration Date], ReportExpRenew.[Policy Executive], ReportExpRenew.[Policy Representative], ReportExpRenew.[Policy Type], ReportExpRenew.[Total Cost];

Open in new window

0
FlysterCommented:
Again, my appologies. I'm at work and don't have access to the only terminal with Access. Take a look at qryUmbrellaCustomers. It gives the 10 records you're looking for. And yes, this one is in Access 97!
Insurance.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael FranzCFOAuthor Commented:
OK..... let me see if I understand outside of the programming. You use the qryUmbrella just to collect the names of the customers that actually have an Umbrella policy. Then use the customer list that was created in the first query to extract the corresponding data with the second query.
0
FlysterCommented:
Yes. And by grouping the customers, you eliminate the duplicates.
0
Michael FranzCFOAuthor Commented:
Awesome and thank you!!! for the fast help
0
FlysterCommented:
Thank you! I'm glad it worked out.
0
Michael FranzCFOAuthor Commented:
I need one more piece of help. How do I reopen this question. The answer is correct, but now I need to use the thoughts and take a deeper dive into another project that uses the same data.
0
FlysterCommented:
You can open a new question and use this one as a reference. I've never posted a question myself so I'm not familiar with the format. If it's like posting these comments, there should be an option to insert a hyperlink. Select link and insert the url of this question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.