Customers & Phones

Hello,
I have a customers table linked to a phones table.
Is there a way to write an update query which will take the first & second
phone number of every customer and put it into respective fields (strPhone1
&
strPhone2) on the customer table, and dump any other phones into a memo fiel
d?
Thanks,
LomasWhy? There is so much wrong with this is is not funny. First normal form
violations in each of your columns, for starters, but beyond that:
Data put into memo fields (text columns, I assume) is pretty much useless in
your database from then on. All of the things you can do with it is
basically lost. Best case, you use a query to make a view of the data like
you are asking for and abstract it out from there. I know it takes more
work to do it right at first but it is worth it in the long run. Not to
mention that you can ask here for any query help to make the abstractions
work.
A moderately reasonable solution would be to have two columns <purpose>Phone
and <otherPurpose>Phone in your customer table if they will always have the
same purpose and then the phone table to keep other phone types.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"optidev" <optidev@.community.nospam> wrote in message
news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
> Hello,
> I have a customers table linked to a phones table.
> Is there a way to write an update query which will take the first & second
> phone number of every customer and put it into respective fields
> (strPhone1 &
> strPhone2) on the customer table, and dump any other phones into a memo
> field?
> Thanks,
> Lomas
>|||Ok, let me explain a little bit better.
I am writing this sql code as part of a modification to a database. After
this update query comes the drop tblPhones statement. This sql statement, i
n
its final form, will handle the range of phone types.
It seems to me that allowing a user an unlimited number of phone numbers
will have little practical value, and will be more difficult to query.
Modifying at this stage, while there are still few users, shouldn't cause
too much of an impact.
"Louis Davidson" wrote:

