Multiply Of Columns Based On New Group

Dec 19, 2013

Product IdIsPrimaryQuantity
P0011
P001.102
P001.204
P001.305
P0021
P002.106
P002.207
P002.309
P002.4010
P002.5011

Need the query for result each group shows multiplied value of group quantity and last row of the group is shown with NULL

Product IdSubProductQuantity
P001 40
P001 P001.3NULL
P002 41580
P002 P002.5NULL

This is same as [URL] ....

View 5 Replies


ADVERTISEMENT

Get Group And Multiply Records

Dec 20, 2013

1.Create the tables with insert queries
2. provide the result as required in an temp table
3. Display the expected result

======================================================================
CREATE TABLE and Insert Data
======================================================================
use master
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON [PRIMARY]

[Code] .....

View 14 Replies View Related

SUM Multiply Based On Column Entry

Sep 5, 2012

SELECT EmployeeId, PiecemealType,SUM(PiecemealQty) /100 AS TotalTrays, ActivityId
FROM SR.dbo.PayTrays
WHERE WorkDate > '2012-01-01'
AND WorkDate < '2012-12-31'
GROUP BY EmployeeId, PiecemealType, ActivityId
ORDER BY EmployeeId, PiecemealType

PiecemealType is the type of tray
1= 15 Items per tray
2= 12 Items per tray
3= 8 Items per tray
4= 6 Items per tray

I'm trying to work out a bonus for 1 cent per item worked. The divide by 100 moves the total from cent to dollar and the SUM is adding all weekly values together.I just need to work out how to multiply the the SUM value for trays by the 4 different PiecemealType values. If it was static i could just add *15 to my SUM.

View 2 Replies View Related

Reporting Services :: Row Group Total Broken Down Into Columns Based On Field Value

Sep 25, 2015

In report builder 3.0 I have row groups. I want a total at the end of each row but I want the total to be broken down by 3 columns based on 3 possible values of a field in the dataset. The report expands as the date range entered is increased. I want the total of clinic id + service id + program id + protocol id + appointment date but I want the total column to be broken down by appts that have shown or not shown or canceled.  

See screenshots below for seeing how I have it configured. Is this possible? I have tried every combination of possibilities but I keep getting the row total in each of the 3 columns comprised of the total column.

and

The results look like: 

The last Total column displays the entire row count NOT separated by the show, no show, and cancel status'.   I have tried filters and different expressions but keep getting the same output. Is this even possible?

View 9 Replies View Related

Transact SQL :: Multiply Multiple Columns From One Row

Nov 23, 2015

I have a table with the following format

I am looking for a way to get the PRODUCT of all columns and group by M_DOMA, [FROM] - Basically multiply all columns on the row that are not M_DOMA or [FROM].

Is this possible using T-SQL 2012?

View 5 Replies View Related

How To Use Group By (group Tasks Based On Projects)

May 28, 2007

Hi folks,

I have a Projects , each project have many tasks now i want to display tasks replated to each project:

for example:







Project1-------------------->task1

task2

task3

task4



Project2----------------------->task4

task5

task6





.............................................projectN.....................





how to write query for this



i have 2 tables:

Project .......>columns are projectid

Task------------->columns are projectid, taskid

|


View 4 Replies View Related

Hiding/Showing Columns Based On The Columns Present In The Dataset

Jun 27, 2007

I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.

Can I do that??

Any reply showing me the right way is appricited.



-Thanks,

Digs

View 3 Replies View Related

T-SQL (SS2K8) :: Select Group On Multiple Columns When At Least One Of Non Grouped Columns Not Match

Aug 27, 2014

I'd like to first figure out the count of how many rows are not the Current Edition have the following:

Second I'd like to be able to select the primary key of all the rows involved

Third I'd like to select all the primary keys of just the rows not in the current edition

Not really sure how to describe this without making a dataset

CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,

[Code] .....

Group by fails me because I only want the groups where the Edition_fk don't match...

View 4 Replies View Related

Calculated Columns Based On Multiple Columns?

Aug 12, 2014

MS SQL 2008 R2

I have the following effectively random numbers in a table:

