lordfa9

I need to change the column name of one of my tables for example

ItemID		  ItemName1					  Coffee

changed to

StockID		  StockName1					  Coffee

i need an SQL statement to do that, i want the name to be changed (instead of using an AS command)I was also reading something about using the ALTER TABLE and ALTER COLUMN commands, are these the correct command(s) to use and if so, how do i use them in a statement?

I see what you mean. You are going to need to look into GROUP BY and MAX()

your answer is really going around me, seeing as that i don't really understand the terms you are using:

• open recordset
• load all the data into a dictionary first, and then iterate through the dictionary

so do you mean that each coloum has its individual sql statement?

select a,b,c,d,e,sum(f) from table group by a,b,c,d,e having sum(f)>0"

SELECT department FROM yourtablename WHERE item_name NOT LIKE 'Geo positioning system'

SELECT department FROM yourtablename WHERE item_name NOT LIKE "*Geo positioning system*"

Ooo so its like such:

SELECT Stock.Item, Stock.Itemname, Sum(Stock.QTY) AS TOTAL_QUANTITYFROM StockGROUP BY Stock.ITEM, Stock.Itemname;

So this means that all the rows not using the aggregrate function has to be added to the group by?

doesn't matter if you write it all in one line or not.EDIT: just some guy is right you need to use & _ to tell your code it contuinues ont he next line....didn't see that part of the question when I posted.

sql="SELECT Subcode, DateGiven, DateDue, Information & _FROM " & tblname & " & _Order BY " & sort & " " & ordering & "" & _

recreated my file, seems to work now :)might be hardware or software though, i am now using a new platform.By the way, just a general query, when coding or coding general in notepad (Platform migration: Dreamweaver----> Notepad not good ). If i do the following to a piece of code will there be any difference?

sql="SELECT Subcode, DateGiven, DateDue, Information FROM " & tblname & " Order BY " & sort & " " & ordering & ""

typed in one complete line (notepad expands horizontally forever)or If i press enter to shift the various parts code to a new line eg.

sql="SELECT Subcode, DateGiven, DateDue, Information FROM " & tblname & " Order BY " & sort & " " & ordering & ""

will this affect the code? because i recall doing that before and it seems to cause a lot of funny errors, i didn't do it now and it seems alright

I have the following tables, for simplicity I only include one row from eachLOCA

ItemCode		 QtyFish		 20

LOC B

ItemCode		QtyFish		 30

Is there any way I can get the script to display as such:

ItemCode		LOCA		LOCBFish		20		   30

And so on and so forth for every extra item?

I have the following SQL statementSELECT Stock.Itemcode, Sum(Stock.QTY) AS TOTAL_QUANTITYFROM StockGROUP BY Stock.Itemcode;This SQL works fine, however when I add itemname to the statement:SELECT Stock.Itemcode, Stock.Itemname, Sum(Stock.QTY) AS TOTAL_QUANTITYFROM StockGROUP BY Stock.ITEM; It gives me the error ‘You tried to execute a query that does not include the specified expression ‘itemname’ as part of an aggregate function’. Background: there may be more than one entry of the same item in the table (it’s a result of the merging of a few tables) and the first statement is to calculate the total number of each type of stock

Let's say I have these two tables:LOC A

Itemcode	QtyFish		1Meat		3Eggs		4

LOC B

Itemcode	QtyFish		4Meat		3Eggs		1

How do I merge them together using a macro in Ms Access so that I get a table with the total of all the items? For example:

Itemcode	QtyFish 		5Meat		6Eggs		5

Do you have Macromedia Dreamweaver?if you do (and not to confuse you) its better to use a frameset.

The Frame TagThe <frame> tag defines what HTML document to put into each frame In the example below we have a frameset with two columns. The first column is set to 25% of the width of the browser window. The second column is set to 75% of the width of the browser window. The HTML document "frame_a.htm" is put into the first column, and the HTML document "frame_b.htm" is put into the second column:<frameset cols="25%,75%"><frame src="frame_a.htm"><frame src="frame_b.htm"></frameset>

just to add on, to submit data into the database you will have to use an INSERT statement, using the variablesMore info on how to submit variables can be found on the w3schools tutorial

For example if you want to search for peppers in your "vegetables' table, your sql will be like thisSELECT * FROM vegetablesWHERE name LIKE "*peppers*"this will return red peppers, green peppers etc etcyou can substitute peppers for a user submitted value

actually i really feel it depends on the situation that you are inif you want to do a couple of simple projects and never touch programming again, i really reccomend you learn ASp as w3schools can really provide everything you need and you can pick it up really fast if you are learning this as a longterm life skill then php is the way to go

Hi Guys!I have problem : I have table Lottery include fields: ID,Name,Ticket1,Ticket2,Ticket3 ( With a user have max three ticket). I make a program use for Lottery (with Ticket1,ticket2,ticket3). I user random() function.I Want break up my table folow :I will add 2 field in to my table have name : Ticket and NewID and NewID field is Autocrement. With a user have three ticket .Example : ID Name Ticket1 Ticket2 Ticket3 23 John 001 003 112will Break up: NewID ID Name Ticket 1 23 John 001 2 23 John 003 3 23 John 112And finish : I will dial number in NewID use random() functionPlease help me

	   [b]ID		 Name		  Ticket1	Ticket2	Ticket3[/b]		23		 John			 001		  003		112

table to this format?----->

   [b]NewID	  ID	  Name	   Ticket [/b] 		  1			23	   John		  001		  2			23	   John		  003		  3			23	   John		  112

I was also thnking that since you want to do a lottery, world it be better to use the random() function on the ticket number and then using a JOIN to find out who owns the winning ticket?

This may work:

SELECT (SUM(LOCA.Quantity) + SUM(LOCB.Quantity)) AS 'Number of Fish'FROM LOCA, LOCB WHERE LOCA.StockID=LOCB.StockID AND LOCA.Stock = 'Fish' AND LOCB.Stock = 'Fish'

it may be Access not your code. Try running the same query in SQL mode in Access

SELECT SUM(Quantity) AS 'Number of Fish'FROM INVENTORY WHERE Stock = 'Fish'

SELECT SUM(Quantity) AS 'Number of Fish'FROM LOCA INNER JOIN LOCB ON LOCA.StockID=LOCB.StockID WHERE Stock = 'Fish'

LOCA and LOCB are the tables for the two diferent locationsi need to find out how much fish i have in all locations

I was just wondering if anybody else has a problem with using inner join in ASP cause whenever i use an inner JOin command in my SQL query it always dosn't work For example:

<%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "c:/webdata/northwind.mdb"set rs=Server.CreateObject("ADODB.recordset")sql="SELECT Employees.NameFROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID"rs.Open sql, conn%>

is it my syntax?

Ok lets say i have this tabeINVENTORY

Stock				Location			   QuantityFish					 A						  2Fish					 B						  3Fish					 C						  4

How do i write an SQL statement to find out what is the total amount of fish i have in all locations?

double check the calues in the top 2 $cfg lines, i think its not correct.a friend say that the address should be http://localhost, not sure bout it thoughdid you make any changes to config.inc.php? The root pw should be blank

hmm i tried using inner join once for my SQL statement once but it didn't really work.Maybe you are better off combining the two tables together?

i not really sure about the convert to excel part but if you are willing to use Ms Access as a go between (user types data into ASP form which is entered into Ms Access, whose tables you can convert to excel) u should read w3school's asp and ado tutorial 1st