Unanswered: Some problems with queries - can you help?
I have a restaurant database, with one table in particular where a very specific kind of query is required. This table is called MenuItems, and within it are conventional fields like MenuItemID, etc, but there is also MenuSeason, DateAdded, and Current.
The DateAdded field is a regular input field. The Current field is calculated from DateAdded IIf([DateAdded]=_____, yes). However I need two things:
1. I need some kind of query that will say, OK, if the DateAdded field is set between this date of this month (NOT year), then the MenuSeason field is automatically set to Autumn, or Spring, or whatever the appropriate season may be.
2. Secondly, I need a function so that, when I am running a query to compile statistics about menu items ordered during one season (of three month duration), then the criteria for the dates that menu items were ordered on goes something like:
"Show the menu items ordered between the two dates that correspond to the season displayed in the MenuSeason field."
Hope this makes sense and that you'll be able to help me...if you want to send your reply via email, my address is firstname.lastname@example.org
You have some redundant data there. Whereas menuseason and current are both calculated from dateadded, I will disregard their presence at the table level. I would highly suggest assigning an integer field to track seasons, 1-4. In that way, you will avoid rather messy parsing statements.
SELECT MenuItemID, ItemName, DateAdded, Iif(DateAdded = ___, -1, 0) AS Current, IIF((Month(Current) BETWEEN x AND x) AND (day(Current) BETWEEN x AND x), "spring", iif(Month(Current) BETWEEN x AND x) AND (day(Current) BETWEEN x AND x), "summer", iif(Month(Current) BETWEEN x AND x) AND (day(Current) BETWEEN x AND x), "autum", "winter")))) AS season
So you see where that gets a little messy? Here's an example using the integer method:
Thanks for your reply, I appreciate your thoughts. I think I'll go for the separate table thing, just because it's so simple.
However, with the integer method you mentioned, where do the dates come into that? Sorry, I'm not very familiar with much of the language and so I don't completely understand the terminology (though I get the basic idea).
This sounds strange, but I assume this snippet of code, if it were to be used, would be typed into the expression builder box? I'm a semi-beginner with databases - learning the programming language one day would be great, though.
Ok, since you are looking at the additional table method, the second question just became MUCH MUCH easier (which is why I was waiting for a reply ).
The date ranges for the seasons will be stored in your seasons table. So the table would look like this:
In this way, you can pull the date range through the season_id, which you have assigned to each menuitem.
SELECT MenuItemID, ItemName, seasons.start_date, seasons.end_date,
WHERE OrderedItems.MenuItemID = MenuItems.MenuItemID AND
OrderedItems.Ordered_date BETWEEN seasons.start_date AND seasons.end_date)
FROM MenuItems INNER JOIN Seasons ON MenuItems.season_id=Seasons.season_id
So, that would give you an ID, name and count of items that were ordered in season.