Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    80

    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 grace_adair@yahoo.co.uk

    Thankyou.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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:

    SELECT MenuItemID, ItemName, IIF(season = 1, "winter", iif(seasion = 2, "spring, iif(season = 3, "summer", "autum")))

    If you want to make it REALLY easy on yourself, you could create a table to hold definitions for the seasons, including start date, end date, and description, then the query becomes:

    SELECT MenuItemID, ItemName, seasons.Description

    There are similar tangles with the second question, which I won't go into till yougive me an idea which direction you'd like to go.

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    Posts
    80

    Thanks - probably the table idea for simplicity

    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.

    What is your suggestion for the second question?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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:

    season_id
    description
    start_date
    end_date

    In this way, you can pull the date range through the season_id, which you have assigned to each menuitem.

    eg:
    Code:
    SELECT MenuItemID, ItemName, seasons.start_date, seasons.end_date,
       (SELECT COUNT(MenuItemID) 
        FROM OrderedItems 
        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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2004
    Posts
    80

    Thanks

    Thanks very much for your reply

    Will try that very soon and let you know how it goes.

    - Grace

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •