Crosstab Query Grouping By More Than One Field/column

Apr 19, 2009

In access you can create a crosstab query grouping by more than one field/column and totaling on more than 1 field/column

ie Goup by Acc No then Cost Centre
Sum on costs by date -up to 31

View 9 Replies


ADVERTISEMENT

Return Grouping Based On Date Field

Mar 18, 2012

I have a date field 08/09/2011 and want to monitor an item to ensure it's "Approved" before it reaches this date.

The groups are:

Late = this date or past
Within 1 week
Within 2 weeks
Within 3 weeks
Over 3 weeks

How to do this, I tried Nested, and I've tried lookup but I'm not sure how to tackle it.

View 5 Replies View Related

Excel 2007 :: Pivot Table - Daily And Monthly Field Grouping

Oct 17, 2011

I'm using Excel 2007 and am having an issue with grouping/ungrouping fields in pivot tables.

I have 2 separate pivot tables, both from the same named data source, but summarizing different data selections. Both tables include the date field, I am trying to produce both a daily and a monthly table, but whenever I change the grouping/ungrouping of the date field setting on one table, the other table changes to the same grouping.

Is there anyway to have one table with an ungrouped date field and one table with the grouped to month date field?

View 1 Replies View Related

Access Query - Field Name Recognized As VBA Function

Jul 10, 2013

I'm trying to query Access for some data. One of the fields I need to grab is called MOD, which also happens to be a VBA function. How do I get Excel to recognize MOD as a field name instead of a VBA function? (Renaming it is not an option)

Code:
stSQL1 = "SELECT MOD FROM CST_ClaimbyLop WHERE CoCName='" & myCST & "'"

View 2 Replies View Related

Change/Move Pivot Table Row Field To Column Field

Apr 23, 2008

In building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?

View 3 Replies View Related

Function CROSSTAB()?

Jul 27, 2006

where it function CROSSTAB(Label, Definition, Array, Create_Outline, CreateName, MultipeValues, Auto_DrillDouwn)

View 2 Replies View Related

Array :: Crosstab Table

Jun 26, 2008

I've never worked with arrays before but after looking through some books, would like to attempt a crosstab table. I currently have data in cells A1:K8316. Row 1 consists of my headings, which include Team (column K), Ind/Dir (column I), and Hrs (column G). There are a total of 25 different teams and I would like to sum their hours for both direct and indirect. (The columns for Team and Ind/Dir are based on vlookup tables).

I've tried some of the steps I'm finding in the book but, unfortunately, they're assuming I know how to even start and what to select - which I don't.

View 9 Replies View Related

Pivot Table And Crosstab

Dec 5, 2008

how a crosstab query works in ms access. Below is a visual. IN my source material, the columns are Category, Commodity, Supplier, and Dollars. I need to be able to get only the top 5 suppliers in descending dollar rank in the columns adjacent to the pivot table, as displayed below. Try as i might, i have been unable to get this to work using a pivot because i don't know how to limit the supplier columns to only the top 5.

View 9 Replies View Related

Reversal Of A Crosstab Table To A List

Sep 22, 2008

I am trying to convert the table below into a 3 column list that I can then import into SQL Server from a .XLS file, using ODBC .

Assets01/09/0802/09/0803/09/0804/09/0805/09/0806/09/0807/09/0808/09/0809/09/0810/09/0811/09/08

MQBH073520.773540.413592.333578.543531.293535.043485.913543.463544.161789.03
MT1072688.693658.223410.453400.191915.563401.81
3586.713870.793846.383878.4P08

P123182.63323.393225.873299.541635.611641.7
1615.983304.913313.791637.02Totals52097.561575.5561750.5864889.2554803.6754775.2661905.5465112.9563407.6266701.1861598.34
The table supplied, starting at B8 demonstrates one of 8 worksheets in the spreadsheet... of which I would like all exported to 8 named worksheets in a new spreadsheet in the list format as:

Asset | Date | Value

I need to ignore the Totals column and row,
The table can grow or reduce the assets and
grow the days of the month, starting again at the begining of each month. I have to run a report each day of the month.

