Can anyone give insight on the proper way to store names in a multi
language database? For instance in English we store First, Middle,
Last. In Spanish there could be multiple last names. In Chinese the
whole name is only one name, (fits in one field). Any experience with
something like this?
Peter Cwik
It really depends on the requirements of the application, and whether you
are attempting to separate family name from given name, or preserve name
order for presentation. (In different cultures, the first name is the
family name, whereas in English, the first name is the given name.)
You may have different rules for different localizations, in defining what
is FirstName and LastName, but also have a common FullName field that is
populated in a language-specific or culture-specific manner.
Again, your business requirements will have much to do with the solution.
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Peter Cwik" <cwik4@.cox.net> wrote in message
news:1135790334.343599.101660@.z14g2000cwz.googlegr oups.com...
> Can anyone give insight on the proper way to store names in a multi
> language database? For instance in English we store First, Middle,
> Last. In Spanish there could be multiple last names. In Chinese the
> whole name is only one name, (fits in one field). Any experience with
> something like this?
> Peter Cwik
>
|||In general, "Family Name" and "Given Name" are less confusing than
"First", "Last", "Christian", "Sur", etc on multi-language forms. A
simple way to organize the columns might be something like this:
NameID(pk), FamilyName, GivenName, Name2, Name3,Name4, NameType --
where FamilyName is the only required name field and NameType is a
foreign key describing the naming convention of that particular person.
So long as the order preference is spelled out in the NameType, I don't
see a reason to distinguish between a middle name, second given name,
or second family name. You can use CASE to distinguish between them in
your select statements:
SELECT CASE NameType
WHEN '3a' THEN GivenName +' '+ Name4 +' '+ FamilyName
WHEN '4b' THEN Name2 +' '+ GivenName +' '+ FamilyName
WHEN '5c' THEN FamilyName +' '+ GivenName
ELSE GivenName+' '+FamilyName END AS FullName
>From Table1
(If you want, you could nest the CASEs so that they check fields for
null values before plugging them in so as to allow people to predict
naming conventions that might apply after marriage, confirmation, etc.)
--L
Showing posts with label names. Show all posts
Showing posts with label names. Show all posts
Monday, March 26, 2012
Multi Language
Can anyone give insight on the proper way to store names in a multi
language database? For instance in English we store First, Middle,
Last. In Spanish there could be multiple last names. In Chinese the
whole name is only one name, (fits in one field). Any experience with
something like this?
Peter CwikIt really depends on the requirements of the application, and whether you
are attempting to separate family name from given name, or preserve name
order for presentation. (In different cultures, the first name is the
family name, whereas in English, the first name is the given name.)
You may have different rules for different localizations, in defining what
is FirstName and LastName, but also have a common FullName field that is
populated in a language-specific or culture-specific manner.
Again, your business requirements will have much to do with the solution.
--
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Peter Cwik" <cwik4@.cox.net> wrote in message
news:1135790334.343599.101660@.z14g2000cwz.googlegroups.com...
> Can anyone give insight on the proper way to store names in a multi
> language database? For instance in English we store First, Middle,
> Last. In Spanish there could be multiple last names. In Chinese the
> whole name is only one name, (fits in one field). Any experience with
> something like this?
> Peter Cwik
>|||In general, "Family Name" and "Given Name" are less confusing than
"First", "Last", "Christian", "Sur", etc on multi-language forms. A
simple way to organize the columns might be something like this:
NameID(pk), FamilyName, GivenName, Name2, Name3,Name4, NameType --
where FamilyName is the only required name field and NameType is a
foreign key describing the naming convention of that particular person.
So long as the order preference is spelled out in the NameType, I don't
see a reason to distinguish between a middle name, second given name,
or second family name. You can use CASE to distinguish between them in
your select statements:
SELECT CASE NameType
WHEN '3a' THEN GivenName +' '+ Name4 +' '+ FamilyName
WHEN '4b' THEN Name2 +' '+ GivenName +' '+ FamilyName
WHEN '5c' THEN FamilyName +' '+ GivenName
ELSE GivenName+' '+FamilyName END AS FullName
>From Table1
(If you want, you could nest the CASEs so that they check fields for
null values before plugging them in so as to allow people to predict
naming conventions that might apply after marriage, confirmation, etc.)
--L
language database? For instance in English we store First, Middle,
Last. In Spanish there could be multiple last names. In Chinese the
whole name is only one name, (fits in one field). Any experience with
something like this?
Peter CwikIt really depends on the requirements of the application, and whether you
are attempting to separate family name from given name, or preserve name
order for presentation. (In different cultures, the first name is the
family name, whereas in English, the first name is the given name.)
You may have different rules for different localizations, in defining what
is FirstName and LastName, but also have a common FullName field that is
populated in a language-specific or culture-specific manner.
Again, your business requirements will have much to do with the solution.
--
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Peter Cwik" <cwik4@.cox.net> wrote in message
news:1135790334.343599.101660@.z14g2000cwz.googlegroups.com...
> Can anyone give insight on the proper way to store names in a multi
> language database? For instance in English we store First, Middle,
> Last. In Spanish there could be multiple last names. In Chinese the
> whole name is only one name, (fits in one field). Any experience with
> something like this?
> Peter Cwik
>|||In general, "Family Name" and "Given Name" are less confusing than
"First", "Last", "Christian", "Sur", etc on multi-language forms. A
simple way to organize the columns might be something like this:
NameID(pk), FamilyName, GivenName, Name2, Name3,Name4, NameType --
where FamilyName is the only required name field and NameType is a
foreign key describing the naming convention of that particular person.
So long as the order preference is spelled out in the NameType, I don't
see a reason to distinguish between a middle name, second given name,
or second family name. You can use CASE to distinguish between them in
your select statements:
SELECT CASE NameType
WHEN '3a' THEN GivenName +' '+ Name4 +' '+ FamilyName
WHEN '4b' THEN Name2 +' '+ GivenName +' '+ FamilyName
WHEN '5c' THEN FamilyName +' '+ GivenName
ELSE GivenName+' '+FamilyName END AS FullName
>From Table1
(If you want, you could nest the CASEs so that they check fields for
null values before plugging them in so as to allow people to predict
naming conventions that might apply after marriage, confirmation, etc.)
--L
Multi Language
Can anyone give insight on the proper way to store names in a multi
language database? For instance in English we store First, Middle,
Last. In Spanish there could be multiple last names. In Chinese the
whole name is only one name, (fits in one field). Any experience with
something like this?
Peter CwikIt really depends on the requirements of the application, and whether you
are attempting to separate family name from given name, or preserve name
order for presentation. (In different cultures, the first name is the
family name, whereas in English, the first name is the given name.)
You may have different rules for different localizations, in defining what
is FirstName and LastName, but also have a common FullName field that is
populated in a language-specific or culture-specific manner.
Again, your business requirements will have much to do with the solution.
--
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Peter Cwik" <cwik4@.cox.net> wrote in message
news:1135790334.343599.101660@.z14g2000cwz.googlegroups.com...
> Can anyone give insight on the proper way to store names in a multi
> language database? For instance in English we store First, Middle,
> Last. In Spanish there could be multiple last names. In Chinese the
> whole name is only one name, (fits in one field). Any experience with
> something like this?
> Peter Cwik
>|||In general, "Family Name" and "Given Name" are less confusing than
"First", "Last", "Christian", "Sur", etc on multi-language forms. A
simple way to organize the columns might be something like this:
NameID(pk), FamilyName, GivenName, Name2, Name3,Name4, NameType --
where FamilyName is the only required name field and NameType is a
foreign key describing the naming convention of that particular person.
So long as the order preference is spelled out in the NameType, I don't
see a reason to distinguish between a middle name, second given name,
or second family name. You can use CASE to distinguish between them in
your select statements:
SELECT CASE NameType
WHEN '3a' THEN GivenName +' '+ Name4 +' '+ FamilyName
WHEN '4b' THEN Name2 +' '+ GivenName +' '+ FamilyName
WHEN '5c' THEN FamilyName +' '+ GivenName
ELSE GivenName+' '+FamilyName END AS FullName
>From Table1
(If you want, you could nest the CASEs so that they check fields for
null values before plugging them in so as to allow people to predict
naming conventions that might apply after marriage, confirmation, etc.)
--L
language database? For instance in English we store First, Middle,
Last. In Spanish there could be multiple last names. In Chinese the
whole name is only one name, (fits in one field). Any experience with
something like this?
Peter CwikIt really depends on the requirements of the application, and whether you
are attempting to separate family name from given name, or preserve name
order for presentation. (In different cultures, the first name is the
family name, whereas in English, the first name is the given name.)
You may have different rules for different localizations, in defining what
is FirstName and LastName, but also have a common FullName field that is
populated in a language-specific or culture-specific manner.
Again, your business requirements will have much to do with the solution.
--
Cheers,
'(' Jeff A. Stucker
\
Senior Consultant
www.rapidigm.com
"Peter Cwik" <cwik4@.cox.net> wrote in message
news:1135790334.343599.101660@.z14g2000cwz.googlegroups.com...
> Can anyone give insight on the proper way to store names in a multi
> language database? For instance in English we store First, Middle,
> Last. In Spanish there could be multiple last names. In Chinese the
> whole name is only one name, (fits in one field). Any experience with
> something like this?
> Peter Cwik
>|||In general, "Family Name" and "Given Name" are less confusing than
"First", "Last", "Christian", "Sur", etc on multi-language forms. A
simple way to organize the columns might be something like this:
NameID(pk), FamilyName, GivenName, Name2, Name3,Name4, NameType --
where FamilyName is the only required name field and NameType is a
foreign key describing the naming convention of that particular person.
So long as the order preference is spelled out in the NameType, I don't
see a reason to distinguish between a middle name, second given name,
or second family name. You can use CASE to distinguish between them in
your select statements:
SELECT CASE NameType
WHEN '3a' THEN GivenName +' '+ Name4 +' '+ FamilyName
WHEN '4b' THEN Name2 +' '+ GivenName +' '+ FamilyName
WHEN '5c' THEN FamilyName +' '+ GivenName
ELSE GivenName+' '+FamilyName END AS FullName
>From Table1
(If you want, you could nest the CASEs so that they check fields for
null values before plugging them in so as to allow people to predict
naming conventions that might apply after marriage, confirmation, etc.)
--L
multi from multi
Hi,
in the select i got a cell with strings like Jhon#@.#Yos#@.#Rami and i want to
check in the where cluase if one of the names (jhon, Yos or Rami) is in
(Rami, Mos, Dave (not a close list, i get it from another cell). is that
possible?
can i split the string and check for the name in one select statment?
What do i need to use? can i use decode or case?
SagiHere is an example form Anith
--Modify it for your needs
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
drop table Numbers
"sagi aviram" <crisrobin@.013.net.il> wrote in message
news:440530d7$1@.news.barak.net.il...
> Hi,
> in the select i got a cell with strings like Jhon#@.#Yos#@.#Rami and i want
> to check in the where cluase if one of the names (jhon, Yos or Rami) is in
> (Rami, Mos, Dave (not a close list, i get it from another cell). is that
> possible?
> can i split the string and check for the name in one select statment?
> What do i need to use? can i use decode or case?
> Sagi
>|||I'm trying to do this like that:
SELECT request_id REQUEST_ID, parameter6 NO__OF_DAYS, parameter13 TEAM
FROM KCRT_REQUEST_DETAILS
WHERE request_type_id=30132
and (SUBSTR(replace(CONCAT(parameter13, '#@.#'), '#@.#' , ' in
([p.MULTI].TO_STRING) or '), 1, length(replace(CONCAT(parameter13, '#@.#'),
'#@.#' , ' in ([p.MULTI].TO_STRING) or '))-4) )
p.MULTI resolve to ('jhon', 'Yos', 'Rami')
the string in parameter13 is something like Jhon#@.#Yos#@.#Rami and changing.
i was thinking to add #@.# to the end of the string, replace the #@.# with the
string ' in ([p.MULTI]) or ' and then cut the 4 last letters from the end
and get (after the system will resolve the [p.MULTI] token) this:
and ('Jhon' in ('Jhon', 'Yos', 'Rami') or 'Yos' in ('Jhon', 'Yos', 'Rami')
and that will be great but the [p.MULTI] token include ' so it make it
wrong.
any help in here?
Sagi
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OqTIZNPPGHA.812@.TK2MSFTNGP10.phx.gbl...
> Here is an example form Anith
> --Modify it for your needs
> SELECT IDENTITY(INT) "n" INTO Numbers
> FROM sysobjects s1
> CROSS JOIN sysobjects s2
> GO
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5,33,229,1,22'
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
> from numbers where substring(','+@.Ids,n,1)=','
> AND n < LEN(@.Ids) + 1
> drop table Numbers
>
>
> "sagi aviram" <crisrobin@.013.net.il> wrote in message
> news:440530d7$1@.news.barak.net.il...
>
in the select i got a cell with strings like Jhon#@.#Yos#@.#Rami and i want to
check in the where cluase if one of the names (jhon, Yos or Rami) is in
(Rami, Mos, Dave (not a close list, i get it from another cell). is that
possible?
can i split the string and check for the name in one select statment?
What do i need to use? can i use decode or case?
SagiHere is an example form Anith
--Modify it for your needs
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
drop table Numbers
"sagi aviram" <crisrobin@.013.net.il> wrote in message
news:440530d7$1@.news.barak.net.il...
> Hi,
> in the select i got a cell with strings like Jhon#@.#Yos#@.#Rami and i want
> to check in the where cluase if one of the names (jhon, Yos or Rami) is in
> (Rami, Mos, Dave (not a close list, i get it from another cell). is that
> possible?
> can i split the string and check for the name in one select statment?
> What do i need to use? can i use decode or case?
> Sagi
>|||I'm trying to do this like that:
SELECT request_id REQUEST_ID, parameter6 NO__OF_DAYS, parameter13 TEAM
FROM KCRT_REQUEST_DETAILS
WHERE request_type_id=30132
and (SUBSTR(replace(CONCAT(parameter13, '#@.#'), '#@.#' , ' in
([p.MULTI].TO_STRING) or '), 1, length(replace(CONCAT(parameter13, '#@.#'),
'#@.#' , ' in ([p.MULTI].TO_STRING) or '))-4) )
p.MULTI resolve to ('jhon', 'Yos', 'Rami')
the string in parameter13 is something like Jhon#@.#Yos#@.#Rami and changing.
i was thinking to add #@.# to the end of the string, replace the #@.# with the
string ' in ([p.MULTI]) or ' and then cut the 4 last letters from the end
and get (after the system will resolve the [p.MULTI] token) this:
and ('Jhon' in ('Jhon', 'Yos', 'Rami') or 'Yos' in ('Jhon', 'Yos', 'Rami')
and that will be great but the [p.MULTI] token include ' so it make it
wrong.
any help in here?
Sagi
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OqTIZNPPGHA.812@.TK2MSFTNGP10.phx.gbl...
> Here is an example form Anith
> --Modify it for your needs
> SELECT IDENTITY(INT) "n" INTO Numbers
> FROM sysobjects s1
> CROSS JOIN sysobjects s2
> GO
> DECLARE @.Ids VARCHAR(200)
> SET @.Ids = '5,33,229,1,22'
> SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
> from numbers where substring(','+@.Ids,n,1)=','
> AND n < LEN(@.Ids) + 1
> drop table Numbers
>
>
> "sagi aviram" <crisrobin@.013.net.il> wrote in message
> news:440530d7$1@.news.barak.net.il...
>
Multi column report as a subreport
I have a table report where amoung other things there is a list of names
from another data set. I don't want the names to print in one column because
the list maybe more than one page. I can create a multi column report for
just the names and when printed or print preview will display in multiply
columns (good). But when I add that report as a subreport to my main report
I believe it ignores the subreport's 'report properties' and uses the main
'report's properties'?
Any suggestions?
JohnI found this in 2005 Books online, I would guess that 2000 is the same.
Apparently the column setting is inherited from the parent report.
Denny
SQL Server 2005 Books Online
Writing Multi-Column Reports
Updated: 5 December 2005
You can design a report that uses a multi-column layout, similar to a
traditional newspaper column where data flows down multiple adjacent
columns. A multi-column layout applies to the entire report. It is not
possible to specify a multi-column layout on the top half of the report, and
a tabular layout on the bottom half of the report. When you specify a
multi-column layout, the report server creates each column as a series of
very narrow pages that are rendered in close sequence, giving the appearance
of multiple columns. Properties that you set at the page level are applied
to each column in the report. You can define as many columns you want.
For best results, use data regions that provide repeating rows of data (for
example, table or list box). A list box placed within a multi-column report
will display data from the top left of the page to the bottom left of the
page, and then continue the list in the adjacent column at the top of the
page. If you want to use text boxes or images, put them in a list so that
they repeat in each column.
If you are accustomed to using subreports to embed a separate report within
a parent report, be aware that you cannot use subreports to get the same
outcome in a multi-column layout. In a multi-column report, a subreport
inherits the column settings of the parent report. This means that if you
define a multi-column layout on a subreport, the subreport ignores the
column settings that are specified for it. It also means that you cannot use
subreports to create a free-form or single column layout within the
multi-column report. Subreports that you include in a multi-column report
always use the column settings of the parent report
"johnsh" <johnsh@.axiumae.com> wrote in message
news:%23%23usVHbhGHA.4368@.TK2MSFTNGP03.phx.gbl...
>I have a table report where amoung other things there is a list of names
>from another data set. I don't want the names to print in one column
>because the list maybe more than one page. I can create a multi column
>report for just the names and when printed or print preview will display in
>multiply columns (good). But when I add that report as a subreport to my
>main report I believe it ignores the subreport's 'report properties' and
>uses the main 'report's properties'?
> Any suggestions?
> John
>
from another data set. I don't want the names to print in one column because
the list maybe more than one page. I can create a multi column report for
just the names and when printed or print preview will display in multiply
columns (good). But when I add that report as a subreport to my main report
I believe it ignores the subreport's 'report properties' and uses the main
'report's properties'?
Any suggestions?
JohnI found this in 2005 Books online, I would guess that 2000 is the same.
Apparently the column setting is inherited from the parent report.
Denny
SQL Server 2005 Books Online
Writing Multi-Column Reports
Updated: 5 December 2005
You can design a report that uses a multi-column layout, similar to a
traditional newspaper column where data flows down multiple adjacent
columns. A multi-column layout applies to the entire report. It is not
possible to specify a multi-column layout on the top half of the report, and
a tabular layout on the bottom half of the report. When you specify a
multi-column layout, the report server creates each column as a series of
very narrow pages that are rendered in close sequence, giving the appearance
of multiple columns. Properties that you set at the page level are applied
to each column in the report. You can define as many columns you want.
For best results, use data regions that provide repeating rows of data (for
example, table or list box). A list box placed within a multi-column report
will display data from the top left of the page to the bottom left of the
page, and then continue the list in the adjacent column at the top of the
page. If you want to use text boxes or images, put them in a list so that
they repeat in each column.
If you are accustomed to using subreports to embed a separate report within
a parent report, be aware that you cannot use subreports to get the same
outcome in a multi-column layout. In a multi-column report, a subreport
inherits the column settings of the parent report. This means that if you
define a multi-column layout on a subreport, the subreport ignores the
column settings that are specified for it. It also means that you cannot use
subreports to create a free-form or single column layout within the
multi-column report. Subreports that you include in a multi-column report
always use the column settings of the parent report
"johnsh" <johnsh@.axiumae.com> wrote in message
news:%23%23usVHbhGHA.4368@.TK2MSFTNGP03.phx.gbl...
>I have a table report where amoung other things there is a list of names
>from another data set. I don't want the names to print in one column
>because the list maybe more than one page. I can create a multi column
>report for just the names and when printed or print preview will display in
>multiply columns (good). But when I add that report as a subreport to my
>main report I believe it ignores the subreport's 'report properties' and
>uses the main 'report's properties'?
> Any suggestions?
> John
>
Friday, March 23, 2012
Mulitple Select Statements in One Query
I'm trying to make a query in Access that will search for events in a single month where the events all have different names. I have SELECT Count(*) AS EVENT1 in September FROM blah WHERE Date LIKE Sep and EVENT LIKE 1 and then I have another SELECT COUNT statement for event2 in sept with event2 being the heading for the column and it aparently doesn't like that. I'm sorry, but I'm really new to this so I don't know if I'm explaining my problem correctly.SELECT Event, COUNT(*)
FROM myTable99
WHERE EventDat >= '9/1/2003' AND EventDate < '10/1/2003'
GROUP BY Event
?|||could you show some sample rows of the table, and then some sample result rows that you expect to get
rudy|||number date type
224433 sep fha
224432 sep fha
224424 sep fnma
234443 oct fha
I want it to run the query on September and if type = fha count those and if type = fnma count those so I would want it to return
FHA in Sep FNMA in Sep
2 1
And then I'll run a separate query on Oct with the same types in the headings|||sorry it didn't post correctly that should be a 2 under FHA in sep and a 1 under the FNMA in Sep|||please tell me you did not name your column "date" :eek:
select sum(case when type='fha'
then 1 else 0 end) as "FHA in Sep"
, sum(case when type='fnma'
then 1 else 0 end) as "FNMA in Sep"
from yourtable
where [date] = 'sep'
rudy
http://r937.com/|||Thanks for the advice and yes I labeled my column Date, it was lock_effective_date, but I didn't feel like typing that a bunch of times. So what is the problem with that? Too general?|||reserved word, will cause syntax errors if you aren't careful|||What happens when you get more than those [types]?
And if you don't like type (good for you), better get used to [brackets]|||I have about 20 different types. I've got them labeled as ProgramName right now. And I'll change "date" to something else.
What do the brackets do? Set it apart as being a field name and not some special modifier or something?|||exactamundo|||I hate to ask y'all to spoon feed me, but I tried that code and it didn't work and this really isn't my forte.
SELECT SUM(case when ProductName LIKE 'FHA30*'
then 1 else 0 end) as "FHA in Sep"
, sum(case when ProductName LIKE 'FNMA30*'
then 1 else 0 end) as "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*';
It threw a syntax error.|||As close as I can get:
USE Northwind
GO
CREATE TABLE myTable99 ([number] int, [date] char(3), [type] varchar(5))
GO
INSERT INTO myTable99 ([number], [date], [type])
SELECT 224433, 'sep', 'fha' UNION ALL
SELECT 224432, 'sep', 'fha' UNION ALL
SELECT 224424, 'sep', 'fnma' UNION ALL
SELECT 234443, 'oct', 'fha'
GO
SELECT [type] + ': ' +CONVERT(varchar(10), COUNT(*)) AS Denorm INTO #bk_Temp
FROM myTable99
WHERE [date] = 'sep'
GROUP BY type
DECLARE @.Result varchar(8000)
SELECT @.Result = ''
SELECT @.Result = @.Result + Denorm + ' ' FROM #bk_Temp
SELECT RTRIM(@.Result)
GO
DROP TABLE myTable99
GO
DROP TABLE #bk_Temp
GO|||I'm sorry, but that's totally past my understanding. I've already got the table in there I was just trying to make heads and tails of the code that r937 supplied for the if-then statement in the SUM section:
select sum(case when type='fha'
then 1 else 0 end) as "FHA in Sep"
, sum(case when type='fnma'
then 1 else 0 end) as "FNMA in Sep"
The syntax error it throws is a missing operator in the statement:
SUM(case when ProductName LIKE 'FHA30*'
then 1 else 0 end)
I think the rest of it is okay.|||Originally posted by poontz13
I'm sorry, but that's totally past my understanding.
My sample is cut and paste-able, and should run in QA with no problem..
check it out...
You're problem though, is everytime you get a new type, you'll be hosed..|||your use of asterisks as wildcards is consistent with microsoft access, not sql server
if it's actually access, then shame on you for posting an access question in the sql server forum without mentioning it
this would explain why the CASE structure throws an error
in access you have to use IIF
rudy|||I'm sure you're getting frustrated with me Brett, but again, I'm really not too good at coding; to my understanding, your code looks like it is creating a table and then picking the number, date and type and putting them in there. I have a table with about 450 entries where the number is the primary key and I just want it to search through the table and count the number of items with a specific productName that occurs in a given month. I tried the following code, but it returned the number 72 which is the total number of items in September:
SELECT COUNT(ProductName LIKE 'FHA30*') AS ["FHA in Sep"],
COUNT(ProductName LIKE 'FNMA30*') AS ["FNMA in Sep"]
FROM Calculations2
WHERE LockDate LIKE '*Sep*';
So obviously my count isn't specifying the productName correctly and it is just couting all items.
Any other advice? I do appreciate it.|||AW, CRAP
my bad
sincere apologies
yes, you did mention access
hang on a sec and i'll rewrite the query for ya...
rudy|||I'm sorry! Yes it's access, I'm new to the forum and I guess I didn't see the Access forum...so, I need to use:
SELECT SUM(IIF ProductName LIKE 'FHA30*'
then 1 else 0 end) as "FHA in Sep"
, sum(IIF ProductName LIKE 'FNMA30*'
then 1 else 0 end) as "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*';|||SELECT SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA in Sep"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*'|||That worked! (Not that I doubted it would once I phrased it right to y'all) I truly appreciate it, this will help me out a lot.|||great
once again, i apologize for chiding you, when in fact you did mention access right in your first sentence
rudy|||No problem...you're just trying to help me.
I do have one other question.
Instead of hard coding different queries for each month...can I prompt the user when they click on the query to type in the month they want to find?|||yes
is LockDate an actual date/time field?
SELECT [enter month 1 - 12] as "month"
, SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA"
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12]|||Originally posted by r937
yes
is LockDate an actual date/time field?
SELECT [enter month 1 - 12] as "month"
, SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA"
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12]
Okay, I understand your code, right now it's set up as 01-Sep-03. I left it as text when I imported it from excel to access just for uniformity sake. So if I prompt the user to enter 1-12, I guess I'll have to define somewhere that 1 = Jan, 2 = Feb, ... or whatever my date column has as the abreviation for the month? If so where do I put the declaration.|||Access...ok now I understand the confusion...
I was going to give you this
USE Northwind
GO
CREATE TABLE myTable99 ([number] int, [date] char(3), [type] varchar(5))
GO
INSERT INTO myTable99 ([number], [date], [type])
SELECT 224433, 'sep', 'fha' UNION ALL
SELECT 224432, 'sep', 'fha' UNION ALL
SELECT 224424, 'sep', 'fnma' UNION ALL
SELECT 234443, 'oct', 'fha'
GO
DECLARE @.SQL varchar(2000), @.Select varchar(1000)
SELECT @.SQL = ' SELECT ', @.Select = ''
SELECT @.Select = @.Select + 'SUM(CASE WHEN [type] = ' + '''' + [Type] + '''' + ' THEN 1 ELSE 0 END) AS ' + [Type]+','
FROM (SELECT DISTINCT [type] FROM myTable99 WHERE [date] = 'sep') AS A
SELECT @.Select = SUBSTRING(@.Select,1,LEN(@.Select)-1)
SELECT @.SQL = @.SQL + @.Select
SELECT @.SQL = @.SQL + ' FROM myTable99 WHERE [date] = ' + '''' + 'sep' + ''''
SELECT @.SQL
EXEC(@.SQL)
DROP TABLE myTable99
GO
But since you don't have QA, it's useless...
What it does though is what you want..it's dynamic sql and build a select based on data in your table...
I've done stuff in Access.(sortof) using the make query function...
But it's been a loooooong time...|||I got ya...thanks for trying to help though, so now I guess you can see why I was extremely confused. I sort of understood what your code was saying, but got lost in there somewhere. Thanks again.|||you will get lots more mileage, and simpler queries, if you convert your column from text to datetime
meanwhile, try this --
... where LockDate like '*' + [enter month xxx] + '*'
and don't forget to change it in the SELECT as well
rudy|||Originally posted by r937
you will get lots more mileage, and simpler queries, if you convert your column from text to datetime
meanwhile, try this --
... where LockDate like '*' + [enter month xxx] + '*'
and don't forget to change it in the SELECT as well
rudy
I'll change it to date/time, makes sense, so when the user inputs the month number, what tells access that 4 equates to the 4th month and not the 4th day or 4th year? Also, if I use what you stated above, the user would then have to know that September is Sep right? Like if they typed in Sept then it wouldn't work. Am I right? So again your method for date/time would be better.|||>> what tells access that 4 equates to the 4th month
because the query would be using the MONTH() function on the datetime field|||But again...ALL of this is a presentation layer issue that can probably be more easily handled with code...
You're using Access 2000 and Forms?
AND, if you are, don't you expect that you need n number of controls to accept the results?
Your row length is variable
And if the User type in a wrong date, it'll yell at them...|||Oh I see...in the WHERE statement month() is a function, I thought it was being treated like an object as declared in the SELECT like in JAVA. I'm going to try this...I appreciate it.|||The error now is "The SELECT statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrect."
Here's my code:
SELECT [enter month 1 - 12] as "Month",
SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS ["FHA 30 in Sep"],
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12];|||SELECT [enter month 1 - 12] as myMonth,
SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS [FHA 30 in Sep],
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12];
Spaces in column names is not a good thing...
EDIT Look at that, the same exact time...
double quote in Access mean data, [] mean columns|||take the square brackets off the alias
... AS "FHA 30 in Sep"|||HECK YEAH!!! It works...thanks soo much. I sure you haven't heard the last from me. Thanks. Rudy...do you have a problem with me emailing you if I have a specific question? If it's general I'll obviously post it to the forum.|||no, no problem, although i have a hard time seeing why it wouldn't be general enough to let others take a crack at it
i mean, look at brett, how eager he was, there are bound to be others, and i may not always be available...
rudy
FROM myTable99
WHERE EventDat >= '9/1/2003' AND EventDate < '10/1/2003'
GROUP BY Event
?|||could you show some sample rows of the table, and then some sample result rows that you expect to get
rudy|||number date type
224433 sep fha
224432 sep fha
224424 sep fnma
234443 oct fha
I want it to run the query on September and if type = fha count those and if type = fnma count those so I would want it to return
FHA in Sep FNMA in Sep
2 1
And then I'll run a separate query on Oct with the same types in the headings|||sorry it didn't post correctly that should be a 2 under FHA in sep and a 1 under the FNMA in Sep|||please tell me you did not name your column "date" :eek:
select sum(case when type='fha'
then 1 else 0 end) as "FHA in Sep"
, sum(case when type='fnma'
then 1 else 0 end) as "FNMA in Sep"
from yourtable
where [date] = 'sep'
rudy
http://r937.com/|||Thanks for the advice and yes I labeled my column Date, it was lock_effective_date, but I didn't feel like typing that a bunch of times. So what is the problem with that? Too general?|||reserved word, will cause syntax errors if you aren't careful|||What happens when you get more than those [types]?
And if you don't like type (good for you), better get used to [brackets]|||I have about 20 different types. I've got them labeled as ProgramName right now. And I'll change "date" to something else.
What do the brackets do? Set it apart as being a field name and not some special modifier or something?|||exactamundo|||I hate to ask y'all to spoon feed me, but I tried that code and it didn't work and this really isn't my forte.
SELECT SUM(case when ProductName LIKE 'FHA30*'
then 1 else 0 end) as "FHA in Sep"
, sum(case when ProductName LIKE 'FNMA30*'
then 1 else 0 end) as "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*';
It threw a syntax error.|||As close as I can get:
USE Northwind
GO
CREATE TABLE myTable99 ([number] int, [date] char(3), [type] varchar(5))
GO
INSERT INTO myTable99 ([number], [date], [type])
SELECT 224433, 'sep', 'fha' UNION ALL
SELECT 224432, 'sep', 'fha' UNION ALL
SELECT 224424, 'sep', 'fnma' UNION ALL
SELECT 234443, 'oct', 'fha'
GO
SELECT [type] + ': ' +CONVERT(varchar(10), COUNT(*)) AS Denorm INTO #bk_Temp
FROM myTable99
WHERE [date] = 'sep'
GROUP BY type
DECLARE @.Result varchar(8000)
SELECT @.Result = ''
SELECT @.Result = @.Result + Denorm + ' ' FROM #bk_Temp
SELECT RTRIM(@.Result)
GO
DROP TABLE myTable99
GO
DROP TABLE #bk_Temp
GO|||I'm sorry, but that's totally past my understanding. I've already got the table in there I was just trying to make heads and tails of the code that r937 supplied for the if-then statement in the SUM section:
select sum(case when type='fha'
then 1 else 0 end) as "FHA in Sep"
, sum(case when type='fnma'
then 1 else 0 end) as "FNMA in Sep"
The syntax error it throws is a missing operator in the statement:
SUM(case when ProductName LIKE 'FHA30*'
then 1 else 0 end)
I think the rest of it is okay.|||Originally posted by poontz13
I'm sorry, but that's totally past my understanding.
My sample is cut and paste-able, and should run in QA with no problem..
check it out...
You're problem though, is everytime you get a new type, you'll be hosed..|||your use of asterisks as wildcards is consistent with microsoft access, not sql server
if it's actually access, then shame on you for posting an access question in the sql server forum without mentioning it
this would explain why the CASE structure throws an error
in access you have to use IIF
rudy|||I'm sure you're getting frustrated with me Brett, but again, I'm really not too good at coding; to my understanding, your code looks like it is creating a table and then picking the number, date and type and putting them in there. I have a table with about 450 entries where the number is the primary key and I just want it to search through the table and count the number of items with a specific productName that occurs in a given month. I tried the following code, but it returned the number 72 which is the total number of items in September:
SELECT COUNT(ProductName LIKE 'FHA30*') AS ["FHA in Sep"],
COUNT(ProductName LIKE 'FNMA30*') AS ["FNMA in Sep"]
FROM Calculations2
WHERE LockDate LIKE '*Sep*';
So obviously my count isn't specifying the productName correctly and it is just couting all items.
Any other advice? I do appreciate it.|||AW, CRAP
my bad
sincere apologies
yes, you did mention access
hang on a sec and i'll rewrite the query for ya...
rudy|||I'm sorry! Yes it's access, I'm new to the forum and I guess I didn't see the Access forum...so, I need to use:
SELECT SUM(IIF ProductName LIKE 'FHA30*'
then 1 else 0 end) as "FHA in Sep"
, sum(IIF ProductName LIKE 'FNMA30*'
then 1 else 0 end) as "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*';|||SELECT SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA in Sep"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*'|||That worked! (Not that I doubted it would once I phrased it right to y'all) I truly appreciate it, this will help me out a lot.|||great
once again, i apologize for chiding you, when in fact you did mention access right in your first sentence
rudy|||No problem...you're just trying to help me.
I do have one other question.
Instead of hard coding different queries for each month...can I prompt the user when they click on the query to type in the month they want to find?|||yes
is LockDate an actual date/time field?
SELECT [enter month 1 - 12] as "month"
, SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA"
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12]|||Originally posted by r937
yes
is LockDate an actual date/time field?
SELECT [enter month 1 - 12] as "month"
, SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA"
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12]
Okay, I understand your code, right now it's set up as 01-Sep-03. I left it as text when I imported it from excel to access just for uniformity sake. So if I prompt the user to enter 1-12, I guess I'll have to define somewhere that 1 = Jan, 2 = Feb, ... or whatever my date column has as the abreviation for the month? If so where do I put the declaration.|||Access...ok now I understand the confusion...
I was going to give you this
USE Northwind
GO
CREATE TABLE myTable99 ([number] int, [date] char(3), [type] varchar(5))
GO
INSERT INTO myTable99 ([number], [date], [type])
SELECT 224433, 'sep', 'fha' UNION ALL
SELECT 224432, 'sep', 'fha' UNION ALL
SELECT 224424, 'sep', 'fnma' UNION ALL
SELECT 234443, 'oct', 'fha'
GO
DECLARE @.SQL varchar(2000), @.Select varchar(1000)
SELECT @.SQL = ' SELECT ', @.Select = ''
SELECT @.Select = @.Select + 'SUM(CASE WHEN [type] = ' + '''' + [Type] + '''' + ' THEN 1 ELSE 0 END) AS ' + [Type]+','
FROM (SELECT DISTINCT [type] FROM myTable99 WHERE [date] = 'sep') AS A
SELECT @.Select = SUBSTRING(@.Select,1,LEN(@.Select)-1)
SELECT @.SQL = @.SQL + @.Select
SELECT @.SQL = @.SQL + ' FROM myTable99 WHERE [date] = ' + '''' + 'sep' + ''''
SELECT @.SQL
EXEC(@.SQL)
DROP TABLE myTable99
GO
But since you don't have QA, it's useless...
What it does though is what you want..it's dynamic sql and build a select based on data in your table...
I've done stuff in Access.(sortof) using the make query function...
But it's been a loooooong time...|||I got ya...thanks for trying to help though, so now I guess you can see why I was extremely confused. I sort of understood what your code was saying, but got lost in there somewhere. Thanks again.|||you will get lots more mileage, and simpler queries, if you convert your column from text to datetime
meanwhile, try this --
... where LockDate like '*' + [enter month xxx] + '*'
and don't forget to change it in the SELECT as well
rudy|||Originally posted by r937
you will get lots more mileage, and simpler queries, if you convert your column from text to datetime
meanwhile, try this --
... where LockDate like '*' + [enter month xxx] + '*'
and don't forget to change it in the SELECT as well
rudy
I'll change it to date/time, makes sense, so when the user inputs the month number, what tells access that 4 equates to the 4th month and not the 4th day or 4th year? Also, if I use what you stated above, the user would then have to know that September is Sep right? Like if they typed in Sept then it wouldn't work. Am I right? So again your method for date/time would be better.|||>> what tells access that 4 equates to the 4th month
because the query would be using the MONTH() function on the datetime field|||But again...ALL of this is a presentation layer issue that can probably be more easily handled with code...
You're using Access 2000 and Forms?
AND, if you are, don't you expect that you need n number of controls to accept the results?
Your row length is variable
And if the User type in a wrong date, it'll yell at them...|||Oh I see...in the WHERE statement month() is a function, I thought it was being treated like an object as declared in the SELECT like in JAVA. I'm going to try this...I appreciate it.|||The error now is "The SELECT statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrect."
Here's my code:
SELECT [enter month 1 - 12] as "Month",
SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS ["FHA 30 in Sep"],
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12];|||SELECT [enter month 1 - 12] as myMonth,
SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS [FHA 30 in Sep],
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12];
Spaces in column names is not a good thing...
EDIT Look at that, the same exact time...
double quote in Access mean data, [] mean columns|||take the square brackets off the alias
... AS "FHA 30 in Sep"|||HECK YEAH!!! It works...thanks soo much. I sure you haven't heard the last from me. Thanks. Rudy...do you have a problem with me emailing you if I have a specific question? If it's general I'll obviously post it to the forum.|||no, no problem, although i have a hard time seeing why it wouldn't be general enough to let others take a crack at it
i mean, look at brett, how eager he was, there are bound to be others, and i may not always be available...
rudy
Subscribe to:
Posts (Atom)