> Why? There is so much wrong with this is is not funny. First normal form
> violations in each of your columns, for starters, but beyond that:
> Data put into memo fields (text columns, I assume) is pretty much useless
in
> your database from then on. All of the things you can do with it is
> basically lost. Best case, you use a query to make a view of the data lik
e
> you are asking for and abstract it out from there. I know it takes more
> work to do it right at first but it is worth it in the long run. Not to
> mention that you can ask here for any query help to make the abstractions
> work.
> A moderately reasonable solution would be to have two columns <purpose>Pho
ne
> and <otherPurpose>Phone in your customer table if they will always have th
e
> same purpose and then the phone table to keep other phone types.
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "optidev" <optidev@.community.nospam> wrote in message
> news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
>
>|||On a conceptual level, SSN, LastName and Ethnicity are attributes of the
Customer, but PhoneNumber and Address are not. One or more PhoneNumber(s)
are related to a specific Customer at a given moment in time, but could
belong to an entirely different Customer tomorrow. Consider, relating them
using a CustomerPhone table with StartDate and EndDate columns to keep an
audit whenever their profile changes.
"optidev" <optidev@.community.nospam> wrote in message
news:6B7323C5-3FE7-41AB-8EFD-0F13C978E1F3@.microsoft.com...
> Ok, let me explain a little bit better.
> I am writing this sql code as part of a modification to a database. After
> this update query comes the drop tblPhones statement. This sql statement,
> in
> its final form, will handle the range of phone types.
> It seems to me that allowing a user an unlimited number of phone numbers
> will have little practical value, and will be more difficult to query.
> Modifying at this stage, while there are still few users, shouldn't cause
> too much of an impact.
>
> "Louis Davidson" wrote:
>|||This is exactly what he wants to avoid, though it is still not a good idea
:)
To answer your original question, you will have to define what data makes a
phone number first or second. If you go ahead with this change, I would at
least suggest you have a column for home phone, one for mobile phone, etc.
rather than phone1, phone2. This is really bad design. On the other hand,
limiting the user to only one phone number of a specific type is a valid
design that doesn't leave the end user guessing what they are storing where.
Post your table structures and someone will help write the query..
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <someone@.microsoft.com> wrote in message
news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
> On a conceptual level, SSN, LastName and Ethnicity are attributes of the
> Customer, but PhoneNumber and Address are not. One or more PhoneNumber(s)
> are related to a specific Customer at a given moment in time, but could
> belong to an entirely different Customer tomorrow. Consider, relating them
> using a CustomerPhone table with StartDate and EndDate columns to keep an
> audit whenever their profile changes.
> "optidev" <optidev@.community.nospam> wrote in message
> news:6B7323C5-3FE7-41AB-8EFD-0F13C978E1F3@.microsoft.com...
>|||Hi ,
Write a update trigger for your main table and put a code like this , i cant
exactly remember right now
if updated(firstPhone) || updated(secondPhone)
......
"optidev" <optidev@.community.nospam> wrote in message
news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
> Hello,
> I have a customers table linked to a phones table.
> Is there a way to write an update query which will take the first & second
> phone number of every customer and put it into respective fields
> (strPhone1 &
> strPhone2) on the customer table, and dump any other phones into a memo
> field?
> Thanks,
> Lomas
>|||I *was* planning to have have different columns for the different phone
types. I just didn't want to make the question too complicated. :)
Do you still think it is better to completely normilize the tables? In that
case I would need to keep track of the current primary address for use in
display and reporting. I don't see much practicality in knowing a customer'
s
previous addresses. Either way, I would also split the phones table if I wa
s
going to keep it this way.
Here are the tables with the added columns for phone types:
tblFamilies: --
pkFamilyID
strLastName
strAddress1
strAddress2
strCity
strZip
strHomePhone1
strHomePhone2
strHomeFax
strFamilyNotes
tblCustomers: --
pkCustomerID
fkFamilyID
strTitle
strFirstName
strMiddleInitial
strPostTitle
strWorkPhone1
strWorkPhone2
strWorkFax
strMobilePhone
strPager
strVoiceMail
strCustomerNotes
tblPhones: --
pkPhoneID
fkPhoneTypeID
fkFamilyID
fkCustomerID (one of these is null - either family or customer)
strPhoneNumber
strExtension (I also want to combine the extension with the phone number)
tblPhoneTypes: --
pkPhoneTypeID
numOrder
strDescription
The primary key currently defines what makes the phone number first or secon
d.
This code, for example, retrives all home phone numbers:
SELECT tblFamilies.pkFamilyID, tblPhoneTypes.strDescription, CASE WHEN
tblPhones.strExtension IS NULL
THEN tblPhones.strPhoneNumber ELSE
tblPhones.strPhoneNumber + ' x' + tblPhones.strExtension END AS PhoneNumber
FROM tblPhones INNER JOIN
tblFamilies ON tblPhones.fkFamilyID =
tblFamilies.pkFamilyID LEFT OUTER JOIN
tblPhoneTypes ON tblPhones.fkPhoneTypeID =
tblPhoneTypes.pkPhoneTypeID
WHERE (tblPhoneTypes.strDescription = N'Home')
UNION
SELECT tblFamilies.pkFamilyID, tblPhoneTypes.strDescription, CASE WHEN
tblPhones.strExtension IS NULL
THEN tblPhones.strPhoneNumber ELSE
tblPhones.strPhoneNumber + ' x' + tblPhones.strExtension END AS PhoneNumber
FROM tblPhones INNER JOIN
tblCustomers ON tblPhones.fkCustomerID =
tblCustomers.pkCustomerID INNER JOIN
tblFamilies ON tblCustomers.fkFamilyID =
tblFamilies.pkFamilyID LEFT OUTER JOIN
tblPhoneTypes ON tblPhones.fkPhoneTypeID =
tblPhoneTypes.pkPhoneTypeID
WHERE (tblPhoneTypes.strDescription = N'Home')
I can add this kind of complexity to the statement after I know the coding
method.
Thanks,
Lomas
"Louis Davidson" wrote:

