Multiple Queries

[SafetyGuy]

Good Day,

I have a database that has a series of queries that group information from the table based on a specific ID and then Sum the cost field. Three queries all the same.

Example:

ID Cost
Group Sum

What I am trying to do is create a query that Sums up the Cost field for all three sperate queries based on a specific ID. Would be simple, however, sometimes a table may not have that ID to reference, so it returns a blank query for that table, which effects the other two queries that do have information by not showing them at all. How do I get the query to put a 0 in the field that there may not be records for so that I can get a total cost? Thanks in advance!

 

[arnelgp]

you can create a parameter query:

PARAMETERS [Enter ID] Long;
SELECT ID,
NZ((SELECT SumOfCost FROM qry1 WHERE ID = [Enter ID]), 0) +
NZ((SELECT SumOfCost FROM qry2 WHERE ID = [Enter ID]), 0) +
NZ((SELECT SumOfCost FROM qry3 WHERE ID = [Enter ID]), 0) +
...
...
FROM TableOrQueryWithID;

 

[SafetyGuy]

Arnel,

Thanks for the code, so far it has worked and done exactly what I was asking, but I found another problem while attempting to add the values in the fields. I created another expression field:

Expr1: [Total 1]+[Total 2]

Instead of adding the numbers together, it places the values of each field next to each other like this:

What I want:

1+2=3

What it does:

1+2=12 (Essentially 1&2 put together)

What have I done wrong here?

Edit:

I noticed that I was missing the "+" at the end of the statements, however, now the SQL will not work, it says that the Select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

 

[Galaxiom]

What I want:

1+2=3

What it does:

1+2=12 (Essentially 1&2 put together)

What have I done wrong here?

I initially thought you were using text fields to store numbers.

However on reading the previous posts maybe the problem is that NZ is returning a string. If so then convert it to number.

CLng(Nz(whatever,0)) + ........

 

[arnelgp]

together with Mr.Galaxiom:

Expr1: Val("0" & [Total 1])+Val("0" & [Total 2])

 

[SafetyGuy]

Edit: Posted this before seeing the second response, let me try the expression and see what it does.

Okay,

I did a quick check of everything, I did find one field that was text and not a number. I fixed that and it is still giving me the same problem. I created an expression without that field, but it still does the same thing without it. I traced it back to the initial query where the cost is created. Basically, a Qty is Multiplied by the cost in the first query using an expression. In this table, one ID can have multiple costs. That expression develops a number and works fine. I then use another query to Group the ID's and then Sum all costs associated to that ID, so I get a Group and Sum. The sum is the number that I am using in the final query (the one I am working on now). As far as I can tell, everything is a number, unless expressions in other query's are not generated as numbers, even though they work as such.

 

[SafetyGuy]

together with Mr.Galaxiom:

Expr1: Val("0" & [Total 1])+Val("0" & [Total 2])

Alright,

This worked perfectly! Thanks for your help! You guys are awesome peoples.

SafetyGuy