Count based on date

[voiD]

Hello,

I'm stuck a bit with the following problem and hope somebody will have an idea which might help.
So, I have a table about flats. In the fields of the table, among others, there is a date field which represents the starting date of the flat availability. I'd like to create a query which displays the available nights per flat for each month in the current year. For example, Flat1 is available from 11.08.2014, then I'd like to see in the query result 20 nights for August and 30 for September, 31 for October and so on.
I created a query which displays only the dates for the current year. Then I joined this query with the Flats table, the join condition is query.date >= flat.valid_from_date and displayed 1 in each row in the result as night. This way, I have the available flats for each day with 1 as available night. Then I tried to summarize the number of nights by month and flat. Unfortunately, in the result of this query I got two times bigger number than the number of the actual month for each flat, independently from the availability of the flat in the given month. I could not figure out where I made mistake.
Sorry if it's a bit long!

Thank you in advance for any small piece of idea or suggestion!

VoiD

 

[vbaInet]

Let's see the SQL statement and let's also see some records in a spreadsheet.

 

[voiD]

What records do you mean? From the final result or from a sub-query?

 

[vbaInet]

Anything that's relevant.

 

[voiD]

Well, I found the problem, actually it was me. In the query which provides the dates for the year, I included the current year and the following one. That's why in the result I have all the dates for the current year and the following one and when I summarized the numbers for months, every month was taken two times.
Now, as I found the solution let me share what I did, perhaps it will help to others. So, I have a base table with two fields, one for the calendar days (1-31) and the other for the months (1-12). The following query provides the dates for the current year and the next one.

SELECT CDate([StrDt]) AS Datum
FROM (SELECT Year(Now()) AS [Year], MS.MonthNum, DS.DayNum, ([Year] & "-" & [MonthNum] & "-" & [DayNum]) AS StrDt FROM (SELECT SY_DATE_SUP.DayNum FROM SY_DATE_SUP)  AS DS, (SELECT SY_DATE_SUP.MonthNum FROM SY_DATE_SUP)  AS MS WHERE (((MS.MonthNum) Is Not Null))
union
SELECT Year(Now())+1 AS [Year], MS.MonthNum, DS.DayNum, ([Year] & "-" & [MonthNum] & "-" & [DayNum]) AS StrDt FROM (SELECT SY_DATE_SUP.DayNum FROM SY_DATE_SUP)  AS DS, (SELECT SY_DATE_SUP.MonthNum FROM SY_DATE_SUP)  AS MS WHERE (((MS.MonthNum) Is Not Null)))  AS BASE
WHERE (((IsDate([StrDt]))=True))
ORDER BY CDate([StrDt]);

The result of this query contains one filed, with dates from 01.01.2014 to 31.12.2015.

I use the above query as base in the next query with a table. The table contains data about flats, among others it has an ID (FID) field, a valid from (VF) and valid to field (VT).

SELECT q.Datum, f.FID, 1 AS Nght
FROM MA_FLTS AS f INNER JOIN qry_sup_date_curr_year AS q  ON (q.Datum between f.VF  and  f.VT)
ORDER BY q.Datum, f.FID;

This query results a list like this, for every day in the current and following year for every flat which is active on the give day. The join condition ensures that a given flat taken into consideration during that period when it's valid.

Datum	      FID	Nght
2014.01.01.	1	1
2014.01.01.	2	1
2014.01.01.	3	1
2014.01.01.	4	1
2014.01.01.	5	1
2014.01.01.	6	1
2014.01.01.	7	1

The in the last query, which base is the above one, I have to just summarize the number of nights for every months and I have the number of available nights in the given month. If a new flat is entered it'll be counted only from its valid from date.

Sorry, if it's a bit long and I hope it will be helpful to anybody!

Regards,
VoiD

 

[voiD]

Since my last post, I realized one thing, regarding the query which provides the dates for the given years. When using it in other queries, sometimes the provided dates are not treated by Access as dates. That's why I modified it and with this the results are treated always as dates. The modified query looks like this:

SELECT DISTINCT DateSerial(Year(Now()),[MonthNum],[DayNum]) AS Datum
FROM (SELECT SY_DATE_SUP.DayNum FROM SY_DATE_SUP)  AS DS, (SELECT SY_DATE_SUP.MonthNum FROM SY_DATE_SUP)  AS MS
WHERE (((MS.MonthNum) Is Not Null))
UNION SELECT DISTINCT DateSerial(Year(Now())+1,[MonthNum],[DayNum]) AS Datum
FROM (SELECT SY_DATE_SUP.DayNum FROM SY_DATE_SUP)  AS DS, (SELECT SY_DATE_SUP.MonthNum FROM SY_DATE_SUP)  AS MS
WHERE (((MS.MonthNum) Is Not Null));

If anybody has an improvement idea or comment, it's always welcome!

Regards,
VoiD