Sum Column with IF

[uapak90]

Hey Everyone,
I have a slight problem.
I am making a database for the Microbiology department for my work, and I have several tables. I want to make a query using these fields:

Date
Room/Location
NumberOfTimes

What the field number of times is , the number of times they checked the samples that day. So in the query, I want these fields to show up:

Date
Room/Location
Total number of samples(and a field I create myself) that will multiply the [NumberOfTimes] * 7.

That works out perfectly , for every day i see the total number ofsamples for that day.

Now I want to make a separate query that would group the different locations and add up the number of samples just for that room.
When I do that
I do GroupBy [Room/Location]
and Sum [TotalNumberOfSamples]
but it gives me a HUGE number, very irrelavant
do you know whats the issue here?

 

[lespaul]

What's your exact SQL for these two queries? Why would you need to multiple by 7? Don't you just need to SUM the NumberofTimes?

SELECT Date, Room/Location, SUM(NumberOfTimes) FROM TableName
GROUP BY Date, Room/Location

Leslie

Have you met Hardy Heron?

 

[uapak90]

thanks for the quick reply
the reason i had to multiply by 7 is cuz for every time they would go, they would check 7 samples, so if they went in 3 times, they would have checked 21 samples..
but i think me summing up a query that was related to a field that was multiplying by 7 messed it up.
I did what you told me to
Group by Room/Location
Total Samples: Sum (numberoftimes)

then in another query
Room/Location
Total : [TotalSamples]*7

that worked
thanks for your help!!