Unanswered: To Teddy (or anyone else) - setting specific date conditions (seasons query)
I really liked your idea (will definitely use it one day!), but unfortunately I remembered that our database has to be fairly simple (it's only a Year 12 assignment) and while I have a thousand and one ideas for ways to make it a phenomenal project, my teacher would not like it one bit, and probably deduct marks for that.
So, I was thinking about the seasons query I had earlier and that you answered. I can't really do it your way because we're only supposed to have 5 tables (and this would make it six). Instead, I wonder whether I could simplify the Seasons concept by doing something akin to the following:
- Setting a condition so that the Current field (in the MenuItems table) is only ticked 'Yes' when the DateAdded field is equal to or after the date that is 90 days less than the current date. How would I do this? I assume it would be via an IIf statement, but I'm not sure exactly what kind of coding would be required and so I hope you can help me there.
- Now, I may not do it this way, but if I feel confident enough(!), perhaps I could set up the DateAdded field so that if it is equal to the starting date of a specified season, or a maximum of 90 days afterwards, then the MenuSeason field would display the appropriate season.
I can't remember if this is what I asked in my other post or not, so if it is, just ignore the question.(!) I was just trying to brainstorm ways to simplify all this, and perhaps discarding the "Between this date and that date, then it's....." idea would be best. Of course, if this part would get too complicated, then I can certainly discard the whole displaying-a-menu-season-name idea altogether.
A condition is set so that the starting date of Winter is the 1st of June of the current (not specifically predefined) year, and that any date that is a maximum of 90 days following is also considered Winter.
When I type 18th June in the DateAdded field, the condition will say, aha, this is within 90 days of the Winter condition, and so I will put Winter in the MenuSeason field.
But if I type 1st September in the DateAdded field, the condition will say, this is not within 90 days of the Winter criteria, but it IS the same date as the Spring criteria, so let's put Spring in the MenuSeason field.
Also, in terms of the actual query, I'd have the ORDERS table with a field DateOrdered, and then obviously the MENUITEMS table with Current, etc. I'd need to run the query so that only menu items actually ordered (as opposed to added) within the last 90 days would be returned in the query.
Would this be simpler? I have the basic idea of what needs to be done, I just need the confirmation and the how-to from the experts to put it in place!!
Thanks very much.
P.S I'll attach the table structure document in case you need it.
I hate to tell you this, but your teacher doesn't know what they're doing.
If their job is to teach you real-life dba skills, they're failing.
I see you are thinking laterally to try and get around the criteria, which is good. Unfortunately it would be MORE complex to use the dateadd function then it would to use the between criteria.
I would flat approach your instructor and say you have this idea that would be INFINANTLY more elegant, simple and user friendly, and you'd like to give it a go. Otherwise it's back to that iif statement I mentioned earlier. You will need to use that iif statement each and everytime you would like to do anything related to seasons.
Or you could put a seasons field in the table, using either an integer 1-4 (as I mentioned previously), or a text string limited to four values.