Subtracting two select count values can't yield a negative result

[tmp]

Hi!

First of all I'd like to thank whoever takes their time to respond, this issue has been bugging me for several hours now.

I should begin by saying that I'm not at all familiar with Microsoft Access, and only barely familiar with SQL. I've done some websites in the past, and managed to get the results I want by some heavy googling and testing, but this time it has me beat.

Basically I only want to take yesterday's registred starts and stops, and then subtract the latter from the first. I thought this would be a piece of cake, but no...

The code I have atm is;

SELECT COUNT (*)
FROM REGISTRY
WHERE (REGISTRY.[START])=Date()-1
-
(SELECT COUNT (*)
FROM REGISTRY
WHERE (REGISTRY.[STOP])=Date()-1);

which I thought would do just that. And it does, until it reaches 0... My problem here is that it doesn't give me a negative result (which I need, if the amount of stops for a given day is higher than the amount of starts). However, if I just do

SELECT 1-2;

I get -1, so obviously the limitation isn't in the Microsoft Access SELECT function.

So, long question short; How the hell do I get the above query to give me a negative result?

HUGE, HUGE thanks if anyone can help me!

 

[plog]

When life gives you a problem, you can't just look at the result, see that its wrong, check the batteries in your calculator and give up.

Dig. First, find out what the correct answer is based on your data. Do this manually, open your table and count how many records had a start day of yesterday. Do the same to find out how many had a stop day of yesterday. Subtract those 2.

Next run just the first SELECT query and see if that number matches what you got when you manually added it. Next run the second SELECT query and see if that number matches what you got when you manually added it.

 

[tmp]

Hi Plog, thanks for your answer!

Sorry for failing to say that in the initial post, but I've tested every part of the query, which works.

Both the main query and the subquery gives the correct result. And as long as the stops are fewer than the starts (meaning, as long as the sum is higher than 0) the query works. But once it hits 0 it doesn't go any lower, which means that if I have one start and six stops, I don't get the result -5, but rather 0.

 

[plog]

I think I can do this in 1 query. Try this SQL and see if it gives the right answer:

SELECT Sum(IIf(Date()-1=[START],1,0)-IIf(Date()-1=[STOP],1,0)) AS Total
FROM REGISTRY;

Since all your data is from the same table, there's no need to do 2 queries. I essentially made the different WHERE clauses of your 2 queries their own field and then subtracted the results.

 

[tmp]

I think I can do this in 1 query. Try this SQL and see if it gives the right answer:

SELECT Sum(IIf(Date()-1=[START],1,0)-IIf(Date()-1=[STOP],1,0)) AS Total
FROM REGISTRY;

Since all your data is from the same table, there's no need to do 2 queries. I essentially made the different WHERE clauses of your 2 queries their own field and then subtracted the results.

You TRULY are a life saver! THANK YOU! Took me a few minutes (about an hour) to figure out exactly how it worked, but now it does exactly what I want (even though a tad bit slowly since it goes through the entire table), but I don't care. It works!

Thanks thanks thanks!