n1,n2,n3,n4,SCORE
1,2,5,9,i
5,20,22,25,i
6,10,12,20,i

I'd like to generate the calculated column SCORE based on various scenarios in the other columns. eg.

if n1<10 and n2<10 then i=i + 1
if n4-n3=1 then i=i + 1
if more than 2 consecutive numbers then i=i + 1

So, I need to build the score. I've tried the procedure below and it works as a pass or fail but is too limiting. I'd like something that increments the variable @test1.

declare @test1 int
set @test1=0
select top 10 n1,n2,n3,n4,n5,n6,
case when (
n1=2 and
n2>5
)
then @test1+1
else @test1
end as t2
from
allNumbers

View 5 Replies View Related

Sum In A Group Based On Other Group Value

Dec 4, 2007



Hi all,
I have a Report and I have used Table in the report to define 5 levels of Sub Groupings: Group1, Group2, Group3, Group4, grpResourceBehavior.

The Sample data which comes is:
Group1 Group2 Group3 Group4 ResourceBehavior BgtHrs BgtAmt
A1 B1 C1 D1 NML 12 12
A1 B1 C1 D1 ODC 0 12

Now, to calculate a field "Cost", I have to use following condition. If the ResourceBehavior is NML, then Cost = BgtAmt/BgtHrs
But, if the ResourceBehavior is ODC, then COst = BgtAmt/(SUm of BgtHrs for the resourcebehavior NML)

I am not able to calculate the Sum of the BgtHrs for different ResourceBehavior. Also, I need this field calculation for each and every group as well, and this makes it all the more complicated.

Any help is appreciated.

Thanks in advance.
Swati

View 6 Replies View Related

Group By Clause Based On Condition

Jun 14, 2014

tblScore contains score for each problem

id problemID score
------------------------
1 1 10
2 2 30

tblSubmission contains problem submissions for each user

id user problemID accepted
-----------------------------------------------
1 UserA 1 0
2 UserA 1 0
3 UserA 1 1
4 UserA 2 1
5 UserB 1 0
6 UserB 1 1
7 UserB 2 1

For UserA :
- For problemID 1
-> submitted three times
-> rejected for first two submission and accepted on third submission.
- For problemID 2
-> submitted one time
-> accepted on first submission

For UserB :
- For problemID 1
-> submitted two times
-> rejected for first submission and accepted on second submission.
- For problemID 2
-> submitted one time
-> accepted on first submission

Now I would like to process the table and want to get the following result :

user Score
--------------------------
UserA 36 (6 + 30)
UserB 38 (8 + 30)

Explanation :
- For each rejected submission, a -2 point penalty.
- UserA have submitted probelmID 1
- > score of problemID 1 is 10.
- > first two times rejectd
- > third time accepted.
-> score = 10 - 4 = 6
- UserA have submitted problemID 2
- > score of problemID 2 is 30
- > first time accepted. No penalty will be counted
- > score = 30

so final score for UserA = 30 + 6 = 36

Similar for UserB.

View 2 Replies View Related

Query Based On Group By Clause

Jun 21, 2008

Hello!
suppose i have two tables, table1 columns(empcode (pk), empDept) and table2 columns(empcode (FK),Date,Attendance) i wanted to write a query to get output like
DEPT ABSENT
-----------------------------
Accounts 10
EDP Section 0 **
Admin 2
Stationary 0**

if no employee is absent in the department it has to display Zero

View 2 Replies View Related

T-SQL (SS2K8) :: Count Record Based On Group

Dec 10, 2014

This my table named myData

CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,

[code]....

I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow

select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,

[code]....

View 1 Replies View Related

SQL 2012 :: Add Serial Number Based On Group By

Mar 23, 2015

I need a new field added 'Field1' which will add SEQUENCE number 1,2,.. based ON GROUP BY MasterID..AND another field TotalCount which will COUNT total masterID (here it will be 2)

