Solved

Sort report data into columns?

Posted on 2003-12-07
17
445 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
ID: 9891781
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
ID: 9891783
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
ID: 9891806
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 18

Expert Comment

by:bonjour-aut
ID: 9891931
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
ID: 9891938
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
ID: 9894766
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
ID: 9895444
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
ID: 9895546
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
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 9895703
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
ID: 9896319
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
ID: 9897001
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
ID: 9902274
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
ID: 9902514
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
ID: 9902581
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
ID: 9911600
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
ID: 9914224
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
ID: 9914256
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need to filter query and have subform updatable 2 26
Access 2016 importing text files 13 21
Why can't I get rid of record selectors on my form? 9 28
Question about DB Schema 27 56
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 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