Unique date field

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi,

I'm having a problem that looks like this:

Date Name1 Amount Name2
1/22/04 Person A 50 Person V
1/22/04 Person A 150 Person W
1/22/04 Person A 50 Person V
1/23/04 Person A 85 Person V


I'd like the data to look like this:

Date Name1 Amount Name2
1/22/04 Person A 250 Blank (or something similar)
1/23/04 Person A 85 Person V

I know how to do the "group by" "sum" and have gotten:

Date Name1 Amount Name2
1/22/04 Person A 100 Person V
1/22/04 Person A 150 Person W
1/23/04 Person A 85 Person V

Is it possible to do as I'd like?

Thanks,
John
 
Maybe. How do you decide whether it should display Person V or Person W?
Is it that you think Person V is first? There is nothing to determine which
of the records for 1/22/04 is first.

Anyway, do your Totals query an omit Name2. Build a second query to pull
the correct Name2 and join with first query.
 
KARL said:
Maybe. How do you decide whether it should display Person V or Person W?
Is it that you think Person V is first? There is nothing to determine which
of the records for 1/22/04 is first.
Click to expand...
From the title of this thread, I assume it is determined by whether the
Click to expand...
date is unique. I've made a further assumption that the date is unique
to each Name1 (but this isn't clear from the OP's spec because they've
only used only one distinct value for Name1)

SELECT DISTINCT T1.[Date], T1.Name1,
(SELECT SUM(T2.Amount)
FROM Test AS T2
WHERE T2.Name1 = T1.Name1
AND T2.[Date] = T1.[Date]) as total_amount,
IIF(1 = (
SELECT COUNT(*)
FROM Test AS T2
WHERE T2.Name1 = T1.Name1
AND T2.[Date] = T1.[Date]
), T1.Name2, NULL) AS name_2
FROM Test AS T1;
 
Back
Top