Searching for sum of columns in Cross Tab Query

[Knelou]

Hello everyone,
I have created a Cross Tab Query and in Datasheet View,I would like in columns the 'Stoppage Code' (3 of them : changeovers C1, C2, C3) and in row I would like the time of these changeovers. But actually, I would like the details in the crosstab of the timings and also the sum of the timings for each changeover. In Design View, I added my table (from where are the data) and selected in the fields : 'Stoppage Code' and 'Time of changeover'. For Stoppage code, in the row Total, I selected Sum and then Value in Crosstab row. I added an other field for time of changeover where I put 'group by' and an other field for time of changeover where I put Sum for Total and Row Heading for Crosstab. Then I created a field for Stoppage Code and put it in Group By and Column Heading. The Datasheet view looks good but I still don't have the sum of time of every column (C1, C2, C3). What can I do for this?

Please let me know if you want a print screen of what I see and tell me how to attach it,
Thank you in advance and sorry for the mistakes, I am not fluent in english !

[June7]

Want to provide db for analysis? Follow instructions at bottom of my post. Identify objects involved.

[Knelou]

Want to provide db for analysis? Follow instructions at bottom of my post. Identify objects involved.

Here are the design view (up) and datasheet view (down) I can see. Actually, my problem is that in datasheet view, I would like, in a bottom row under each column, the sum of time of changeovers for each column (C1, C2, C3). So for example for C1, it should be 12. For C2, should be 39 and for C3, should be 5. Is it possible to have the sum of each column?
Thank you,

[June7]

Query or table in Datasheet view, click Totals button on the ribbon to open a row after last record, select the desired aggregate function.

[Knelou]

Thanks June, it's what I have tried so it works but when I save the query, close it and open it again, it doesn't show the totals (the sums I want), I don't know why... Have an idea?
Also, I have tried a different way, creating a simple query and in design view, added 3 fields: one for time of changeover (total row I selected sum), one for stoppage code (total row : group by) and one for stoppage code again (total row : where and criteria : C1). It directly sums my time of changeover when stoppage code=C1. BUT what I would like is to have also C2 and C3 in the same way in my query. When I try to add fields in design view (stoppage code with "where" and criteria C2 and C3) I doesn't show any data in datasheet view...(just the name of the fields) I tried adding "C1" And "C2" And "C3" in the same criteria (for the same field of stoppage code) but still doesn't work... Any idea for this other way?

Thanks,

[June7]

Rats! CROSSTAB does hold the aggregate row - normal SELECT query does.

Might have to build a report with the CROSSTAB as data source. How stable is this query - will there always be those three fields?

[Knelou]

Good idea, however when I build a report with Crosstab as data source, the totals still don't appear... This is driving me crazy how it appears and disappears like that, I really don't know how to solve it... Actually, this query might have more than 3 fields because in the Stoppage Code, there are C1, C2, C3 but also other codes and I'd like to sum the timings WHERE it's C1, where it's C2, where it's C3, where it's X...and so on. Is that possible you think?

[June7]

A textbox in report footer with Sum() expression works for me.

Problem with building report based on CROSSTAB is that CROSSTAB can be so dynamic. Stabilizing for perpetually running a report is tricky. Review:
http://www.blueclaw-db.com/report_dy...stab_field.htm

[Knelou]

When I try the Sum(C1) in the textbox footer (in report) it appears #Error in the footer... Anyway, if I have to do some changes in the report, it won't be the same as the crosstab, am I right? And do you know if when I change something in table (or query) it can change automatically the data in the report? Because it seems that once you have created a report, everything is frozen, no matter what modifications you do in table/query, the report is still the same as before...

Also, I didn't undertand what I could pick in your hypertext link...? The thing is that I would like to have a report that still can change when you add data in the query or table...
I know it's a lot of questions but I am really stuck in this and have searched everywhere on internet... This Sum problem cannot be solved easily I think, or I miss something...

[June7]

The Sum() calc in report footer works for me. I don't know why yours errors.

It is exactly because of the dynamic nature of CROSSTAB that I referred you to that link. Stabilizing a CROSSTAB so the same fields will appear every time requires some tricks. That link discusses one method.

Another approach is to include a dataset that has all possible Cx values in a query that joins to the Bindler table (join type 'Include all records from Cx table...') before the CROSSTAB and base the CROSSTAB on that first query.

If there are only the 3 Cx values, here is another method: http://datapigtechnologies.com/flashfiles/crosstab.html

[Knelou]

Okay hum I am not SO good in Access so I am really lost in all of this, sorry.... For the first link you gave me, I don't know how to adapt the SQL given for the first query to my problem...because to what the M_order_item_details refers?? It's no where in the table shown in the example... I tried replacing everything but still have some problems finding what are the other things... Looks like this :


SELECT [Bindler timings].[Stoppage code], [Bindler timings].[Time], M_order_item_Details.Qty,nz(qty,0) & " (" & Format([Price],'Currency') & ")" AS Expr1
FROM [Bindler timings] INNER JOIN M_order_item_Details ON L_Sizes.Size_ID = M_order_item_Details.Size_Id
UNION select order_item_id,"98Total",0,format(total_price,'curr ency') from q_customer_order_item_totals;

Also, I tried your joining method, adding a query that contains all the stoppage codes Cx and joining it to the bindler table but my problem is that in the query with all the Cx, I cannot enter the second field time because they won't be the same everytime... I can explain more : actually, my stoppages codes can go to A,B, C1, C2, C3, D... to W. Each day, the employees fill the data so some stoppages codes can be here but not all of them (depends what happenned in production). So when they enter for example "L" code (meanings lunch), they enter in front of it the time it took to have lunch. But everyday, the stoppage codes can be different beetween A and W and for example a break or a changeover can be longer the next day than the previous... So the time will change... I don't know how to do after joining the stoppage code in the 2 tables because for the crosstab, I need a minimum of data entered and I only have the stoppage code for now... Do I enter a few timings as examples so I can create the crosstab?

[June7]

M_order_item_Details refers to a table. In the example it would be 'child' table related to 'master' Orders table. I think it would be equivalent to your Bindler table and the example L_Sizes table would be equivalent to your Categories (the Cx values) table.

BlueClaw has been a good source but search web, I am sure you will find more examples on the topic.

I would have to work with your data to be any further help.

[Knelou]

Thanks, I have tried, still in SQL, this (All stoppage codes is the Categories table):

SELECT [Bindler timings].[Stoppage code], [All Stoppage codes].[Time], [Bindler timings].Qty,nz(qty,0) & " (" & Format([Time],'Currency') & ")" AS Expr1
FROM L_Sizes INNER JOIN [Bindler timings] ON [All Stoppage codes].[Stoppage Code] =[Bindler timings].[Stoppage code]
UNION select [Bindler timings].[Stoppage code],"98Total",0,format(total_time,'currency') from Bindler.Crosstab;

But still doesn't work, computer says there is a Syntax error in JOIN operation, I don't know what's wrong now...
Here are attached screen shots of the datasheet view of my 1 tables (Bindler timings and All stoppage codes)...

[Knelou]

Actually I have been looking for something much more simple than the Crosstabs but I still miss something... I have created a query from my first table (table contaning stoppage codes and time of each stoppage code). In the query, in design view, I added 2 fields. First one called "stoppage code" (in total I selected "group by"), second field called "Time" (in total I selected "Sum"). It works well in datasheet view BUT it shows EVERY stoppage code that is added in my first table. I would like to sum the time only when the stoppage code=C1 but also =C2 and =C3 (3 separated sums then). I tried with only C1, adding a "Time" field in design view (in total "where" and in criteria "C1"). With only C1 it works but then when in criteria I enter "C1" And "C2" (or when I create a new Time field with "where" and "C2" in criteria), it doesn't work.... Have an idea of how I could do?? (I know I asked quite the same thing at the beginning of my topic but I still cannot solve this problem...)

[June7]

Sorry, I just don't know enough about your data. Reading that narrative doesn't help me understand. I would have to work with data. The only other approach I could suggest is VBA procedure that writes records to a 'temp' table.