Grouping Query Output
I know that the sql 'group by' function exists but it's not working the way I want it to.
Brief background:
I'm making a report that displays, among other things,
-Project Name
-Project Number
-Project Manager
-Fiscal year total spending
My problem is, I cannot figure out how to group all my data together based on Project Number and Manager. Each project has several records for Fiscal Year total spending but the same information for project name, number, and manager.
For example, Project 1 called Test has Manager A and 19 enteries for Fiscal Year total spending (i.e. it's the past 19 year's worth of data).
The report includes a header section that lists the project name, project number, and manager. Below the header information is a list of the Fiscal year info. Code:
View Replies
ADVERTISEMENT
I have the following query:
SELECT sponsor, COUNT(sponsor) * 2 AS total FROM Referrals GROUP BY sponsor
Works great, returns the sponsor and the total * 2 of their referrals because that's how much they make per referral. My problem is I need to pull more information from that table yet still maintain my grouping by sponsor. Every time I try to add another field, or even * like
SELECT *, sponsor, COUNT(sponsor) * 2 AS total FROM Referrals GROUP BY sponsor
it wants me to group on all of the fields and I only want to group on the sponsor. I'm not smart enough to know how to nest these or group part of it, etc. Hope I'm making sense.
View Replies
View Related
I have an ASP project that I want to return grouped records from a SQL database.
The data fields are, for example:
Company = TEST, Portfolio = port1
Company = TEST2, Portfolio = port2
Company = TEST, Portfolio = port3
Company and Portfolio would be the fields in a table named "data".
What I need to do is display the results grouped by company.
For Example:
TEST -- port1 -- port3
TEST2 -- port2
I have not been able to get anywhere with this and hope someone can help. The only thing that got me remotely close was "GROUP BY" but it only works with one field. I need to return all of the data fields.
View Replies
View Related
I pull the data out from the sql db. There are quite a few records the same, but diff. ID. I would like to group them. How to do it?
For example: Here is what in the DB
01. English 101 - this is a night class
02. English 101 - this is a night class
0.3 English 101 - this is a night class
I would like it displays only - English 101 this is a night class.
View Replies
View Related
I am stumped as to how to do what appears to be a very simple thing.
Want to display the results of a query containing fields ID and Item,
grouped by the ID. I only want 1 instance of the ID to appear on the
page as a header for each group. There are 10 IDs in the query
results. Thought I could use <% Response.Write (rsName("Item")) WHERE
ID=1 %>, but doesn't work. How else could I accomplish this?
View Replies
View Related
I am building an intranet web page using ASP that searches an Access DB. In the Database I have a field that contains dublicate words. For Ex.
[ SUBJECTS][ORG_LEVEL_1]
-------------------------------------
[Abuse ][Womens Abuse Society ]
[Abuse ][Childrens Abuse Center ]
[Baseball ][Newmarket Baseaball ***.]
When I do a search, My current Script shows the results just as it is shown there, what I would like is for it to show like this.
[Abuse ][Womens Abuse Society ]
[Childrens Abuse Center ]
[BaseBall ][Newmarket Baseball ***. ]
View Replies
View Related
I have a list in a database that I have categorised using a category field. I now want to retrieve all records but grouped by categories.
how do i get my asp to loop through each category creating a recordset for each one rather than hard code a new recordset for each category i would rather it did it dynamically if thats possible ?
so eg
category travel
1 - B&B's records (recid 1)
2- rail info (recid 2)
category local info
1 - swimming baths (recid 3)
2 - museum info (recid 4)
View Replies
View Related
i'm trying to create a report in asp... and i want to group by months... for example
April
location1 $20,333
location2 $20,333
location2 $20,333
May
location1 $30,333
location2 $20,333
location2 $70,333
June
location1 $30,333
location2 $20,333
location2 $70,333
and i am pulling the date field... and example what that looks like it...
4/20/2004 12:00:00 AM
5/20/2004 12:00:00 AM
6/20/2004 12:00:00 AM
View Replies
View Related
I have a Table (Table-name:Problem_List) and Column (Column-name:Title). There are many records in this table with the same (or similar) titles. What i'm trying to achieve, is a list of this column, but instead of 2000 rows, I need the similar names counted list like this...
Blocker : (100)
Serious : (34)
Warning : (430)
etc.
Running: SQL 2000 on IIS6, using ASP
View Replies
View Related
I am writing a report using ASP and am having trouble displaying the data in the way i want. I'm collecting turnovers for various companies and each of the companies has a county code. e.g. Yorkshire is defined as number 2.
I need to group the companies together by county and need to display a totals line for those members only and then on the following lines I want the next county and its companies.
I can group the counties together but the totals line I'm displaying appears after each companies rather than after the group of companies for a given county.
I use a do while loop to check that there is data in the database for each company. I tried putting the totals line outside the loop and that only shows 1 totals line for ALL companies.
View Replies
View Related
I've got a lot of referer variables in my dbase and I'd like to have a query that groups them just by the domain. Everything up to the 3rd "/" should do it. So far, I'm using:
SELECT COUNT(id) AS clicktotal, ref
FROM clicklogs
GROUP BY ref
This only lists everything by the actual referring url. Is there a way to only have it group by the domain within the referring url?
View Replies
View Related
I have a FAQ section on this website I'm developing that is *supposed* to pull data from three different tables: tblFAQ, tblAssignedPanels, and tblPanels.
In tblFAQ, there are 4 columns: faqID, panelID, question, and answer. The panelID links the FAQ with the associated panel in tblPanels based on the same field (panelID). tblPanels also holds the name of the panel that is associated w/ the ID. Code:
View Replies
View Related
I have been playing around with two different ASP scripts,
1 - To display data from a table in columns
2 - To put the data in to categories.
1 - Columns
Code:
View Replies
View Related
I was wondering how some websites produce the paged numbers when displaying search results, so that if more than say 50 pages exist they group them.
For example:
Pages: 1 2 3 4 5 6 7 8 9 10 (10-20) 20-30)
Then when clicked say '10-20' the following would show:
(0-10) 11 12 13 14 15 16 17 18 19 20 (20-30)
Get the idea??
This looks a lot easier than a list of say 300 pages.
View Replies
View Related
My code retrieves a username and a password from a form. Then this information is compared to some usernames and passwords that are stored in a database. The important thing here is that the comparison must be case sensitive meaning that "passWord" is not the same thing as "password"
I have this code, working fine in access 2003
SQL = "SELECT * FROM users WHERE StrComp(username_column,'" & entered_username_in_form & "',0) = 0 AND StrComp(password_column,'" & entered_password_in_form & "',0) = 0"
but get the following error when I run it against my sql 2005 database.
[Microsoft][SQL Native Client][SQL Server]'StrComp' is not a recognized built-in function name.
I don't know the corresponding t-sql for the query.
View Replies
View Related
I have an Access database with two fields that I would like to group by. The two fields are location and department. Field names are "Location" and "DeptName".
I have the code correct for grouping by location. That code looks like this: Code:
View Replies
View Related
I am running a querry on an access database and have set the number of records/page displayed at 20. if there are more than 20 records returned, then 1st page will show the first 20, the next page will show next 20 and so on....
The trouble:
the count of total records displayed is correct and the first page is displayed correctly. But when i click on *Next* to go to the next page, all the records of the database get displayed (not the 2nd page of records from the query).
View Replies
View Related
I am using ASP/MS ACCESS to see how I can query the same database, via 2 formfields.
{name: - search}Textfield 1: - Search by Category
AND/OR By
{name: - searchT}Textfield 2: - Location
Currently,
strSearchwords = Trim(Request.QueryString("search")); where "search" is the name of Textfield1
Which is fine, but how can I set it so that on Submit, the string from search, and searchT are somehow joined together into one string?
View Replies
View Related
How can I retrieve the output that has been sent to the client, e.g
[vbs]
<html>
<head><title>Test</title></head>
<body>
this is output
</body>
</html>
<%
Dim strContents
'Now I want to put the contents in an variable, something like
strContents = Response.Contents '(but that doesn't work)
%>
[/vbs]
View Replies
View Related
With this line of command :
Response.ContentType = "application/vnd.ms-excel"
I can output a file in XLS format with no problem.
But on a specific server, the file pops as an ASP page. My code is fairly simple.
Does the server need to have Excel installed in order to work ??
View Replies
View Related
How do I output an ASP variable insde of Javascript?
View Replies
View Related
The problem i have in my system you can seacrh clients and then display their details. what i wanna do is find the client i want and then have a button called e.g. generate and then the asp pops up a word or rtf document with there details sloted in to a preformatted document.
Now i know i could do this by creating the doc line by line with asp on the file but is there a faster way of just inserting the asp values into placeholders in the word or rtf file which has already been setup?
View Replies
View Related
I'm having a problem controlling the output from an access db and the css two column layout. It goes like this.Left side has a connection string and outputs 3 columns of db data to the leftside of the page. There is also a conection string on the right side of the page. It outputs that db data AFTER the right side processes that data. So the page is staggered, left to right instead of displaying the results across from each other.
View Replies
View Related
I have an ASP program. It outputs HTML with some
XML data islands in it. When I run the program, the
browser sees the HTML and doesn't work the way
I want it to. however, if I view source on the output
page, save the source as an XML file, then open the
saved file, it works properly.
How do I get the browser to treat the output from an
ASP program as XML instead of HTML?
View Replies
View Related
How can I send gzipped (compressed) output back to the browser, using ASP (and IIS)?
I'm looking for a function similiar to PHP's ob_start("ob_gzhandler"); function.
View Replies
View Related
This is really puzzling me; I am developing a site for a client and have uploaded it to a remote server with ASP enabled; I and my friends do not have a problem diplaying the output HTML from my ASP statements however, my client is unable to see it. He says he can see all the other page (CSS layout) but is unable to view the content (output HTML) from my ASP statement.
Now I am clever enough to know that ASP is server side, so I cannot understand why he can't see it- and he has tried this with two of his computers.
The only possible explanation that I can come up with is that he may have Javascript disabled in his browser as my pages contain a small amount of Javascript.
View Replies
View Related
if guy can help how to output if my customer make reservation....enter everything ..etc enter first name, last name. then after click make reservation button it will come out his/her name on the page for his/her referrence ...then it will clear once another customer enter it.
View Replies
View Related
This might sound stupid to you guys, ok i have this:
------------------------------------------------------------
perc = (total*100)/limit
response.write (" You are using ")
response.write perc
response.write (" % Of your 1GB")
-------------------------------------------------------------
but on the output i just need the first two digits, but i get this
You are using 21.8057899 % Of your 1GB
View Replies
View Related
Please guide me. How can I output the records from my recordset in columns in a table? That is, instead of just one record per row in a table, how can I have multiple records per row?
View Replies
View Related
There is no error in the code but can't get output from this code;
<html>
<head>
<title>g</title>
</head>
<% set db=server.CreateObject("ADODB.Connection")
db.Provider="Microsoft.Jet.OLEDB.4.0"
db.open "C:/data/CIS_data.mdb"
VarQuery="SELECT FullName,ICNo FRoM Patient_Details"
set rs=db.Execute(VarQuery) %>
<% Do while NOT rs.EOF %>
<% rs.movenext %>
<% Loop %>
<% rs.close %>
</html>
</head>
View Replies
View Related
if something similar to php (ob_get_contents(), output
buffering) exists in ASP/VBscript.
View Replies
View Related
I haven't being doing that much in ASP, but I have to create this:
It has to read out data from some DB to excel.
The following (corrected) code works if just it outputs as html, once
the Response.ContentType is excel. then it offers to open/save the
file, and stops - file not found - what do I do wrong? Code:
View Replies
View Related
this question is not about SQL exactly
so, I am asking here.
how to get output from sql procedure ?
my sql p. like below
Code:
if not exists(select .....................)
set @result=0
else
set @result=1
so, how can I get @result in ASP
View Replies
View Related