Solved

Sort report data into columns?

Posted on 2003-12-07
17
426 Views
Last Modified: 2012-06-21
I have a report with several fields - job #, invoice #, invoice amount, cost type, supplier and amount.

The cost type field is one of these values:

1. Color separation
2. copy writing
3. Miscellaneous
4. Printing
5. Photography
6. Studio Time

The supplier, cost type and amount are from the same table. SO there for each Supplier, there is a Cost Type and amount.

The user requires to have 6 columns on the report for each cost type. Under each colum should be the appropriate cost amount i.e. for color separation there should be an amount.

How can I sort the fields or rather data on the report into these columns?

Thanks.
CD

p.s. Someone told me that my only option is to use 6 subreports. Is this true?
0
Comment
Question by:clinthammer
  • 10
  • 7
17 Comments
 
LVL 18

Expert Comment

by:bonjour-aut
Comment Utility
Ther is many posiibiities:

generaly speaking, you should provide data in a quer y in the right manner:

do you know how to do crosstab query ?

with a normal query you can do 6 fileds, one for each costtype with the Switch function;

CostType1:switch(costtype="CT1",mount,1=1,0) ....

Regards, Franz
0
 
LVL 18

Expert Comment

by:bonjour-aut
Comment Utility
if you would like to have a better example, post your rlevant table and fielnames as you have them

Regards, Fanz
0
 

Author Comment

by:clinthammer
Comment Utility
Tables: Production
Fieldnames: Autonumber, Markets, Job#, Invoice #, Invoice amount, Client Code, Product code, market code, serial#, month, year, total.
Note: All data for this table is entered in a form "Production".


Table: Job Costs
Fieldnames: Autonumber, Cost Type, Supplier, Amount
Note: Cost Type is a drop down box with values from Cost Type table.
         Autonumber field is set to Autonumber from Production table. This is done in the Production form where the table Job Costs is in a subform.


Table: Cost Type
fieldnames: Autonumber, Cost Type

In the query, Job Costs, the only fields I have are:

Client Code, Job#, Invoice#, Invoice Amount from production table
Supplier, Cost Type, Amount from Job Costs table.

My report "Job Costs" is based on the query Job Costs. The report is first grouped by Client Code, then Job#.

I have never used the "switch" function in a query but if you can give me a sample, I'm sure I can comprehend right away.

0
 
LVL 18

Expert Comment

by:bonjour-aut
Comment Utility
the switch function is just an alterbative ti the Iif Function
the syntax is: Switch(condition1,value1,condition2,value2,condition3,value3,....)
so you can be more flexible
i use it generally instead of Iif, because often yo want to add something later on. furtheron it should perforrm faster.
as i dont know your Job Type Values  i assume T1,T2,T3,.... for this example

so your query should be something like:


SELECT Production.[Client Code], Production.Job#, Production.Invoice#, Production.[Invoice Amount], [Job Costs ]. Supplier, [Job Costs ]. [Cost Type], [Job Costs ]. Amount ,
Switch([Job Costs ]. [Cost Type] = "T1",[Job Costs ]. Amount ,1=1,0) AS Type1Cost,
Switch([Job Costs ]. [Cost Type] = "T2",[Job Costs ]. Amount ,1=1,0) AS Type2Cost,
............
FRROM .........

This will give you additional fieds having either a value or 0
so you can do also a summ in ther report,  if you like

this fields you can place in your report in rows in the detailsection

Regarsds, Franz
0
 
LVL 18

Expert Comment

by:bonjour-aut
Comment Utility
as you may have noticed, the 1=1 looks a bit queer

the reason is, that the Swich gives you a null-value if not condition is true
this may cause problems i subsequent calculatios, so usually give as last statement 1=1,0 with numeric values

Regards, Franz
0
 

Author Comment

by:clinthammer
Comment Utility
Hi Franz,

Thanks for the help. A few comments:

Job type is actually Cost Type. Cost Type includes the following:

1. Color separation
2. Copy writing
3. Miscellaneous
4. Printing
5. Photography
6. Studio Time

Hence, my query will be:

SELECT Production.[Client Code], Production.Job#, Production.Invoice#, Production.[Invoice Amount], [Job Costs ]. Supplier, [Job Costs ]. [Cost Type], [Job Costs ]. Amount ,

Switch([Job Costs ]. [Cost Type] = "Color Separation",[Job Costs ]. Amount ,1=1,0) AS Type1Cost,

Switch([Job Costs ]. [Cost Type] = "Copy Writing",[Job Costs ]. Amount ,1=1,0) AS Type2Cost,

Switch([Job Costs ]. [Cost Type] = "Miscellaneous",[Job Costs ]. Amount ,1=1,0) AS Type3Cost,

Switch([Job Costs ]. [Cost Type] = "Printing",[Job Costs ]. Amount ,1=1,0) AS Type4Cost,

