February 28, 2011 at 2:37 am
Hi,
This must be a simple problem since it's immediate in Access , but I don't know how to do this in SQL.
I'm building a view to be used in a mailmerge. The view includes something like this:
–TABLE 1: FIELD 1 (Surname)
–TABLE 2: FIELD 2 (Contact type: Phone, Mobile, Email) / FIELD 3 (contact value: value of the previous field, the Landline phone, mobile, email, etc)
The view shows fine in SQL; for instance, a guy with 1 landline and 1 mobile appears with 2 consecutive lines.
What I need is to create a new field (I guess this is the easiest way.... others welcome) that is the combination of FIELD2 + FIELD 3 for each value of Field2, to be able to insert this new field in the Word mailmerge. That is:
• Field2 (when value is 'landline' Phone) + Field 3 (its value) > and call this field in the view: FIELD23_phone
• Field2 (when value is 'mobile') + Field 3 > and call this field: FIELD23_mobile
• Field2 (when value is 'email') + Field 3 > and call this field: FIELD23_email
so that I can call the various Field23_ from the mailmerge and obtain the actual values to contact that person.
Anyone can help? If this is just about defining a calculated field a link to how to do it would do too.
Thanks in advance.
February 28, 2011 at 6:42 am
I think you can do what you want as a set of case statements
let me know if this model works for you:
SELECT
OtherFields,
CASE
WHEN Field2 = 'landline' THEN [Field 3] ELSE NULL END AS FIELD23_phone
WHEN Field2 = 'mobile' THEN [Field 3] ELSE NULL END AS FIELD23_mobile
WHEN Field2 = 'email' THEN [Field 3] ELSE NULL END AS FIELD23_email
END
FROM YourTable
Lowell
February 28, 2011 at 7:27 am
Hi Lowell, thanks for the try, but I think this won't fix the problem. There must be something really simple (like calculated or derived fields in Access!).
The structure is similar to this:
1. VIEW in SQL (linked to) > 2. VIEW in ACCESS (sent to) > 3. MAILMERGE in WORD (refers to the fields in 1.
Therefore, in the mailmerge, you need to refer to actual names of fields in SSIL (whatever their names are: i.e. field3).
Another point that I don't get in your code is how you would insert that into the view (see fig attached). I might be wrong but, AFAIK, in the view you just link tables (upper), define fields (mid) or write SQL (lower part of the window).
In the attached file you can see the view structure and SQL, and the table that 'creates' the problem (vwVolunteerContactDetails), since all other tables just contribute with ONE single value to the mailmerge (whereas in this one there can be several phones, emails, etc.).
PS: can't you create 3 calculated fields F1, F2, F3 where F1 exists only when ContactType is Phone (and shows Contactinfo), and same for F2 and F3?
February 28, 2011 at 10:58 am
a_ud (2/28/2011)
PS: can't you create 3 calculated fields F1, F2, F3 where F1 exists only when ContactType is Phone (and shows Contactinfo), and same for F2 and F3?
yes, you can; that's what i was demonstrating...the calculation would use a case statement based on your field that had the contact type(email/mobile/landline)
my snippet would do what you want, i believe.
Lowell
March 1, 2011 at 2:28 am
Thanks, you're probably right and that would be a perfect solution.
I'll try to link that code to my view.
March 16, 2011 at 9:34 am
Lowell (2/28/2011)
I think you can do what you want as a set of case statementslet me know if this model works for you:
SELECT
OtherFields,
CASE
WHEN Field2 = 'landline' THEN [Field 3] ELSE NULL END AS FIELD23_phone
WHEN Field2 = 'mobile' THEN [Field 3] ELSE NULL END AS FIELD23_mobile
WHEN Field2 = 'email' THEN [Field 3] ELSE NULL END AS FIELD23_email
END
FROM YourTable
Pretty sure you can't conditionally name the column like that inside a case statement. If the select returned more than 1 record and Field2 has different values what is the name of the column?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 16, 2011 at 9:45 am
my crappy pseudo code construction was off for the case statement Sean; i meant show what this example does: chop up a single field into 3 seperate fields wit the case stateemnt:
/*--Results
OtherFields FIELD23_phone FIELD23_mobile FIELD23_email
----------- -------------- -------------- --------------
stuff (954) 111-1111 NULL NULL
more stuff NULL (954) 222-2222 NULL
other stuff NULL NULL (954) 333-3333
*/
WITH YourTable(ID,OtherFields,Field2,[Field 3])
AS
(
SELECT 1,'stuff','landline','(954) 111-1111' UNION ALL
SELECT 1,'more stuff','mobile','(954) 222-2222' UNION ALL
SELECT 1,'other stuff','email','(954) 333-3333'
)
SELECT
OtherFields,
CASE WHEN Field2 = 'landline' THEN [Field 3] ELSE NULL END AS FIELD23_phone,
CASE WHEN Field2 = 'mobile' THEN [Field 3] ELSE NULL END AS FIELD23_mobile,
CASE WHEN Field2 = 'email' THEN [Field 3] ELSE NULL END AS FIELD23_email
FROM YourTable
Lowell
March 16, 2011 at 9:47 am
Ahh. gotcha. I got so wrapped up in aliasing the results of the case that I couldn't see the forest through the trees. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 7, 2011 at 10:51 am
Hi Lowell,
I've come across a problem while implementing your code. It works (I expected that!), however, since it's for a mailmerge, when you enter the fields for someone on a doc, some of them will be null. Therefore, the mailmerge will ONLY show 1 value (so back to square one).
So, for instance, if you enter the fields:
FIELD23_PHONE / FIELD23_MOBILE / FIELD23_EMAIL
the mail merge will only show the 1st occurrence, that is:
954- 111 1111
I guess this could be sorted out by forcing in the case that Field3 is not NULL. Could I use then this instruction:
CASE WHEN (Field2 = 'landline' AND [Field 3] IS NOT NULL) THEN [Field 3] ELSE NULL END AS FIELD23_phone ?
Many thanks, a...
July 7, 2011 at 11:11 am
ok, to do that, i think you'll just want to wrap a grouping query , and join that to the original table...
it might look scary, but it's one way to do it:
/*--Results
ID OtherFields ID FIELD23_phone FIELD23_mobile FIELD23_email
1 stuff 1 (954) 111-1111 (954) 222-2222 (954) 333-3333
1 more stuff 1 (954) 111-1111 (954) 222-2222 (954) 333-3333
1 other stuff 1 (954) 111-1111 (954) 222-2222 (954) 333-3333
*/
;WITH YourTable(ID,OtherFields,Field2,[Field 3])
AS
(
SELECT 1,'stuff','landline','(954) 111-1111' UNION ALL
SELECT 1,'more stuff','mobile','(954) 222-2222' UNION ALL
SELECT 1,'other stuff','email','(954) 333-3333'
)
SELECT
YourTable.ID,
YourTable.OtherFields,
SubQueryAlias.*
FROM YourTable
INNER JOIN (
SELECT
ID,
Max(FIELD23_phone) As FIELD23_phone,
Max(FIELD23_mobile) As FIELD23_mobile,
Max(FIELD23_email) As FIELD23_email
FROM (SELECT
ID,
CASE WHEN Field2 = 'landline' THEN [Field 3] ELSE NULL END AS FIELD23_phone,
CASE WHEN Field2 = 'mobile' THEN [Field 3] ELSE NULL END AS FIELD23_mobile,
CASE WHEN Field2 = 'email' THEN [Field 3] ELSE NULL END AS FIELD23_email
FROM YourTable) X
GROUP BY ID
)SubQueryAlias
ON YourTable.ID = SubQueryAlias.ID
Lowell
July 8, 2011 at 3:08 am
Thanks for the prompt reply. I'm not sure I get what goes inside the code, but I'll try.
Essentially, the problem is typical values for this table would be something like:
Field1-Field2-Field3 (3) Field 23 (hypotheticals)
ID -TYPE- INFO
================ ==================
1 - Home - 01234
1 - Mobile - 07756123
2 - Email - abc@email.com
3 - Mobile - 0751234
Now, the problems is:
• If you build with a CASE 3 fields [FIELD23] for everyone (one for each type of contact), when sending that to a mailmerge it will only take the 1st occurrence and show the non-NULL values per line (for person 1, 01234 - NULL-NULL, instead of 01234-07756123).
• Looking at the results, not sure what happens with the grouping query you include.
• All we want is effectively a grouping that shows something like:
ID -Field23_Hom / Field23_Mob / Field23_Email
=======================================
1 - 01234 / 07756123 / NULL
2 - NULL / NULL / abc@email.com
............
so that you can reference all 3 Field23 for each person and get their contact info in the mailmerge.
July 8, 2011 at 5:35 am
yep that's what i figured; my model that i posted last will do what you are asking, but of course you have to adapt it.
I don't think you ever posted actual CREATE TABLE / INSERT INTO statemeents for anyone to be able to test against; as a result you pretty much got examples to adapt instead.
As you get used to the site, you'll see that if you post the exact table structure and give us some sample/fake data to fill it with INSERT INTO commands, we can give you back the tested exact query that will do what you ask with the real data. The more you help us understand the issue, the more we can help.
for an exact, copy paste solution, do the following:
1. paste the actual SQL you are currently using for mailmerge.
2. if the query is coming from just a single table, paste the CREATE TABLE command here as well.
Lowell
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply