Not knowing what you have in your fields, I had to kind of guess, but
something that jumped out at me was the occurrence of both [IngName1]
and [IngName4] in the same record.
[production] Table fields, including some apparently repeating groups:
ProductionDate
PlantNo
ShiftNO
IngName1
IngName2
IngName3
IngName4
IngWeight1
IngWeight2
IngWeight3
IngWeight4
From what you said, my guess is that they both contain the same kind of
stuff. So, I moved them and the [IngWeight...] fields to a separate
Table, as I illustrate below.
I also made some other changes, such as moving the names of materials to
a Table where they are listed just once, and other Tables make reference
to them via a linking key.
Incidentally, you never mentioned units of measure, so for simplicity I
just assumed they were all in kilograms (or tonnes, or ounces) and
omitted that part. If you DO have any different units, what I show here
won't work, and you'll have to convert all of them to compatible units
to do the arithmetic.
OK, first Table is a stripped-down version of your [Production] Table,
omitting all the repeating groups.
[Production] Table Datasheet View:
Production_ID PlantNo ProductionDate ShiftNO
------------- ------- -------------- -------
-2106329789 A 1/12/2006 1
346130729 A 1/13/2006 1
1585204936 A 1/12/2006 2
As I mentioned, I also put the ingredient names into a Table in which
each is mentioned only once. Other Tables needing to refer to "Cement"
use the key number 763168138 instead of the name "Cement"; this avoids
problems arising from misspelling it as "Cemnet" and not noticing the
mistake until after some wrong totals have been reported. Misspelling
the key number will likely cause an instant error message. (But I don't
expect that you will ever see or use the key numbers directly; the Combo
Boxes or other controls will display only the names.)
[Ingredient_Table] Table Datasheet View:
Ingredient_Table_ID Name
------------------- ----
-1292946867 WhiteCement
763168138 Cement
I created a new Table to list all the materials used during any shift
appearing in the [Production] Table. Its contents might look like this:
[Ingredients Used] Table Datasheet View:
Production_ID Ingredient_Table_ID IngWeight
------------- ------------------- ---------
-2106329789 763168138 3
346130729 -1292946867 10
346130729 763168138 10
1585204936 763168138 7
However, as I said, the key values aren't for human beings to look at,
so I defined a Lookup Query to display, instead of these meaningless
[Production_ID] values, a more usable name for each linked record in the
[Production] Table. This Lookup Query I defined thus:
[QL_Production] SQL:
SELECT Production.Production_ID,
[Production]![PlantNo] & " "
& [Production]![ProductionDate]
& "-" & [Production]![ShiftNO] AS Name
FROM Production
ORDER BY Production.PlantNo, Production.ProductionDate,
Production.ShiftNO;
The displayed names look like this, with the plant name ("A") followed
by date and shift. You could, of course, define this differently, to
display any meaningful name that both is short and uniquely identifies a
record in the [Production] Table.
[QL_Production] Query Datasheet View:
Production_ID Name
------------- --------------
-2106329789 A 1/12/2006-1
1585204936 A 1/12/2006-2
346130729 A 1/13/2006-1
To apply a lookup property to a foreign-key field, open the Table in
Table Design View, select the foreign key, select Lookup, and set the
properties as follows:
Display Control = List Box
Row Source = QL_Production
Column Count = 2
Column Widths = 0;1
I also applied a Lookup propery to the foreign key
[Ingredient_Table_ID], but in that case I looked up the name directly
from the [Ingredient_Table] Table. (Same as above, but set Row Source =
Ingredient_Table .)
With Lookup properties set on the foreign keys, the Table is much more
legible:
[Ingredients Used] Table Datasheet View:
Production_ID Ingredient_Table_ID IngWeight
------------- ------------------- ---------
A 1/12/2006-1 Cement 3
A 1/13/2006-1 WhiteCement 10
A 1/13/2006-1 Cement 10
A 1/12/2006-2 Cement 7
Warning: Some people dislike using Lookup properties because they hide
the fact that the fields actually contain the key numbers, not the names
that show up on the datasheet. So if that bothers you, don't use the
Lookup property. But I shall use them for all the foreign keys in my
examples here, such as in the following Table, showing materials
received into inventory. ([Ingredient_Table_ID] is a foreign key
referring to [Ingredient_Table], and I use a Lookup property here.)
[Incoming_Inventory] Table Datasheet View:
Incoming_ PurchaseDate Ingredient_ IngWeight
Inventory_ID Table_ID
--------- ------------ ----------- ---------
-1602432313 1/13/2006 WhiteCement 20
-1167407823 1/12/2006 Cement 30
-919687266 1/13/2006 Cement 15
Having moved the repeating groups out of the [Production] Table, the
Queries to track the changes to inventory for each type of material are
fairly simple. (OK, they appear a bit lengthy here, but just imagine
how spaghetti-like they'd be if you had to include special code for each
of the 4 "IngName/IngWeight" groups in the original Table! It would be
way more difficult to understand.)
First Query in this list grabs date, material, and weight from the
[Incoming_Inventory] Table. The key value, [Ingredient_Table_ID],
probably isn't needed, since I assume the material names are unique, but
it doesn't hurt anything to leave it in here. You don't need to look at
it. (If you do decide to remove it, you'll have to revise the later
Queries that depend on it.)
[Q_01 Incoming Inventory] SQL:
SELECT Incoming_Inventory.PurchaseDate,
Ingredient_Table.Name,
Sum(Incoming_Inventory.IngWeight) AS TotalWeight,
Ingredient_Table.Ingredient_Table_ID
FROM Ingredient_Table INNER JOIN Incoming_Inventory
ON Ingredient_Table.Ingredient_Table_ID
= Incoming_Inventory.Ingredient_Table_ID
GROUP BY Incoming_Inventory.PurchaseDate,
Ingredient_Table.Name,
Ingredient_Table.Ingredient_Table_ID;
So now we have a list of additions to inventory.
[Q_01 Incoming Inventory] Query Datasheet View:
PurchaseDate Name TotalWeight Ingredient_Table_ID
------------ ---- ----------- -------------------
1/12/2006 Cement 30 763168138
1/13/2006 Cement 15 763168138
1/13/2006 WhiteCement 20 -1292946867
We do the same for deductions from inventory, BUT I attached a minus
sign to them so that I could easily calculate totals.
[Q_02 Outgoing Inventory] SQL:
SELECT Production.ProductionDate,
Ingredient_Table.Name,
Sum(-[IngWeight]) AS TotalWeight,
Ingredient_Table.Ingredient_Table_ID
FROM Production INNER JOIN
(Ingredient_Table INNER JOIN [Ingredients Used]
ON Ingredient_Table.Ingredient_Table_ID
= [Ingredients Used].Ingredient_Table_ID)
ON Production.Production_ID
= [Ingredients Used].Production_ID
GROUP BY Production.ProductionDate,
Ingredient_Table.Name,
Ingredient_Table.Ingredient_Table_ID
ORDER BY Production.ProductionDate;
These fields are similar to those in the previous Query. (There's a
reason for making them match -- I want to combine all of them using a
Union Query.) The minus signs indicate that these values act to reduce
the inventory, instead of increasing it.
[Q_02 Outgoing Inventory] Query Datasheet View:
ProductionDate Name TotalWeight Ingredient
_Table_ID
-------------- ---- ----------- ----------
1/12/2006 Cement -10 763168138
1/13/2006 Cement -10 763168138
1/13/2006 WhiteCement -10 -1292946867
A Union Query combines all these.
[Q_03 DailyInventoryChanges] SQL:
SELECT [Q_01 Incoming Inventory].PurchaseDate,
[Q_01 Incoming Inventory].TotalWeight,
[Q_01 Incoming Inventory].Ingredient_Table_ID
FROM [Q_01 Incoming Inventory]
UNION ALL
SELECT [Q_02 Outgoing Inventory].ProductionDate,
[Q_02 Outgoing Inventory].TotalWeight,
[Q_02 Outgoing Inventory].Ingredient_Table_ID
FROM [Q_02 Outgoing Inventory]
ORDER BY [Q_01 Incoming Inventory].PurchaseDate,
[Q_01 Incoming Inventory].Ingredient_Table_ID;
Since I don't intend to look at this, I left the raw key values in and
omitted the ingredient names.
[Q_03 DailyInventoryChanges] Query Datasheet View:
PurchaseDate TotalWeight Ingredient_Table_ID
------------ ----------- -------------------
1/12/2006 -10 763168138
1/12/2006 30 763168138
1/13/2006 -10 -1292946867
1/13/2006 20 -1292946867
1/13/2006 -10 763168138
1/13/2006 15 763168138
The next Query adds up the positive and negative changes for each item
during each day. You could, if you wish, revise the Queries to account
for the materials by shift, or by plant name, but I didn't do that here.
[Q_04 Daily Inventory By Ingredient] SQL:
SELECT Ingredient_Table.Name,
[Q_03 DailyInventoryChanges].PurchaseDate,
Sum([Q_03 DailyInventoryChanges].TotalWeight)
AS [Daily Change]
FROM Ingredient_Table
INNER JOIN [Q_03 DailyInventoryChanges]
ON Ingredient_Table.Ingredient_Table_ID
= [Q_03 DailyInventoryChanges].Ingredient_Table_ID
GROUP BY Ingredient_Table.Name,
[Q_03 DailyInventoryChanges].PurchaseDate
ORDER BY Ingredient_Table.Name,
[Q_03 DailyInventoryChanges].PurchaseDate;
Adding up the total changes for each material for each day (among all
plants and including all shifts for that day), we get the following list:
[Q_04 Daily Inventory By Ingredient] Query Datasheet View:
Name PurchaseDate Daily Change
---- ------------ ----- ------
Cement 1/12/2006 20
Cement 1/13/2006 5
WhiteCement 1/13/2006 10
Now, suppose we want to estimate the total amount of material on hand at
any given time. The following Query can do that:
[Q_05 Cumulative Inventory] SQL:
SELECT [Q_04 Daily Inventory By Ingredient].Name,
[Q_04 Daily Inventory By Ingredient].PurchaseDate,
Sum(Q_04A.[Daily Change])
AS [Cumulative Inventory]
FROM [Q_04 Daily Inventory By Ingredient]
INNER JOIN
[Q_04 Daily Inventory By Ingredient] AS Q_04A
ON [Q_04 Daily Inventory By Ingredient].Name
= Q_04A.Name
WHERE (((Q_04A.PurchaseDate)
<=[Q_04 Daily Inventory By Ingredient]
![PurchaseDate]))
GROUP BY [Q_04 Daily Inventory By Ingredient].Name,
[Q_04 Daily Inventory By Ingredient].PurchaseDate
ORDER BY [Q_04 Daily Inventory By Ingredient].Name,
[Q_04 Daily Inventory By Ingredient].PurchaseDate;
.... with the following results, showing a running balance:
[Q_05 Cumulative Inventory] Query Datasheet View:
Name PurchaseDate Cumulative
Inventory
---- ------------ ----------
Cement 1/12/2006 20
Cement 1/13/2006 25
WhiteCement 1/13/2006 10
I should warn you here that my copy of Access gave me some (spurious)
error messages, such as "Query Too Complex", but since it also worked
properly at times, I assume it was just a problem with my copy and I
hope that you will have no trouble using it. If you DO have trouble,
you could try converting some of the Queries to Make-Table Queries, use
them to construct temporary Tables, and base the subsequent Queries on
those Tables, but that's a lot of hassle if you don't need to do it. So
I suggest trying to run Queries similar to mine unless they give you
problems.
HTH.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
