SQL Count Records Based On 2 Columns

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



ADVERTISEMENT

Query To Count # Records In Db Based On Foreign Key

My SQL is very basic.  How do I create a query that will accept a parameter, an integer, and based on the integer, locate all the matches in a db?
 SELECT     COUNT(*) AS Expr1, tblArticle.ArticleIDFROM         tblArticle INNER JOIN                      tblArticleCategory ON tblArticle.ArticleCatID = tblArticleCategory.ACategoryIDGROUP BY tblArticle.ArticleID
This isn't setting up the query to request a parameter.What am I doing wrong here? I"m trying to get the total number of articles for a particular category ID.

View Replies View Related

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

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 Replies View Related

Questions About Memory Based Bulk Copy Operation(InsertRow Count,array Insert Directly,set Memory Based Bulk Copy Option)

Hi~, I have 3 questions about memory based bulk copy.

1. What is the limitation count of IRowsetFastLoad::InsertRow() method before IRowsetFastLoad::Commit(true)?
For example, how much insert row at below sample?(the max value of nCount)
for(i=0 ; i<nCount ; i++)
{
    pIFastLoad->InsertRow(hAccessor, (void*)(&BulkData));
}

2. In above code sample, isn't there method of inserting prepared array at once directly(BulkData array, not for loop)

3. In OLE DB memory based bulk copy, what is the equivalent of below's T-SQL bulk copy option ?
BULK INSERT database_name.schema_name.table_name FROM 'data_file' WITH (ROWS_PER_BATCH = rows_per_batch, TABLOCK);

-------------------------------------------------------
My solution is like this. Is it correct?

// CoCreateInstance(...);
// Data source
// Create session

 m_TableID.uName.pwszName = m_wszTableName;
 m_TableID.eKind = DBKIND_NAME;

 DBPROP rgProps[1];
 DBPROPSET PropSet[1];

 rgProps[0].dwOptions  = DBPROPOPTIONS_REQUIRED;
 rgProps[0].colid   = DB_NULLID;
 rgProps[0].vValue.vt  = VT_BSTR;
 rgProps[0].dwPropertyID  = SSPROP_FASTLOADOPTIONS;
 rgProps[0].vValue.bstrVal = L"ROWS_PER_BATCH = 10000,TABLOCK";

 PropSet[0].rgProperties  = rgProps;
 PropSet[0].cProperties  = 1;
 PropSet[0].guidPropertySet = DBPROPSET_SQLSERVERROWSET;

 if(m_pIOpenRowset)
 {
  if(FAILED(m_pIOpenRowset->OpenRowset(NULL,&m_TableID,NULL,IID_IRowsetFastLoad,1,PropSet,(LPUNKNOWN*)&m_pIRowsetFastLoad)))
  {
   return FALSE;
  }
 }
 else
 {
  return FALSE;
 }

View Replies View Related

Acceptable Data Based On Count?

I have x amount of tests for a person on a particular day. There are criteria to invalidate a test. I can filter all the bad tests away, but now I need to only accept 2 valid tests per day, even if there are more tests. The tests are filtered according to person ID and then put in order by date. I need to accept the first 2 valid test based on the criteria and reject the others. Any suggestions

View Replies View Related

MDX - Select Count Based On Two Different Dimensions

I have a fact table with a create time dimension and an expiration time dimension.  I'd like to have a calculated member that would compare the (count for create time) / (count with that expiration time).  I already have these counts as measures.
 
I would be able to put the create time dimension in the "row fields" area, and see the ratio (calculated above)
over the different create time periods.
 
Can someone point me in the right direction on how I would create that kind of calculated member?  What would the MDX look like?
 
Thanks for your help

View Replies View Related

Create Record Based On Count

I have the following data,
 
CustomerID             EngID                  EngCount
1                            A11                     2
2                            B12                     1
3                            C10                     3
 
I need to display it as,
 
CustomerID             EngID                  EngCount
1                            A11                     1
1                            A11                     1
2                            B12                     1
3                            C10                     1
3                            C10                     1
3                            C10                     1
 
Create a record based on the EngCount. So CustomerID of 3 has 3 records as shown above.
 
How to do?

View Replies View Related

Updating Field Based On Record Count

I am trying to write a stored procedure that updates a value in a table based on the sort order. For example, my table has a field "OfferAmount". When this field is updated, I need to resort the records and update the "CurrRank" field with values 1 through whatever. As per my question marks below, I am not sure how to do this.


Update CurrRank = ??? from tblAppKitOffers
where appkitid = 3 AND (OfferStatusCode = 'O' OR OfferStatusCODE = 'D')
ORDER BY tblAppKitOffers.OfferAmount Desc


All help is greatly appreciated.

View Replies View Related

