Hi,

I have the following table:

Categorie   ID   Date         Time     Demand    RemDemand
A           01  01.01.2003    19:35     34            ?
A           02  03.04.2003    10:34     12            ?
A           03  01.01.2003    10:25     52            ?
B           04  01.01.2003    11:28     18            ?
C           05  23.04.2003    17:54     09            ?

ID is the primary key. 

I am trying to calculate the remaining demand for each ID. This is the
sum of B.Demand of all datasets with the same category, same date and
larger time:

Categorie   ID   Date         Time     Demand    RemDemand
A           01   01.01.2003   19:35      34          34
B           02   03.04.2003   10:34      12          12
A           03   01.01.2003   10:25      52         105
A           04   01.01.2003   11:28      19          53
C           05   23.04.2003   17:54      09          09

I tried the following query:

SELECT B.Categorie, B.ID, B.Date, B.Time, Sum(B.Demand) AS
RemDemand INTO Temp
FROM Customers AS A, Customers AS B
GROUP BY B.Categorie, B.ID, B.Date, B.Time
HAVING (((A.Categorie)=B.Categorie) And ((A.Date)=B.Date) And
((A.Time)>=B.Time);

This doesn't seem to work. I get an error message saying that B.ID is
not destinct (I am not using the English Version of Access).

What would be the correct query?

Best regards,

Jennifer Borck

Hi, Jennifer:

Excusez-moi, but the data you present in the "results" appears to be
inconsistent with the "input" data.  For the sake of consistency and
expediency, I've constructed an Access 2002 table called Customers
with the following entries:

Categorie   ID   Date         Time     Demand 
A           01  01.01.2003    19:35     34    
B           02  03.04.2003    10:34     12    
A           03  01.01.2003    10:25     52    
A           04  01.01.2003    11:28     19    
C           05  23.04.2003    17:54     09    
 
ID is still the table's primary key, but I've swapped a couple of the
values in the first column and corrected one value in the final
"Demand" column.

Basically your question is about constructing a query that adds a
further "computed" column for "remaining demand" or RemDemand.

The clearest way to state this is probably using a dependent query,
rather than as a join, but since you've already gone down the path of
thinking about this as a join (and since Access is better with joins
than dependent queries), let's complete the journey:

SELECT A.Categorie, A.ID, A.Date, A.Time,
       A.Demand, SUM(B.Demand) As RemDemand
FROM Customers As A, Customers As B
WHERE A.Categorie = B.Categorie
 AND  A.Date = B.Date
 AND  A.Time <= B.Time
GROUP BY A.Categorie, A.ID, A.Date, A.Time, A.Demand
ORDER BY A.ID

You can simply paste this into the SQL view of the Query designer of
Access.  The results should look, as you expected, like this:

Categorie ID    Date     Time    Demand    RemDemand
A          1  1/01/2003  19:35    34        34
B          2  4/03/2003  10:34    12        12
A          3  1/01/2003  10:25    52        105
A          4  1/01/2003  11:28    19        53
C          5  4/23/2003  17:54    9         9

albeit with your default European date format (rather than my American
one).

The HAVING clause (which you attempted to use) would filter on the
records _after_ the GROUP BY clause has worked its magic.  Note that I
used simply a WHERE clause, which acts on the "raw join" before any
GROUP BY totaling takes place.  The final ORDER BY clause is mostly
cosmetic, to put the output records in key order.

Please let me know if I can further clarify the SQL statement or any
other aspect of my answer.

regards, mathtalk-ga

Excellent. Thank you!