Query results will not show on a form or report

[Mabjro]

I have created multiple queries that give the sum of the numbers entered into my [qty] field. Each query has the same function, except the criteria for the records used in the sum changes.

See the following for an example of the queries. You should get a general idea of what I am trying to do even though the syntax is not perfect.

query1=
test1: Sum([tblLabels]![qty])
where [tblLabels]![productNumber]= 1

query2=
test2: Sum([tblLabels]![qty])
where [tblLabels]![productNumber]= 2

and so on...

How can I get the results from the multiple queries to show on one form or report? I can't set the recordsource because the info comes from different queries.

Thank you,

Jonathan

 

[CosmoKramer]

If the only difference in each of these queries is the criteria for [tblLabels]![productNumber], use one query with the In clause:

where [tblLabels]![productNumber] In (1,2,3,4)

Put all of the possible product numbers in the parentheses.




A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[BobJacksonNCI]

Jonathan,

If I'm misunderstanding you incorrectly
you want totals with differing criteria on the same line.

To get sums at runtime, use DSUM.

Syntax

DSum(expr, domain[, criteria])

The DSum function has the following arguments.

Argument Description
expr-An expression that identifies the numeric field whose values you want to total. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.

domain-A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name.

criteria-An optional string expression used to restrict the range of data on which the DSum function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DSum function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DSum function returns a Null.

Put a text box control on your report.
In the control's properties, Data tab, Control Source, enter something like
=DSum("[ProductNumber]", "tblLabels", "[tblLabels]![productNumber]= 1&quot


HTH,
Bob

 

[Mabjro]

I have printed off both of the above responses. I will toy with them early today and post my results later. I think CosmoKramer's (your not the real Kramer, are you?)response will solve my problem. I will have to play with DSUM also as I am sure familiarity with it will be benificial in the future. It appears to be a common tool should be in my toolbox, in a matter of speaking.
Thanks to both of you.
Jonathan

 

[Mabjro]

Typo in the above should read.

It appears to be a common tool that should be in my toolbox, in a matter of speaking.

 

[dhookom]

You should only need one query that will either return multiple rows:
SELECT ProductNumber, Sum([qty]) As SumQty
FROM tblLabels
where [tblLabels]![productNumber] In (1,2);
or one row:
SELECT Abs(Sum([qty]*[ProductNumber]=1)) As SumQty1,
Abs(Sum([qty]*[ProductNumber]=2)) As SumQty2
FROM tblLabels;



Duane
MS Access MVP