How To Count Cases For Different Groups Based On Different Criteria

 

Hello,

I need to create a query that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd,  bnb_newcases_salesd etc)

Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)

MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is null

BNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC')








12-Jan

19-Jan

26-Jan

2-Feb

9-Feb

16-Feb



MTS





















New Cases Received

85

84

79

98

79

95



Sales

30

32

27

40

42

38



SalesD

47

34

37

23

23

37



CreditR

44

29

26

35

55

54



CreditB

6

12

9

5

7

13



CreditS

-

-

-

-

3

-



CreditP

10

11

11

24

17

7



MTS Subtotal

140

125

110

144

151

150

























BNB





















New Cases Received

12

13

14

14

6

11



Sales

-

-

-

-

-

-



SalesD

-

-

-

-

-

-



CreditR

12

11

12

10

5

9



CreditB

8

13

9

17

16

6



CreditS

-

-

2

-

-

-



CreditP

1

1

1

1

4

3



BNB Subtotal

21

25

24

28

26

19

























Total





















New Cases Received

97

97

93

112

85

106



Sales

30

32

27

40

42

38



SalesD

47

34

37

23

23

37



CreditR

56

40

38

45

60

63



CreditB

14

25

18

22

23

19



CreditS

-

-

2

-

3

-



CreditP

11

12

12

25

21

10



Grand Total

161

150

134

172

177

169

 

This is just a very brief bit of code

SELECT MTS_new_cases_sales, mts_new_cases_salesd €¦€¦.

FROM vwCreditN
WHERE mts_sales_new_cases = ( )...
and (status = 'Calculated' OR status = 'REJECTED')...

 

Can you please show me how to accomplish this?

Thank you in advance for your effort,

 

Rhonda

View Replies View Related

Count Up Based On Unique Value -- Brain Freeze

 

Ok... I know this is something that I've seen and probably done before....
 
I have a value that will show up multiple times in a table.  I want to order the table by that value and then count up from 1 to however many items that it shows up as.
 
The example below shows the results I want.  Value1 and Value2 are in the table, LineNumber is what I need to generate.
 







Value1
Value2
LineNumber

1
Hey
1

1
Can
2

1
Someone
3

1
Help 
4

1
With 
5

1
This
6

2
Very
1

2
Simple 
2

2
Issue
3

2
As
4

2

5

3
Have 
1

3

2

3
Brain
3

3
Freeze
4

 
 
--Thanks--

View Replies View Related

Trigger To Update Table Based On COUNT Of Column

Hello again,
 
I'm hoping someone can help with with a task I've been given. I need to write a trigger which will act effectively as a method of automatically distributing of incoming call ticket records. See DDL below for creation of the Assignment table, which holds information on the call ticket workload.
 
 



Code Snippet
CREATE TABLE #Assignment
 (CallID INT IDENTITY(1500,1) PRIMARY KEY,
 AssignmentGroup VARCHAR(25),
 Assignee VARCHAR(25)
 )
GO
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Jim Smith')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('Service Desk', 'Joe Bloggs')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Donald Duck')
INSERT #Assignment (AssignmentGroup, Assignee)
VALUES ('PC Support', 'Mickey Mouse')
 
GO
 
SELECT COUNT(CallID) AS [Total Calls], AssignmentGroup, Assignee
FROM #Assignment
GROUP BY AssignmentGroup, Assignee
ORDER BY COUNT(CallID) DESC , AssignmentGroup, Assignee
 
 




What I need to do is write a trigger for on INSERT to automatically update the Assignee column with the name of the person who currently has the least active calls. For example, using the data above, the next PC Support call will go to Mickey Mouse, and the next two Service Desk calls will go to Jim Smith.

 
So, the logic for the trigger would be
 
