Didier:
Grouping the first query I posted will give you the number of distinct dates
for each production ID. If you want to restrict in to a particular time
period then you can put parameters in the subquery for that:
PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT [ProductionIDField ],
COUNT (*) AS [DistictDatesCount]
FROM (SELECT DISTINCT [ProductionIDField], [DateField]
FROM [YourTable]
WHERE [ [DateField] BETWEEN
[Enter start date:] AND [Enter end date:])
GROUP BY [ProductionIDField];
This would prompt for the start and end dates at runtime. Note that it’s a
good idea to always declare parameters of date/time data type as other wise a
date entered in short date format could be interpreted as an arithmetical
expression
The second query will give the number of days between the highest and lowest
dates per production ID, so to make the count inclusive you need to add 1.
Again you can use parameters to restrict the result to a date range if you
wish:
PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT [ProductionIDField],
(MAX([DateField])-MIN([DateField]))+1
AS [DaysBetween]
WHERE [DateField] BETWEEN
[Enter start date:] AND [Enter end date:]);
Ken Sheridan
Stafford, England
Blackstar said:
I seem to have been writing with my head in the clouds, it makes no sence
that last sentence.
So what I meant was this.
There are for example 5 days in a period of a month where a production occurs.
So I need 2 values
1st one: the exact number of days between the first day of the production
and the last day. example: start of production 1 okt , end production 30 okt
= 30 days
2nd one: The exact number days for that purticular production, where the
double dates (sometimes I have 2 or 3 records of the same day with different
hours in a seperate field) are counted as one day. so 1,14,14,17, 17, 18 and
30 okt make 5 days of production.
Regards
Didier
Ken Sheridan said:
Didier:
Count the number of rows returned by a subquery which uses the SELECT
DISTINCT option, e.g.
SELECT COUNT (*)
FROM (SELECT DISTINCT [DateField]
FROM [YourTable]);
For the second:
SELECT [ProductionIDField],
MAX([DateField])-MIN([DateField])
AS [DaysBetween]
FROM [YourTable]
GROUP BY [ProductionIDField];
I'm not sure what you mean by 'the exact number days to found effectively in
the query'?
Ken Sheridan
Stafford, England
Blackstar said:
Hi,
I'm having difficulties with queries.
I want to count the number of UNIQUE dates in a query, but still, if in
properties I set it as unique, it still counts 2 day's if their are 2 and the
same dates.
Can anybody help me?
Also I would like to find the first and last date of a productionID and
calculate the number of days between these two particular days AND the exact
number days to found effectively in the query.
In the query their are to be found a datefield, an ProductionIdfield and
several fields concerning the production
Regards,
Didier
