Math with ASP

[schase]

Does anybody know a method to add a field in a database table by asp code? Especially if you could add only for a month specified? "If you feel a post has been helpful to you, click on the link at the bottom of their post to cast a vote for "TipMaster Of The Week". You don't need to be the one who asked the question to vote"

Stuart

 

[link9]

<%
sql = &quot;ALTER TABLE Months ADD COLUMN january INTEGER&quot;

connection.execute sql
%>


Is that what you mean? I don't get your &quot;month specified&quot; question. Could you be a bit more specific about what you mean?

The above code will add the column, january, to the table, Months, with the datatype, INTEGER.


paul

 

[schase]

Hi Link,

Thank you for your response.

I have a scores table that have the columns of
Callsign | No. Flights | Scores | Kills | Death | Last Flown

the table is populated whenever a game flight ends. So there are individual records for each callsign.

The report would add together all of the items for a particuliar callsign.

The default page shown when someone clicks scoresheet would be for the current month. I would also like to have links below where they could click say January 2002 and it will display January's. The date field is short date i.e. 01/02/2002.

I was able to accomplish it with 2 queries in access, the first summed the records by the day and the second added the day's together - not sure why the first wouldnt.

And it only shows those with a score > 0 instead of showing those without a score - having a score of zero.

Not sure if it is better accomplished within Access or through ASP.

Thank you in advance.

Stuart

 

[schase]

Actually I think I have it well most of it. With an Access Query.

But, does anyone know how to show just zero values for those without a score.

 

[link9]

Say I wanted to sum all no.flights by month. You have to create your own field using datePart() like this:

SELECT
/*SUM NUMBER OF FLIGHTS*/
SUM(f.[No. Flights]),

/*PULL OUT THE MONTH FROM THE LAST FLOWN FIELD*/
DatePart('mm',f.[Last Flown])

FROM scores

/*SELECT ONLY RECORDS FROM JANUARY*/
WHERE DatePart('mm',f.[Last Flown]) = 1

/*GROUP BY MONTH*/
GROUP BY DatePart('MM',f.[Last Flown]);

This syntax is for T-SQL, which is SQL Server, but I'm sure that Access has it's own datePart function, if this particular syntax does not work for you. This is the general idea, though.


paul

 

[schase]

ohhh ok ok I'm following you

And I could probably have a clickable link to pull a particuliar month's data like a where date=