> This is exactly what he wants to avoid, though it is still not a good idea
> :)
> To answer your original question, you will have to define what data makes
a
> phone number first or second. If you go ahead with this change, I would a
t
> least suggest you have a column for home phone, one for mobile phone, etc.
> rather than phone1, phone2. This is really bad design. On the other hand
,
> limiting the user to only one phone number of a specific type is a valid
> design that doesn't leave the end user guessing what they are storing wher
e.
> Post your table structures and someone will help write the query..
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "JT" <someone@.microsoft.com> wrote in message
> news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
>
>|||Interesting... Now I am thinking of normalizing the addresses and
denormalizing the phones... I still don't really see a point in keeping a
history of phone numbers.
What would be the best way to set up the query for retriveing the current
primary address in such a case?
As for the normalized phones table, I ran into a problem when a customer
asked me to export a list of them. He needed a column for each phone type
(Home1, Home2, HomeFax, etc.). How would I set up the query to put the firs
t
home number (it found) into the Home1 column, the second into Home2, etc.
What it there are (let's say) 15 of them? Also, how would I set up the quer
y
for reporting purposes. (I don't want every one to print when I print the
report.)
Thanks,
Lomas
"JT" wrote:

> Ask the users if they need a history of changes to a user's profile. If yo
u
> are shipping product to a customer's address, then knowing the address whe
re
> you actually shipped a product on a specific date would not only be useful
> but critical. Also, the marketing department may be interested in datamini
ng
> the changes in their customer's demographics. If the customers who generat
e
> the most sales are migrating to another geographical area, then that would
> be significant.
> "JT" <someone@.microsoft.com> wrote in message
> news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
>
>|||What is is the customer's most recent address?
select top 1
Address
from
CustomerAddress
where
CustomerID = @.CustomerID
order by
StartDate desc
What was the customer's address on the date that the product was shipped?
select top 1
Address
from
CustomerAddress
where
CustomerID = @.CustomerID and
StartDate <= @.ShipDate
order by
StartDate desc
If you have a normalized database and want to return a crosstab query
(Home1,Home2,Cell1,Cell2,etc) for reporting purposes, you can use inline
sub-queries like below. However, this will perform better and is more
flexible when implemented on the client side using Crystal, Excel or
whatever your reporting tool happens to be.
select
CustomerName,
(select bottom top 1 Phone from CustomerPhone where CustomerID =
@.CustomerID and PhoneType='H' order by StartDate desc) as Home1,
(select bottom 1 Phone from (select bottom top 2 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='H' order by
StartDate desc) as x) as Home2,
(select bottom 1 Phone from (select bottom top 3 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='H' order by
StartDate desc) as x) as Home3,
(select bottom top 1 Phone from CustomerPhone where CustomerID =
@.CustomerID and PhoneType='C' order by StartDate desc) as Cell1,
(select bottom 1 Phone from (select bottom top 2 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='C' order by
StartDate desc) as x) as Cell2,
(select bottom 1 Phone from (select bottom top 3 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='C' order by
StartDate desc) as x) as Cell3
from
Customers
where
CustomerID = @.CustomerID
"optidev" <optidev@.community.nospam> wrote in message
news:A0E6085D-D6EE-42FC-BCC9-524FFFDEAF5A@.microsoft.com...
> Interesting... Now I am thinking of normalizing the addresses and
> denormalizing the phones... I still don't really see a point in keeping a
> history of phone numbers.
> What would be the best way to set up the query for retriveing the current
> primary address in such a case?
> As for the normalized phones table, I ran into a problem when a customer
> asked me to export a list of them. He needed a column for each phone type
> (Home1, Home2, HomeFax, etc.). How would I set up the query to put the
> first
> home number (it found) into the Home1 column, the second into Home2, etc.
> What it there are (let's say) 15 of them? Also, how would I set up the
> query
> for reporting purposes. (I don't want every one to print when I print the
> report.)
> Thanks,
> Lomas
>
> "JT" wrote:
>|||select bottom doesn't work
"optidev" wrote:
> Interesting... Now I am thinking of normalizing the addresses and
> denormalizing the phones... I still don't really see a point in keeping a
> history of phone numbers.
> What would be the best way to set up the query for retriveing the current
> primary address in such a case?
> As for the normalized phones table, I ran into a problem when a customer
> asked me to export a list of them. He needed a column for each phone type
> (Home1, Home2, HomeFax, etc.). How would I set up the query to put the fi
rst
> home number (it found) into the Home1 column, the second into Home2, etc.
> What it there are (let's say) 15 of them? Also, how would I set up the qu
ery
> for reporting purposes. (I don't want every one to print when I print the
> report.)
> Thanks,
> Lomas
>
> "JT" wrote:
>