Query To Total Columns AND Rows
Jun 2, 2005
Is it possible to total columns in a query? Right now, I have a query that produces the following column counts, but I'd like to total Pending, Overdue, etc. This data is being displayed in a subform.
Process Pending Overdue Total
-------------------------------------
Engineering 1 2 3
Procurement 0 6 6
<etc>
-------------------------------------
TOTAL 1 8 9 <- this is the line I want to add
Here's what the query (qryStatusRptB) looks like thus far:
Field: Process
Table: tblProcesses
Total: Group By
Field: Pending
Table: qryStatusRptA
Total: Sum
Field: Overdue
Table: queryStatusRptA
Total: Sum
Field: TotalRecords: Count(*)
Table:
Total: Expression
Can someone explain how to get the TOTAL ROW in here? (I can do it via another query, but that won't work since the data is displayed in a subform. I've tried crosstabs without success.)
Many thanks,
Christine
View Replies
ADVERTISEMENT
Aug 8, 2013
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
View 2 Replies
View Related
Aug 29, 2011
How do I make the 'total rows' in query permanently visible? Right now, every single time when I click on to my query, I will have to go to HOME---> RECORDS ---> TOTALS then the Total row will appear. And when I export that query, the TOTAL ROW is missing. how to make the total rows visible always?
View 4 Replies
View Related
Oct 30, 2013
I currently have a query that pulls selected data from a table. There can be multiple rows of data, and two columns include dollar amounts and quantities. I have a total line going at the bottom so I can see the Grant Total of all the rows (for dollar amounts and quantity).
Is it possible to add a column to this query that will calculate the expression:..?
=Grand Total of Dollar Amts for selected data/Grand Total of Quantity for selected data
I tried to use a query in a query, but must have done something wrong because it just said circular reference.
View 2 Replies
View Related
Aug 1, 2013
I have the following query which returns 2 columns, where 2 fields are summed :-
Code:
SELECT Sum(Stats.[No of Invoices Checked]) AS [SumOfNo of Invoices Checked], Sum(Stats.[No of Incorrect invoices]) AS [SumOfNo of Incorrect invoices]
FROM Stats
WHERE (((Stats.Period)=[Forms]![frmMain]![cboSingleMonth].[Value]));
This displays :-
345 - 988
How can I have the data displayed as
345
988
when I run the query.
View 4 Replies
View Related
Jun 20, 2013
I am new to Access and have been hitting a wall and seem to be finding nowhere that has this same type of task.
I have a table(Table 1 ) with 3 columns...
Table 1
I am trying to run a query that returns Table 1 but excludes rows that meet multiple criteria. I need to return rows that do NOT contain the combination of criteria below
Criteria
1) Lot Number - Left([Lot Number],2) = "PT"
2) Transaction Desc. - "Put-away"
Basically, i want exclude a "Lot number" starting with "PT" IN COMBINATION WITH a "Transaction Desc" of "Put-away". The goal is for the query to return ROW2 and ROW3 and exclude ROW1.
ROW1 would be excluded from the query result because it meets both criteria.
ROW2 and ROW3 would be included because it does not fully meet all criteria.
View 3 Replies
View Related
Jul 31, 2013
I am trying to convert a table that looks like the following...
Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff
I would like to covert it to....
Customer 000462 000224
Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff
I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.
View 12 Replies
View Related
Jul 10, 2013
I have a cross tab query that displays data by customer (rows) and MONTHS (columns).
However I need the columns to be the 12 months of the year 1 to 12.
However, if the selected data for a particular customer does not have any records in a month then I get an error in the report as the cross tab query only selects the months with data.
How do I get the report to show 0 or a blank in these columns
View 2 Replies
View Related
Mar 29, 2012
I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.
The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.
I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.
Current:
ID / Regular Hours / OT Hours
101 / 70 / 7.5
102 / 30 / 0
103 / 5 / 0
Needed:
ID/ Hours / Type
101 / 70 / Reg
101 / 7.5 / OT
102 / 30 / Reg
103 / 5 / Reg
I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.
Access 2007, Windows 7.
View 5 Replies
View Related
Mar 22, 2007
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
View 3 Replies
View Related
Feb 27, 2007
Hi all,
Is there a way i can transform through a query or vba?
A | B | C
701 70-6
701 70-7
701 70-9
702 70-1
702 70-3
..... etc
transform it to
701 | 70-6 | 70-7 | 70-9
702 | 70-1 | 70-3
703 ....
Thanks in advance
View 3 Replies
View Related
Sep 20, 2006
I'm filling in for someone who has a strange love for mail merge and now I need to covert employee history from a row to a column for the mail merge.
I currently have a list of employees and every change in position is listed as a new row. Since all employees are not the same some employees have more rows than others. What I need to do is move the history rows into columns. So what I'll end up with is one single row per employees with their history going to the right. Some employees will have more columns than others. I tried doing a crosstab but that will only let me pick one column from the original table.
I have three columns that I need repeated over and over to reflect their work history.
this is what im working with
Name or ID will be the key
Job start date
Job title
department
currently every one of theese fields is in a column but every entry has its own row.
I need a single row for every employee and the columns to repeat to the right to reflect every move.
Can someone help me :D I'm stuck :confused:
I doesnt matter if the output is in something other than access
View 3 Replies
View Related
Mar 20, 2005
I have a two tables in my database related to employee ratings
Employee
TokenID
Division
Band
PMRating
TokenID
H1
Now this statement
Code:SELECT DISTINCT Employee.Band FROM Employee
This would display me all the band levels within Employee table in each row..
Is there any way I could built a query to the Band names as seperate columns?
i'm doing this so as to display the average H1 rating for each Band level ..that is grouped by Employee.Division..
View 2 Replies
View Related
Aug 28, 2006
Hello All,
I have created a table in access with the following column headings:
ID, A, B, C, D, E.
Column ID contains 9 digits text;
Column A column contains email address;
---------------------------------
Column B contains Currency ($); |
Column C contains Currency ($); | Source Fields
Column D contains Currency ($); |
---------------------------------
Column E contains order number;
Now I need to normalize this table to produce a new table with this structure - UNION QUERY is ruled b/c i have more than the three source fields:
ID, Source, and AMT
Where -
ID: would equal to ID;
Source: would be the value of the heading such as B or C or D;
AMT: would be the currency value of each corresponding column.
Help with the Module or any other way to do this will be greatly appericated.
Thank you.
-testcode
View 3 Replies
View Related
Jun 28, 2007
Hello,
I'm helping someone can help me with a little problem I am having with a peculiar data arrangement in Access. I have the following scenario. My table is set up with the following columns:
AgentID Skill_1 Skill_2 Skill_3 Skill_4
100 51 52 53 54
I want to create a new table that arranges the data like this:
AgentID Skill
100 51
100 52
100 53
100 54
Does anyone have any suggestions how I can do this?
View 5 Replies
View Related
Mar 26, 2013
I have the following scenario and do not wish to use a crosstab for various reasons. I am combining three queries into one. The last query has information as follows:
Project Type Rev Budget
12345 Debt 100,000
23456 Impact 50,000
34567 Other 25,000
There are hundreds of these line items. The first two queries hold the project information as well and that would be the field that I would use to join each query. Currently the information above is in line form and I need to put it in column form to coincide with my other data. The final result needs to have the following column headings:
Project, Desc, Exp Budget, Expenses, Debt, Impact, Other, Rev Budget
Please note that the first four headings come from the first two queries so I have this taken care of. how do I change the query to put the Type by line item as a column heading and sum the Rev Budget accordingly without creating a table and doing appends or update queries? Can it be done in the query where I am combining the three queries?
View 2 Replies
View Related
Apr 17, 2014
I have extraction report from calculation program and dont have access to tables. When I import it to MS Access it looks like attached Sheet1. Is there a simple SQL query or VBA code which will allow me to import the data to new Sheet2 table? Fields 1,7&13 will always be the same and I want them to be column names, Fields 4, 10 &14 contains data which will be different each time I delete the old calculation from Sheet1 and import a new one. I would like to be done in Access as I have to import few hundred calculations and store it in Sheet2 table.
View 2 Replies
View Related
Jan 5, 2008
New to MS-Access and I'm trying to help our HR with creating a production tracking table.
This is just a small simple table with all 12 months of the year and a total production column for summing up everything that has been produced for the year. But being "very" new to access I have zero clue on how to do this. I would appreciate any Access for this dummy help I can get . Thanks in advance.
View 3 Replies
View Related
Jan 13, 2015
I have two columns in my report that at the bottom it gives me the following total, as I put below. I am trying to take the two totals that is gives and get the percentage. 1058/3024 = 35%.
=Sum([TotScrwBlank]) =Sum([TotalAmount]) .349 or 35%
1058 3024
I had did the equation as =Sum([TotScrwBlank])/([TotalAMount]), but this is giving me the answer of 46% which is not correct.
View 3 Replies
View Related
Jul 18, 2015
I have a personal expenditure data base indicating daily expenditure on various heads ( File Attached). I want table to reflect expenditure on various heads on daily basis in COLTOT column. Any subsequent change in any of the field to be dynamically updated.Presently I am exporting file to excel and calculating the total there and then manually updating the COLTOT field. Other alternative I tried was to create a form and make a text field and sum the fields using formula like =nz([milk])+nz([vegetables]). I feel it may be unwieldy for such large number of expenditure heads.
View 14 Replies
View Related
Mar 11, 2007
Hi, I have data from a form on my website in a text file, that corresponds to each visitor's input, each 13 lines in the form belongs to one visitor, as shown (twice) at the end of this message.
What I would like to do is have each visitors inputs translated to ONE row, with 13 columns/fields each. It could be appended to the same table or preferably generated in a new one. Note, there are no blank fields, some won't have data after their title, i.e. addy_line_2: is often blank, but at the very least, addy_line_2: or another field name is always there.
It would ROCK if I could also automatically take the name of each field out, i.e. each name is continuous characters up to the : (colon) ...
Thanks in advance, my Access knowledge is obviously limited, I'm sure this is fairly simple!
The fields:
Phone: 213-555-1212
Submit: Continue
addy_line_1: 1000 Melrose Place
addy_line_2:
badge:
city: Los Angeles
email:
homegroup:
name:
program:
state: CA
volunteer:
zip:
Phone:
Submit: Continue
addy_line_1:
addy_line_2:
badge:
city:
email:
homegroup:
name:
program:
state:
volunteer:
zip:
thank you thank you thank you thank you thank you !!
View 1 Replies
View Related
Jan 19, 2006
Hi everyone,
I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:
When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )
The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:
Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06
The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:
Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
*Where the blanks are simply left empty, rather than putting in the word 'blank'
Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:
Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.
Regards
Tony
View 1 Replies
View Related
Jan 19, 2006
Hi everyone,
I can't get my head around this so I'm looking for some help if possible please, there are two questions, the first is:
When in the query, I want the criteria for the date selection to be a question, ie. "[Week Start Date?]" but I want the actual criteria selection to be from the start date plus 5 days, the only way I've done that so far is to do ">=[From?] and <=[To?]", which uses two questions and I don't seem to be able to do ">=[Week Start Date?] and <=[Week Start Date?]+5" which seems basically correct, but I expect I'm writing it incorrectly (basic access knowledge I'm afraid :( )
The second question (after I've got the 5 day date criteria sorted) is that the query produces a table that shows basically the following:
Name Store Date Visited
Tom Bury 18/01/06
Tom Bury 19/01/06
Tom Diss 20/01/06
Dick Thetford 18/01/06
Harry Diss 20/01/06
The query is based on a part week period with the starting date ALWAYS a Monday, so no more than Mon-Sat will appear, I want to put the information into a table or query, so the result ends up as:
Name Store Mon Tue Wed Thu Fri Sat
Tom Bury 18/01/06 19/01/06 'Blank' 'Blank' 'Blank' 'Blank'
Tom Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
Dick Thetford 18/01/06 'Blank' 'Blank' 'Blank' 'Blank' 'Blank'
Harry Diss 'Blank' 'Blank' 20/01/06 'Blank' 'Blank' 'Blank'
*Where the blanks are simply left empty, rather than putting in the word 'blank'
Essentially converting the "[From?]" (as stated earlier) or "[Week Start Date?]" to Monday, that date + 1 to Tuesday, etc, BUT putting multiple dates relating to name and store criteria onto one record :eek:
Beyond me I'm afraid, any pointers would be seriously appreciated, I expect I'm approaching the problem from the wrong angle.
Regards
Tony
View 2 Replies
View Related
Dec 18, 2007
I have a table with 8 columns titled - Destination, Mon, Tues, Wed …to.. Sun. This is shown as "OriginalTable" worksheet in the attached example workbook. I want to create a new table from this original table that looks like "NewlTable" worksheet in the attached example. So I will have a new table that has 3 columns titled - Destination, Day, Weight. Weekdays will become rows so each destination will have 7 rows, one for each day of the week. The measure will be listed against each day of the week. Can someone please look at the attached example for me and help me with this.
Many thanks in advance.
View 2 Replies
View Related
Nov 18, 2004
I don't know if it is the right place to post this question, but I know many people here are good at both Access and Excel. The question is:
In an excel sheet,
A1: Smith, John
A2: 111 Pine St.
A3: San Diego, CA
A4: (555) 128-549
A5: Jones, Sue
A6: 222 Oak Ln.
A7: New York, NY
A8: (555) 238-1845
A9: Anderson, Tom
A10: 333 Cherry Ave.
A11: Chicago, IL
A12: (555) 581-4914
2. Type the following formula in cell C1:
=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
3. Fill this formula across to column F, and then down to row 3.
4.Note that the data is now displayed in cells C1 through F3 as follows:
Smith, John 111 Pine St. San Diego, CA (555) 128-549
Jones, Sue 222 Oak Ln. New York, NY (555) 238-1845
Anderson, Tom 333 Cherry Ave. Chicago, IL (555) 581-4914
This is exactly what I want. And I can do step 3 manually to copy that formula to each cell. But,
My question is in step 3. If I have thousands of lines, how can I fill that formula to thousands cells? from C1-C1000, F1-F1000.
I really appreciate it. Thanks.
View 1 Replies
View Related
Jun 30, 2006
Hi all,
I have a query which runs off a table. I have the following Fields as columns in query: WeekID, A, B, C, D, E
For each week, the letters correlate points given. So for week 1, "A" could have 1 point, "B" could have 3 points, etc.
I want to run a Query that will show A, B, C, D, E as rows like the following:
Letter_Week 1_____Week 2_____Week 3
A_______1_________5___________3___
B_______3_________4___________2___
C_______2_________1___________7___
D_______6_________3___________3___
D_______3_________1___________4___
I can't run a crosstab because it will only crosstab values within all of A, B, etc.
Any idea guys? Thanks in advance, as always.
Caliboi
View 3 Replies
View Related