date range into group

[Franky27]

Im trying to manage next thing


Table 1 (Season)

ID | startdate | End date | description
1 2006-09-01 | 2007-08-31 | Season 2007
2 2007-09-01 | 2008-08-31 | Season 2008
3 2008-09-01 | 2009-08-31 | Season 2009
4 2009-09-01 | 2010-08-31 | Season 2010


Table 2 (Products)
ID | productname | date
1 | Product A | 2008-04-14 |
2 | Product B | 2007-08-25 |
3 | Product C | 2009-07-01 |


Now I want to make a query that gives the right Season for these products. So when date is for example 2007-09-15 then this is a product of season 2008 because the date is in the range 2007-09-01 till 2008-08-31 in the first table.

So query should be look like this

ID | productname | date | Season
1 | Product A | 2008-04-14 | Season 2008
2 | Product B | 2007-08-25 | Season 2007
3 | Product C | 2009-09-11 | Season 2010

thanks

 

[Trevor G]

Create a new query and select both tables, then join the tables on the ID fields, select the fields you want to display then change the view does that give you the correct data?

If not upload a sample of the database.

 

[HiTechCoach]

Create a new query and select both tables, then join the tables on the ID fields, select the fields you want to display then change the view does that give you the correct data?

Trevor, the issue is the there is not any ID field to use to create a join

Franky27,
I normally handle this by using a sub query to find the record in the Season table where the Product's date falls between the season's start and end dates.

See: Subquery basics

 

[Trevor G]

He is clearly stating there is an ID field in both tables!

Table 1 (Season)

ID | startdate | End date | description
1 2006-09-01 | 2007-08-31 | Season 2007
2 2007-09-01 | 2008-08-31 | Season 2008
3 2008-09-01 | 2009-08-31 | Season 2009
4 2009-09-01 | 2010-08-31 | Season 2010


Table 2 (Products)
ID | productname | date
1 | Product A | 2008-04-14 |
2 | Product B | 2007-08-25 |
3 | Product C | 2009-07-01 |

 

[HiTechCoach]

He is clearly stating there is an ID field in both tables!

I agree that both tables have a primary key. If you joined on the to primary key fields then this would have to be a one-to-one relationship. It is not a a one-to-one relationship in this case.

The issues here is that there is not a direct relationship between the tables. The Season's Primary key is not stored as a foreign key in the products table or vice versa.

 

[Franky27]

I upload a example database with the 2 tables and made an extra yable how the result of the query should look like.

Hope this will be clear enough

Thanks

 

[HiTechCoach]

Franky27,

I have not had a chance to download your database. I am not at a place where I can do that.


Did you get a chance to try to create sub query?

 

[HiTechCoach]

See if this sub query works for you:

SELECT Products.Id, Products.FormulierNummer, Products.FormulierDatum, (SELECT TOP 1 Dupe.Seizoen FROM Season AS Dupe 
WHERE  Dupe.Begindatum <= Products.FormulierDatum and Products.FormulierDatum <= Dupe.Einddatum
ORDER BY Dupe.Seizoen DESC) AS ProductSeason
FROM Products;

 

[Franky27]

Thanks Thanks Thanks HiTechCoach,

Great it works perfect

thanks

 

[HiTechCoach]

You're welcome.

Glad that worked for you.