Calculations in queries

[Guest]

Boy I feel like a simpleton sometimes. I am working on a query (or queries,
I suspect) to calculate whether the # parts on hand is less than the # parts
required.

In the parts table, I have a field for parts required
In a different table I maintain inventory transactions (parts added, parts
used). This table is connected to the parts table by unique ID#

So, I've done a query that pulls part number, the qty required (from the
parts table) and then has an expression (parts on hand) that calculates the
parts added - parts used).

Once I have that, I want to build a query that will feed a report to
identify which parts have fewer parts on hand than required (i.e. if the
machine breaks down and you have to rebuild it, do you have enough "bolts" on
hand to build a new machine).

Well, I did a second query that uses the first one and tried to do another
expression subtracting the OnHand from the QtyRequired. And then set the
criteria to >0. A couple problems have crept up. One, as I've played
around, I get an error that this isn't part of the aggregate function (I've
tried several: Expression, Sum, Where, etc.). Also, in the first query, I
want it to drop a Zero value in to the QtyAdded - Qty Used (even if there are
no records in that table because there have been no transaction). That would
help the calculation in the second query.

Gosh, am I explaining this well at all? Can anyone help?

 

[Guest]

Please post the SQL statements for the queries that you have now.

 

[Guest]

I don't know how to post the SQL statements. But I can better describe the
two queries. The first has three "columns":

1st: PartName (from part table)
2nd: Qty (this indicates the Qty required and is in the part table)
3rd: On Hand: Sum([InvAcqQty])-Sum([InvUsedQty]) (these are from the
inventory transaction table and is where I am calculating Parts on Hand.
This is where I'd like to have it enter a 0 if there has been no records
added to the transaction table indicating that there have been no parts
acquired)


The second query has 4 "columns"
1st: PartName
2nd: Qty
3rd: On Hand (calculated in the above query)
4th: Short: [Qty]-[On Hand] Here is where I try to identify which parts are
short quantities needed. This runs fine (although only calculates when there
is a value in On Hand. If there is none, it leaves this field blank, even
though there is a value in Qty. Since I only want to display those parts
what are short inventory I try putting a >0 in the criteria -- that's when I
get the error that it is not part of the aggregate function. By the way, the
Total row in the column is set to Expression.

Does this help? If you can tell me how to post the SQL statements (I'm
assuming they are somewhere behind the scenes), I'd be happy to do that.

Thanks!

 

[Guest]

Well, I've found a way around it, but it is taking 3 queries. Maybe that's
just how it has to be. I have the first two I mentioned. I was able to get
zeros in the calculated field by using an IIF statement.

Then in qry 2 I just calculate the amount short, without adding the criteria

0 (I kept getting the error that it wasn't part of the aggregate function if

I put the criteria in).

THen I built a 3rd query in which I could put the criteria (once it was not
working off a calculated field).

Messy, but it seems to work.

I don't know how to post the SQL statements. But I can better describe the
two queries. The first has three "columns":

1st: PartName (from part table)
2nd: Qty (this indicates the Qty required and is in the part table)
3rd: On Hand: Sum([InvAcqQty])-Sum([InvUsedQty]) (these are from the
inventory transaction table and is where I am calculating Parts on Hand.
This is where I'd like to have it enter a 0 if there has been no records
added to the transaction table indicating that there have been no parts
acquired)


The second query has 4 "columns"
1st: PartName
2nd: Qty
3rd: On Hand (calculated in the above query)
4th: Short: [Qty]-[On Hand] Here is where I try to identify which parts are
short quantities needed. This runs fine (although only calculates when there
is a value in On Hand. If there is none, it leaves this field blank, even
though there is a value in Qty. Since I only want to display those parts
what are short inventory I try putting a >0 in the criteria -- that's when I
get the error that it is not part of the aggregate function. By the way, the
Total row in the column is set to Expression.

Does this help? If you can tell me how to post the SQL statements (I'm
assuming they are somewhere behind the scenes), I'd be happy to do that.

Thanks!

Please post the SQL statements for the queries that you have now.