By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,459 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

How to show in a report no duplicate for the first and total only for the next field?

100+
P: 180
I have a table name[list] which a field name [Company], [PA4] and [SA4]. In the company field it has a list of APMC,APMC,FEC,PNPI,PNPI,VIL. In the field PA4 it has a list of 1,1,2,1,2,2. And in the field SA4 it has a list of 2,2,1,2,1,1. I have a report name [JobReport], and I want to show only in the Company field that has no duplicate values, so it must show only APMC,FEC,PNPI,VIL. And in the PA4 and SA4 field, I want to show only total of each Company.
I tried in a report design to set the hide duplicate property of company into Yes. But I don't know how to show only the total of each company for PA4 and SA4 fields.
Please see image link.

Feb 22 '17 #1

✓ answered by jforbes

It sounds like you could use a Query like the following as the basis for your Report:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   List.Company
  3. , Sum(List.PA4) AS SumOfPA4
  4. , Sum(List.SA4) AS SumOfSA4
  5. FROM List
  6. GROUP BY List.Company

Share this Question
Share on Google+
3 Replies


100+
P: 180
=Sum(IIf([Company]="APMC",[PA4]))
I figured out the sum for each company, using the formula above. My problem now is, How can I only companies with no duplicates and no spaces below. example, the first column of company is APMC then followed by FEC, PNPI and VIL.
They must look something like the image link I provided.
Feb 22 '17 #2

jforbes
Expert 100+
P: 1,107
It sounds like you could use a Query like the following as the basis for your Report:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   List.Company
  3. , Sum(List.PA4) AS SumOfPA4
  4. , Sum(List.SA4) AS SumOfSA4
  5. FROM List
  6. GROUP BY List.Company
Feb 22 '17 #3

100+
P: 180
Thanks alot jforbes, it works perfectly. Now I can design my report easily. Thanks again!
Feb 23 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.