Hi,
my result set for a Data Driven Subscription consists of 24 subscribers with
associated data so I would expect 24 emails. For some reason 32 emails are
sent - i.e. there are some double-ups.
Any ideas on this?
thanks
MattDo you always get 32? RS should not send duplicate emails. In some rare
cases it could happen, but these cases only have to do with system crashes.
You are not on Beta2 are you? Beta2 did have issues with duplicate emails.
If you can post the log file that might also shed some light. It would be
best to get a log file with just a single execution of the subscription.
You can do this by restarting the ReportServer service and then having the
subscription kick off. After the subscription finishes sending mails, copy
off the log file. Just make sure no other subscriptions are firing at the
same time.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:uSoQBN6kEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Hi,
> my result set for a Data Driven Subscription consists of 24 subscribers
with
> associated data so I would expect 24 emails. For some reason 32 emails
are
> sent - i.e. there are some double-ups.
> Any ideas on this?
> thanks
> Matt
>
Showing posts with label expect. Show all posts
Showing posts with label expect. Show all posts
Thursday, March 22, 2012
Wednesday, March 21, 2012
Data displayed in duplicates
amatuer
How do you expect to get a right answer without posting some data to be
tested and an expected result.?
Take a look at DISTINCT , GROUP BY clause in the BOL
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> Code below:
> If Session("ID") = 1 Or Session("ID") = 2 Then
> select case (request.form("Individual"))
> case "Individual" sql = "Select V_Monthreport.Q_ID,
> V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> V_Monthreport.PersID=" & session("Pers_ID") & " And
> V_Monthreport.DeptID=" & request.form("SectID") & " And
> V_Monthreport.Maand=" & request.form("Maand") & " And
> V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> V_Monthreport.Q_ID"
> case "Section"
> case "Summary"
> end select
> End If
> <% while not rstMain.eof %>
> <tr>
> <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Question") %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Answer") %></font></td>
> </tr>
> <% rstMain.movenext
> wend%>
> <% End If %>
> I am experiencing a problem in that the data when displayed is
> duplicated. The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
>here's the table:
CREATE TABLE [dbo].[V_Monthreport] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PersID] [int] NULL ,
[DeptID] [int] NULL ,
[Datum] [datetime] NULL ,
[Maand] [int] NULL ,
[Jaar] [int] NULL ,
[Q_ID] [int] NULL ,
[Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[V_Monthreport_Q] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AfdelingID] [int] NULL ,
[Afdeling] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Seksie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QNo] [int] NULL ,
[Question] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
) ON [PRIMARY]
& data looks like this:
V_Monthreport_Q:
14 1 Agricultural Service Agricultural Information Services 1 Marketing
of available new services of the Department (List Opportunities)
15 1 Agricultural Service Agricultural Information
Services 2 Opportunity/Involvement in identifying and development of
new clients; products; projects
16 1 Agricultural Service Agricultural Information
Services 3 Involvement in sustaining and improvement of the level of
expertise, as well as the knowledge and skills
17 1 Agricultural Service Agricultural Information Services 4 Intra
and inter-departmental co-operation (disposition/view)
18 1 Agricultural Service Agricultural Information
Services 5 Agricultural condition/State of Agriculture: Climate
(temperature, wind, etc)
19 1 Agricultural Service Agricultural Information
Services 6 Agricultural condition/State of Agriculture: Veld (coverage,
nutritional value)
20 1 Agricultural Service Agricultural Information
Services 7 Agricultural condition/State of Agriculture: Moisture status
21 1 Agricultural Service Agricultural Information
Services 8 Agricultural condition/State of Agriculture: Livestock
(conditions)
22 1 Agricultural Service Agricultural Information
Services 9 Agricultural condition/State of Agriculture: Commodity
prices
23 1 Agricultural Service Agricultural Information
Services 10 Agricultural condition/State of Agriculture: Labour
24 1 Agricultural Service Agricultural Information
Services 11 Agricultural condition/State of Agriculture: Security /
Safe keeping
25 1 Agricultural Service Agricultural Information Services 12 Client
Relations
26 1 Agricultural Service Agricultural Information Services 13 Threats
/ Concerns
V_Monthreport:
53 4 1 6/21/2006 6 2006 1 Test1
54 4 1 6/21/2006 6 2006 2 Test2
55 4 1 6/21/2006 6 2006 4 Test4
56 4 1 6/21/2006 6 2006 5 Test5
57 4 1 6/21/2006 6 2006 6 Test6
58 4 1 6/21/2006 6 2006 8 Test8
59 4 1 6/21/2006 6 2006 9 Test9
60 4 1 6/21/2006 6 2006 10 Test10
61 4 1 6/21/2006 6 2006 11 Test11
62 4 1 6/21/2006 6 2006 12 Test12
63 4 1 6/21/2006 6 2006 13 Test13
Sorry but this was the best i could.see if you cn help Uri
Dimant.thanx.
Uri Dimant wrote:[vbcol=seagreen]
> amatuer
> How do you expect to get a right answer without posting some data to be
> tested and an expected result.?
> Take a look at DISTINCT , GROUP BY clause in the BOL
>
>
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...|||No so much
INSERT INTO Table.. VALUES (...
Can you provide a full DDL?
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150883115.391130.319520@.p79g2000cwp.googlegroups.com...
> here's the table:
> CREATE TABLE [dbo].[V_Monthreport] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [PersID] [int] NULL ,
> [DeptID] [int] NULL ,
> [Datum] [datetime] NULL ,
> [Maand] [int] NULL ,
> [Jaar] [int] NULL ,
> [Q_ID] [int] NULL ,
> [Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[V_Monthreport_Q] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [AfdelingID] [int] NULL ,
> [Afdeling] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [Seksie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [QNo] [int] NULL ,
> [Question] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
> ) ON [PRIMARY]
> & data looks like this:
> V_Monthreport_Q:
> 14 1 Agricultural Service Agricultural Information Services 1 Marketing
> of available new services of the Department (List Opportunities)
> 15 1 Agricultural Service Agricultural Information
> Services 2 Opportunity/Involvement in identifying and development of
> new clients; products; projects
> 16 1 Agricultural Service Agricultural Information
> Services 3 Involvement in sustaining and improvement of the level of
> expertise, as well as the knowledge and skills
> 17 1 Agricultural Service Agricultural Information Services 4 Intra
> and inter-departmental co-operation (disposition/view)
> 18 1 Agricultural Service Agricultural Information
> Services 5 Agricultural condition/State of Agriculture: Climate
> (temperature, wind, etc)
> 19 1 Agricultural Service Agricultural Information
> Services 6 Agricultural condition/State of Agriculture: Veld (coverage,
> nutritional value)
> 20 1 Agricultural Service Agricultural Information
> Services 7 Agricultural condition/State of Agriculture: Moisture status
> 21 1 Agricultural Service Agricultural Information
> Services 8 Agricultural condition/State of Agriculture: Livestock
> (conditions)
> 22 1 Agricultural Service Agricultural Information
> Services 9 Agricultural condition/State of Agriculture: Commodity
> prices
> 23 1 Agricultural Service Agricultural Information
> Services 10 Agricultural condition/State of Agriculture: Labour
> 24 1 Agricultural Service Agricultural Information
> Services 11 Agricultural condition/State of Agriculture: Security /
> Safe keeping
> 25 1 Agricultural Service Agricultural Information Services 12 Client
> Relations
> 26 1 Agricultural Service Agricultural Information Services 13 Threats
> / Concerns
> V_Monthreport:
> 53 4 1 6/21/2006 6 2006 1 Test1
> 54 4 1 6/21/2006 6 2006 2 Test2
> 55 4 1 6/21/2006 6 2006 4 Test4
> 56 4 1 6/21/2006 6 2006 5 Test5
> 57 4 1 6/21/2006 6 2006 6 Test6
> 58 4 1 6/21/2006 6 2006 8 Test8
> 59 4 1 6/21/2006 6 2006 9 Test9
> 60 4 1 6/21/2006 6 2006 10 Test10
> 61 4 1 6/21/2006 6 2006 11 Test11
> 62 4 1 6/21/2006 6 2006 12 Test12
> 63 4 1 6/21/2006 6 2006 13 Test13
> Sorry but this was the best i could.see if you cn help Uri
> Dimant.thanx.
>
> Uri Dimant wrote:
>|||> The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
Do you get duplicates when you run the same query from Query Analyzer? The
same rows will be returned multiple times when more than one row exists in
both tables with the same value in the joined columns.
Hope this helps.
Dan Guzman
SQL Server MVP
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> Code below:
> If Session("ID") = 1 Or Session("ID") = 2 Then
> select case (request.form("Individual"))
> case "Individual" sql = "Select V_Monthreport.Q_ID,
> V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> V_Monthreport.PersID=" & session("Pers_ID") & " And
> V_Monthreport.DeptID=" & request.form("SectID") & " And
> V_Monthreport.Maand=" & request.form("Maand") & " And
> V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> V_Monthreport.Q_ID"
> case "Section"
> case "Summary"
> end select
> End If
> <% while not rstMain.eof %>
> <tr>
> <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Question") %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Answer") %></font></td>
> </tr>
> <% rstMain.movenext
> wend%>
> <% End If %>
> I am experiencing a problem in that the data when displayed is
> duplicated. The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
>|||Code below:
If Session("ID") = 1 Or Session("ID") = 2 Then
select case (request.form("Individual"))
case "Individual" sql = "Select V_Monthreport.Q_ID,
V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
V_Monthreport.PersID=" & session("Pers_ID") & " And
V_Monthreport.DeptID=" & request.form("SectID") & " And
V_Monthreport.Maand=" & request.form("Maand") & " And
V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
V_Monthreport.Q_ID"
case "Section"
case "Summary"
end select
End If
<% while not rstMain.eof %>
<tr>
<td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
%></font></td>
<td><font face="Tahoma" size="-2" color="Black"><%=
rstMain("Question") %></font></td>
<td><font face="Tahoma" size="-2" color="Black"><%=
rstMain("Answer") %></font></td>
</tr>
<% rstMain.movenext
wend%>
<% End If %>
I am experiencing a problem in that the data when displayed is
duplicated. The data in the db is not duplicated. So I dont have a clue
why the data is displayed in duplicates. Any comments on y?|||amatuer
How do you expect to get a right answer without posting some data to be
tested and an expected result.?
Take a look at DISTINCT , GROUP BY clause in the BOL
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> Code below:
> If Session("ID") = 1 Or Session("ID") = 2 Then
> select case (request.form("Individual"))
> case "Individual" sql = "Select V_Monthreport.Q_ID,
> V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> V_Monthreport.PersID=" & session("Pers_ID") & " And
> V_Monthreport.DeptID=" & request.form("SectID") & " And
> V_Monthreport.Maand=" & request.form("Maand") & " And
> V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> V_Monthreport.Q_ID"
> case "Section"
> case "Summary"
> end select
> End If
> <% while not rstMain.eof %>
> <tr>
> <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Question") %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Answer") %></font></td>
> </tr>
> <% rstMain.movenext
> wend%>
> <% End If %>
> I am experiencing a problem in that the data when displayed is
> duplicated. The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
>|||here's the table:
CREATE TABLE [dbo].[V_Monthreport] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PersID] [int] NULL ,
[DeptID] [int] NULL ,
[Datum] [datetime] NULL ,
[Maand] [int] NULL ,
[Jaar] [int] NULL ,
[Q_ID] [int] NULL ,
[Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[V_Monthreport_Q] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AfdelingID] [int] NULL ,
[Afdeling] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Seksie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QNo] [int] NULL ,
[Question] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
) ON [PRIMARY]
& data looks like this:
V_Monthreport_Q:
14 1 Agricultural Service Agricultural Information Services 1 Marketing
of available new services of the Department (List Opportunities)
15 1 Agricultural Service Agricultural Information
Services 2 Opportunity/Involvement in identifying and development of
new clients; products; projects
16 1 Agricultural Service Agricultural Information
Services 3 Involvement in sustaining and improvement of the level of
expertise, as well as the knowledge and skills
17 1 Agricultural Service Agricultural Information Services 4 Intra
and inter-departmental co-operation (disposition/view)
18 1 Agricultural Service Agricultural Information
Services 5 Agricultural condition/State of Agriculture: Climate
(temperature, wind, etc)
19 1 Agricultural Service Agricultural Information
Services 6 Agricultural condition/State of Agriculture: Veld (coverage,
nutritional value)
20 1 Agricultural Service Agricultural Information
Services 7 Agricultural condition/State of Agriculture: Moisture status
21 1 Agricultural Service Agricultural Information
Services 8 Agricultural condition/State of Agriculture: Livestock
(conditions)
22 1 Agricultural Service Agricultural Information
Services 9 Agricultural condition/State of Agriculture: Commodity
prices
23 1 Agricultural Service Agricultural Information
Services 10 Agricultural condition/State of Agriculture: Labour
24 1 Agricultural Service Agricultural Information
Services 11 Agricultural condition/State of Agriculture: Security /
Safe keeping
25 1 Agricultural Service Agricultural Information Services 12 Client
Relations
26 1 Agricultural Service Agricultural Information Services 13 Threats
/ Concerns
V_Monthreport:
53 4 1 6/21/2006 6 2006 1 Test1
54 4 1 6/21/2006 6 2006 2 Test2
55 4 1 6/21/2006 6 2006 4 Test4
56 4 1 6/21/2006 6 2006 5 Test5
57 4 1 6/21/2006 6 2006 6 Test6
58 4 1 6/21/2006 6 2006 8 Test8
59 4 1 6/21/2006 6 2006 9 Test9
60 4 1 6/21/2006 6 2006 10 Test10
61 4 1 6/21/2006 6 2006 11 Test11
62 4 1 6/21/2006 6 2006 12 Test12
63 4 1 6/21/2006 6 2006 13 Test13
Sorry but this was the best i could.see if you cn help Uri
Dimant.thanx.
Uri Dimant wrote:[vbcol=seagreen]
> amatuer
> How do you expect to get a right answer without posting some data to be
> tested and an expected result.?
> Take a look at DISTINCT , GROUP BY clause in the BOL
>
>
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...|||No so much
INSERT INTO Table.. VALUES (...
Can you provide a full DDL?
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150883115.391130.319520@.p79g2000cwp.googlegroups.com...
> here's the table:
> CREATE TABLE [dbo].[V_Monthreport] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [PersID] [int] NULL ,
> [DeptID] [int] NULL ,
> [Datum] [datetime] NULL ,
> [Maand] [int] NULL ,
> [Jaar] [int] NULL ,
> [Q_ID] [int] NULL ,
> [Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[V_Monthreport_Q] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [AfdelingID] [int] NULL ,
> [Afdeling] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [Seksie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [QNo] [int] NULL ,
> [Question] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
> ) ON [PRIMARY]
> & data looks like this:
> V_Monthreport_Q:
> 14 1 Agricultural Service Agricultural Information Services 1 Marketing
> of available new services of the Department (List Opportunities)
> 15 1 Agricultural Service Agricultural Information
> Services 2 Opportunity/Involvement in identifying and development of
> new clients; products; projects
> 16 1 Agricultural Service Agricultural Information
> Services 3 Involvement in sustaining and improvement of the level of
> expertise, as well as the knowledge and skills
> 17 1 Agricultural Service Agricultural Information Services 4 Intra
> and inter-departmental co-operation (disposition/view)
> 18 1 Agricultural Service Agricultural Information
> Services 5 Agricultural condition/State of Agriculture: Climate
> (temperature, wind, etc)
> 19 1 Agricultural Service Agricultural Information
> Services 6 Agricultural condition/State of Agriculture: Veld (coverage,
> nutritional value)
> 20 1 Agricultural Service Agricultural Information
> Services 7 Agricultural condition/State of Agriculture: Moisture status
> 21 1 Agricultural Service Agricultural Information
> Services 8 Agricultural condition/State of Agriculture: Livestock
> (conditions)
> 22 1 Agricultural Service Agricultural Information
> Services 9 Agricultural condition/State of Agriculture: Commodity
> prices
> 23 1 Agricultural Service Agricultural Information
> Services 10 Agricultural condition/State of Agriculture: Labour
> 24 1 Agricultural Service Agricultural Information
> Services 11 Agricultural condition/State of Agriculture: Security /
> Safe keeping
> 25 1 Agricultural Service Agricultural Information Services 12 Client
> Relations
> 26 1 Agricultural Service Agricultural Information Services 13 Threats
> / Concerns
> V_Monthreport:
> 53 4 1 6/21/2006 6 2006 1 Test1
> 54 4 1 6/21/2006 6 2006 2 Test2
> 55 4 1 6/21/2006 6 2006 4 Test4
> 56 4 1 6/21/2006 6 2006 5 Test5
> 57 4 1 6/21/2006 6 2006 6 Test6
> 58 4 1 6/21/2006 6 2006 8 Test8
> 59 4 1 6/21/2006 6 2006 9 Test9
> 60 4 1 6/21/2006 6 2006 10 Test10
> 61 4 1 6/21/2006 6 2006 11 Test11
> 62 4 1 6/21/2006 6 2006 12 Test12
> 63 4 1 6/21/2006 6 2006 13 Test13
> Sorry but this was the best i could.see if you cn help Uri
> Dimant.thanx.
>
> Uri Dimant wrote:
>|||> The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
Do you get duplicates when you run the same query from Query Analyzer? The
same rows will be returned multiple times when more than one row exists in
both tables with the same value in the joined columns.
Hope this helps.
Dan Guzman
SQL Server MVP
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> Code below:
> If Session("ID") = 1 Or Session("ID") = 2 Then
> select case (request.form("Individual"))
> case "Individual" sql = "Select V_Monthreport.Q_ID,
> V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> V_Monthreport.PersID=" & session("Pers_ID") & " And
> V_Monthreport.DeptID=" & request.form("SectID") & " And
> V_Monthreport.Maand=" & request.form("Maand") & " And
> V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> V_Monthreport.Q_ID"
> case "Section"
> case "Summary"
> end select
> End If
> <% while not rstMain.eof %>
> <tr>
> <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Question") %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Answer") %></font></td>
> </tr>
> <% rstMain.movenext
> wend%>
> <% End If %>
> I am experiencing a problem in that the data when displayed is
> duplicated. The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
>|||Thanx Dan, i sorted it out. The prob was that the inner join was not
unique.Anothr one had to be created in order not to yield more then the
required possible results.
Dan Guzman wrote:[vbcol=seagreen]
> Do you get duplicates when you run the same query from Query Analyzer? Th
e
> same rows will be returned multiple times when more than one row exists in
> both tables with the same value in the joined columns.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
How do you expect to get a right answer without posting some data to be
tested and an expected result.?
Take a look at DISTINCT , GROUP BY clause in the BOL
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> Code below:
> If Session("ID") = 1 Or Session("ID") = 2 Then
> select case (request.form("Individual"))
> case "Individual" sql = "Select V_Monthreport.Q_ID,
> V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> V_Monthreport.PersID=" & session("Pers_ID") & " And
> V_Monthreport.DeptID=" & request.form("SectID") & " And
> V_Monthreport.Maand=" & request.form("Maand") & " And
> V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> V_Monthreport.Q_ID"
> case "Section"
> case "Summary"
> end select
> End If
> <% while not rstMain.eof %>
> <tr>
> <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Question") %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Answer") %></font></td>
> </tr>
> <% rstMain.movenext
> wend%>
> <% End If %>
> I am experiencing a problem in that the data when displayed is
> duplicated. The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
>here's the table:
CREATE TABLE [dbo].[V_Monthreport] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PersID] [int] NULL ,
[DeptID] [int] NULL ,
[Datum] [datetime] NULL ,
[Maand] [int] NULL ,
[Jaar] [int] NULL ,
[Q_ID] [int] NULL ,
[Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[V_Monthreport_Q] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AfdelingID] [int] NULL ,
[Afdeling] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Seksie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QNo] [int] NULL ,
[Question] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
) ON [PRIMARY]
& data looks like this:
V_Monthreport_Q:
14 1 Agricultural Service Agricultural Information Services 1 Marketing
of available new services of the Department (List Opportunities)
15 1 Agricultural Service Agricultural Information
Services 2 Opportunity/Involvement in identifying and development of
new clients; products; projects
16 1 Agricultural Service Agricultural Information
Services 3 Involvement in sustaining and improvement of the level of
expertise, as well as the knowledge and skills
17 1 Agricultural Service Agricultural Information Services 4 Intra
and inter-departmental co-operation (disposition/view)
18 1 Agricultural Service Agricultural Information
Services 5 Agricultural condition/State of Agriculture: Climate
(temperature, wind, etc)
19 1 Agricultural Service Agricultural Information
Services 6 Agricultural condition/State of Agriculture: Veld (coverage,
nutritional value)
20 1 Agricultural Service Agricultural Information
Services 7 Agricultural condition/State of Agriculture: Moisture status
21 1 Agricultural Service Agricultural Information
Services 8 Agricultural condition/State of Agriculture: Livestock
(conditions)
22 1 Agricultural Service Agricultural Information
Services 9 Agricultural condition/State of Agriculture: Commodity
prices
23 1 Agricultural Service Agricultural Information
Services 10 Agricultural condition/State of Agriculture: Labour
24 1 Agricultural Service Agricultural Information
Services 11 Agricultural condition/State of Agriculture: Security /
Safe keeping
25 1 Agricultural Service Agricultural Information Services 12 Client
Relations
26 1 Agricultural Service Agricultural Information Services 13 Threats
/ Concerns
V_Monthreport:
53 4 1 6/21/2006 6 2006 1 Test1
54 4 1 6/21/2006 6 2006 2 Test2
55 4 1 6/21/2006 6 2006 4 Test4
56 4 1 6/21/2006 6 2006 5 Test5
57 4 1 6/21/2006 6 2006 6 Test6
58 4 1 6/21/2006 6 2006 8 Test8
59 4 1 6/21/2006 6 2006 9 Test9
60 4 1 6/21/2006 6 2006 10 Test10
61 4 1 6/21/2006 6 2006 11 Test11
62 4 1 6/21/2006 6 2006 12 Test12
63 4 1 6/21/2006 6 2006 13 Test13
Sorry but this was the best i could.see if you cn help Uri
Dimant.thanx.
Uri Dimant wrote:[vbcol=seagreen]
> amatuer
> How do you expect to get a right answer without posting some data to be
> tested and an expected result.?
> Take a look at DISTINCT , GROUP BY clause in the BOL
>
>
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...|||No so much
INSERT INTO Table.. VALUES (...
Can you provide a full DDL?
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150883115.391130.319520@.p79g2000cwp.googlegroups.com...
> here's the table:
> CREATE TABLE [dbo].[V_Monthreport] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [PersID] [int] NULL ,
> [DeptID] [int] NULL ,
> [Datum] [datetime] NULL ,
> [Maand] [int] NULL ,
> [Jaar] [int] NULL ,
> [Q_ID] [int] NULL ,
> [Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[V_Monthreport_Q] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [AfdelingID] [int] NULL ,
> [Afdeling] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [Seksie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [QNo] [int] NULL ,
> [Question] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
> ) ON [PRIMARY]
> & data looks like this:
> V_Monthreport_Q:
> 14 1 Agricultural Service Agricultural Information Services 1 Marketing
> of available new services of the Department (List Opportunities)
> 15 1 Agricultural Service Agricultural Information
> Services 2 Opportunity/Involvement in identifying and development of
> new clients; products; projects
> 16 1 Agricultural Service Agricultural Information
> Services 3 Involvement in sustaining and improvement of the level of
> expertise, as well as the knowledge and skills
> 17 1 Agricultural Service Agricultural Information Services 4 Intra
> and inter-departmental co-operation (disposition/view)
> 18 1 Agricultural Service Agricultural Information
> Services 5 Agricultural condition/State of Agriculture: Climate
> (temperature, wind, etc)
> 19 1 Agricultural Service Agricultural Information
> Services 6 Agricultural condition/State of Agriculture: Veld (coverage,
> nutritional value)
> 20 1 Agricultural Service Agricultural Information
> Services 7 Agricultural condition/State of Agriculture: Moisture status
> 21 1 Agricultural Service Agricultural Information
> Services 8 Agricultural condition/State of Agriculture: Livestock
> (conditions)
> 22 1 Agricultural Service Agricultural Information
> Services 9 Agricultural condition/State of Agriculture: Commodity
> prices
> 23 1 Agricultural Service Agricultural Information
> Services 10 Agricultural condition/State of Agriculture: Labour
> 24 1 Agricultural Service Agricultural Information
> Services 11 Agricultural condition/State of Agriculture: Security /
> Safe keeping
> 25 1 Agricultural Service Agricultural Information Services 12 Client
> Relations
> 26 1 Agricultural Service Agricultural Information Services 13 Threats
> / Concerns
> V_Monthreport:
> 53 4 1 6/21/2006 6 2006 1 Test1
> 54 4 1 6/21/2006 6 2006 2 Test2
> 55 4 1 6/21/2006 6 2006 4 Test4
> 56 4 1 6/21/2006 6 2006 5 Test5
> 57 4 1 6/21/2006 6 2006 6 Test6
> 58 4 1 6/21/2006 6 2006 8 Test8
> 59 4 1 6/21/2006 6 2006 9 Test9
> 60 4 1 6/21/2006 6 2006 10 Test10
> 61 4 1 6/21/2006 6 2006 11 Test11
> 62 4 1 6/21/2006 6 2006 12 Test12
> 63 4 1 6/21/2006 6 2006 13 Test13
> Sorry but this was the best i could.see if you cn help Uri
> Dimant.thanx.
>
> Uri Dimant wrote:
>|||> The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
Do you get duplicates when you run the same query from Query Analyzer? The
same rows will be returned multiple times when more than one row exists in
both tables with the same value in the joined columns.
Hope this helps.
Dan Guzman
SQL Server MVP
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> Code below:
> If Session("ID") = 1 Or Session("ID") = 2 Then
> select case (request.form("Individual"))
> case "Individual" sql = "Select V_Monthreport.Q_ID,
> V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> V_Monthreport.PersID=" & session("Pers_ID") & " And
> V_Monthreport.DeptID=" & request.form("SectID") & " And
> V_Monthreport.Maand=" & request.form("Maand") & " And
> V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> V_Monthreport.Q_ID"
> case "Section"
> case "Summary"
> end select
> End If
> <% while not rstMain.eof %>
> <tr>
> <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Question") %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Answer") %></font></td>
> </tr>
> <% rstMain.movenext
> wend%>
> <% End If %>
> I am experiencing a problem in that the data when displayed is
> duplicated. The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
>|||Code below:
If Session("ID") = 1 Or Session("ID") = 2 Then
select case (request.form("Individual"))
case "Individual" sql = "Select V_Monthreport.Q_ID,
V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
V_Monthreport.PersID=" & session("Pers_ID") & " And
V_Monthreport.DeptID=" & request.form("SectID") & " And
V_Monthreport.Maand=" & request.form("Maand") & " And
V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
V_Monthreport.Q_ID"
case "Section"
case "Summary"
end select
End If
<% while not rstMain.eof %>
<tr>
<td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
%></font></td>
<td><font face="Tahoma" size="-2" color="Black"><%=
rstMain("Question") %></font></td>
<td><font face="Tahoma" size="-2" color="Black"><%=
rstMain("Answer") %></font></td>
</tr>
<% rstMain.movenext
wend%>
<% End If %>
I am experiencing a problem in that the data when displayed is
duplicated. The data in the db is not duplicated. So I dont have a clue
why the data is displayed in duplicates. Any comments on y?|||amatuer
How do you expect to get a right answer without posting some data to be
tested and an expected result.?
Take a look at DISTINCT , GROUP BY clause in the BOL
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> Code below:
> If Session("ID") = 1 Or Session("ID") = 2 Then
> select case (request.form("Individual"))
> case "Individual" sql = "Select V_Monthreport.Q_ID,
> V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> V_Monthreport.PersID=" & session("Pers_ID") & " And
> V_Monthreport.DeptID=" & request.form("SectID") & " And
> V_Monthreport.Maand=" & request.form("Maand") & " And
> V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> V_Monthreport.Q_ID"
> case "Section"
> case "Summary"
> end select
> End If
> <% while not rstMain.eof %>
> <tr>
> <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Question") %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Answer") %></font></td>
> </tr>
> <% rstMain.movenext
> wend%>
> <% End If %>
> I am experiencing a problem in that the data when displayed is
> duplicated. The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
>|||here's the table:
CREATE TABLE [dbo].[V_Monthreport] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PersID] [int] NULL ,
[DeptID] [int] NULL ,
[Datum] [datetime] NULL ,
[Maand] [int] NULL ,
[Jaar] [int] NULL ,
[Q_ID] [int] NULL ,
[Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[V_Monthreport_Q] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AfdelingID] [int] NULL ,
[Afdeling] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Seksie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QNo] [int] NULL ,
[Question] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L
) ON [PRIMARY]
& data looks like this:
V_Monthreport_Q:
14 1 Agricultural Service Agricultural Information Services 1 Marketing
of available new services of the Department (List Opportunities)
15 1 Agricultural Service Agricultural Information
Services 2 Opportunity/Involvement in identifying and development of
new clients; products; projects
16 1 Agricultural Service Agricultural Information
Services 3 Involvement in sustaining and improvement of the level of
expertise, as well as the knowledge and skills
17 1 Agricultural Service Agricultural Information Services 4 Intra
and inter-departmental co-operation (disposition/view)
18 1 Agricultural Service Agricultural Information
Services 5 Agricultural condition/State of Agriculture: Climate
(temperature, wind, etc)
19 1 Agricultural Service Agricultural Information
Services 6 Agricultural condition/State of Agriculture: Veld (coverage,
nutritional value)
20 1 Agricultural Service Agricultural Information
Services 7 Agricultural condition/State of Agriculture: Moisture status
21 1 Agricultural Service Agricultural Information
Services 8 Agricultural condition/State of Agriculture: Livestock
(conditions)
22 1 Agricultural Service Agricultural Information
Services 9 Agricultural condition/State of Agriculture: Commodity
prices
23 1 Agricultural Service Agricultural Information
Services 10 Agricultural condition/State of Agriculture: Labour
24 1 Agricultural Service Agricultural Information
Services 11 Agricultural condition/State of Agriculture: Security /
Safe keeping
25 1 Agricultural Service Agricultural Information Services 12 Client
Relations
26 1 Agricultural Service Agricultural Information Services 13 Threats
/ Concerns
V_Monthreport:
53 4 1 6/21/2006 6 2006 1 Test1
54 4 1 6/21/2006 6 2006 2 Test2
55 4 1 6/21/2006 6 2006 4 Test4
56 4 1 6/21/2006 6 2006 5 Test5
57 4 1 6/21/2006 6 2006 6 Test6
58 4 1 6/21/2006 6 2006 8 Test8
59 4 1 6/21/2006 6 2006 9 Test9
60 4 1 6/21/2006 6 2006 10 Test10
61 4 1 6/21/2006 6 2006 11 Test11
62 4 1 6/21/2006 6 2006 12 Test12
63 4 1 6/21/2006 6 2006 13 Test13
Sorry but this was the best i could.see if you cn help Uri
Dimant.thanx.
Uri Dimant wrote:[vbcol=seagreen]
> amatuer
> How do you expect to get a right answer without posting some data to be
> tested and an expected result.?
> Take a look at DISTINCT , GROUP BY clause in the BOL
>
>
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...|||No so much
INSERT INTO Table.. VALUES (...
Can you provide a full DDL?
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150883115.391130.319520@.p79g2000cwp.googlegroups.com...
> here's the table:
> CREATE TABLE [dbo].[V_Monthreport] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [PersID] [int] NULL ,
> [DeptID] [int] NULL ,
> [Datum] [datetime] NULL ,
> [Maand] [int] NULL ,
> [Jaar] [int] NULL ,
> [Q_ID] [int] NULL ,
> [Answer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[V_Monthreport_Q] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [AfdelingID] [int] NULL ,
> [Afdeling] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [Seksie] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [QNo] [int] NULL ,
> [Question] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
> ) ON [PRIMARY]
> & data looks like this:
> V_Monthreport_Q:
> 14 1 Agricultural Service Agricultural Information Services 1 Marketing
> of available new services of the Department (List Opportunities)
> 15 1 Agricultural Service Agricultural Information
> Services 2 Opportunity/Involvement in identifying and development of
> new clients; products; projects
> 16 1 Agricultural Service Agricultural Information
> Services 3 Involvement in sustaining and improvement of the level of
> expertise, as well as the knowledge and skills
> 17 1 Agricultural Service Agricultural Information Services 4 Intra
> and inter-departmental co-operation (disposition/view)
> 18 1 Agricultural Service Agricultural Information
> Services 5 Agricultural condition/State of Agriculture: Climate
> (temperature, wind, etc)
> 19 1 Agricultural Service Agricultural Information
> Services 6 Agricultural condition/State of Agriculture: Veld (coverage,
> nutritional value)
> 20 1 Agricultural Service Agricultural Information
> Services 7 Agricultural condition/State of Agriculture: Moisture status
> 21 1 Agricultural Service Agricultural Information
> Services 8 Agricultural condition/State of Agriculture: Livestock
> (conditions)
> 22 1 Agricultural Service Agricultural Information
> Services 9 Agricultural condition/State of Agriculture: Commodity
> prices
> 23 1 Agricultural Service Agricultural Information
> Services 10 Agricultural condition/State of Agriculture: Labour
> 24 1 Agricultural Service Agricultural Information
> Services 11 Agricultural condition/State of Agriculture: Security /
> Safe keeping
> 25 1 Agricultural Service Agricultural Information Services 12 Client
> Relations
> 26 1 Agricultural Service Agricultural Information Services 13 Threats
> / Concerns
> V_Monthreport:
> 53 4 1 6/21/2006 6 2006 1 Test1
> 54 4 1 6/21/2006 6 2006 2 Test2
> 55 4 1 6/21/2006 6 2006 4 Test4
> 56 4 1 6/21/2006 6 2006 5 Test5
> 57 4 1 6/21/2006 6 2006 6 Test6
> 58 4 1 6/21/2006 6 2006 8 Test8
> 59 4 1 6/21/2006 6 2006 9 Test9
> 60 4 1 6/21/2006 6 2006 10 Test10
> 61 4 1 6/21/2006 6 2006 11 Test11
> 62 4 1 6/21/2006 6 2006 12 Test12
> 63 4 1 6/21/2006 6 2006 13 Test13
> Sorry but this was the best i could.see if you cn help Uri
> Dimant.thanx.
>
> Uri Dimant wrote:
>|||> The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
Do you get duplicates when you run the same query from Query Analyzer? The
same rows will be returned multiple times when more than one row exists in
both tables with the same value in the joined columns.
Hope this helps.
Dan Guzman
SQL Server MVP
"amatuer" <njoosub@.gmail.com> wrote in message
news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
> Code below:
> If Session("ID") = 1 Or Session("ID") = 2 Then
> select case (request.form("Individual"))
> case "Individual" sql = "Select V_Monthreport.Q_ID,
> V_Monthreport.Answer, V_Monthreport_Q.Question FROM V_Monthreport INNER
> JOIN V_Monthreport_Q ON V_Monthreport.Q_ID = V_Monthreport_Q.QNo Where
> V_Monthreport.PersID=" & session("Pers_ID") & " And
> V_Monthreport.DeptID=" & request.form("SectID") & " And
> V_Monthreport.Maand=" & request.form("Maand") & " And
> V_Monthreport.Jaar=" & request.form("Jaar") & " Order By
> V_Monthreport.Q_ID"
> case "Section"
> case "Summary"
> end select
> End If
> <% while not rstMain.eof %>
> <tr>
> <td><font face="Tahoma" size="-2" color="Black"><%= rstMain("Q_ID")
> %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Question") %></font></td>
> <td><font face="Tahoma" size="-2" color="Black"><%=
> rstMain("Answer") %></font></td>
> </tr>
> <% rstMain.movenext
> wend%>
> <% End If %>
> I am experiencing a problem in that the data when displayed is
> duplicated. The data in the db is not duplicated. So I dont have a clue
> why the data is displayed in duplicates. Any comments on y?
>|||Thanx Dan, i sorted it out. The prob was that the inner join was not
unique.Anothr one had to be created in order not to yield more then the
required possible results.
Dan Guzman wrote:[vbcol=seagreen]
> Do you get duplicates when you run the same query from Query Analyzer? Th
e
> same rows will be returned multiple times when more than one row exists in
> both tables with the same value in the joined columns.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "amatuer" <njoosub@.gmail.com> wrote in message
> news:1150881311.092232.176500@.y41g2000cwy.googlegroups.com...
Monday, March 19, 2012
Data Design Issues
I am working on a Project Management application, and I have two data design issues I am debating.
The key element of this app, as you might expect, is Project. The project will proceed through many phases, from Planning, to Pre-Design, Design, Bid, Construction and Post Construction. All along the way there are a number of discrete tasks that must be performed and tracked.
I bounce back and forth in my mind between a single Project table that encapsulates all of these tasks, but am hesitant because I'm not a big fan of large monolithic tables. Alternatively, I could logically create separate tables for the various phases. However, this would create a series of one-to-one relationships between Project and the Phase tables, and require extra joins. The performance hit would probably not be too bad, but I would need to add extra code in either the app code or stored procedures to create an empty record in each of the phase tables when a new project is added. (Obviously, projects in planning or design will not have active records in the Construction and Post Construction tables). What are your thoughts about these choices?
Secondly, I have to manage data for a lot of individuals, which basically break down into two groups. First are internal employees who will have tasks routed to them, be invited to meetings, etc. Second are external vendors, basically contractors and consultants, who will be performing work and also be invited to meetings and such. I need to track the participants in meetings and inspections, so will have a Meeting Participant table to capture the many-to-many relationship. My issue is structuring the handling of the people. One option is to have a Person table, which basically includes everybody, with a flag field for internal or external people, and categories for their roles. Second is a table for internal folks and a table for external, or separate tables for Consultants, Contractors, and internal Employees. However, this makes capturing the meeting participants more cumbersome.
What is the collective wisdom on these? Thanks!
Jeff LittleThe place to start is the Time Tracker starter kit table design and make modification as needed. The second place is to test drive Enterprise Project Server it comes with Database templates for OLTP and OLAP to build cubes for the project. Check the link below for a demo I attended a while back with OLAP cubes, you can also search the TechNet site for more Project demos. Hope this helps.
http://www.microsoft.com/technet/community/events/project/tnt1-64.mspx
Kind regards,
Gift Peddie|||Here's my suggestion:
Project (ProjectID, ...)
Task (ProjectID, PhaseID, TaskID, TaskTypeID, TaskName, ...)
Phase (PhaseID, Name, ...)
TaskType (TaskTypeID, Name, ...)
Person (PersonID, PersonTypeID, PersonName, Username, ...)
PersonType (PersonTypeID, Name, Internal Bit, ...)|||Hi Jeff,
First off I would go with the idea of multple tables for the project instead of one. I have seen way to many applications where the number of fields and record length is outrageous given that most of the time a third to a half of the fields aren't populated. As for your concerns about adding an empty record, why? Simply do an outer join, where no matching record exists the field values from the applicable table will be null.
Lastly, people are people unless there is some compelling reason such as significant data requirements for internal as opposed to external they belong in the same table.
Cheers
Subscribe to:
Posts (Atom)