Set limit on number of records that can have a particular field value

[romeluc]

I'm not sure if this should go in a new thread. I need to ask another question.

I want to do a quarry that makes a calculation based on my Universities table.

The calculation should:
• Use only the records that have Apply = True
• Subtotal by Country value
• Subtotal = Sum([Application costs] where Centralized = False) + [Application cost] where Centralized = True counted only once

I'm having trouble with the last clause above. I want the records with Centralized ON to be counted only once in the total. The only solution I could think of is to create two quarries, one for Centralized ON and one for OFF, each calculating the total (or just stating the [Application costs] value in the Centralized ON case). Then a third quarry to sum the costs from these two quarries.

I'm sure there is a better way.

 

[romeluc]

UPDATE:

I wrote some SQL code but I keep getting an error

SELECT  Country,
    ( Sum([Application cost (AED)]) - 
                            SELECT  Sum([Application cost (AED)])
                            FROM Universities
                            WHERE Apply = TRUE AND Centralized = True
                            GROUP BY Country 
    ) AS Total

FROM Universities
WHERE Apply = TRUE
GROUP BY Country

I'm getting a syntax error from this. I tested the subquery and it works. I suspect that the problem is that it returns an entire column instead of a single value.

 

[romeluc]

Update:

I was able to change my database design and get the same functionality I wanted.

SELECT  Universities.Country, (Sum([Application Cost (AED)] + Constants.[Cntr Application Cost (AED)] ) ) AS Tot
FROM Universities, Constants
WHERE  Universities.Country = Constants.Country
        AND Apply = TRUE
GROUP BY Universities.Country

Ideally I wanted to add the [Cntr App...] field outside the sum() but that gives an error. I hope this does what I think it does -- it seems to.

There is a bug, however. The [Application Cost...] field should be null or 0 when [Centralized] is ticked, for it to work. I'm thinking of writing code to enforce this constraint.


UPDATE:
IT doesn't work.