Group Column Headings And Sum Their Values
Jul 6, 2005
Hello,
can the following be done? :confused:
I have a one-rowed query, with the following column headings and values:
(see pic1)
I want similiar columns to be grouped and their respective values to be summed.
I.e.
(see pic2)
Can someone plese tell me how this is done in SQL?
I could just add the columns manually through the Design-View, but this would cause that new column headings that are added in the future to not be reflected in the query.
Jean
View Replies
ADVERTISEMENT
Dec 4, 2011
I have this table ("people") and an example of possible rows:
id(key) COL 1 COL2 department country name
1 xx yy KPP USA John
2 zz kk KPP USA John
3 ss ff TLL USA John
4 ww qq PPO Italy Marco
5 jj uu PPO Italy Marco
I have to count the number of distinct DEPARTMENT for each NAME; so, for John should be 2 (KPP and TLL) and for Marco 1 (PPO).
I have tryed in this way:
SELECT
COUNT(DISTINCT department) AS NumberOfDifferentDepartments
FROM people
GROUP BY name;
But Access says me there is a syntax error.
I'm working with MS Access 2002.
View 2 Replies
View Related
Oct 12, 2004
Looking for a way to list / print column headings in a table.
View 2 Replies
View Related
Apr 28, 2006
I am using Access 2000. I have created a crosstab query to run monthly reports based on a fiscal calendar year. My problem is that my column heading is a parameter [Fiscal Period] that returns the fiscal week #s as column headings and they change for each fiscal period. I cannot find a way to list these in the properties box as column headings and therefore they will not display as field names and I cannot create forms or reports from them. Can anyone help?
View 11 Replies
View Related
Nov 1, 2006
I have about 10 columns with data in each column...the columns dont need to show every time. I want to set it up where the user pics the column heading from a drop-down menu and then that column alone will show, none of the other columns are visible...can this be done?
View 1 Replies
View Related
Jan 21, 2008
Hello to everyone!!!i need to ask somethingI CREATE I DYNAMIC QUERY .....HOW CAN I CHANGE THE NAME OF THE COLUMN HEADINGS?THANX IN ADVANCE
View 1 Replies
View Related
Sep 12, 2012
I have a query that creates a table.
I then use another query to sort and remove duplicate entries, using the 'Totals' option Group By and Last.
I then export the datasheet to an Excel spreadsheet.
All goes well, except that the headers on the spreadsheet show LastOfXXXX or GroupByXXXXX. Now I can run a replace function in Excel to remove LastOf and GroupBy from each column header, but it would be more useful if the replace routine was performed prior to the export to Excel.
View 5 Replies
View Related
Aug 17, 2015
I have a Graph that uses a crosstab query to generate the data. Its only a simple query. One of the fields is project type, but in the query this changes to a number instead of text? I would like to have this as text but don't know how to go about it. I have searched forums and tried Allen Browne's suggestion on specifying column headings but no luck.
I have attached the initial query and the crosstab query in the screen shot as well as the graph. My aim is to get the project names in the legend, instead of 1, 2 as shown.
View 6 Replies
View Related
Aug 22, 2006
Not sure if there is a quick answer but I am trying to complete a crosstab query that references a lookup table. I cannot remove the lookup tables because the database was designed by a consultant. The lookup table is referenced as the column heading. The query works fine until I change the column headings in the properties box - it returns the column headings but there are no values. Am I doing something simple wrong or is it having troubles because it is a lookup table for column headings.
View 3 Replies
View Related
Feb 3, 2014
I have inherited an Access 2003 database and have been asked to improve the functionality of said database. Specifically, I have been asked to create some charts for improved reporting. I used the wizard to create a crosstab query to support the chart(s):
Code:
PARAMETERS [Forms]![Switchboard]![ActualDate] DateTime;
TRANSFORM Sum(qryPMCompletion.CountOfWOs) AS SumOfCountOfWOs
SELECT qryPMCompletion.PA_Priority
FROM qryPMCompletion
GROUP BY qryPMCompletion.PA_Priority
PIVOT qryPMCompletion.wo_Completed
WITH OWNERACCESS OPTION;
Now what has happened is that the wo_Completed is a Yes/No field so my column headings and therefore my legend comes up as "-1" and "0" instead of "Closed" and "Open".
Is there anyway to change the name of the column headings or shall I just create my own legend?
View 1 Replies
View Related
Feb 26, 2014
Is there a way of changing the column heading names on the split form portion of an access form. I tried changing them using a query to populate the form and changing the caption of each field in the query and that didn't work. I also tried adding a [Caption] at the design level of the table and that had no affect also. The split form seems to be displaying a portion of the actual field name or something like that.
View 3 Replies
View Related
Mar 5, 2007
Hi all,
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
How do I do this?
Kind Regards,
Matthew
View 1 Replies
View Related
Jan 30, 2014
In my table for duplicate "line no" I have different "contractor" like below.
LINE NO CONTRACTOR
L-0001 C-1000
L-0001 C-2000
L-0003 C-6000
L-0003 C-8000
L-0003 C-9000
L-0004 C-5000
Now I would like to make a query for transposing values like below:
LINE NO CONTRACTOR1 CONTRACTOR2 CONTRACTOR3
L-0001 C-1000 C-2000
L-0003 C-6000 C-8000 C-9000
L-0004 C-5000
how I have to make this query?
View 1 Replies
View Related
Jun 9, 2013
I am trying to group the following Query to find duplicates with no success.
Base Query:
SELECT AccountActivity.AccountID, Mid([details],InStr(1,[details],"-")-1,4) AS FetchedCat, elookup("Neighborhood","Combo","Category='" & [FetchedCat] & "'") AS Neighborhood
FROM AccountActivity
WHERE (((Mid([details],InStr(1,[details],"-")-1,4)) Like "?-??"));
Find Duplicates Query:
SELECT AccountsWithNeighborHood.[AccountID], AccountsWithNeighborHood.[FetchedCat], AccountsWithNeighborHood.[Neighborhood]
FROM AccountsWithNeighborHood
WHERE (((AccountsWithNeighborHood.[AccountID]) In (SELECT [AccountID] FROM [AccountsWithNeighborHood] As Tmp GROUP BY [AccountID] HAVING Count(*)>1 )))
ORDER BY AccountsWithNeighborHood.[AccountID];
View 3 Replies
View Related
Mar 8, 2015
I have a table with following two columns
TechName Version
SQLServer 2000
SQLServer 2003
SQLServer 2005
SQLServer 2008
SQLServer 2008R2
Oracle 11G
MSOffice 2000
MSOffice 2003
MSOffice 2007
MSOffice 2010
How can i show this data as components eqch version for a techname to be one component order need not follow
TechName Component1 Component2 Component3 Component4 Component5
SqlServer 2000 2003 2005 2008 2008R2
Oralce 11G NULL NULL NULL NULL
MSOffice 2000 2003 2007 2010 NULL
View 4 Replies
View Related
Sep 11, 2007
I am trying to count/find the page count. or more like to find how many pages(blanks) are within Y(each Y)..
example:
(fieldl1) (field2) (what im trying to get in the nxt field)
1 Y 3
2
3
4 Y 1
5 Y 5
6
7
8
9
10 C 1
Do i use group by?? im not sure, when i use count, it counts all of the Ys and gives me one the total of all of them. Im trying to find the total of each group of Y's...
can anyone help me out??
View 8 Replies
View Related
Mar 16, 2013
Query is based on 1 table" tblTimeCnv_AgeGroups
Fields:
AgeGroup Time Ranking
30-& under 11.22 1
30-& under 10.41 2
30-& under 9.22 3
30-39 11.32 1
30-39 9.53 2
30-39 9.34 3
30-39 9.30 4
See attachment
My Ranking field is:
Ranking: DCount("*","[tblTimeCNV_AgeGroups]","[AgeGroup]=" & [AgeGroup] & " and time <= " & [time])
In the Ranking column the result is: #error in the first 3 rows then zeroes
View 3 Replies
View Related
Apr 26, 2007
Hello,
I am not sure what cateogory to choose for this question but I would greatly appreciate your help.
I have 5 buttons in my option group:
1. very often
2. often
3. occasionally
4. rarely
5. never
I have to produce a report using description of each button in my report, not numeric value. I heard something about global variable but I am not sure how to do it.
Many thanks,
Debbie
View 1 Replies
View Related
Feb 9, 2005
I have five Option Groups on my form. I was running into a problem where after selecting options for one record and then moving onto the next record, the previous options would stay selected for the new record.
I "thought" I had found a solution by setting the Option Group values to Null in the OnCurrent event of the form, but I found a problem in that when going back to a previous record, it is nulling out the previously selected options. Doh!
Any ideas on how to get around this problem? I want to be able to go from one record to the next without carried over options, but I also want to be able to go back to already entered records without having them nulled out.
View 3 Replies
View Related
Jul 5, 2006
I have created an option group with two buttons (New, Used). In another text box I used a formula taking values true or false on option group. The formula is not working. It goes like this.
=IIf([radNew],[/U][/B]Null,IIf([radUsed],[/B]IIf([txtAgeofCar]<184,[txtExciseinTotal]=Null............
View 2 Replies
View Related
Jul 20, 2006
Hi there,
I inserted an option group with 3 checkboxes (Yes, No, Re-open). My problem is that I see the values of these checkboxes (1=yes, 2=no, 3=re-open) in my table. I want to see just text and no number. How's that possible ?
Thanks
stronghold29
View 1 Replies
View Related
May 24, 2006
Hi,
im after a option group that stores multiple values, the only way, long winded as it will be is to create a table with all the values then have tickboxes for all of them.
Is there a simpler cleaner way?
View 1 Replies
View Related
Jul 3, 2006
I want to create an opion group with two rad buttons in it. The values of those button wont be values but as true or false in order to use it in another text box to performa calculations. EX.
=IIf([radNew],"Hello",IIf([radUsed],IIf([txtAgeofCar]<184,[txtExciseinTotal]=Null,IIf([txtAgeofCar] Between 185 And 365,[txtExciseinTotal]*(-0.15),IIf([txtAgeofCar] Between 366 And 1095,[txtExciseinTotal]*(-0.2),IIf([txtAgeofCar] Between 1096 And 1825,[txtExciseinTotal]*(0),IIf([txtAgeofCar]>1825,[txtExciseinTotal]*(0.25))))))))
As you can see radNew and radUsed are the Options Buttons. Its not working in an option group.
Thanks in advance
View 2 Replies
View Related
Sep 15, 2013
I am currently using the below code to query values that have a unique "OverrideDescription" and where by the "Upload Date" is always the latest.
SELECT t1.*
FROM [DaisyServiceRates-Amended] AS t1 LEFT JOIN [DaisyServiceRates-Amended] AS t2 ON (t1.OverrideDescription = t2.OverrideDescription) AND (t1.[Upload Date] < t2.[Upload Date])
WHERE t2.OverrideDescription IS NULL;
However I also need to be able to do a unique count of the [OverrideDescription] field.
I have found methods whereby I can do use, but they all use a GROUP BY function, the issue being that when I use this method it prevents be from updating the values saying "recordset not updateable".
Any method that does not use the GROUP function. I did also try preforming the COUNT on a separate table and doing as JOIN, but this also prevented updates to the date.
View 1 Replies
View Related
Aug 19, 2013
I have a report that returns timesheet info on employees. The group-by is on the employee, and the detail records are projects billed-to, including how much time billed to each project by that employee.
Parts of the detail line are accomplished by dlookups, and I was hoping to sum() these values on each employee/group, in the employee footer area.
Per the image, the circles show where I hope to show the sum'd value from text boxes indicated by the arrows...
In the group footer on employee I've set text boxes to:
Code:
=Sum(DLookUp("ProjectLeaveHours","qryTimeRpt_LeaveOnly"))
...basically taking the dlookup accomplished in the detail line and trying to sum() it... No luck.
Code:
=Sum([RADefaultHours])
...using the name of the control where the dlookup is accomplished prompts me to declare the value of the "RADefaultHours" - indicating the report text box control doesn't know what I'm talking about...
Code:
=Sum([Reports]![rptTimesheetReport]![RADefaultHours])
...trying the above again, but hoping to properly declare what is being sum'd... No luck.
...I saw some posts regarding setting "running sum over group" - and I tried that on the text box in the footer where the sum() is being attempted, but no luck.
Can I do this this way? Perhaps I need to set some other variable that is more agreeable than a 'textbox with a dlookup' to do the sum when grouping?
View 4 Replies
View Related
Aug 1, 2006
Hello!
I have problem designing query for table whitch have 4 columns(ID,Date,Name,Value). I have one record each day for station(Name).
Now I would like to get a difference between each day for a month. (See picture)
Thanks in advance!
View 2 Replies
View Related
