The charity I work for provides a delivery service for groceries, food, wine and flowers in the city and neighbourhoods. The data used to be recorded with Excel but as I am about to leave, I have successfully been allowed to transfer the data to Access - making it easier for future developers to work with the data.
I would like to offer the functionality of displaying in a report,the number of orders for a particular shop per year. For example, let's say that we have 5 different Aldis in the DB. They are stored as Aldi Southern Quarter, Aldi West, Aldi East etc.
I used the following SQL code to create a query:
PARAMETERS theyear SHORT;
SELECT Month([deliverydate]) AS theMonth,
Year([deliverydate]) AS Year,
Count(tblshoppingtaxi.shoppingtaxiid) AS Orders
INNER JOIN tblshoppingtaxi
ON tblshops.[shopid] = tblshoppingtaxi.[shopid_fk]
GROUP BY Month([deliverydate]),
HAVING ( ( ( Year([deliverydate]) ) = [theyear] )
AND ( ( tblshops.shortname ) LIKE "Aldi*" ) );
What happens is, that it groups all Aldis by month
and displays the number of orders for that particular month.
I do not want this - I just want to have a total for all Aldis per month.
Can this be done with SQL or should I resort to something else?