Combining fields

Not concatenation, more... err.. I don't know what you'd call it.

SELECT
DISTINCT [C01241 Opened].[Col004] AS OpenerEmail,
[C01241 External Data].[DMCEMAIL] AS ExternalDataEmail,
[C01241 Internal Data].[Col15] AS InternalDataEmail
FROM [C01241 Opened]
LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]

(Apologies for the table/col names, this is all very temporary)

So I've got a table, [C01241 Opened], which details all the people who registered. Those people might turn up in table [C01241 External Data], or they might turn up in [C01241 Internal Data]. Yes, they will always be in one or the other, and no, they won't appear in both.

At the moment, I just pull in the email address. But the client, of course, wants a whole bunch of fields that occur in the 'original data' tables: Firstname, Lastname, Company, Favourite color, etc.

What I want to know is if - and how - I can make the query output one column for each of the required fields, but populate it from either of the two 'original data' tables, depending on where their email address pops up in.

Does that make sense?look into union if I understand your question corretly this should work|||I might not be understanding UNION correctly, but if I do, then I should have added that the two 'original data' tables have absolutely no similarities in structure. Does this make a difference?

Edit:
(sorry, that was a stupid thing to say and I've just realised why :rolleyes: Thanks :) )|||Use the Coalesce function:
SELECT DISTINCT
[C01241 Opened].[Col004] AS OpenerEmail,
Coalesce([C01241 External Data].[DMCEMAIL], [C01241 Internal Data].[Col15]) AS DataEmail
FROM [C01241 Opened]
LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]

Column Totals / Sum by Date

HI,
I am new to RS and I am running into some problems create reports. What I
would like to do is create a report that will count all distinct rows for a
"Users" column for every single date. I am able to get the total users from
the "Users" column but the problem is getting a running list of totals by
date. There is no date field in the database. There is a date field for
enrollment and unenroll but these are not the dates I am looking for. I
would like every single date to be totaled.
Do you know of any examples of this on the web or an example that you could
send me?
Any help would be great!
ThanksYou can't report on anything that's not in your data source. It sounds like
you're trying to get a count of users for each date. You'll have to solve
that in your source query first, then you can report on it. My suggestion
is to create a new reference date table with a record for each date in the
range you want to report on. That's only 365 records per year, so make as
many years as you want. Then join to the Users table on ReferenceDate
between EnrollDate and UnenrollDate.
A nice benefit of having a reference date table is that you can put other
data in each record as well, such as week number, quarter, fiscal year and
calendar year, for easy grouping. Yeah, it's denormalized, but makes
reporting a snap. It's easy to do this is in Excel, then import the data
into SQL.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"ACD" <ACD@.discussions.microsoft.com> wrote in message
news:66C00F58-9E89-456B-9931-5E2E5E964645@.microsoft.com...
> HI,
> I am new to RS and I am running into some problems create reports. What
> I
> would like to do is create a report that will count all distinct rows for
> a
> "Users" column for every single date. I am able to get the total users
> from
> the "Users" column but the problem is getting a running list of totals by
> date. There is no date field in the database. There is a date field for
> enrollment and unenroll but these are not the dates I am looking for. I
> would like every single date to be totaled.
> Do you know of any examples of this on the web or an example that you
> could
> send me?
> Any help would be great!
> Thanks