Complex SQL query with a group and two rows in one

Question

Ok, I need some help. Usually I am well versed in SQL queries, but this is confusing for me. By the way, this is not homework, this is the real situation in the Access database, and I wrote my requirements below.

---

Here is my table layout. This is in Access 2007, if that matters; I am writing a query using SQL.

Id (primary key)
PersonID (foreign key)
EventDate
NumberOfCredits
SuperCredits (boolean)

There are events that people go to. They can earn regular loans, or super loans, or both at the same event. The SuperCredits column is true if the row represents the number of super credits received at this event, or false if they represent ordinary credits.

So, for example, if there is an event in which 174 people attend, and they earn 3 regular loans and 1 super loan at the event, the following two lines will be added to the table:

ID PersonID EventDate NumberOfCredits SuperCredits
1  174      1/1/2010  3               false
2  174      1/1/2010  1               true

It is also possible that a person could do two things in an event, so there can be more than two columns for one event, and it might look like this:

ID PersonID EventDate NumberOfCredits SuperCredits
1  174      1/1/2010  1               false
2  174      1/1/2010  2               false
3  174      1/1/2010  1               true

Now we want to print the report. Here are the report columns:

PersonID
LastEventDate
NumberOfNormalCredits
NumberOfSuperCredits

The report will have one line per person. The line will show the last event at which the person was present, as well as the normal and super credits that the person earned at this event.

What I ask of you is to write or help me write an SQL query for SELECT data and GROUP BY and SUM () and something else. Or, let me know if for some reason this is not possible, and how to organize my data to make it possible.

---

, , . , , . , , ...

Answer 1

/ (, PersonLastEvents):

select PersonId, max(EventDate) as LastEventDate
from Events
group by PersonId

. Access, , , , .

select l.PersonId, l.LastEventDate, 
  sum(case when e.SuperCredits = 'false' then e.NumberOfCredits end) 
    as NumberOfNormalCredits
  sum(case when e.SuperCredits = 'true' then e.NumberOfCredits end) 
    as NumberOfSuperCredits
from PersonLastEvents l
join Events e on l.PersonId = e.PersonId and l.LastEventDate = l.EventDate
group by l.PersonId, l.LastEventDate

, , , (NormalCredits, SuperCredits), () .