Re: help on query for a report

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks for your reply, while I go through and digest what both of you have
responded with, I have to back up to my ing1 through ing4 fields. My
production manager uses my production FORM to input the daily production of
two different plants. on part of the form he has to input the mix design used
to make a certain block. a mix design would consist of 3 to 4 different
ingredients eg. cement, sand, stone, and slag. I have a table wich has all
the possible ingredients and on the form there is a combo box that he uses.
when he chooses an ingredient it automatically populates a default value for
weight and pulls a cost value from a query that stores the latest cost for
that particular ingredient. From what both have stated a concern about having
ing1 ing2 ing3 and ing4 is this something I should revamp before going on to
creating this report. The manager has to see all ingredients on his form
when he types them in. if they all have the same field name would he not just
be overwriting each ingredient and end up with only one ingredient on that
days production. Thank you for your help.

Bart

"Vincent Johns" wrote:

> 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 Data*** 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 Data*** 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 Data*** 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 Data*** 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 Data*** 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 data***. 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 Data*** 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 Data*** 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 Data*** 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 Data*** 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 Data*** 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 Data*** View:
>
> Name PurchaseDate Cumulative
> Inventory
.

Quantcast