Get Only ONE Row Per Item With Different Fields
Nov 25, 2007
Hi,
I'm a bit stuck here.
I have a table called STM_COMM and it contains following fields: id, member_id, email_addr, tel_nr, fax_nr
A member can have multiple email addresses, telephone numbers and faxnumbers. so we could have something like this:
id member_id email_addr tel_nr fax_nr
-------------------------------------------------------
1 5 info@test.com
2 5 555687 568774
3 66 test@go.nl 65897854
4 5 another@one.com
What I would like to have is only ONE (1) row PER member with only the FIRST (not max!) entered known data. In the above case, I'd like to have this as result:
member_id email_addr tel_nr fax_nr
----------------------------------------------------
5 info@test.com 555687 568774
66 test@go.nl 65897854
I've tried lots of things (max, inner select ...) but I can't seem to figure it out.
Thank you for the help !
Christophe
PS: I'm working on a sql server 2005
View 1 Replies
ADVERTISEMENT
May 14, 2007
Hi
I have a old report that was pointing to a different database, and i changed the connection string for that report and changed the field names (As per my new stored Procedure). In some places I keep getting this run time error, i have tried looking for all the hidden references to this field and i cannot find any parameter that the old database had. The error messages that i get are as follows:
[rsFieldReference] The Hidden expression for the table €˜tblContactInfo€™ refers to the field €˜PL_Admin_ShowInKit€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Hidden expression for the table €˜tblContactInfo€™ refers to the field €˜PL_FA_ShowInKit€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Hidden expression for the table €˜tblWithdrawals€™ refers to the field €˜PW_Comment€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Can Someone please help me out as to how should i get rid of these error messages, they are kinda driving me nuts.
Regards,
Karen
View 3 Replies
View Related
Dec 6, 2013
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
View 5 Replies
View Related
Mar 6, 2007
Hi,
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
THANKS
View 3 Replies
View Related
Jul 12, 2007
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
View 1 Replies
View Related
Jan 18, 2007
hi everyone
what the matrix's class name ?
where is the matrix's dll?
Can i create a new class extends the matrix?
I want to override the matrix's onpaint method.
Can i do this ?
why the table not have the column group?
View 9 Replies
View Related
Oct 16, 2014
I am having trouble finishing the last bit of a report. The report shows orders that customers have placed that contain 0 promo items, All promo items (all items in order are promo items), and a mix of promo and non promo (at least 1 promo item and 1 non-promo item). Ive simplified this a bit for ease of understanding but lets assume we have 2 tables: A Promo table that contains the items on promotion and the dates that promotion is valid, and a Sales table, that contains the order number, order date, and sku ordered.
I've already written code that finds orders that have at least 1 promo item in them, and using that, I can determine what orders have 0 promo items in them. Where I am stuck is taking the orders that have at least 1 promo item in them, and separating them into orders that have only promo items, and those that have both promo and not promo items in them. Also, there are several promos throughout the year (called "Offers") so in my code below, you can see 2 different Offers ("JF" and "MA") with their corresponding dates they are valid. They will never overlap. My results also have to be split out by Offer so management can look at the results of each offer separately. Here is some code:
Code:
create table #Promos (
Offer varchar(2) null,
SKU int null,
StartDt date null,
EndDt date null
[Code] ....
So my results should show OrderNo A1111 in the Promo and No Promo group because of SKU 5 not being promotional during the time that order was placed. OrderNo A2222 should be in the Promo Only group because both SKUs on the order were promotional at the time the order was placed.
View 6 Replies
View Related
Jan 28, 2008
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
View 3 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Jan 26, 2008
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
View 1 Replies
View Related
Jul 23, 2005
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies
View Related
Jul 2, 2015
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
View 3 Replies
View Related
Jul 20, 2005
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies
View Related
Jun 30, 2000
Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
View 2 Replies
View Related
Jul 31, 2013
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
View 5 Replies
View Related
Jan 12, 2005
I want read
dr[2] = SqlDataReader dr1.item("lastmane");
from database
but I have error expected
I don't while
SqlDataReader dr1 = cmd.ExecuteReader();
dr1.Read();
dr[2] = SqlDataReader dr1.item("lastmane");
View 2 Replies
View Related
Apr 23, 2008
select i.a,
w.c,
w.e
from table1 i,table2 w where
i.a=w.a (Here c is the primary key for each a)-iam getting o/p like this
a c e
aaa x 2
aaa y 10
bbb y 5
ccc x 3
ccc y 4
i need o/p in this way (ie sum of e for each a)
a e
aaa 12
bbb 5
ccc 7
Is it possible
Thanx
View 1 Replies
View Related
Jan 11, 2014
I´m trying to get a query where I´ll get an answer like this:
+--------------+----------------+----------------+------------------+-----------------+
| champion_id | champion_name | champion_item1 | champion_item... | champion_item16 |
+--------------+----------------+----------------+------------------+-----------------+
| 1 | Legolas | boots | ... | sword |
+--------------+----------------+----------------+------------------+-----------------+
This is the scenario how I set up my tables (let me know if I should change it):
table champions
+--------------+----------------+
|champion_id | champion_name |
+--------------+----------------+
| 1 | Legolas |
+--------------+----------------+
table equipment
+--------------+----------------+------------------+-----------------+
| champion_id | champion_item1 | champion_item... | champion_item16 |
+--------------+----------------+------------------+-----------------+
| 1 | 5 | ... | 22 |
+--------------+----------------+------------------+-----------------+
table items
+--------------+----------------+
| item_id | item_name |
+--------------+----------------+
| 5 | boots |
+--------------+----------------+
| 22 | sword |
+--------------+----------------+
But I still don´t know how to call the equipment table and get champion name and item names for the numbers. What did I do wrong and how can I fix it?
The foreign keys are in place.
View 5 Replies
View Related
Jan 21, 2008
Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
regards.
Julio Diaz.
View 1 Replies
View Related
Dec 14, 2004
Not 100% sure how to do this so I would appreciate some directions. I have 2 tables, Systems and Contacts. A system can have 1 to infinite contacts and contact can have 1 to infinite systems. So I use a 3rd table Sys_Con to add the contact needed for each system.
Now my question is once I add the System and contact how do I know for sure which ContactID to add in my Sys_Con table? Because while I am doing this operation maybe someone can add an other contact. So is there a way for my store proc Add_Contact to return the contactID needed for my store proc Add_Sys_Con ?
TABLE Sys_Con
SystemID int
ContactID int
Table Contact
ContactID int Identity
ContactName
...
Table System
SystemID
...
View 3 Replies
View Related
Mar 7, 2007
How can I get the value of the last Item in a coums?SELECT COLUMN FROM DATABASE ORDER BY COLUMN DESC?something with Fields(0).value?Thank you in advance
View 4 Replies
View Related
Feb 27, 2007
Hi gurus
I have sql server 2000 that was working fine until today(figures), today I opened EM and under sql server group where used to be all my information now there is NO ITEM, nothing shows(database,Nothing) The server is working as I had no complaints from my user on data not been saved or getting data from but its strange that I can not see all my info like I used to.- If I go into sql query analizer, it ask me to connect to local which I do and I can see all my databases from there but once I close that window ,on my EM nothing shows.
I checked all services andevery one of them seems to be up and runnig
Thanks forall the help I can get
View 4 Replies
View Related
Aug 8, 2007
Hi Every Body,
I have one table it's called -Access.It contains two columns name of that is Door,Emp_Id.
example Door Emp_Id
10 1
10 2
10 3
11 4
12 5
11 1
this is the value in that table.......
I want get Which Door number maximum Access from the table.....
Thanks & Regards,
S.Sajan
View 7 Replies
View Related
May 2, 2007
Hi Everyone I have an ADP and I need to create a report that will give me the total number of each item. I need the report to show how many [Violation Type], and the total [Loss] per violation. Can anyone help please
Code Snippet
CREATE TABLE [dbo].[Revised_MainTable] (
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Inspector] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation Type] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss] [money] NULL ,
[Loss Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Security/GC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
View 9 Replies
View Related
Feb 28, 2008
Hi all,
I have some problem about parameter to query data. My parameter is available from dataset but it doen not have blank or null value. So when I want to select this...
SELECT *
FROM mytable
WHERE (@param_person_id IS NULL OR @param_person_id = person_id)
cannot success. This statement means "if @param_person_id has null value, this statement is select every person_id. On the other way, if @param_person_id is not null value, this statement select only specific person_id".
So I want to add blank or null value to my dataset for working with select statement.
How can I do?
Thank you very much
View 1 Replies
View Related
Feb 19, 2007
Is there anyway to sum a value that resides in a textbox?
ie; =Sum(ReportItems!MyTextBox.value) ?
View 7 Replies
View Related
May 19, 2007
I added something by mistake! If I delete it it gives me an `are you sure?` message that makes me think it'll actually delete the file from the hard drive. As I've accidentally added something from SQL Server (I was trying to add a database and got carried away) I don't think that's such a good idea. I've googled and found this:http://msdn2.microsoft.com/en-us/library/1ee8zw5t(VS.80).aspx which helpfully states:----- To remove a solution item In Solution Explorer, select the item you want to remove. On the Edit menu, choose Remove. ----- which would be great, if only the Edit menu had a remove option. Any ideas?
View 2 Replies
View Related
Apr 1, 2008
Hi,I want to check that Is there row exists in table or not.Please correct me. Dim conn As SqlConnection Dim comm As SqlCommand Dim reader As SqlDataReader Dim connstring As String connstring = ConfigurationManager.ConnectionStrings("iharyana").ConnectionString conn = New SqlConnection(connstring) comm = New SqlCommand("select * from test where username=@username", conn) comm.Parameters.Add("@username", Data.SqlDbType.VarChar, 20) comm.Parameters("@username").Value = uname conn.Open() reader = comm.ExecuteReader While reader.Read If reader.Item("username").ToString = "" Then Response.Redirect("http://www.iharyana.com") End If End While reader.Close() conn.Close()
View 1 Replies
View Related
Apr 10, 2008
I using SQL SERVER
just wondering which command I can use to query items status ?
in my TABLEA
Product(D | Name | Type | Status
1 Apple Fruit 00021234
2 Banana Fruit 00031244
3 Tube Clothe 00012131
4 Tee Clothe 00041111
4 pants Clothe 00041112
4 tank Clothe 00051111
4 Jeans Clothe 00061111
4 Dress Clothe 00051111
in TABLEB
ID StatusID Status
1 0001 A
2 0002 B
3 0003 C
4 0004 D
5 0005 E
6 0006 F
use this record as a example :
ProductID | Name | Type | Status
1 Apple Fruit 00021234
use TableA.Status reference to TableB.StatusID ( compare 0001 , need not to care about the last 4 numbers ) so the status is A
AB is Group 1
C is Group 2
DEF is Group 3
in my TABLEA
Product(D | Name | Type | Status
1 Apple Fruit 00021234
2 Banana Fruit 00031244
3 Tube Clothe 00012131
4 Tee Clothe 00041111
4 pants Clothe 00041112
4 tank Clothe 00051111
4 Jeans Clothe 00061111
4 Dress Clothe 00051111
I want the result looks as follow
TYPE Group 1 Group 2 Group 3
Fruit 1 1 0 //because Apple Fruit 00021234 → 0002 = B → Group 1 And Banana Fruit 00031244 →0003 =C→Group 2
Clothe 1 0 5
I need to run a query to get the result like that , and then put the result to my DataGrid then show to user...
can I just use SQL Query to get the result? I just try to use like sum�Substring ... trying to get the result but I still can't wirte a query to work for this question..
which command I should use for this question? or it must be done in code-behind ?
can you please give me help?
thank you very much
View 4 Replies
View Related
Jan 26, 2004
Thats it
How do you receive the last item(row) in a table.
Thanks
View 11 Replies
View Related
Jun 29, 2005
Hi,
If you browse to Amazon and drill down through categories until a paged
listing appears, you will see in each item of the listing that the
description rendered as Book Description, is a cutdown version of the
full description, with a ... and "Read More" hyperlink. How is
this achieved programatically? I dont know if thre is an efficient way
in SQL Server to do this, or whether it would be easier to have a
ShortHand field and a Description field, both of which are teh full
description when shown in the Details page, but the ShortHand is the
field rendered in listings. This seems like a simplistic solution
and I am thinking there may be programmatic means of doing the same -
has anybody got any experience in this area?
Thanks!
jr.
View 4 Replies
View Related
Feb 8, 2004
Hello All.
I need your advise on this .....
I have a table with Bill_Doc, Bill_Item and Bill_Doc_Count fields. I need to update Bill_Doc_Count with 1 only on the first Bill_Item. Is this possible? I tried min and max but they don't work for me.
Please help. Thanks a million.
For example,
Bill_Doc Bill_Item Bill_Doc_Count
123 1 1
123 2 Null
123 3 Null
124 1 1
125 1 1
125 2 Null
Best regards
View 5 Replies
View Related
Aug 26, 2015
My data look like this:
ITEMID DESC QTY PRICE
134 ITEM1 4 $200
134 ITEM1 1 $200
134 ITEM1 1 $200
how do I write SQL stmt to add the quantity if the itemid is the same?
The result should be:
ITEMID DESC QTY PRICE
134 ITEM1 6 $200
View 2 Replies
View Related
