Additional Information

Also, some people do not have middle names, how would I write the query to
pull this out? Thanks again.
"Mike Collins" wrote:

> In the following query, I will be making 6 joins for each ID in the Proble
ms
> table to get a person's full name from our personnel table. Is there anoth
er
> way to do this than with the query I have below?
> Select TPRTitle, p1.FirstName + ' ' + p1.LastName As Originator,
> p2.FirstName + ' ' + p2.MiddleName + ' ' + p2.LastName As Screener
> From TPRs t
> Join common..personnel p1 On p1.PersonnelID = t.OriginatorID
> Join common..personnel p2 On p2.PersonnelID = t.ScreenerID
> ...
> CREATE TABLE [Personnel] (
> [PersonnelID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_Personnel_PersonnelID] DEFAULT (newid()),
> [OrganizationID] [uniqueidentifier] NOT NULL ,
> [Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MiddleName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Service] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OfficeSymbol] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastModified] [datetime] NOT NULL ,
> CONSTRAINT [PK_Personnel] PRIMARY KEY CLUSTERED
> (
> [PersonnelID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [Problems] (
> [ProblemID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_TPRs_TPRID] DEFAULT (newid()),
> [OriginatorID] [uniqueidentifier] NOT NULL ,
> [ClassifiedByID] [uniqueidentifier] NOT NULL ,
> [ScreenerID] [uniqueidentifier] NOT NULL ,
> [SubjectMatterExpertID] [uniqueidentifier] NOT NULL ,
> [TestDirectorID] [uniqueidentifier] NOT NULL ,
> [TestEventID] [uniqueidentifier] NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Location] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Function] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OS] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ProblemSource] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SequenceOfEvents] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProblemDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WorkAround] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_tblTPRs] PRIMARY KEY CLUSTERED
> (
> [ProblemID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_TPRs_TestEvents] FOREIGN KEY
> (
> [TestEventID]
> ) REFERENCES [TestEvents] (
> [TestEventID]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:5C6CE19B-3FCE-422E-B546-3A1FDC7DD28B@.microsoft.com...
> Also, some people do not have middle names, how would I write the query to
> pull this out? Thanks again.
>
> "Mike Collins" wrote:
>
From where I sit, you will need to do the joins. A couple of suggestions
however.
1. Use ISNULL for the MiddleName column. If it is empty, you will not get
a row back because by default NULL Concatenation returns NULL. So
something like: ISNULL(p1.FirstName, '') + ' ' + ISNULL(p1.MiddleName,
'')...
2. Since I don't see any FK constraints in the Problem table that will
guarantee that Originators, Screeners and so forth exist in the Personnel
table, you may want to use LEFT JOINS to ensure that you get rows back.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks...that helps a lot. I forgot about using the IsNull function. It
greatly simplifies the query. One question I have since you mentioned foreig
n
keys. The personnel table is located in another database. Is there a way to
create a foreign key that will span databases? Thanks for your help.
"Rick Sawtell" wrote:

> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:5C6CE19B-3FCE-422E-B546-3A1FDC7DD28B@.microsoft.com...
>
> From where I sit, you will need to do the joins. A couple of suggestions
> however.
> 1. Use ISNULL for the MiddleName column. If it is empty, you will not ge
t
> a row back because by default NULL Concatenation returns NULL. So
> something like: ISNULL(p1.FirstName, '') + ' ' + ISNULL(p1.MiddleName,
> '')...
> 2. Since I don't see any FK constraints in the Problem table that will
> guarantee that Originators, Screeners and so forth exist in the Personnel
> table, you may want to use LEFT JOINS to ensure that you get rows back.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:CBAC9424-506B-40A3-85DD-8945A4AC61B5@.microsoft.com...
> Thanks...that helps a lot. I forgot about using the IsNull function. It
> greatly simplifies the query. One question I have since you mentioned
> foreign
> keys. The personnel table is located in another database. Is there a way
> to
> create a foreign key that will span databases? Thanks for your help.
>
You can't do it across DB's AFAIK. You can use sprocs and/or triggers for
this however, but it may slow your system down. Then again, you will
guarantee integrity that way. It's up to you.
Rick

Addition to the above.

I forgot to add, you'll see that ZONE has less records than STATE, thats
because the current system is storing NAMES not keys, and a ZONE name can be
in more than on STATE, e.g. STATE:"New York" can have a zone called
ZONE:"AGRICULTURE" and STATE:"California" can have a zone called
ZONE:"AGRICULTURE"Sorry, another thing... the record counts in the first post are DISTINCT
counts...

Adding full table names

I need to have the query select from dbname.dbo.client
instead of just client, in my query thats going to my sql server from crystal..is there a way to make it change to that format?
thanksCan u describe more ur problem, I m not getting

Adding data to SQL database through Access

My table resides in a SQL database: It has two fields:
1. NameID - KEY 2. Name
I want the user to have the ability to add new names in an Access form and
disallow any names that already exist. SQL does not give me the ability to
set the Name field to 'No Duplicates' and Access does not let me set the NO
DUP property since this is a linked table.
What code or settings is required to disallow duplicates in a no-key field
between Access and SQL?First of all, are you using a .mdb or .adp file
I think the best way to do it is use the DLookup function in Access on the
AFterUpdate Event...
Please look at DLookup in the Help file in MS Access
Ed
"SharonInGa" wrote:

> My table resides in a SQL database: It has two fields:
> 1. NameID - KEY 2. Name
> I want the user to have the ability to add new names in an Access form and
> disallow any names that already exist. SQL does not give me the ability to
> set the Name field to 'No Duplicates' and Access does not let me set the N
O
> DUP property since this is a linked table.
> What code or settings is required to disallow duplicates in a no-key field
> between Access and SQL?
>|||What you seem to be looking for is a unique constraint. You
can find more information on this in SQL Server books
online. It's the same as the 'No Duplicates' in Access.
-Sue
On Thu, 20 Jan 2005 13:37:02 -0800, "SharonInGa"
<SharonInGa@.discussions.microsoft.com> wrote:

>My table resides in a SQL database: It has two fields:
>1. NameID - KEY 2. Name
>I want the user to have the ability to add new names in an Access form and
>disallow any names that already exist. SQL does not give me the ability to
>set the Name field to 'No Duplicates' and Access does not let me set the NO
>DUP property since this is a linked table.
>What code or settings is required to disallow duplicates in a no-key field
>between Access and SQL?

Adding data to SQL database through Access

My table resides in a SQL database: It has two fields:
1. NameID - KEY 2. Name
I want the user to have the ability to add new names in an Access form and
disallow any names that already exist. SQL does not give me the ability to
set the Name field to 'No Duplicates' and Access does not let me set the NO
DUP property since this is a linked table.
What code or settings is required to disallow duplicates in a no-key field
between Access and SQL?
First of all, are you using a .mdb or .adp file
I think the best way to do it is use the DLookup function in Access on the
AFterUpdate Event...
Please look at DLookup in the Help file in MS Access
Ed
"SharonInGa" wrote:

> My table resides in a SQL database: It has two fields:
> 1. NameID - KEY 2. Name
> I want the user to have the ability to add new names in an Access form and
> disallow any names that already exist. SQL does not give me the ability to
> set the Name field to 'No Duplicates' and Access does not let me set the NO
> DUP property since this is a linked table.
> What code or settings is required to disallow duplicates in a no-key field
> between Access and SQL?
>
|||What you seem to be looking for is a unique constraint. You
can find more information on this in SQL Server books
online. It's the same as the 'No Duplicates' in Access.
-Sue
On Thu, 20 Jan 2005 13:37:02 -0800, "SharonInGa"
<SharonInGa@.discussions.microsoft.com> wrote:

>My table resides in a SQL database: It has two fields:
>1. NameID - KEY 2. Name
>I want the user to have the ability to add new names in an Access form and
>disallow any names that already exist. SQL does not give me the ability to
>set the Name field to 'No Duplicates' and Access does not let me set the NO
>DUP property since this is a linked table.
>What code or settings is required to disallow duplicates in a no-key field
>between Access and SQL?

Adding data to SQL database through Access

My table resides in a SQL database: It has two fields:
1. NameID - KEY 2. Name
I want the user to have the ability to add new names in an Access form and
disallow any names that already exist. SQL does not give me the ability to
set the Name field to 'No Duplicates' and Access does not let me set the NO
DUP property since this is a linked table.
What code or settings is required to disallow duplicates in a no-key field
between Access and SQL?First of all, are you using a .mdb or .adp file
I think the best way to do it is use the DLookup function in Access on the
AFterUpdate Event...
Please look at DLookup in the Help file in MS Access
Ed
"SharonInGa" wrote:
> My table resides in a SQL database: It has two fields:
> 1. NameID - KEY 2. Name
> I want the user to have the ability to add new names in an Access form and
> disallow any names that already exist. SQL does not give me the ability to
> set the Name field to 'No Duplicates' and Access does not let me set the NO
> DUP property since this is a linked table.
> What code or settings is required to disallow duplicates in a no-key field
> between Access and SQL?
>|||What you seem to be looking for is a unique constraint. You
can find more information on this in SQL Server books
online. It's the same as the 'No Duplicates' in Access.
-Sue
On Thu, 20 Jan 2005 13:37:02 -0800, "SharonInGa"
<SharonInGa@.discussions.microsoft.com> wrote:
>My table resides in a SQL database: It has two fields:
>1. NameID - KEY 2. Name
>I want the user to have the ability to add new names in an Access form and
>disallow any names that already exist. SQL does not give me the ability to
>set the Name field to 'No Duplicates' and Access does not let me set the NO
>DUP property since this is a linked table.
>What code or settings is required to disallow duplicates in a no-key field
>between Access and SQL?