Totals Query with Two Date Parameters

  • Thread starter Thread starter Jeff Wilkin
  • Start date Start date
J

Jeff Wilkin

I need to create a query to calculate how much of each type of alcohol
should have been dispensed based upon the number of cocktails sold for any
given time period(s). Date parameters are supplied by two comboboxes,
cboStart and cboEnd (formatted as Date/Time), which lookup their respective
values from tblLog, on an unbound form "frmSelectToPrint". (cboEnd is
disabled until a selection is made in cboStart, at which point it is enabled
and filtered to only show dates greater than cboStart.) Can it be done this
way, and if so how? Or is tblLog structured improperly?

**************
Tables:

tblLog 'no date overlaps but often gaps
LogID '<autonum> PK
BeginDate 'short date (will always be greater than most recent
EndDate)
EndDate ' short date (will always be >= BeginDate and < today)

tblConsumed
ConsumptionID '<autonum> PK
CocktailID 'long FK
LogID 'long FK
QtySold 'long

tbl Cocktails
CocktailID '<autonum> PK
Cocktail 'text

tblCocktailIngredients
CocktailID 'long Compound Key with IngredID
IngredID 'long FK to tblIngredients
AmtPerCocktail 'long (units in cl)
****************

Hoping that someone takes pity and helps me out.
TIA,
Jeff
 
Hi,


Make a query that will imply all the tables, join them ( the joins seem
evident, I won't describe them in any details), change the SELECT query type
to a TOTAL query (click on the summation button on the toolbar, you would
get an extra line, Total, in the grid). Drag IngredID in the grid, keep the
GroupBy. Drag AmtPerCockatail, change the GroupBy to Sum. Drag the beginDate
and EndDate in the grid, change the GroupBy to Where, and add the
FORMS!FormName!AppropriateComboBoxName in the criteria line. Have the
mentioned form opened and its combo box filled, then take a look at the
query, you should see the result you need. To package the presentation, you
can assign the query to a listbox rowsource property (or a subform
recordsource).



Hoping it may help,
Vanderghast, Access MVP
 
Vanderghast,

Thanks so much for your help! After a slight modification to get the total
amount of alcohol, a few extra joins, and more than a few searches in my
English/ German dictionary (now I know what 'Bedingungen' means), I finally
have a query to feed my report. I've included the finished query for anyone
interested.

Thanks again,
Jeff

SELECT tblIngredients.IngredName,
Sum([tblConsumed].[QtySold]*[tblCocktailIngredients].[AmtPerCocktail]) AS
Verbraucht
FROM tblIngredientType INNER JOIN (tblIngredients INNER JOIN (tblLog INNER
JOIN ((tblCocktails INNER JOIN tblCocktailIngredients ON
tblCocktails.CocktailID = tblCocktailIngredients.CockailID) INNER JOIN
tblConsumed ON tblCocktails.CocktailID = tblConsumed.CocktailID) ON
tblLog.LogID = tblConsumed.LogID) ON tblIngredients.IngredID =
tblCocktailIngredients.IngredID) ON tblIngredientType.IngredTypeID =
tblIngredients.IngredTypeID
WHERE (((tblLog.BeginDate)>=[Forms]![frmSelectToPrint]![cboStart]) AND
((tblLog.EndDate)<=[Forms]![frmSelectToPrint]![cboEnd]))
GROUP BY tblIngredients.IngredName, tblIngredientType.IsSpirit
HAVING (((tblIngredientType.IsSpirit)=True));
 
Back
Top