How do I create an inventory report with units in stock?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create a report to show what inventory is left, but can't use the
sample report that is included because I need to list my product name by
size, type and color. Any suggestions? thanks!
 
It sounds like first of all you need multiple tables for your products
because each product can have multiple size, type and color. Your tables
need to look like:
TblProductSize
ProductSizeID
ProductSize

TblProductType
ProductTypeID

TblProductColor
ProductColorID
ProductColor

TblProduct
ProductID
ProductName

TblProductVariation
ProductVariationID
ProductID
ProductSizeID
ProductTypeID
ProductColorID
Inventory

Typically Inventory is included as a field in a product table as shown
above. You then need a way in the receiving and shipping parts of your
database to adjust inventory for each transaction.

Now to your question. You need a report/subreport. Create a query based on
TblProduct. Use this query as the recordsource of the main report. In the
reports Sorting and Grouping, sort ProductName. Create a query based on
TblVariation, TblProductSize, TblProductType and TblProductColor. Include
the fields you need for your subreport.

Be sure the LinkMaster and LinkChild properties for the subreport control on
the main report get set to ProductID.
 
Back
Top