Line item query

I have a query that I am trying to write that will get demographic
information from one table and then get journal entries from another
table. However, I want all the journal entries to be concatenated
together so in the end I only have one record for each person.
Any ideas?
Thanks!!use coalesce|||I tried the COALESCE function. It still returns mutiple rows for each
record.|||This is what I am dealing with:
Demographics Table
Person (PK)
Name
Address
City
State
Journal Table
Person (FK)
Journal
Each person can have only one record in Demographics. But there may be
multiple records for each person in Journal. I need to take all the
records from Journal, match them up to the records in Demographics and
then output it all as one record per person.
Thanks for the help.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
multiple records[sic] for each person in Journal.<<
What you attempted to post makes no sense. Rows are not anything like
records, But ignoring that, if this "person_<something>" is a key,
then BY DEFINITION there can be multiple rows per person in the Journal
table.
If I understand what you mean, that is called violating First Normal
Form and we do not do this in an RDBMS. You want to use a 1950's COBOL
file system with variant records and an OCCURS clause.|||What CELKO means to say is you can do this in the application rather than
the database. Applications work well for these, not so much with database
queries.
If you are on SQL 2005 you can use the XML functionality to concatenate the
values together. Do a search in this newsgroup for XML and concatenation,
and you'll find a neat trick that makes this very simple.
If you are not on 2005 yet, I think you are stuck with a cursor, which can
be written simpler and more efficiently in most programming languages.
"CJEN" <christopher.t.davis@.gmail.com> wrote in message
news:1146667896.994197.34100@.u72g2000cwu.googlegroups.com...
> This is what I am dealing with:
> Demographics Table
> Person (PK)
> Name
> Address
> City
> State
> Journal Table
> Person (FK)
> Journal
> Each person can have only one record in Demographics. But there may be
> multiple records for each person in Journal. I need to take all the
> records from Journal, match them up to the records in Demographics and
> then output it all as one record per person.
> Thanks for the help.
>|||I might have misunderstood what you're trying to do, but what about
something like this:
CREATE TABLE People(personid int, name varchar(50))
CREATE TABLE Journals(entrydate datetime, PersonID int, entry
varchar(200))
INSERT INTO People
SELECT 1, 'Rod'
UNION ALL
SELECT 2, 'Jane'
UNION ALL
SELECT 3, 'Freddy'
INSERT INTO Journals
SELECT '2006-04-02', 1, 'Up above the streets and houses'
UNION ALL
SELECT '2006-04-01', 1, 'etc...'
UNION ALL
SELECT '2006-04-06', 2, 'I think Rod and Freddy are conspiring against
me'
UNION ALL
SELECT '2006-04-07', 2, 'I must strike first...'
GO
CREATE FUNCTION GetJournal(@.PersonID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.TotalJournal varchar(8000)
SET @.TotalJournal = ''
SELECT @.TotalJournal = @.TotalJournal + ', ' + Entry FROM Journals
WHERE PersonID = @.PersonID
RETURN @.TotalJournal
END
GO
SELECT PersonID, Name, dbo.GetJournal(PersonID)
FROM People
DROP FUNCTION GetJournal
DROP TABLE Journals
DROP TABLE People
GO

Line Chart Color

Hello,

My line chart displays all lines in a single color (the series color). I'd like a single color per item in the series just like the bar chart. Is there a way to change this? Or is the only differentiation the markers?

Michael

To put different colors for lines in line chart, Please select chart properties-goto Data-under values goto Edit-Apperance-goto Series Style...-select Border and Line-Color. give suitable color here. (and i think line and marker will have the same color.)|||

Merin,

I did see that option. But doing that changes the colors for ALL lines in my chart. I'd like my series to have different colors, if possible. Any ideas?

Michael

|||for each value u need to select colors. u may have different values inside data in chart properties , for each case u need to follow the same path and choose different color.it worked for me. i hope it will work for u also|||

Ugh, that sounds a lot messier than I thought, especially since my series are all dynamic. Can I reference the color pallete using an array? Or perhaps I'll wait to SRS 2008 with the Dundas charts.

Michael

|||What you mean by dynamic series?|||I mean I have multiple lines on the chart, one for each series value. All the lines are the same color.|||i am trying to do the same thing too.

i was able to for a 100% stacked column chart, using multiple data values, and still specify a color for each value in the stacked column chart (3 of them in my case).

however for my current line chart with only 1 data value, the default colors are automatic according to a color scheme, but i want to be able to define the colors. i was thinking of the following methods using coding under the chart properties -? data -? values (double click the value item) -? edit chart value -? appearance -? series style -? border and line -? color -? ?x

(refer to code snippet)

and the color will be hardcoded to the value string of the data. alternative you can put it as a database column. these are just my ideas. would be great if someone out there has a better solution ... always open to learn.

rev-boy

=IIf(
Fields!DATA_COLUMN.Value = "Type 1", "Green",
IIf
(
Fields!DATA_COLUMN.Value = "Type 2", "Red",
IIf
(
Fields!DATA_COLUMN.Value = "Type 3", "Brown", "Black"
)
)
)

Line Chart Color

Hello,

My line chart displays all lines in a single color (the series color). I'd like a single color per item in the series just like the bar chart. Is there a way to change this? Or is the only differentiation the markers?

Michael

To put different colors for lines in line chart, Please select chart properties-goto Data-under values goto Edit-Apperance-goto Series Style...-select Border and Line-Color. give suitable color here. (and i think line and marker will have the same color.)|||

Merin,

I did see that option. But doing that changes the colors for ALL lines in my chart. I'd like my series to have different colors, if possible. Any ideas?

Michael

|||for each value u need to select colors. u may have different values inside data in chart properties , for each case u need to follow the same path and choose different color.it worked for me. i hope it will work for u also|||

Ugh, that sounds a lot messier than I thought, especially since my series are all dynamic. Can I reference the color pallete using an array? Or perhaps I'll wait to SRS 2008 with the Dundas charts.

Michael

|||What you mean by dynamic series?|||I mean I have multiple lines on the chart, one for each series value. All the lines are the same color.|||i am trying to do the same thing too.

i was able to for a 100% stacked column chart, using multiple data values, and still specify a color for each value in the stacked column chart (3 of them in my case).

however for my current line chart with only 1 data value, the default colors are automatic according to a color scheme, but i want to be able to define the colors. i was thinking of the following methods using coding under the chart properties -? data -? values (double click the value item) -? edit chart value -? appearance -? series style -? border and line -? color -? ?x

(refer to code snippet)

and the color will be hardcoded to the value string of the data. alternative you can put it as a database column. these are just my ideas. would be great if someone out there has a better solution ... always open to learn.

rev-boy

=IIf(
Fields!DATA_COLUMN.Value = "Type 1", "Green",
IIf
(
Fields!DATA_COLUMN.Value = "Type 2", "Red",
IIf
(
Fields!DATA_COLUMN.Value = "Type 3", "Brown", "Black"
)
)
)