View 9 Replies View Related

Hide Column After Grouping/Outlining

Mar 6, 2009

When my user clicks a Group by button in Excel (+)(-), I want that event to kick off a macro that will hide columns D and E.

The idea is that when the user views data at different levels of aggregation, the spreadsheet will 'de-clutter' itself by hiding columns that the user doesnt need to see.

There is no obvious Excel event that responds to Group By. Can a Class Module achieve this? If so, can someone give me a 30 second overview?

View 3 Replies View Related

Pulls Crosstab From Access; How To Update New Months

Jul 16, 2008

I have a crosstab query in Access that groups by month and year. In Excel 2003, I have a query that pulls in the crosstab query.

At the time, I couldn't just pull in the Access table. For the other Access tables and queries, I could go to Data >> Import External Data >> Import Data >> select the database as a source >> select the table name.

However, to get the crosstab query, I had to go to Data >> Import External Data >> New Database Query >> MS Access Data Source* >> browse to my database >> select the crosstab query name >> select the data I wanted.

Since I wanted all the data, there did not seem to be a difference. But now that new dates are being added to the database, there *is* a problem. When I wrote the query, I only included the months available -- up to June 08. Now that there is data in July 08, that month exists as a heading in the crosstab, but Excel's query doesn't know to pull it! I have to manually edit the query to add the new month.

So my question is... is there a way to do this automatically? I have two crosstabs in five different spreadsheets, making a total of ten manual updates I have to remember to do each month.

View 9 Replies View Related

Grouping Same Values By A Number Sequence For A Column

Mar 13, 2013

I am trying to group the same values in columns together and i was thinking about having a number count. What's the function that might be able to display this (display whats in Column B in the example below)?

Values
5000 1
5000 1
6000 2
7000 3
7000 3
8000 4
8000 4
8000 4
9000 8
10000 9
10000 9

View 1 Replies View Related

VBA Macro Code For Grouping Based On Column A

May 1, 2014

i need to group the values based on A, B, C columns.

which means all "Chickpea_B-1_11area_3d.csv " will have same timestamp and mean_area_3d values, only D column vlaue changes.....

in the same way, all "Chickpea_B-1_12area_3d.csv" will have same timestamp and mean_area_3d values, only D column vlaue changes.....

now i need all Chickpea_B-1_11area_3d.csv values to be grouped into one. and chickpea_B-1_12area_3d.csv values to be grouped into one..

if you see the output sheet, you will understand clearly, which i did manually for your easy understanding...

i did manually and also attached the sheet for your kind reference and easy understanding... where i grouped all Chickpea_B-1_11_area_3daver_domain_area, fill_factor, leaf_angle_theta, plantHeight, projected_leaf_a into one.... where b and c cols are same for all the D E F G H I Values. in the same way for Chickpea_B-1_12_area_3d and all other below values to be grouped.

like this i have to do for 1000 files. please find the attachment...

View 12 Replies View Related

Multiple Dimension Crosstab Convert To Flat File

Oct 6, 2012

VB:
Sub CreateFlat()
Dim wsData As Worksheet
Dim wsNew As Worksheet

[Code].....

In 2006 posting, this code was presented to the Forum. It works perfectly for a one dimension Crosstab. I have 4 dimensions that I need copied to the output.

I have attempted to modify the code, but nothing appears to adjust the pull of more than the first column of dimension data.

see the attached spreadsheet for the Crosstab data and desired output.

View 8 Replies View Related

Vlookup Using 2 Condition One Based On Devices And Grouping Column?

May 27, 2014

Attach below are 2 sample file and inside it consist of device,grouping and lb1,tb1,ab1. How do I use excel formulae based on 2 condition and set from vlookup with 2 condition file to vlookup with 2 condition 1 file like a vlookup.

View 11 Replies View Related

Want To Return Value From 2nd Column Based On Match In 1 Field With First Column

Sep 23, 2009

I'm working on a project in which I have 50 states + DC listed by 2-letter abbreviation, and want to return a value in another column that will be a non-unique number (some will duplicate in the second column.)

Also need to do this same operation with first column (state) and third column, which also is comprised of non-unique numbers.

Hlookup, lookup & pivot tables seem to not be a fit for this.

View 9 Replies View Related

VBA Listbox Column Query

Nov 2, 2008

I have a database of customers in sheet1 and can search colA by using .find command and find part of text string entered in textbox1, results are filtered and shown in listbox1. my problem is the listbox is only showing 9 columns and i need 12.

i think this is because i am using the additem command.

can anyone suggest a way around this or alternative coding method??

Sub cmbFindAll_Click()
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
Set rFilter = Sheet1.Range("a2", Range("f65536").End(xlUp))
Set rng = Sheet1.Range("a2", Range("a65536").End(xlUp))
strFind = Me.TextBox1.Value
With Sheet1
If Not .AutoFilterMode Then .Range("A2").AutoFilter
rFilter.AutoFilter Field:=1, Criteria1:=strFind & "*"......................

View 9 Replies View Related

How To Define Column In MS Query

Mar 25, 2009

How to define column in MS Query (ODBC)?

i have one column like Date of purchase. what i want is to define column based on date of purchase the results showing month of the date of purchase

View 9 Replies View Related

Query Grabs The Column Name And Then The Value

Sep 25, 2009

1. When I query it grabs the column name and then the value I am looking for, how do I just grab the value?

2. How can I have the query return the results in the order that they are obtained? For example, lets say I query 3 data points. The first is 1, second is 2, and 3. But when they are put on the excel SS they are in the order 3, 2, 1.

3. In addition to the table I am grabbing below, I have a second table I want to query. The table name is two words, and last time I tried to query it, it did not work.

Sub proSQLQuery1()
Dim varConnection
Dim varSQL

For i = 0 To 1

varConnection = "ODBC; DSN=MS Access Database;DBQ=C:Documents and SettingskbreslinDesktopLydia.mdb; Driver={Driver do Microsoft Access (*.mdb)}"
varSQL = "SELECT VitalStats.birth_date FROM VitalStats WHERE VitalStats.Cases_ID='" & Cells(1 + i, 1).Value & "'"

With ActiveSheet.QueryTables.Add(varConnection, Worksheets("Temp Hold").Cells(2, 4))

.CommandText = varSQL
.Refresh BackgroundQuery:=False

End With
Next i
End Sub

View 9 Replies View Related

Column Sequencing Using Microsoft Query?

Sep 7, 2004

I used the Get External Data function within Excel and the query returned the fields in alphabetical order. I went back into the query and rearranged the fields into a more logical order, but when refreshed the query continues to return the fields to Excel in the original alphabetical order.

Am I missing something, or do you only get one shot at the order of the fields being returned?

View 2 Replies View Related

Excel Column In Access Query Via VBA

Mar 12, 2012

I have the below code that will do a data dump of an access table into excel based on an SQL query, is there a way to do a select statement that can match against the data in column C on the spreadsheet.

like:

"Select * From Bsmart where Serial_Number = ##[EXCEL COLUMN C1:Lastrow]##

Code:
Dim bkNew As Workbook
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String
Dim SQl1, SQL2, SQL3, SQL4, SQL5, varSQl As String

stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

[Code] .....

View 1 Replies View Related

Create New Tabs For Every Different Field Value Within A Column

Jun 2, 2014

I'm trying to reduce some manual labor that I do each month by figuring out a way to create additional tabs for every different "branch" within a given column from a master table. The branch numbers may not necessarily be the same each month as some old may go and some new ones may come.

I have attached two excel workbooks. One shows the master "Data Table" and the other shows what I want it to look like "After Sorting". As you can see, I have to create a separate tab for each of the branches listed in column A on the master table. This becomes really cumbersome, especially when we really have about 20 branches but I'm only using these 6 for the example.

Data Table.xlsxAfter Sorting.xlsx

View 3 Replies View Related

Formula To SUM For A Given Field Type Specified In Another Column

Oct 27, 2009

I'm sure this will be a very simple one for someone - it leaves me *head scratching* though! I have attached a VERY generic example and it includes what I am trying to SUM for the given data. From the example if we look at Question Z1:
"Total Cost of All types of "Lemons"" = ?? I could simply do