Switch([Job Costs ]. [Cost Type] = "Photography",[Job Costs ]. Amount ,1=1,0) AS Type5Cost,

Switch([Job Costs ]. [Cost Type] = "Studio Time",[Job Costs ]. Amount ,1=1,0) AS Type6Cost,

FROM .........

Question 1: since my sql knowledge is limited, why do some fields have brackets while some don't e.g.

SELECT Production.[Client Code], Production.Job#, Production.Invoice#, Production.[Invoice Amount], [Job Costs ]. Supplier, [Job Costs ]. [Cost Type], [Job Costs ]. Amount ,

I thought the brackets were limited to text or numbers field types but:
Client Code is a text field
Job# is a text field
Invoice# is a text field
Invoice Amount is a number
Cost Type is a text field
Supplier is a text field
Amount is a number

0
 
LVL 18

Expert Comment

by:bonjour-aut
Comment Utility
You need brackets, where you have non-standard object names, e.g. spaces

i do object names without spaces, only seperator used "_" (underscore)

in your case i would do :  Client Code >> ClientCode, Cost Type >> CostType

you need not to change that now afterwards, just be aware of the need of brackets.
some people alway do brackets for that reason

... FROM Production INNER JOIN [Job Costs] ON Production.Job# = [Job Costs].MissingJob#;

Please note, that you will need a field in the Job Costs table, indicating to which Job# this recrd belongs - i put the MissingJob# in the  query. the fieldname is your decision name.

regards, Franz
0
 

Author Comment

by:clinthammer
Comment Utility
Franz,

My sql statement includes the above mentioned and the following:

FROM Production INNER JOIN [Job Costs] ON Production.Autonumber=[Job Costs].Autonumber
GROUP BY Production.Autonumber, Production.[Job #], [Job Costs].Supplier, [Job Costs].[Cost Type], [Job Costs].Amount, Production.[Client Code], Production.Total, Production.[Invoice #], Production.[Invoice Amount];

do I need to add the TypexCost (i.e. x = 1, 2, 3 or whatever relevant number) in the sql statement in the Group By line?

CD
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 18

Expert Comment

by:bonjour-aut
Comment Utility
no, the grouping [Job Costs].[Cost Type], is suficent for that

what i am wondering about is the grouping by [Job Costs].Amount

this will result in no grouping at all, because all records will  have slightly different amounts ?
same for

furtheron: Production.Total, Production.[Invoice #], Production.[Invoice Amount];
are redundant for my opinion, grouping by one of them should be sufficent

!!!!  most important: if you use grouping, you must have DomainFunctions in the columns as Sum(), Count(), First(), .......................

so i would start with Grouping left out. if the query is correct, the start to add gruoping and decide, which type of aggregation is fiting to each column.

Regards, Franz
0
 

Author Comment

by:clinthammer
Comment Utility
Franz,

Would my grouping affect the problem below.

For each of the TypeCost fields (6 of them), I have placed them in the detail section of my report.

The label for each of these fields (Color Separation, Copy Writing etc.). is in the header of another field - so that the labels don't get repeated.

Now in the detail section, I am trying to get the total of each row of TypeCost records.
This is my formula which is placed in another text box:

=([Type1Cost])+([Type2Cost])+([Type3Cost])+([Type4Cost])+([Type5Cost])+([Type6Cost])

However, when I go that I get all the values in the TypeCost fields contactenated (sp?).
e.g if Type1Cost = 0
Type2Cost = 200
Type3Cost = 0
Type4Cost =0
Type5Cost =0
Type6Cost = 0

The value for the the total of each row is 02000000. Am I entering the incorrect operator for addition?
0
 
LVL 18

Accepted Solution

by:
bonjour-aut earned 250 total points
Comment Utility
1. Grouping in query and report are different tasks. If you do a report, you need not to do grouping in the query. it is redundant. in the report you can do also sums and counts directly in the repective group header or footer.

2. row total of cost: you do not need to do a summ by formula, you have the sum already in the [Job Costs].Amount field in the query.

3. nevertheless remarks on the result of the addition by formula: as it looks, the fields in the report are string, so each value needs to wrapped in the Eval() function: =Eval([Type1Cost])+Eval([Type2Cost])+Eval([..

remark on the sum() or count() in the Group header/footer: this works only on the bound fields from the datasource, not on calculated fields. so e.g. Sum([Job Costs].Amount ) will always give correct sums corresponding to the actual grouplevel in nested groupings. The Sum(CalculatedFormfield) will give improper results.

Regards, Franz
0
 

Author Comment

by:clinthammer
Comment Utility
The Eval() function works brilliantly. My report is coming together perfectly as well.

There is only one more problem is am trying to tackle - it deals with sorting. If I can't figure it out I will post back here.

Thanks bonjour-aut/Franz.

CD
0
 

Author Comment

by:clinthammer
Comment Utility
Actually my sorting problem has something to do with the switch function i.e. 1=1,0

As of now for each of the TypeCost fields (6 of them), I have placed them in the detail section of my report.

The label for each of these fields (Color Separation, Copy Writing etc.). is in the header of another field - so that the labels don't get repeated.

So basically when I run the report it lists each value for the typecost. The problem is with the typecost fields that have 0 as a value.

e.g. Job# = 01/12/LP/11223-03-04
Studio time (type6cost) = 80
Photography (type5cost) = 90
Printing (type4cost) = 8000
Miscellaneous (type3cost) = 90 and 10
Copy Writing (type2cost) = 80
Color Separation (type1cost) = 70 and 90

Hence my report looks like this:

Total  Color Separation  Copy Writing   Miscellaneous   Printing   Photography   StudioTime
80                0                     0                    0                  0               0                  80
90                0                     0                    0                  0               90                 0
8000            0                     0                     0                8000           0                   0
90                0                    0                      90               0               0                   0
70                70                  0                       0                0               0                   0
80                0                    0                      0                 0               0                   80
10                0                    0                     10                 0              0                   0
80                0                    80                    0                  0               0                  0
90               90                   0                       0                 0                0                 0

Instead of the 0's I only want the fields that have values. So rather my report should look like:


Total       Color Sep      Copy Writing   Miscellaneous   Printing   Photography   StudioTime
8410                70                     80             90             8000          90                80    
180                  90                      0              10               0              0                 80

So I am guesing I need to write a function/code whereby if the field is 0 then check for the next field. Some sort of loop maybe?

I will work on this and keep you updated.

CD
0
 
LVL 18

Expert Comment

by:bonjour-aut
Comment Utility
you have two choices:

A. doing a grouping on the base query:

SELECT    ...... Sum(Switch(....6.)) AS StudioTime, Sum(Switch(..5.)) AS Photgraphy, ...
GROUP BY Job#

B. doing a reportgrouping on Job# and having the data fields as   =Sum(type6cost)
yo can leave the detail section empty, if you do not need details

Regards, Franz
0
 

Author Comment

by:clinthammer
Comment Utility
I have grouped them.

This is the result whenI group them by Job#:

Total       Color Sep      Copy Writing   Miscellaneous   Printing   Photography   StudioTime
8590                160                     80             100             8000          90                160    

I don't think you understand my problem :) What Iwant the query to do it if e.g.

Switch([Job Costs].[Cost Type]="Color Separation",[Job Costs].Amount,1=1,0)

If the cost type here is color separation, it assigns the value. Else, if it is not Color Separation, it gives it a null value. Instead of a null value, I want it to go to the next record until it finds a non null value.

This way I can eliminate the zeroes in my report. Need to see the db to be more clear?

Thanks.
CD
0
 
LVL 18

Expert Comment

by:bonjour-aut
Comment Utility
looks like i did not understand your question before. now that i see what you mean, it does not really help:

you do not want

Total  Color Separation  Copy Writing   Miscellaneous   Printing   Photography   StudioTime
80                0                     0                    0                  0               0                  80
90                0                     0                    0                  0               90                 0
8000            0                     0                     0                8000           0                   0
90                0                    0                      90               0               0                   0
70                70                  0                       0                0               0                   0
80                0                    0                      0                 0               0                   80
10                0                    0                     10                 0              0                   0
80                0                    80                    0                  0               0                  0
90               90                   0                       0                 0                0                 0

and not

Total       Color Sep      Copy Writing   Miscellaneous   Printing   Photography   StudioTime
8590                160                     80             100             8000          90                160    

but

Total       Color Sep      Copy Writing   Miscellaneous   Printing   Photography   StudioTime
8410                70                     80             90             8000          90                80    
180                  90                      0              10               0              0                 80

which is in fact

Total  Color Separation  Copy Writing   Miscellaneous   Printing   Photography   StudioTime
80                0                     0                    0                  0               0                  80
90                0                     0                    0                  0               90                 0
8000            0                     0                     0                8000           0                   0
90                0                    0                      90               0               0                   0
70                70                  0                       0                0               0                   0
80                0                    80                    0                  0               0                  0



and

Total  Color Separation  Copy Writing   Miscellaneous   Printing   Photography   StudioTime
80                0                    0                      0                 0               0                   80
10                0                    0                     10                 0              0                   0
90               90                   0                       0                 0                0                 0

Which is record 1,2,3,4,5,8  and 6,7,9

What i do not understand with this example, is the criteria for this two groups.
If you can explain them to me, we can hopefully finish this task.

0
 
LVL 18

Expert Comment

by:bonjour-aut
Comment Utility
concerning the 0-values:

in

Total       Color Sep      Copy Writing   Miscellaneous   Printing   Photography   StudioTime
8410                70                     80             90             8000          90                80    
180                  90                      0              10               0              0                 80

you have 0-values in the second sum-line

what about that ?

Regards, Franz
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

763 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

8 Experts available now in Live!

Get 1:1 Help Now