[BethN]

I added a field for donated items in the Products Subform. I would like that total to be reflected in the summary report. I tried entering the following expression in an unbound box:

=Sum([Inventory Transactions]![UnitsReceived])-Sum([Inventory Transactions]![UnitsSold])-Sum([Inventory Transactions]![UnitsShrinkage])-Sum([Inventory Transactions]![UnitsDonated])-Sum([Inventory Transactions]![UnitsGivenAway])

When I try to run the report, I receive a box titled "Enter Parameter Value" and a field titled "Inventory Transactions!UnitsReceived"

I am new to databases. Please help!

[ScottGem]

When you get a parameter prompt like that it means that Access can't find the object being referenced so it assumes you want to supply it on the fly, so it prompts you for it. Usually this means you spelled something incorrectly. Check your formula for the spelling of the UnitsReceived field.

On a separate note. Your database could be better designed. Instead of having separate fields for the type of inventory transaction, you should have a field for transaction type. The values of that field would be pulled from a lookup table that would look like this:

tluTransType
TransTypeID
TransType

And the data like this:
ID Type
1 Received
2 Donated
26 Sold
27 Shrinkage
28 Given Away

The reason I have a gap in the codes is so types with an ID >=26 are outgoing and the rest are incoming. This makes it easy to calculate inventory. The way I do it is with a series of queries like this:

SELECT TransactionID, ProductID, IIF(TransTypeID >25, Quantity*-1,Quantity) As AdjQty
FROM Transactions;

This gives you the quantity as either a positive or negative value depending on the direction of the transaction. Next I use:

SELECT ProductID, SUM(AdjQty) As QOH
FROM qryAdjQty
GROUP By ProductID;

This will give you the quantity on Hand (QOH) by product.

[BethN]

ScottGem,

Thanks for your reply, but I'm honestly more lost with your code. I was using the template Inventory Control. It gives the fields sold and shrinkage and the summary report takes those into account but when I added my own fields, i.e. UnitsDonated and tried to create the expression with the expression builder, I ran in to trouble.
The spelling should be correct and I didn't type it in, but used it from the expression builder.

I tried to find the Units on Hand expression, but couldn't. If you could help some more I would appreciate it.

Thanks!

[ScottGem]

There are two ways to do Inventory Control. One is transactional, where each movement of stock, either in or out is recorded. The other way is do periodic physical counts. I'm not sure which way this template is working. In a transactional system, each transaction should be done as I described. Also, sometimes Microsoft templates are not designed according to best practices.

But getting back to the specific error message, I am positive what that messages. So somewhere you are referencing a value that Access can't find.

[ScottGem]

Beth,
In response to your PM, the answer is yes, but you have to post a few more posts before I can reply to the PM.

[BethN]

Scott,

I believe this is a transactional database as I am able to enter info by date on the Product subform and it has columns for #Received, #Sold, and #Shrinkage. I've added the columns ProductID (which I pulled from the Products table) and UnitsDonated and UnitsGivenAway, which I first added on to the InventoryTransactions Table.

After re-reading your earlier post, I understand a little better about what you were coding, but have no idea where the code should appear. I took an Access class about 10 years ago and its like Greek to me now.

I thought I remembered something about having to create a query before you can create a report? So, I tried to create a report using the data from the InventoryTransactions Table. I was in design view and after adding all the fields I wanted, I put my cursor in the next field and called up the expression builder. This is what I entered:

UnitsAvailable: [Inventory Transactions]![UnitsReceived]-[Inventory Transactions]![UnitsSold]-[Inventory Transactions]![UnitsShrinkage]-[Inventory Transactions]![UnitsDonated]-[Inventory Transactions]![UnitsGivenAway]

Unfortunately, once I clicked OK the whole expression appeared in the field. I tried to run the query anyway. The UnitsAvailable column appears but is empty.

My hope was to create a total per line and then somehow create a sum of those totals by ProductID. Will this work or am I way off base?

Thanks again for your help!

[ScottGem]

As I said, in a transactional database, you should have a transaction type field and a quantity field. NOT separate fields for each transaction type. So you would have separate records for each transaction. Then you would use the queries I suggested to calculate QOH.

[BethN]

Hi Scott,

Thanks for your help, I finally got it to work after starting a new database.

I have another question for queries. I have added a 'note' field in the transaction table. Can I use the IIF in query to return the summary totals of all items with the same note. i.e. 6 of the 10 lines have "donated to Father Hester" in the note field. I want the query to show me information only from those 6 lines and I want the info grouped by ProductCode sums. i.e.

"donated to Father Hester"
M4C 6
M4F 13
TwineW 19

If so, would it look something like this:?

SELECT TransactionNote, ProductCode, IIF(TransactionNote="donated to Father Hester",TransactionNote,? ) As DonatedItems
FROM Transactions;

[ScottGem]

A couple of issues here. First, if you need to sort, group or filter records by a common value you need to standardize. What happens if you type "donated to Father Hester" one time and then "donated to Fr Hester" the next time? You would not be able to get all the records. So you need to plan your database accordingly.

This is where look up tables come in. Look up tables are the same as any table except for their purpose. Generally they have a structure like:

ID
Description

For example the Transaction Type table I mentioned earlier. By insuring standardized entry, you allow for grouping and filtering.

To address the specific question, no you would not use an IIF() for this. What you want is a WHERE clause (or criteria). If you are using Query Design mode (which I recommend), you will see a criteria row. In that row you can enter criteria to filter the query. In your example you would enter "donated to Father Hester" as the criteria for the Notes field.

IN SQL mode this would look like"
SELECT ProductCode, Notes
FROM Transactions
WHERE Notes = "
SELECT ProductCode, Notes
FROM Transactions
WHERE Notes = ";

[BethN]

Hi Scott,

I tried two different things. The first was to do a lookup in design view of a field and I got my queries to run correctly. However, I don't know how to add another choice in the field.

Then I tried creating a table with all the valuable so I could add more later. I then created a new table with a field that looked up the valuables from the table. I now can't get the queries to run correctly. They display the fields I want but the fields are empty. I've struggled with this for a few days, I hope you can help.

Thanks!

[BethN]

Scott,

Never mind, I figured it out. Thanks for all your help!

[ScottGem]

First, I do NOT recommend using lookup fields on the table level. These cause more problems then they are worth. Partially because they mask the actual data being stored which is likely the problem you encountered. Do all your lookups using lookup controls (combo & lists boxes and options groups) on your forms.

If you create a combo or list using the wizard and use it in conjunction with a lookup table, then all you need to add items to the list is to add a record to the lookup table.