Hi again,

The search engine has at last located your 'Summation' post; I copy and
paste the SQL here.

SELECT tblInventoryA.Batchcode, tblProd1A.QtyMade, Sum(tblKitsSent.QtySent)
AS SumOfQtySent, tblInventoryA.LossedorTossed,
[QtyMade]-nz([QtySent])-nz([LossedorTossed]) AS OnHand
FROM tblProd1A LEFT JOIN (tblInventoryA LEFT JOIN tblKitsSent ON
tblInventoryA.Batchcode=tblKitsSent.Batchcode) ON
tblProd1A.Batchcode=tblInventoryA.Batchcode
GROUP BY tblInventoryA.Batchcode, tblProd1A.QtyMade,
tblInventoryA.LossedorTossed, [QtyMade]-nz([QtySent])-nz([LossedorTossed]);

What you are trying to do is obvious. Why you need the NZ function is also
obvious. However I stared long and hard at the code wondering why it worked
at all and why you did not keep getting the aggregate function message.

OK, let me suggest some SQL code that will do what you want provided that
tblInventoryA has zero or one rows for the BatchCode, that is there are not
multiple fields for LossedorTossed for each BatchCode. Oh, by the way, if
you omit the second argument of NZ the function returns a zero-length string
which is not what you want when summing numbers

SELECT tblProd1A.Batchcode, Sum(NZ(tblKitsSent.QtySent,0)) AS TotalSent,
First(NZ(tblInventoryA.LossedorTossed,0)) AS QtyScrapped,
First(NZ(tblProd1A.QtyMade,0)) AS TotalMade,
[TotalMade]-[TotalSent]-[QtyScrapped] AS QtyOnHand
FROM (tblProd1A LEFT JOIN tblInventoryA ON tblProd1A.BatchCode =
tblInventoryA.BatchCode) LEFT JOIN tblKitsSent ON tblInventoryA.BatchCode =
tblKitsSent.Batchcode
GROUP BY tblProd1A.BatchCode;

The use of NZ for the QtyMade aggregate is probably redundant; there will
always be a non null value. You could consider changing the join between
tblProd1A and tblInventoryA to be INNER JOIN if you only want rows in your
result set where an inventory record exists as well as a production record.

Have fun.

Rod

"Rod Plastow" wrote:

AG / Jeff,

As I understand it the requirement is not uncommon. Many industries -
aircraft spares, pharmaceuticals , foodstuffs - need to track their
production and distribution by batch. Where this requirement seems a little
different is that there are three production processes that need (?) to be
kept separate.

I went back to the drawing board and came up with four (perhaps five) tables
as follows.

Inventory Item. This table has a PK that is the item id and contains all
common attributes such as description. It is I think akin to Jeff's 'Main'
table.

Production Process (or Type). AG, this is akin to your A, B & C. I would
expect three rows on this table. The Inventory Item table contains a foreign
key to the Production Process if each item is manufactured by one and only
one process. Otherwise you need to resolve the many:many relationship with a
cross-reference table. (Hence the possible fifth table mentioned above.)

Batch. This is a child table of Production Process. If your batch
numbering is unique across all processes then the batch number is sufficient
as a PK, otherwise you need to append the PK of Production Process. The
table attributes include a foreign key for Inventory Item. The table
contains all the production related data.

Inventory. This has the same key(s) as Batch and strictly 3rd Normal Form
suggests both tables should become one. However there is a case for
separating the data as the purpose of the two tables and their life cycles
are completely different. This table also has Inventory Item as a foreign
key. The table contains all the storage/stocking related data, shelf lives,
quantities on hand, etc.

If you follow all the relationships I think there will be no problem in
enforcing relational integrity and specifying cascade update and delete as
appropriate. This proposal does not solve the redundancy problem that will
occur on the Batch table, but I believe the extent of that problem is now
much reduced. If its still bad then sub typing is the way to go.

Hope this helps,

Rod

PS Sorry AG, I cannot follow up you Summation post as the search engine
keeps hanging.

"Jeff Boyce" wrote:

If your main table holds the MainID and ThingType, and that's it, one of
your sub-tables can hold a SubTableID, MainID, and all the attributes
specific to that table's sub-type. Each of the others hold the same.

I don't see how you'd end up with orphans if you first create a MainID for
the Thing, then add a sub-table record with the MainID (as a foreign key),
it's own SubTableID (the primary key), and whatever attributes apply.

The next Thing you add to the Main table might be a different type, so after
adding a MainID in the Main table, you'd have to add the new sub-table
record in the appropriate (different) subtable.

Where are the orphans coming in?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Access Greenhorn" wrote in
message news
Jeff, you possibly give me hope.

I am not familiar with sub-typing in an Access context. I'm assuming that
I
would put the fields common to all the production tables into one table
and
then class each type of item as a subtype and hold the details of the
batches
in the subtype tables.

But how is this all structured such that I would avoid my current problem
of
not being able to reinforce referential integrity due to orphans?

Thanks,
AG