total columns

[phpatrick]

TEELT SHIFT1      SHIFT2	SHIFT3
WORT            284	246	10
COUR            101	24	3
DIVER	        80	62	33

this is the result of a query. how do I make the sum, total for each row (I need the total to get the percentages).
e.g. total of row two should be 128.
Is a crossquery a way to solve this ?

 

[PHV]

What about SHIFT1+SHIFT2+SHIFT3 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

what's the SQL of the query that gives you these results? What results would you rather have?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[phpatrick]

SCHIFT = column heads
This is a query and I like the total of the 3 columns (SHIFT1+SHIFT2+SHIFT3). When I try to refer to in an extra column to these columns in the same query (to make the sum), he can't do this.

e.g. COUR = 128

 

[dhookom]

It appears this query is a crosstab query. If so, you should be able to set up another Row Heading that is similar to your Value. As per Leslie's reply... show us your SQL.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[phpatrick]

ok - SHIFT1+SHIFT2+SHIFT3 works
but little problem remains. If there is nothing for SHIFT3 or SHIFT2. He is not counting, making the totals. Should I work with iif or is there an easier way

 

[phpatrick]

SELECT Q_001.TEELT_TAKEN, Q_001.AantalVanrefTEELTSHIFT1 AS SHIFT1, Q_002.AantalVanrefTEELTSHIFT2 AS SHIFT2, Q_003.AantalVanrefTEELTSHIFT3 AS SHIFT3, [SHIFT1]+[SHIFT2]+[SHIFT3] AS Expr1
FROM (Q_001 LEFT JOIN Q_002 ON Q_001.TEELT_TAKEN = Q_002.TEELT_TAKEN) LEFT JOIN Q_003 ON Q_002.TEELT_TAKEN = Q_003.TEELT_TAKEN
ORDER BY Q_001.AantalVanrefTEELTSHIFT1 DESC;

 

[dhookom]

I think you could have used a crosstab rather than a separate query for each shift. A crosstab would also provide an easy solution to a "row" total.

You can use Nz(SHIFT1,0)+Nz(SHIFT2,0)+Nz(SHIFT3,0) to total but I would still consider using a crosstab query that uses the Shift as the Column Heading.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[phpatrick]

1. perhaps, but not much experienced with crosstab queries.
I looked up some examples of crosstabs and the data of the time for example where grouped in one fields. I have tree different SHIFTs columns.

2. to counter the problem of the fields where there is not a value for, I ad this IIf([SHIFT3]>0;[SHIFT3];0). The moment I added an zero he could make totals. Is Nz(SHIFT1,0) another (better) way ?

 

[phpatrick]

I tried the function Nz. Thats not ok. When I have two values in two colomns, column1 = 1 and column2 = 0 then I get 10 and not 1 for the totals.

 

[dhookom]

If you actually have three shift fields in one of your tables then your table structure is not normalized. I would normalize it with a union query and then create a crosstab based off the union query.

Use the crosstab solution as your first option. The second option should be to use
Val(Nz(SHIFT1,0))+Val(Nz(SHIFT2,0))+Val(Nz(SHIFT3,0))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

Additionally, if this is all from a single table, if you will provide details of that table and your expected results, it will be much easier for us to help you.

 

[phpatrick]

its ok, I got my result, perhaps not in the best way, but it works.

I used 3 TimeShifts. Morning, Afternoon, Evering. I was concerted to put this in one field. I think I could not manage this in my form and report. Each contactperson put his timeshifts in the form.