View 5 Replies View Related

Resetting A Currency Field Column

Feb 26, 2008

I have a column (L8 down) of cells formatted as $'s

At the end of the column is a sum formula.

At the end of each month the client wants to 'reset' all the $ amounts back to $0.00

Is there a macro that would reset the column of $'s back to $0.00 without removing the formulas like ClearContents does?

View 9 Replies View Related

Hiding A Column Based On A Field Value

Apr 4, 2008

I have a worksheet called "Calendar" with values D4:X4

if the value "Name" appears in any of these cells then the corresponding column where the entry appears will be hidden. otherwise the column will be visible.

View 9 Replies View Related

Repeated Web Query To Fill A Column With Data

Mar 31, 2014

I have simplified this spreadsheet from the original to get to the core of what I want it to do. There are three tabs: 1) tab:"query fields" is an area that places data that is retrieved from the web, in this case it retrieves tables from yahoo finance given a ticker input. The ticker input cell is currently on the "score card" tab. 2) tab:"score card" in this tab you can input a ticker symbol in the indicated box and it will populate the "query fields" with corresponding data. This tab also extracts certain fields from the retrieved tables and performs a pass/fail test then produces a score %

This is great if I'm doing one symbol at a time, however I eventually would like to 'loop' this process such that a score field is populated for a list of tickers, hence tab 3) "list". Ideally I'd create a control button to initiate the web retrieval process and it would go down my list, retrieve the table data, perform the pass/fail tests, then populate the corresponding score % in the cell next to the ticker symbols. I know I can keep running separate web queries for specific symbols, but I guess my secondary goal is to have this be as dynamic as possible, since the ticker list will change and the list could be sizable at times.

CSH- web query.xlsx

View 3 Replies View Related

List - Query- The Value Into The D Column Should Change Automatically

Dec 11, 2007

I want whenever i select any dept in the B column say ID or Design... the value into the D column should change automatically...

say if i select in B5 as ID then D5 value should show me India

******** ******************** ************************************************************************>Microsoft Excel - resource_sheet_11_Dec_07_V2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDE1Project(Client - Name- Length)Dept.Resource 1-Oct2 3Template 4 5New Course Sample Template ID 6New Course Sample Template ID 7New Course Sample Template DesignDesign Pool 8New Course Sample Template BuildBuild Pool Project Schedule [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

MS-Query Convert Datetime Column To Date

Jan 6, 2010

I am working on MS-Query in Excel.

Selecting few datetime columns by using ms query but it returns that columns data in wrong format.

And CONVERT function is also not working in ms-query.

Below is the MS-Query:

SELECT `Consolidated$`.Payroll_ID, `Consolidated$`.Full_Name, `Consolidated$`.`Current Designation`, `Consolidated$`.`Date of Joining`, `Consolidated$`.Department,

View 9 Replies View Related

Auto Populate Field With Date When Other Field Is Selected From Validation List

Jul 4, 2013

In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.

Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.

Currently Col K has a validation list which comprises dates from 1st of each month from July to June.

I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.

The information is currently part of a Table in Excel 2007 that has about 500 records.

IdeasList

*
J
K

2
Status
Month Completed

3
In Progress
*

4
Complete
Jul-13

5
*
*

6
*
*

Data Validation in Spreadsheet

Cell
Allow
Datas
Input 1
Input 2

J4
List
*
=ValidationList_Status
*

K4
List
*
=ValidationList_Months
*

View 2 Replies View Related

Data Summary From Key Field In Column To New Sheet?

Aug 7, 2012

I have data like this:

A B C
1 INC0123 00:00 00:12
2 INC0123 00:13 00:27
3 INC0123 00:28 00:42
4 INC0127 00:00 00:20
5 INC0127 00:21 00:48
6 INC0128 00:00 00:22
etc

and what I would like is a summary sheet that looks like this

A B C
1 INC0123 00:00 00:42
2 INC0127 00:00 00:48
3 INC0128 00:00 00:22
etc

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved