Unpivot Crosstab?
Feb 27, 2008Does anyone know if there an access equivalent to the SQL server 'unpivot' command relating to crosstab queries or tables?
View Replies
ADVERTISEMENT
General :: How To Unpivot Table
Dec 31, 2013I need now somehow 'unpivot' this table and in order to do it I need to concatenate columns and rows names and then match correct values so based on the above example, I would need to receive table with the following two columns:
Item X Size 1 | 0
Item X Size 2 | 4
Item X Size 3 | 6
Item Y Size 1 | 0
I have been trying various solutions found via Google but nothing seems to be working.
Queries :: Unpivot / Normalize Table
Nov 15, 2013Just wondering if I'm missing an SQL trick for normalizing this data I inherited from an Excel spreadsheet.
Current data:
Code:
Case | Insp1 | Insp2 | Insp3 | Insp4 | Insp5 | Rnsp1 | Rnsp2 | Rnsp3 | Rnsp4 | Rnsp5
---------------------------------------------------------------------------------
1234 WEEDS TRASH CAR <null> <null> WEEDS CAR <null> <null> <null>
1235 TRASH <null> <null> <null> <null> TRASH <null> <null> <null> <null>
Normalized table:
Code:
Case | Violation | InspectionType
-----------------------------------
1234 WEEDS Initial
1234 TRASH Initial
1234 CAR Initial
1234 WEEDS Reinspect
1234 CAR Reinspect
1235 TRASH Initial
1235 TRASH Reinspect
Right now I'm having to do it with ten different queries (each with its own query behind it to massage the original data), one for each Excel field, and checking for blank entries before appending. And there's going to be about 50 Excel tabs to gather up... anything I'm missing besides the basic slog?
Queries :: Unpivot Table With Union Query
Feb 2, 2014I am using below Union Query to unpivot attached table. Basically this is a table with some clothes and their sizes (in yellow). Now my problem is that below query works well only when all sizes are in the same row but as per attachment I may have them potentially over three different rows.
I can determine which row is for which item based on size range column (in red) but I still do not know how to modify my query to look at different rows depending on value of size range column, especially that as you can see there are multiple values pointing out to the same size range i.e. 0-6 and 1-4 point out to Size Range 1.
Unfortunately I cannot change the layout of the table to have all sizes in one row
Code:
Select Field1, Field2, "0" as Size,Field3 as Qty from orderform where Field3 > 0;
Union All Select Field1, Field2, "1" as Size, Field4 as Qty from orderform where Field4 > 0;
Union All Select Field1, Field2, "2" as Size, Field5 as Qty from orderform where Field5 > 0;
Union All Select Field1, Field2, "3" as Size, Field6 as Qty from orderform where Field6 > 0;
Union All Select Field1, Field2, "4" as Size, Field7 as Qty from orderform where Field7 > 0;
Union All Select Field1, Field2, "5" as Size, Field8 as Qty from orderform where Field8 > 0;
Union All Select Field1, Field2, "6" as Size, Field9 as Qty from orderform where Field9 > 0;
Crosstab Query Based On Crosstab??
Sep 21, 2007Hi all, I am utterly unsure if what I want to do is even possible:
I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).
There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.
I want to make another crosstab query which performs a calculation. To keep it simple:
If (RewCOCredit>WrapCOCredit) Then
5-RewCOCredit
Else 'WrapCOCredit>RewCOCredit
5-WrapCOCredit
Please help!!!
Crosstab Help
Mar 23, 2006I have a table that holds review information on staff:
tblReviews:
StaffID (Number)
ReviewDate (Date/Time)
Completed (Yes/No)
Everytime a review is carried out it is added as a new record, however, I need the information to be presented in a crosstab so it looks something like this:
StaffID Review1 Review2 Review3
17 01/01/2006 04/04/2006 05/05/06
20 01/01/2006 04/04/2006 05/05/06
25 01/01/2006 04/04/2006 05/05/06
Other then creating a table and playing with a bit of vba does anyone have a solution?
Thanks.
Help Using Crosstab
Sep 16, 2007Im trying to use crosstab to help me out count how many pages are within a document...
for example
in my "break" field. there are records that contain D's(d=document), and blank records(blanks=pages)...im trying to use crosstab to help me count how many are within each document....but for some reason, its not coming out right...can anyone help me?
i keep getting this error when i try running my crosstab query...
"Too many crosstab column headers(21521)"
is it possible since i have so many records...i cant perform this task??
heres a cpy of my db
Crosstab Help
Nov 26, 2007I want to combine the data from 2 tables in a report. I think I need to do this through a crosstab query, but I've no idea how as it needs to calculate it's data.
The row heading needs to be [DateWorked] from the "Hours" table.
Each Column heading needs to be every [StaffPIN] from the "StaffData" table.
I want it to display the number of hours each staff member worked for a perticular day. The "Hours" table holds each staff member's start and finish time. I have a function called workedHours that, given the start and finish time it will return hours worked (a shift can overlap 2 days so DateDiff wouldn't work).
Can anyone help?
Crosstab Help?..
Dec 19, 2007Hello,
I have a table with the following info:
ID, CSR, Option 1, Option 2, Option 3, all the way to option 12.In the Option fields, you can enter a number from 1 - 12. What I'm looking for is a summary report that will give the number of times each number appears for each field. Something like this:
Option 1: 1 = 20 times, 2 = 5 times, 3 = 15 times
Option 2: 2 = 5 times, 2 = 10 times, 3 = 0 times
So on and so on. Would this be done with a crosstab query, or am I way off?
Thanks in advance for your help.
Alex
Crosstab Query
Aug 17, 2005Hello All,
Your Help Required. I have send you a Database, in which I have used Crosstab query, I just want when I select the report from switchboard, and enter the datefrom / dateto (fields names) than click the preview report. Required report is open.
I have faced following problems
1-When I have selected the report and click the preview button. Report is not opened.
2-I have used cross tab query and link with the switchboard. But when I have run the query this msg is appeared “Microsoft Jet engine Does not recognized these field(name)”
Thanx
ami
Crosstab Limitations
Oct 6, 2005Does anyone know what the maximum rows & columns are for a crosstab query as I cannot find them anywhere?
When I create one that is too big, it just gives the message of how many columns I have rather then how many I'm allowed.
Any help would be greatly recieved.
Regards
Carly
Crosstab & Forms
Dec 15, 2006Hmz,
I have never tried this, it seems easy enough but in fact... I simply cannot find the answer.
I want to display a crosstab query as a (sub)form. The basis of this is that a form requires the columns to be fixed. The crosstab will offcourse grow and grow in the number of columns.
I would like to show the form as a datasheet to support this purpose.
I was thinking maybe I can dynamicaly add the (new) column(s) before opening the form, but how do I do that?
Greets & Thanks
Yours
Crosstab Query
Sep 23, 2005I am am wondering if it is possible to create a crosstab query that displays alphanumeric values and not numeric (computational) values.
Ex:
Table contains the following details:
Name Branch
Bob 111
Bob 222
Joe 333
Pam 444
I want to use a crosstab so I can view the data as follows:
Name Branch1 Branch2
Bob 111 222
Joe 333
Pam 444
Is this possible? I've been playing around with it and it doesnt look doable. Perhaps there is a better way of getting the data into this format? Any suggestions would be greatly appreciated!
Super Thanx.
O.
Crosstab Query
Oct 2, 2005Hi all
I'm trying to create a crosstab query with criteria that refers to a combo box on a form to allow me to filter data before the query is created. However when I refer to the combo box (e.g. = forms!frmSite!CmbSiteName) I am told that access does not recognise this as a 'valid field name or expression'. With a normal select query I dont have the same problem. Is there a way around this?
Cheers
Rhys
Crosstab Query
Oct 4, 2005Hi, I have a crosstab query I have set up, calculating the cost of a product per month
it should look like
1 2 3 4
gek22 £55 £66 £77 £88
er44 £99 $100 £101 £102
tt66 £103 £104 £105 £106
but instead it looks like
1 2 3 4
gek22 £55 £88
gek22 £66
gek22 £77
er44 £99
er44 $100 £102
er44 £101
tt66 £103 £106
tt66 £103
tt66 £104 £105
Admittedly this is my first time doing a crosstab query, but I have both my column and rowe headers set to group by, I kinda assumed this would group them in much the same way a pivot table does in excel
Any help on this would be great
Hmm, doesnt want to keep the spacing in it :( hopefully you can get the idea
Crosstab Qry From Union Qry
Dec 6, 2005I have a UNION qry in which I have Out, Incoming and internal invoices as RELATIONSHIPs, and based on it a CROSSTAB qry, and now there are 2 new categories which are goods that were returned - and there are 2 because:
1. is return from Internal and
2. is return from Outgoing invoices.
I need to calculate the current InStock (based on articalNumbers) with this in mind:
InStock = Incoming - OUTgoing - Internal + returnfromInternal + returnFromOutgoing
However, I cant seem to get a returns to the same crosstab qry in order to include them in calculation
the real names of variables are not important but the principle on which I should base my qry is the problem for me (please have in mind that I first started working in access about 2 months ago)
Is there anything wrong with the principle of my work here?
t i a...
Crosstab #Name? Problem
Jan 20, 2006OK, been awhile since I posted anything here. I have a weird one, hope there is a solution.I have a form that uses a crosstab query for it's data. The problem is the crosstab query has a column heading that (in my example) represents warehouses of which there are ten. So when the form was originally created it had ten text boxes for quantity values by product for each warehouse. The problem is depending on when the query is ran there will be times when there are no values for one or more warehouses, when this happens the crosstab query does not show those warehouses at all as column headings, therefore the text boxes on the form do not have a corresponding field on the query and, because of that it displays the - #Name? in the text box.:mad: How would one go about dealing with this situation??Thanks!
View 2 Replies View RelatedCrosstab Group By On Value
Mar 29, 2006Hi there I have the following predicament,
I want to run a crosstab query on a table that looks like the below:
ID STORE RECEIVED ITEM RESPONSE
1, 123, 2006-03-30, Name, Tony
1, 123, 2006-03-30, Position, Baron
1, 123, 2006-03-30, Name, Simon
1, 123, 2006-03-30, Position, Mgr
1, 321, 2006-03-30, Name, Sarah
1, 321, 2006-03-30, Position, Analyst
The crosstab query as it stands only pulls back the first and last records as it wont allow grouping on the value field, which in this case is the response field. All other fields are row headings.
The report looks like this:
1, 123, 2006-03-30, Tony, Baron
1, 321, 2006-03-30, Sarah, Analyst
Any ideas on how I can bring back both reccords, eg Group on the all fields? It should look like this:
1, 123, 2006-03-30, Tony, Baron
1, 123, 2006-03-30, Simon, Baron
1, 321, 2006-03-30, Sarah, Analyst
Thanks for your time!
Cheers
Tony
Crosstab Query
Jul 6, 2006I have a report based on a crosstab query and the data changes every time the report is run even though the data is not updated. Can anyone shed some light?
View 1 Replies View RelatedCrosstab Queries HELP!!!
Aug 30, 2006I have a crosstab query where I can compile the totals per day that I want. What I need to do is then group these days into weeks by the following Sunday for a week ending total. How can I link each day for its corresponding week ending date. I attatched an image if it helps.
View 4 Replies View RelatedCrosstab Query?
Jan 25, 2007I have a table with the following info:
Date/shift/name
1-1-07/1/tom
1-1/07/1/Bill
1-1-07/2/frank
1-1-07/2/Ben
1-1-07/3/Scott
1-1-07/3/Terry
1-2-07/1/tom
1-2/07/1/Bill
1-2-07/2/frank
1-2-07/2/Ben
1-2-07/3/Scott
1-2-07/3/Terry
1-3-07/1/tom
1-3/07/1/Bill
1-3-07/2/frank
1-3-07/2/Ben
1-3-07/3/Scott
1-3-07/3/Terry
What I want is the date to be displayed horizontally and the the names vertically with the shift as the intersection:
1-1-07 1-2-07 1-3-07
Tom 1 1 1
Bill 1 1 1
Frank 2 2 2
Ben 2 2 2
Scott 3 3 3
Terry 3 3 3
is this possible with a crosstab query or do I have to go about doing it some other way? If a crosstab query is possible how do you go about doing it?
Any help would be greatly appreciated.
Crosstab Query
May 30, 2007I need help with a crosstab query ( :eek: ). I would like the column headings to be the last 6 months, the row headings to be billers, and the data in the middle to be both the date that a payment was made (falling within the month headings) and also the amount paid in that payment. Sometimes there might be more than one payment to a biller in a month or there might be no payments to that biller in a month. All of the raw data needed is in one table. I have read about crosstab queries, tried to use the wizard, and looked at examples but I can't get it to work:confused: . Could someone show me how this should be written? :)
Something like this is what I need:
_______________Jan______________Feb_____________Ma r__________Apr
Power............1/3....200..............2/4...250...........3/7....225........4/5.....250
Lease............1/15...1200..........2/15...1200..........3/16..1200.......4/20...1200
Insurance.......1/16...175.............2/20...175...................0.....................0
.............................................. 2/26...350
Water/Gas.......1/6...150.............2/10...175...........3/3...150.........4/10...175
Thanks a lot...
Crosstab Query Help
Jun 29, 2007Hi,
I need a query which displays data as a crosstab query would but gives me the capability to edit data in the query. Basicaly, I have a Resource column, a Month column, and an Allocation column (among others). I need the months to appear in columns and the allocations to be summed by month. (Months are never repeated though, so it's not really a sum...) Can anyone help me out on this?
Thanks!
Crosstab Issue
Jul 12, 2007I need some help with a crosstab query. I’m not to experienced using it and I’m pretty sure it’s what I need to use. Here is a quick breakdown on the key fields in the tables.
Table: TBL_BOM
ProjectID
ResType
Res_subcat
UnitPrice
Monthneeded
Table:TBL_Budget2008
projectid
Totaltax ie $1,026
Freight ie $100
I created a crosstab query and it looks exactly how I need it to. I've included a word doc with the query examples.
All of the above data is from the TBL_BOM table only. I need to add the freight and the total tax to this query from the TBL_Budget2008 table. The common field is the project id. There is only one project id each in the budget table. I need to take the total tax and freight for each project and have it add into only one of the months, or to be distributed between the months. I cannot put data in an empty fields. It must go into either 1 field with data or into be distributed into the populated fields for its shared projected.
Crosstab Query
Aug 16, 2007My crosstab query doesn't take the textbox value from the form. It says it doesnt recognize [forms]![frmMain]![txtEndDate] as a valid field name or expression. How can I solve this?
TRANSFORM Sum(tblDowntimeHrs.downtimeHrs) AS downtimeHrsOfSum
SELECT tblEquipmentType.Equipment_Type
FROM tblEquipmentType INNER JOIN (tblCategory INNER JOIN tblDowntimeHrs ON tblCategory.category = tblDowntimeHrs.category) ON tblEquipmentType.type = tblDowntimeHrs.type
WHERE (((tblDowntimeHrs.date)>=[Forms]![frmMain]![txtStartDate] And (tblDowntimeHrs.date)<=[forms]![frmMain]![txtEndDate]))
GROUP BY tblEquipmentType.Equipment_Type
PIVOT tblCategory.category_description;
in my select query with the same table, it works fine:
SELECT tblEquipmentType.Equipment_Type, tblCategory.category_description, Sum(tblDowntimeHrs.downtimeHrs) AS downtimeHrsOfSum
FROM tblEquipmentType INNER JOIN (tblCategory INNER JOIN tblDowntimeHrs ON tblCategory.category = tblDowntimeHrs.category) ON tblEquipmentType.type = tblDowntimeHrs.type
WHERE (((tblDowntimeHrs.date)>=[Forms]![frmMain]![txtStartDate] And (tblDowntimeHrs.date)<=[forms]![frmMain]![txtEndDate]))
GROUP BY tblEquipmentType.Equipment_Type, tblCategory.category_description;
thanks.
Crosstab Delima
Aug 29, 2007I find myself using crosstab queries frequently and am baffled about this one issue. If anyone as insight to this, I would love to hear it.
For the recordsource for the crosstab, I use a query, for this example, I will call it query1. query1 will have the following criteria in a date field:
Between Forms!NameOfForm!StartOfDateField and Forms!NameOfForm!EndOfDateField
However when I attempt to run the crosstab, I get the following msg:
"The Microsoft Jet Database does not recognize Forms!NameOfForm!StartOfDateField as a valid field name or expression.
I know it's valid as I can run many other queries, that the logic was copied out of and they all work.
I've also attempted the same criteria within the crosstab using a WHERE statement, but it returns the same msg.
I can still accomplish what I'm after by running make table queries and using the tables as the recordsources for the crosstabs, but I shouldn't have to do that.
What seems to be the issue with the crosstab queries that prohibit the ability to call a criteria such as this.
Thanks in advance for any insight.
p.s. and yes the form that holds the DATES is OPEN