Automatically Generate a Report Based on Two Different Sets ofRelated Tables

[Odeh Naber]

Hiya folks!

I was what the best way would be to do the following - I would
appreciate your input:

Imagine you have a restaurant, and you only sell the following two
products:

1. Mixed Salads: each is composed of 100 grams of lettuce and 200
grams of tomatoes.
2. Hamburgers: each composed of 1 bun and 200 grams of minced meat.

What I want to do, is to be able to register for each day (or for a
specific period), the number of Mixed Salads and Hamburgers that were
sold. And then, the report would automatically tell me how much
lettuce, tomatoes, buns and minced meat was consumed.

I was thinking to have one set of related tables to create the recipe
for each product:

TBLRECIPEDEFINITION
ProductID
ProductName

TBLRECIPEDETAILS
IngredientID
Ingredient
Unit
Quantity
ProductID

The way I would like to register the sales is the following:

TBLSALESPERIOD
SalesPeriodID
DateFrom
DateTo

TBLSALESDETAILS
ProductName
QuantitySold
SalesPeriodID

What do you think? The main problem here is I do not know how to
generate a report that can automatically tell me what quantity of each
ingredient that was consumed for each period? Any advice? Another
way I may register the sales is the following (for each period, I
would register the section of the restaurant or hotel that the sales
took place, and for each section the sales that were made). I am not
yet sure which I will work with:

TBLSALESPERIOD
SalesPeriodID
DateFrom
DateTo

TBLSALESSECTION
SectionID
Section
SalesPeriodID

TBLSALESDETAILS
ProductName
QuantitySold
SectionID

Looking forward to your advice!

Thank you!

 

[Scott Shearer]

I suggest that tblSalesPeriod, tblSalesSection and tblSalesDetails should be
changed/removed.

What you really want here is to be able to generate a report based on a date
range - tblSalesPeriod is probably unneccessary.

You will need a table to record sales with the following fields:
IDSales (autonumber and key)
ProductID (foreign key -> product table)
Quantity
IDLocation (foreign key -> location table)

It sounds like you want to record where the sales were made. You will need
a table to record locations that might look like this:
IDLocation (autonumber and key)
Location

With this structure, you can easily create a query for a given date range
that will show the quantity of products sold, quantity of ingredients used
and where the products were sold.

I would suggest that you write a query that generates the data that you want
in your report and then use the report wizard to get you started on the
actual report. You can group by product, location, etc. in the report.