UPDATE #Assignment
SET Assignee = (SELECT Assignee FROM #Assignment WHERE COUNT(CallID) = MIN(COUNT(CallID))
 
 
But that's only the logic, and obviously it doesn't work with the syntax being nothing like correct.
 
Does any one have an idea or pointers as to how I should go about this?
 
Grateful for any advice, thanks
matt

View Replies View Related

Count Of Columns &<&> 0

How would you count the number of columns with a value not equal to 0 foreach row in a table?Thanks!Joe

View Replies View Related

SQL Count Columns

Hello,

I'm attempting to count the number of COLUMNS that have a specified value in a given record.

For example, suppose I have the following table:
NameDay1Day2Day3
JohnYNY
JaneYYY
JoeNNY

I'd like a querty to loop through each record and provide a total for each person for Days Present, such as the following:

Name, DaysPresent
John, 1
Jane, 3
Joe, 1

Is this possible to do in MS SQL? I'm familiar with the COUNT function but have never used it to count the columns.

Regards,
Cody

View Replies View Related

Count With 2 Columns?

im trying to display a category name in one column and then the number of books in that category in another column.

SELECT CATEGORY, COUNT(DISTINCT CATEGORY) "Category Total" FROM BOOKS;
Gives me an error saying something about category not being a single group function. What am I doing wrong?

View Replies View Related

Rows Based On Columns

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 Replies View Related

Help With Count Records

Hello All,
 I'm wondering if you guys can help me with a problem to count every record in a table; however, I must match this table with another table to get the category names.  I have tried this SQL statement in the SQL Express and it works very great.  
SELECT aspnet_Category.CategoryName, COUNT(*) AS Expr1FROM aspnet_Category INNER JOINaspnet_resources ON aspnet_Category.ApplicationID = aspnet_resources.ApplicationId AND aspnet_Category.CategoryID = aspnet_resources.CategoryIDGROUP BY aspnet_Category.CategoryNameORDER BY aspnet_Category.CategoryName
However, when I tried to put this into my code, it gives me a error. <System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, True)> _Function GetDataByCount_CategoryID() As CategoryDataTableGetDataByCount_CategoryID = Adapter.GetDataByCount_CategoryIDEnd FunctionFailed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Can you please help me to fix this error? 
Thank you, Vic. 

View Replies View Related

Get Records Count

i have this function
it return 0 but the sql statement in the sql query return the right number?how is that
i want to get the number of records any other idea or fix?
Public Function UserAlbumPhotoQuota(ByVal userID As Integer) As BooleanDim Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)
Dim strSQL As StringDim dr As SqlDataReader
strSQL = "SELECT *, (select count(*) from userAlbumPic where userID=" & userID & ") as rec_count from userAlbumPic "Dim cmd As New SqlCommand()cmd = New SqlCommand(strSQL, Conn)
Conn.Open()
dr = cmd.ExecuteReader()
dr.Read()userQuota = dr("rec_count").ToString
Conn.Close()
End Function

View Replies View Related

Count The Records

I need to export records to a text file and simply index them. i.e. in the pipe delimited file I need a column with the ordinal value of each row.
Example:

1|"Jane Doe"|"23 Western ave"|...
2|"Jamie Delom"|"5 East Street|...
3|"Nat Girshon"|"5678 Main Street|...
.
.

Would anyone be able to tell me how I could build this within the SQL query without creating a physical table and using IDENTITY functions?

View Replies View Related

Count All &>0 Records

count(foundedamount>0)
does not seem work, how can I count all record and fundedamount>0?

THX

View Replies View Related

Count Records

Hi
i am just an beginner with sql and i am quite sure this will be a simple question
i am looking for a methode to count the number of records how can i do this?

greetz

View Replies View Related

Count The Columns In A Spreadsheet

 

Does anyone know how to get a count of columns in a spreadsheet before importing ?
 
Thanks in advance

View Replies View Related

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

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 Replies View Related

Output Several Columns Based On Rows

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 Replies View Related

Discovering AGE Based On Date Columns

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 Replies View Related

Why Can't I Use Columns Based On Case Down In My Where Clause

 

I am writing a fairly simple sql, and I would like to write something like
 



Code Snippet
select

firstname as firstname,
case

when firstname = 'Peter' then 'yes'
else

'no'
end as whatever
 
from

MyTable
where

whatever = 'yes'
 

 
And this should then select out the rows where column number 2 is 'yes'.
 
It doesn't work, and I have to copy the firstname = 'Peter' into the where clause.
 
But why?


 

View Replies View Related

Count Number Of Records

Using linq what would be the most efficient method of counting the number of users in the users table? 

View Replies View Related

How To Count Number Of Records

hi
can anyone tell me how to count number of records(rows) in a table without using "COUNT" function.for practise iam trying to implement it through queries.

View Replies View Related

Get Records With A Count Total

Hello, I am having problems with this query below:
 1 SELECT Table1.Email AS Email,
2 Table2.UserName AS Username,
3 Table3.Members_Paid AS Paid,
4 (SELECT DISTINCT COUNT(*)
5 FROM Table3 AS e JOIN Table3 AS m
6 ON e.Members_Sponsor = m.Members_ID
7 WHERE (e.Members_Sponsor = m.Members_ID)) AS TotalRecords
8 FROM Table1 INNER JOIN
9 Table2 ON Table1.UserId = Table2.UserId INNER JOIN
10 Table3 ON Table2.UserId = Table3.UserID
11 WHERE (Table3.Members_Sponsor = @UserId)Basicly what I am trying to do is get all members that belong to a certain manager along with those members count total of members they have below them.The code above is giving me the count of the first member only, not different counts for each member.Hope you understand what I am trying to say and do here. Hope someone can help me out cause this hase been driving me crazy for a few days now.
 

View Replies View Related







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