CREATE TABLE #Temp1
( MasterID INT,
ClientName VARCHAR(10),
ProductName VARCHAR(50)

[code]...

View 0 Replies View Related

How Can I Hide A Table Column Based On A Group ?

Jul 12, 2007

I am using Reporting Services 2000. If you find out that Reporting Services 2005 would resolve this issue, please lemme know also. But I want to mention that I would prefer a way to fix this without changing Reporting Services versions.

I have a table that has a group on ProductTypes. This group is set to PageBreak at end.
What I need to do is to conditionally hide an entire column based on the current group's ProductType.

Examples
Page 1 = Product 1 = Column 1 is visible
Page 1 = Product 1 = Column 2 is visible
Page 2 = Product 2 = Column 1 is hidden
Page 2 = Product 2 = Column 1 is visible

Can you help me figure this one out ? I've tried everything I found on the net, especially everything on this page : http://blogs.msdn.com/chrishays/rss.xml

Thank you in advance.

View 2 Replies View Related

SSRS: Sum Of A Column With Subtotals Based On A An ID Group

Jul 18, 2007


Hello,
I'm just starting to analyze with SQL 2005 reporting services and trying to build a test report using the report wizard.

This is how i've got it in SQL reports now:

Opportunity ID Product Name Cost
--------------------- --------------------- ----------------
1 Orange $1
1 Apple $2
1 Grapes $4
2 Orange $1
2 Apple $2
3 Orange $1


Now, what if I wanted to group them to provide subtotals like:


Order Product Name Cost
--------------------- --------------------- ----------------
1 Orange $1
Apple $2
Grapes $4
-------------------------------
SubTotal $7

2 Orange $1
Apple $2
-----------------------------
SubTotal $3

3 Orange $1
------------------------------
SubTotal $1

The problem that i'm having right now is on the layout designer somehow programming the subtotals based on the IDs.
Am i thinking in the right direction that I should be using the layout designer to do this? Or should this be done programmatically?

Thanks.
Mike

View 6 Replies View Related

Can Table Created On Different File Group Based On Schema

Feb 17, 2012

Provided that we have 2 type of tables, the first type of tables with schema schema_a and the second type with schema schema_b.Also 2 filegroups apart from primary created: filegroup_a and filegroup_b.How can I configure so that when table with schema schema_a created, it can be put on filegroup filegroup_a automatically and when table with schema schema_b created, it can be put on filegroup filegroup_b automatically?

View 1 Replies View Related

SQL Server 2012 :: Select Top X% From Group Based On Total?

Mar 21, 2014

What I need to do it select the top 80 percent of records per group based on the group total. To be clear I am not trying to just grab the top x percent of rows.

Table 1:

DealerID RepairID
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
2 11
2 12
2 13

Table 2:

RepairID Tire
1 225/65R17 102T
2 225/65R17 102T
3 225/65R17 102T
4 235/60R18 102V
5 235/60R18 102V
6 235/60R18 102V
7 235/60R18 102V
8 205/55R16 89H
9 205/70R15 89H
13 225/65R17 102T

Table 1 has the total number of repair orders per dealer. This can be obtained by simply grouping on DealerID and counting the number of RepairIDs.

Table 2 has information on some of the repair orders and it is needed to select the top 80% of tire sizes. This table will be joined to Table 1 and grouped by DealerID and Tire.

Desired Output:

DealerIDTire RepairsOfThisTireRepairCount PercentOfTotalRepairOrders
1 235/60R18 102V4 10 40
1 225/65R17 102T3 10 30
1 205/55R16 89H1 10 10
2 225/65R17 102T1 3 33

The equation I am given to calculate the top tires per dealer is as follows: Total # of tires for the size / Total # of repair orders per dealer.

Here is what I have so far though I think I might have to rewrite all of it.

SELECT
DealerID ,
COUNT(RepairID)
INTO #TotalDealerRepairOrders
FROM
dbo.Table1
GROUP BY
DealerID
SELECT

[code].....

View 9 Replies View Related

Combining Results Of Multiple Rows Based On Group?

Jul 17, 2013

I have a table of attributes set up as follows:

ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.

I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:

ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks

View 5 Replies View Related

Power Pivot :: Calculation Based Upon Subtotal In Group

Aug 4, 2015

PowerPivot 2013. In this example I am trying to get revenue per employee (highlighted in yellow)

In order to do that I need the numerator to appear in every row (red arrow in subtest)

I thought this would do the trick, but ...

=CALCULATE(sum('JOB COST DETAILS'[Job Line Income Amount]),ALLEXCEPT(Employee,Employee[Employee Name]))

View 7 Replies View Related

Write A Query To Group Records Based On Speed (specific Value Of Zero)?

Jun 28, 2012

I need to write a query to group records based on speed (specific value of zero). Consider the following scenario:

Table - Vehicle_Event

Vehicle_Id____Date_Time______________Speed
C1____________2012-06-28_10:10:00____5
C1____________2012-06-28_10:11:00____0
C1____________2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00____4
C1____________2012-06-28_10:14:00____3

[code].....

OUTPUT_Required:

Vehicle_Id____Date_Time___________________________ __________Speed
C1____________2012-06-28_10:10:00___________________________5
C1____________2012-06-28_10:11:00_to_2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00___________________________4
C1____________2012-06-28_10:14:00___________________________3
C1____________2012-06-28_10:15:00_to_2012-06-28_10:18:00____0

[Code] .....

I need the start and end time of consecutive records of the same vehicle with 0 speed ordered by date_time. If there is more than one consecutive record with zero speed it needs to be grouped together.

View 6 Replies View Related

SQL Server 2012 :: Assigning A (Group ID) Based On Consecutive Values?

Jul 31, 2014

I have a data set that looks something like like this:

Row# Data
1 A
2 B
3 B
4 A
5 B
6 B
7 A
8 A
9 A

I need wanting to assign a group ID to the data based on consecutive values. Here's what I need my data to look like:

Row# Data GroupID
1 A 1
2 B 2
3 B 2
4 A 3
5 B 4
6 B 4
7 A 5
8 A 5
9 A 5

You'll notice that there are only two values in DATA but whenever there is a flip between them, the GroupID increments.

View 2 Replies View Related

No Of Columns In Group By

May 18, 2000

hi,
i have a doubt , how many columns we can use in GROUP BY clause(or size)
in BOL i saw the following hint.

Columns in GROUP BY, ORDER BY
sql6.5: 16
sql 7.0: Limited only by number of bytes

Can anyone tell me How many bytes or how many columns?
thank u in advance

kavira

View 1 Replies View Related

Rows Based On Columns

Feb 3, 2008

I know this may have been asked before but can someone pls hel mw out here. i even tried to use the Crosstab SP that i found out on this site but it is not for what i need.

I have a Table/View called [Shipment] with the data below.

ShipNo Supplier
=================
1 CFA
1 TFA
2 LRA
2 LRB
3 ABC
4 TFA

I want the following as my result.

ShipNo Supplier1 Supplier2
==========================
1 CFA TFA
2 LRA LRB
3 AB
4 TFA

Thx.
Rav

View 6 Replies View Related

Display Data In Reports Based On Active Directory Group Membership...

Aug 9, 2007

Hello,

I am fairly new to SQL 2005 and Reporting Services.

We are trying to create a report that will display sales data based on group membership from Active Directory.

For example, if USER1 logs in and looks at a Year to Date Sales report, it will only show data that pertains to his group. If USER2 logs in and accesses the same report, it will display different Year to Date information because he is in a different group.

Background Information: We are running SQL 2005 Enterprise Edition Service Pack 2 with Analysis and Reporting Services. We are delivering the reports through a Sharepoint site.

Please let me know if anyone has a good way to tackle this.

Thanks,

Justin

View 6 Replies View Related

Change Property Based On Actual State Of Collapsed/expanded Group

Jan 12, 2007

Hi, please I would like to use something like this>

=IIF(table1_group2 is Collapsed,True,False)

I want to generated some event based on actual state of e.g. report group. if user expand group make this event otherwise (group is collapsed) make another event.

Thanks for your advice.

View 2 Replies View Related

How To Return A LOT Of Other Columns With Group By

Jan 19, 2008

I have a MyNames table which contains over 100 columns. The Name column may have the same name in many rows. The data looks like this.

Name..........Factor1..........Factor2...
John..........X................Y
John..........X................X
Mary..........T................Y
Mary..........V................Y

I need a result table containing all 100 columns from MyNames but only the unique names in the name column. I don’t care which name row is used.

Name..........Factor1..........Factor2...
John..........X................Y
Mary..........V................Y

I have tried DISTINCT and GROUP BY.
DISTINCT will unique on every column listed so I will end up with the name repetred in the Name column.

SELECT DISIINCT Name, Factor1, Factor2 => I do not want the Factor columns involved in the distinct. This will cause the same name to appear in more than one row.

Group by will requires that I place the remaininf 99 columns in a function in order to have the columns listed.

SELECT Name, MAX(Address)... => adding 99 fields.

Is there a way get only the unique Names with the 99 other columns with out having to manually put the other 99 columns in functions?

View 13 Replies View Related

Concatination For A Columns For Group By

May 15, 2008



hi all,

The situation is something like this
id name
------------------
1 lad
1 big
2 sen
2 zen

The output would be
id name
------------------
1 lad,big
2 sen,zen

Is there a way to implement this is Reporting services.This has to be achieved on the fly which the report is being executed !
Any help on this is highly appreciated .

View 1 Replies View Related

How To Do Update Of Select Columns Based On...

Jun 21, 2007

the following criteria.
i have the selection all done but am trying to figure out how to do the following:
if column4 < 0 then add column4 to column3, move 0 to column4;
if column3 < 0 then add column3 to column2, move 0 to column3;
if column2 < 0 then add column2 to column1, move 0 to column2;
add column3 to column4;
move column2 to column3;
move column1 to column2;
if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;

these are all numeric data types.

View 7 Replies View Related

Discovering AGE Based On Date Columns

May 28, 2008

Hello.

I have three INT columns in a table that record the users birth year, month, and day.

BDAY_DAY (INT)
BDAY_YEAR (INT)
BDAY_MONTH (INT)

I'd like to include a function in my query that will return their Age in years based on these three columns.



I found this function on the internets, but I'm not sure how to build a DATETIME object using the three int date columns to pass to the function. If you could help me there it'd be most appriciated.


Create FUNCTION dbo.GetAge (@DOB datetime, @Today Datetime) RETURNS Int
AS
Begin
Declare @Age As Int
Set @Age = Year(@Today) - Year(@DOB)
If Month(@Today) < Month(@DOB)
Set @Age = @Age -1
If Month(@Today) = Month(@DOB) and Day(@Today) < Day(@DOB)
Set @Age = @Age - 1
Return @AGE
End

Usage (how do i pass the three columns into this function??)

SELECT Last_Name, First_Name, ssn, dob
FROM Employee_Data e (nolock)
WHERE Cust_Id = 'Customer1'
and dbo.GetAge(e.Date_Of_Birth, getdate()) >= 21

View 3 Replies View Related

Output Several Columns Based On Rows

Jun 9, 2008

I'll show my schema first, then I'll explain what I'm doing:

--------------------------------------------------
Events
--------------------------------------------------
ID | E_Title
--------------------------------------------------


--------------------------------------------------
EventOptionGroups
--------------------------------------------------
ID | EOG_EventID | EOG_OptionGroupID
--------------------------------------------------


--------------------------------------------------
OptionGroups
--------------------------------------------------
ID | OG_Title
--------------------------------------------------


--------------------------------------------------
Options
--------------------------------------------------
ID | O_OptionGroupID | O_Description
--------------------------------------------------


--------------------------------------------------
EventRegistration
--------------------------------------------------
ID | ER_EventID | ER_Name
--------------------------------------------------


--------------------------------------------------
RegistrantOptions
--------------------------------------------------
ID | RO_EventRegistrationID | RO_OptionGroupID | RO_Selection
--------------------------------------------------



There are several events. Each event has several different sessions (stored in EventOptionGroups), and each session has a certain number of options (stored in Options).

A user can sign up for an event, and their information is stored in EventRegistration. They can choose an option for each session in the event. For each option they choose, a new row is added to RegistrantOptions.

For each row in EventRegistration, I want to output the user's information, and then the option they chose for each session in the event. Like this:

----------------------------------------------------------------------
E_Title | ER_Name | OG_Title1 | OG_Title2 | OG_Title3
----------------------------------------------------------------------
Event | Bob | O_Description1 | O_Description2 | O_Description3

So in that example, that event had 3 sessions.

Right now, I can only output E_Title and ER_Name, I don't know how to output the session information

View 2 Replies View Related

Sum Columns Based On A Single Column Value

Sep 10, 2014

I’m trying to figure out a way to sum columns for similar IDs, based on the contents of a single field. For example, if I’m calculating attendance percentages for students, and codes P and T count as Present, and codes A and E count as Absent, I would want to total Present and Absent codes separately, in their own columns. I would then like to use those totals to calculate percentage, but I can do that. It’s the SUM based on column value (by ID) that is giving issue.

If I have the following view:

IDLNFNCDTotal
123456MearsBenP12
123456MearsBenA2
123456MearsBenT6
234567NortonSusanP15
234567NortonSusanA2
234567NortonSusanT2
234567NortonSusanE1

I would like something like this:

IDLNFNPresentAbsentPercentage
123456MearsBen18290.0
234567NortonSusan17385.0

I’ve been playing around with nested queries, but nothing’s working. This is a glimpse of the mess that I’ve created trying to sort this out. Many errors.

I just noticed that I used a simpler example than the SQL I included, so I modified it a bit. There are additional fields that I'll need to include, but I want to get the logic working correctly. From there, I can handle the rest. So here's a more appropriate code example showing the direction I'm trying to go with this.

SELECT ID, [Last Name], [First Name], CD, Present, Absent, CAST(LEFT(Present / (Absent + Present) * 100, 5) AS varchar) + '%' AS Percentage
FROM (SELECT ID, CD, TotalAHD, CAST
((SELECT SUM(TotalAHD) AS Expr1
FROM SumAHDforAttndPercentages AS p

[Code] ...

View 2 Replies View Related

SQL Count Records Based On 2 Columns

Oct 15, 2007

Newbie alrt...

I am trying to create an asp page that will update an Access 2000 database. I need to update records if the user exists and create a new record if the user does not exist. Most of the variables are pulled from a separate "post" form.

I am using 2 pieces of info to find duplicates, as employee numbers can be assigned to multiple employees. Right now I have the page check for a duplicate employee id number then check for a duplicate last name. Unfortunately it is running each check separately, so if the last name is duplicated anywhere, it is sending a duplicated value.


here is the chunk of code in question... all RF_variables are request.form variables


cnt="SELECT COUNT(emp_id) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE emp_id='" & RF_emp_id & "'"
set again=conn.Execute(cnt)
dup=again("Xnum")


if dup>=1 then
cnt="SELECT COUNT(lname) AS Xnum FROM " & RF_course
cnt=cnt & " WHERE lname='" & RF_lname & "'"
set again=conn.Execute(cnt)
dupl=again("Xnum")

if dupl=1 then
upd="UPDATE " & RF_course & " SET "
upd=upd & "section" & RF_section & "='" & RF_score & "'"
upd=upd & " WHERE emp_id='" & RF_emp_id & "'"
upd=upd & " AND lname='" & RF_lname & "'"
on error resume next
conn.Execute upd
else
ins="INSERT INTO " & RF_course
ins=ins & " (lname,fname,emp_id,cname,"
ins=ins & "section" & RF_section & ")"
ins=ins & " VALUES "
ins=ins & "('" & RF_lname & "',"
ins=ins & "'" & RF_fname & "',"
ins=ins & "'" & RF_emp_id & "',"
ins=ins & "'" & RF_cname & "',"
ins=ins & "'" & RF_score & "')"
on error resume next
conn.Execute ins
end if
else
ins="INSERT INTO " & RF_course
ins=ins & " (lname,fname,emp_id,cname,"
ins=ins & "section" & RF_section & ")"
ins=ins & " VALUES "
ins=ins & "('" & RF_lname & "',"
ins=ins & "'" & RF_fname & "',"
ins=ins & "'" & RF_emp_id & "',"
ins=ins & "'" & RF_cname & "',"
ins=ins & "'" & RF_score & "')"
on error resume next
conn.Execute ins
end if




Hopefully this is understandable.
If anyone can offer any help I would greatly appreciate it.

Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved