asked on

Sort report data into columns?

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?

bonjour-aut

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

bonjour-aut

if you would like to have a better example, post your rlevant table and fielnames as you have them

Regards, Fanz

clinthammer

ASKER

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.

bonjour-aut

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

bonjour-aut

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

clinthammer

ASKER

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

bonjour-aut

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

clinthammer

ASKER

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

bonjour-aut

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

clinthammer

ASKER

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?

ASKER CERTIFIED SOLUTION

bonjour-aut

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

clinthammer

ASKER

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

clinthammer

ASKER

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

bonjour-aut

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

clinthammer

ASKER

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

bonjour-aut

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.

bonjour-aut

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