|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Customers & PhonesHello,
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 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 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. -- Show quote---------------------------------------------------------------------------- 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, 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. Show quote "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 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 > > > > > 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. Show quote "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: > >> 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 >> 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 >> > >> >> >> 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.. -- Show quote---------------------------------------------------------------------------- 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" <some***@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... >> 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: >> >>> 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 >>> 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 >>> > >>> >>> >>> > > 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 was 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 second. ------------------------ 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 Show quote "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" <some***@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... > >> 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: > >> > >>> 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 > >>> 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 > >>> > > >>> > >>> > >>> > > > > > > > Ask the users if they need a history of changes to a user's profile. If you
are shipping product to a customer's address, then knowing the address where you actually shipped a product on a specific date would not only be useful but critical. Also, the marketing department may be interested in datamining the changes in their customer's demographics. If the customers who generate the most sales are migrating to another geographical area, then that would be significant. Show quote "JT" <some***@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... >> 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: >> >>> 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 >>> 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 >>> > >>> >>> >>> > > 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 Show quote "JT" wrote: > Ask the users if they need a history of changes to a user's profile. If you > are shipping product to a customer's address, then knowing the address where > you actually shipped a product on a specific date would not only be useful > but critical. Also, the marketing department may be interested in datamining > the changes in their customer's demographics. If the customers who generate > the most sales are migrating to another geographical area, then that would > be significant. > > "JT" <some***@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... > >> 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: > >> > >>> 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 > >>> 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 > >>> > > >>> > >>> > >>> > > > > > > > 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 Show quote "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: > >> Ask the users if they need a history of changes to a user's profile. If >> you >> are shipping product to a customer's address, then knowing the address >> where >> you actually shipped a product on a specific date would not only be >> useful >> but critical. Also, the marketing department may be interested in >> datamining >> the changes in their customer's demographics. If the customers who >> generate >> the most sales are migrating to another geographical area, then that >> would >> be significant. >> >> "JT" <some***@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... >> >> 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: >> >> >> >>> 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 >> >>> 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 >> >>> > >> >>> >> >>> >> >>> >> > >> > >> >> >> select bottom doesn't work
Show quote "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 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: > > > Ask the users if they need a history of changes to a user's profile. If you > > are shipping product to a customer's address, then knowing the address where > > you actually shipped a product on a specific date would not only be useful > > but critical. Also, the marketing department may be interested in datamining > > the changes in their customer's demographics. If the customers who generate > > the most sales are migrating to another geographical area, then that would > > be significant. > > > > "JT" <some***@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... > > >> 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: > > >> > > >>> 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 > > >>> 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 > > >>> > > > >>> > > >>> > > >>> > > > > > > > > > > > > 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) ........ Show quote "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 >  |
|||||||||||||||||||||||
Other interesting topics