Start date and end date query (1 Viewer)

[Nevsky78]

Hi all,

I have absolutely no idea how to solve this issue.

I've attached a stripped down version of a small order database I'm working on.

A user would enter an order, the amount and the date the order is required by.

As you can see from tbl_seasons, the business has financial periods that match the first and last 6 months of each year. Each season has a start date and end date.

What I'm trying to build are two queries:

1. A query which lists all orders and has an extra field which shows the "season_id" that the order (date) relates to (based one the start date and end date in tbl_seasons)
2. A totals query which shows the total order amounts by season

Does anyone have any ideas how I might build these 2 queries.

Thanks in advance!
Nick

 

[plog]

I'd get read of tbl_season and instead use this function when you need to determine a season:

Function getSeason(d)
    ' takes a date (d) and determines season code
        ret = "SS"
        If (Month(d) >=7) Then ret = "AW"
    ' seasons begins on 1/1 (SS) and 7/1 (AW)' 
        getSeason = ret & Year(d)
     
End Function

Then to use it in a query you would do this:

Season: getSeason([YourDateFieldHere])

Be sure to replace '[YourDateFieldHere]' with the name of your field that has the date you are basing the season on.

Using a function instead allows calculates seasons without them being in the table.

 

[Nevsky78]

Hi plog,

Thanks so much for that - that's far easier and just what I'm looking for!

Excellent, really appreciate the help.

Phew!
Nick