Summing mutiple items in a query

[papadega3000]

I have a sum query issue I am trying to work out and I have trouble trying to coordinate it.I am trying to build sum queries inside one select query that would allow me to sum point values in a separate table. The problem I am foreseeing is that I have three tables all connected with realationships. The structure with the fields is below:

Table1:
Test Case
Description
...


Table2:
Run
Description
test_case
...

Table3:
Task
Points
Group (Run)
Date1
Date2
Date3
...

Relationships:
Table1.Test_case-->Table2.test_case ( 1-many)
Table2.Run--> Table3.Group ( 1-many)


I want to be able to sum the total amount of points in a test case. Currently I am able to sum the number of points per run by doing a sum query on SUM(Table3.Points) in a select query that queries Table2 and Table3. But I try to do that with select query for Table1 and Table2 the query returns the same sum for all test cases. That sum is equal to the point total for the 1st entry in Table1.
Can I add a criteria somehow to make the sum work for each Test Case entry in Table1?

Also in regards to this same summing issue I spawned a second issue. I wanted to run a sum query on Table3 that would allow me to sum all the entries in that table that had valid entries in the Date1 field. I tried the following criteria.

Not IsNull([Date1])

The query came back with the same output as the above sum query.

What I am trying to accompolish essentially is the to get two sums out of the query. The first sum would be to sum all of the points totals for each Test Case. The other being able to sum point totals based of if a Date1 Field being filled in for each Task.

I am pretty sure I can do this in one query and just manipulate the data in the form of expressions and sum queries.

I am still searching through this forum for valid suggestions. But any thoughts will be welcome.

Thanks in advance.

 

[Ziggy1]

first make your Sum query and then make another query and join the sum table to the query.

for the date criteria try... Is not null ...by itself don't put the date1 field in the criteria

 

[papadega3000]

Ziggy1,


Thank you for your relpy. I will try your suggestions.

Although I have a question on your first suggestion.

You state to make the sum query first which I have done. Then your second suggestion is to join sum table to the query. So If I understand correctly you want to join the test case field to the query. Something like this:

Sum1: Sumquery JOIN Test Case

or Do I need to specify the Table before the Field

Sum1: Sumquery JOIN Table1.Test Case

I am will give this a shot in the morning because I have some prior commitments however, I think I understand what you saying just kind of questionable on implementation. If I get a chance I will work it tonight.

Thanks again.

 

[Ziggy1]

A query can be joined to a table the same way 2 tables are joined, and 2 queries can be joined...actually more than 2.

without having your data to work with it's harder to visualize...but one thing to watch out for is when you add the table/queries if a default join appears due to relationships..depending on what you want to do you may want to delete it and join to another field

also there are 3 different join types...double click on the join line and pick the one that applies.

I use the Query design grid and then edit accordingly..you can switch to SQL view in design grid. If you still don't get what I mean..post a sample DB with some mock data

 

[papadega3000]

I did some reading and I totally understand what you presented and believe it will work. I actually tried implementing it however, when I run the query it comes back with nothing so I think I just need to switch the type of JOIN I am doing. I will keep at it and get back to you if I come across any new issues.

 

[papadega3000]

Ziggy1,

I am still having issues with summing with the join query. I posted a zipped version of the application I am making (well most of it I had to cut the file size down to upload it) But the neccessary tables and queries are there to look at.

What I am trying to do is sum point values of the tasks in relation to the test case. these are separate tables and currently I have two queries called StatusTestQuery and StatusRunQuery. StatusRunQuery actually does sum the correct value of points for each run but when I do the same thing for the StatusTestQuery it give the sum of only the first field for both test cases in the database. If you execute both the queries side by side you will see what I mean. If you could assist me or make a example query that would be helpful.

Also, I tried the IsNotNull and that expression did not work. I am currently exploring the uses of the Nz function in an IIF statement.


Thanks again for your help.

 

[papadega3000]

Hello,

I have figured out my joining problem.. The joins I have in place were fine I was just using the wrong field in my query to get the sum. The join I had was Test_Case.Test_case joined with Run.Test_case and the other join was between Run.run and Task.Group. I was running the query on Test_Case.Test_case and Task.Points to get the sum of the point for each test case. I changed the query to Run.Test_case and Sum of Task.Points and it yielded the correct answer.

Even though I figured it out. Thanks for pointing me in the right direction.