SumOFQuantity

  • Thread starter Thread starter Rene
  • Start date Start date
R

Rene

I have following query which calculates total of quantity of all records
with same ID:
SELECT tblInventory.ID, Sum(tblInventory.Quantity) AS SumOfQuantity...

In anothet table I have a field [Required] which is related to ID.

I would like to get:
Shortage: [Required]-[SumOfQuantity]
But then it makes it an Parameterquery.

Is it not possible to make a reference to [SumOfQuantity] in the same
Selectquery?
Or do I have to make a second query for this ?
Or use a recordset ?

Rene
 
Use the other table and this query in a new query joined on ID. Select the
[Required] and then have output field Shortage: [Required]-[SumOfQuantity] in
the grid.
 
Rene,

Comments in-line.


Rene said:
I have following query which calculates total of quantity of all records
with same ID:
SELECT tblInventory.ID, Sum(tblInventory.Quantity) AS SumOfQuantity...

In anothet table I have a field [Required] which is related to ID.

I would like to get:
Shortage: [Required]-[SumOfQuantity]
But then it makes it an Parameterquery.

Is it not possible to make a reference to [SumOfQuantity] in the same
Selectquery?
Click to expand...

No.


Or do I have to make a second query for this ?
Yes.


Or use a recordset ?
Click to expand...

That's another option, but substantially more complicated.
 
I have made a 2nd query in design view and have the result I wanted but...
I would like to use this 2nd query as recourdsource for a form in vba.

1st qryTotal: SELECT tblInventory.ID, Sum(tblInventory.Quantity) AS
SumOfQuantity...
2nd qryShortage: SELECT qryTotal.SumOfQuantity, tblStandard.Required,
[Required]-[SumOfQuantity] AS [Shortage]...

Is it possible not to use a saved query and use the SQL(=qryShortage) as
recourdsource in vba?
When I have only one SQL it is no problem but I do not know how to to it
with the second SQL based on the first.

Now I want something like:
me.recourdsource=qryShortage

Rene
 
Back
Top