Products Report Help

[Mya48]

I used the Inventory template and it's great. The database works great but I
am having problems generating a report that will give the data I want. These
are the fields in the products table:

ProductID
ProductName
CategoryID
Supplier
ItemNumber
Unit
QuantityPerUnit
UnitPrice
ReorderLevel
Discontinued
LeadTime
Photo

The problem I have is that when we distribute colored paper or folders to
employees, they don't always want a box of folders or a full ream of paper.
In the QuantityPerUnit field, I have listed that there's 500 sheets per unit
(ream) or 25 folders per unit (box). How then would I get an accurate report
as to how much has been spent if Joe ordered two reams of paper and John
ordered 40 sheets of paper? The way the report is set up right now, it would
show 1,040 sheets of paper has been spent. This turns out to be an out liar
when I run a report on the most commonly used supplies because 1,040 would
show up at the top when in fact it should only be 2 reams plus 40 sheets, but
that's where I'm stuck. I don't know how to fix the Units issue. Any help
is appreciated.

 

[Larry Linson]

You are going to have to determine how you handle the situation in which you
have two different units of measure. It is often the case that material is
purchased in one (larger) unit of measure, and distributed or sold in a
different (smaller) unit of measure... in that case, generally, adding to
inventory translates the larger unit to the smaller unit. I suspect you are
may have to keep track in your database of "pages" and provide for the form
into which the withdrawal information is entered to translate between reams
and pages.

Access' "psychic features" have not yet been announced, so the user is going
to have to indicate "reams" or "pages/sheets". Templates aren't expected to
always be usable for production without some modification for user-specific
instances such as this.

Larry Linson
Microsoft Office Access MVP

 

[Mya48]

How would I translate it?

You are going to have to determine how you handle the situation in which you
have two different units of measure. It is often the case that material is
purchased in one (larger) unit of measure, and distributed or sold in a
different (smaller) unit of measure... in that case, generally, adding to
inventory translates the larger unit to the smaller unit. I suspect you are
may have to keep track in your database of "pages" and provide for the form
into which the withdrawal information is entered to translate between reams
and pages.

Access' "psychic features" have not yet been announced, so the user is going
to have to indicate "reams" or "pages/sheets". Templates aren't expected to
always be usable for production without some modification for user-specific
instances such as this.

Larry Linson
Microsoft Office Access MVP

 

[Larry Linson]

I'm sorry, but I can't devote the time to downloading the Inventory Template
and analyzing it. There does not, from the Fields you list, and your
description, appear to be enough information to do the calculation I would
propose.

You indicate you use the Quantity Per Unit of 500 (or 25 per Box for
Folders), and if Unit is the text description, I am at a loss to identify
where you keep the "Quantity of this Item". If Unit is the number of units
of the defined Quantity, then I don't see where the name of the unit is
kept. All three will be needed for the calculation.

Assuming you can, on input, convert Reams to Sheets, and you will always
issue as Sheets, then on output you could calculate as follows, where
lngWholeReams is a variable, lngAdditionalSheets is a variable, and the
value comes from the Unit Field:

WholeReams = Unit\QuantityPerUnit
AdditionalSheets = Unit Mod QuantityPerUnit

This calculation would best be placed in the Query that is RecordSource for
the Report. Then you would still face the problem of the "description of
the unit" which we have not resolved.

Perhaps a simpler approach would be to keep two entries of each item of this
type... for the full Unit (Ream or Box) and another for less-than-full Unit.
When you need to issue by the Sheet, you could remove a Unit from the
Paper-by-the-Ream line, decrementing it by one, and add 500 sheets to the
Paper-by-the-Sheet Line. In that case, you'd end up with two lines for the
Item on your Report, but no database modifications would be required.
Depending, of course, on the number of types of paper and folders, this
might be a few extra lines, or many.

Or, if your accounting department agrees and you do not have to allocate
costs down to the sheet of paper or individual folder to a department or
individual (I'd think individual sheets of paper would be "accounting
overkill" costing more to track than to ignore), you might just keep a
separate stack... remove a ream when you run out, but don't even bother with
inventory entries for quantities less than a ream or a box. As I recall some
work environment stockrooms over the years, I suspect they worked this way.

In others, the individual departments would withdraw the full unit, and the
individuals working in the department would pick up the number of sheets or
folders they needed from the deparment supply cabinet.

I fear I have not been much help, and I also fear that some re-design to the
database application created from the template may be required unless the
final "practical approach" is workable for you.

Larry Linson
Microsoft Office Access MVP