convert date to Series

A

a

thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
 
M

Michel Walsh

Hi,


If you have a Iotas table, one field, iota, with values from 0 to, say, 999,
then


SELECT [Begin date]+Iotas.iota, Company, ID
FROM mytable , Iotas
WHERE myTable.[end date] <= myTable.[Begin date] + Iotas.Iota





To get a Iotas table, make a Ds table, one field, D, with values form 0 to
9. In a query, use:

SELECT D.Ds+ 10*D.Ds_1 + 100* D.Ds_2 As Iota
FROM Ds, Ds As Ds_1, Ds As Ds_2


and make a table out of it (make the iota field the primary key).


Hoping it may help,
Vanderghast, Access MVP
 
R

Ron Weiner

Michel

Looks like you have a typo in your example to create the 0 to 1000 Iota
number query. The SELECT needs to be TablenameDOTFieldname as in:

SELECT Ds.D + 10 * Ds_1.D + 100 * Ds_2.D AS Iota
FROM Ds, Ds AS Ds_1, Ds AS Ds_2

Having a table of Numbers in a database comes in handy for solving a whole
range of common database tasks/problems where needed values do not exist.
I have always called the table Numbers and the field Num, I am still trying
to get my brain wrapped around Iotas. Below is the query I use in Access to
create the table without having to create and drop the intermediate table n
with rows 0 through 9.

SELECT [n1s].[n]+10*[n10s].[n]+100*[n100s].[n] AS Num
INTO Numbers
FROM [SELECT 0 as n From MSysACEs Union Select 1 From MSysACEs Union Select
2 From MSysACEs Union Select 3 From MSysACEs Union Select 4 From MSysACEs
Union Select 5 From MSysACEs Union Select 6 From MSysACEs Union Select 7
From MSysACEs Union Select 8 From MSysACEs Union Select 9 From MSysACEs]. AS
n1s,
[SELECT 0 as n From MSysACEs Union Select 1 From MSysACEs Union Select 2
From MSysACEs Union Select 3 From MSysACEs Union Select 4 From MSysACEs
Union Select 5 From MSysACEs Union Select 6 From MSysACEs Union Select 7
From MSysACEs Union Select 8 From MSysACEs Union Select 9 From MSysACEs]. AS
n10s,
[SELECT 0 as n From MSysACEs Union Select 1 From MSysACEs Union Select 2
From MSysACEs Union Select 3 From MSysACEs Union Select 4 From MSysACEs
Union Select 5 From MSysACEs Union Select 6 From MSysACEs Union Select 7
From MSysACEs Union Select 8 From MSysACEs Union Select 9 From MSysACEs]. AS
n100s;

The above make table query is unique to Access. The Sql version of the
query is even simpler as Sql does not complain when you do not provide a
table in the union query. Here is my Sql version

SELECT [n1s].[n]+10*[n10s].[n]+100*[n100s].[n] AS Num
INTO Numbers
FROM (SELECT 0 as n Union Select 1 Union Select 2 Union Select 3 Union
Select 4 Union Select 5 Union Select 6 Union Select 7 Union Select 8 Union
Select 9) AS n1s,
(SELECT 0 as n Union Select 1 Union Select 2 Union Select 3 Union Select 4
Union Select 5 Union Select 6 Union Select 7 Union Select 8 Union Select 9)
AS n10s,
(SELECT 0 as n Union Select 1 Union Select 2 Union Select 3 Union Select 4
Union Select 5 Union Select 6 Union Select 7 Union Select 8
Union Select 9) AS n100s
Order by Num

Ron W

Michel Walsh said:
Hi,


If you have a Iotas table, one field, iota, with values from 0 to, say, 999,
then


SELECT [Begin date]+Iotas.iota, Company, ID
FROM mytable , Iotas
WHERE myTable.[end date] <= myTable.[Begin date] + Iotas.Iota





To get a Iotas table, make a Ds table, one field, D, with values form 0 to
9. In a query, use:

SELECT D.Ds+ 10*D.Ds_1 + 100* D.Ds_2 As Iota
FROM Ds, Ds As Ds_1, Ds As Ds_2


and make a table out of it (make the iota field the primary key).


Hoping it may help,
Vanderghast, Access MVP


a said:
thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


You are right, I inverted the field name with the table name. :)


Vanderghast, Access MVP


Ron Weiner said:
Michel

Looks like you have a typo in your example to create the 0 to 1000 Iota
number query. The SELECT needs to be TablenameDOTFieldname as in:

SELECT Ds.D + 10 * Ds_1.D + 100 * Ds_2.D AS Iota
FROM Ds, Ds AS Ds_1, Ds AS Ds_2

Having a table of Numbers in a database comes in handy for solving a whole
range of common database tasks/problems where needed values do not exist.
I have always called the table Numbers and the field Num, I am still trying
to get my brain wrapped around Iotas. Below is the query I use in Access to
create the table without having to create and drop the intermediate table n
with rows 0 through 9.

SELECT [n1s].[n]+10*[n10s].[n]+100*[n100s].[n] AS Num
INTO Numbers
FROM [SELECT 0 as n From MSysACEs Union Select 1 From MSysACEs Union Select
2 From MSysACEs Union Select 3 From MSysACEs Union Select 4 From MSysACEs
Union Select 5 From MSysACEs Union Select 6 From MSysACEs Union Select 7
From MSysACEs Union Select 8 From MSysACEs Union Select 9 From MSysACEs]. AS
n1s,
[SELECT 0 as n From MSysACEs Union Select 1 From MSysACEs Union Select 2
From MSysACEs Union Select 3 From MSysACEs Union Select 4 From MSysACEs
Union Select 5 From MSysACEs Union Select 6 From MSysACEs Union Select 7
From MSysACEs Union Select 8 From MSysACEs Union Select 9 From MSysACEs]. AS
n10s,
[SELECT 0 as n From MSysACEs Union Select 1 From MSysACEs Union Select 2
From MSysACEs Union Select 3 From MSysACEs Union Select 4 From MSysACEs
Union Select 5 From MSysACEs Union Select 6 From MSysACEs Union Select 7
From MSysACEs Union Select 8 From MSysACEs Union Select 9 From MSysACEs]. AS
n100s;

The above make table query is unique to Access. The Sql version of the
query is even simpler as Sql does not complain when you do not provide a
table in the union query. Here is my Sql version

SELECT [n1s].[n]+10*[n10s].[n]+100*[n100s].[n] AS Num
INTO Numbers
FROM (SELECT 0 as n Union Select 1 Union Select 2 Union Select 3 Union
Select 4 Union Select 5 Union Select 6 Union Select 7 Union Select 8 Union
Select 9) AS n1s,
(SELECT 0 as n Union Select 1 Union Select 2 Union Select 3 Union Select 4
Union Select 5 Union Select 6 Union Select 7 Union Select 8 Union Select 9)
AS n10s,
(SELECT 0 as n Union Select 1 Union Select 2 Union Select 3 Union Select 4
Union Select 5 Union Select 6 Union Select 7 Union Select 8
Union Select 9) AS n100s
Order by Num

Ron W

Michel Walsh said:
Hi,


If you have a Iotas table, one field, iota, with values from 0 to, say, 999,
then


SELECT [Begin date]+Iotas.iota, Company, ID
FROM mytable , Iotas
WHERE myTable.[end date] <= myTable.[Begin date] + Iotas.Iota





To get a Iotas table, make a Ds table, one field, D, with values form 0 to
9. In a query, use:

SELECT D.Ds+ 10*D.Ds_1 + 100* D.Ds_2 As Iota
FROM Ds, Ds As Ds_1, Ds As Ds_2


and make a table out of it (make the iota field the primary key).


Hoping it may help,
Vanderghast, Access MVP


a said:
thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
Click to expand...
 
A

a

I have apply your solution and work correct but with one notes:
the begin date series begin very high (example)
begin date end date
1/1/2004 10/1/2004

the query tell me
expr1:
4/6/2005
5/6/2004
etc

is there any missing data in this expr ( [Begindate]+Iotas.iota AS Expr1)
thank you for your help and answer



Michel Walsh said:
Hi,


If you have a Iotas table, one field, iota, with values from 0 to, say, 999,
then


SELECT [Begin date]+Iotas.iota, Company, ID
FROM mytable , Iotas
WHERE myTable.[end date] <= myTable.[Begin date] + Iotas.Iota





To get a Iotas table, make a Ds table, one field, D, with values form 0 to
9. In a query, use:

SELECT D.Ds+ 10*D.Ds_1 + 100* D.Ds_2 As Iota
FROM Ds, Ds As Ds_1, Ds As Ds_2


and make a table out of it (make the iota field the primary key).


Hoping it may help,
Vanderghast, Access MVP


a said:
thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
 
A

a

I have apply your solution and work correct but with one notes:
the begin date series begin very high (example)
begin date end date
1/1/2004 10/1/2004

the query tell me
expr1:
4/6/2005
5/6/2004
etc

is there any missing data in this expr ( [Begindate]+Iotas.iota AS Expr1)
thank you for your help and answer



Michel Walsh said:
Hi,


If you have a Iotas table, one field, iota, with values from 0 to, say, 999,
then


SELECT [Begin date]+Iotas.iota, Company, ID
FROM mytable , Iotas
WHERE myTable.[end date] <= myTable.[Begin date] + Iotas.Iota





To get a Iotas table, make a Ds table, one field, D, with values form 0 to
9. In a query, use:

SELECT D.Ds+ 10*D.Ds_1 + 100* D.Ds_2 As Iota
FROM Ds, Ds As Ds_1, Ds As Ds_2


and make a table out of it (make the iota field the primary key).


Hoping it may help,
Vanderghast, Access MVP


a said:
thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
 
A

a

Thank you vrey much for your help and answer thank you:
Mr Micherl Walsh ([MVP]):
Next Question:
begin date end date number of days
20/01/2004 25/01/2004 5
Mr Micherl Walsh ([MVP]):
can I returen only number of days only not 1000 record
example for the result:
20/1/2004
21/1/2004
22/1/2004
23/1/2004
24/1/2004
25/1/2004

Thank you very much thank you






Michel Walsh said:
Hi,


You may add an


ORDER BY [Begindate]+Iotas.iota ASC



so that the dates would be ordered together, rather than "presented" at
random...


Hoping it may help,
Vanderghast, Access MVP



a said:
I have apply your solution and work correct but with one notes:
the begin date series begin very high (example)
begin date end date
1/1/2004 10/1/2004

the query tell me
expr1:
4/6/2005
5/6/2004
etc

is there any missing data in this expr ( [Begindate]+Iotas.iota AS Expr1)
thank you for your help and answer



Michel Walsh said:
Hi,


If you have a Iotas table, one field, iota, with values from 0 to,
Click to expand...
say,
999,
then


SELECT [Begin date]+Iotas.iota, Company, ID
FROM mytable , Iotas
WHERE myTable.[end date] <= myTable.[Begin date] + Iotas.Iota





To get a Iotas table, make a Ds table, one field, D, with values form
Click to expand...
Click to expand...
0
to
Click to expand...
Click to expand...
 
A

a

Thank you very much (((sorry Next Question...))) (Difficult question)
Available dates (you can say table1)
Begin date end date
1/1/2004 10/1/2004
Reservation dates (you can say table2)
Begin date end date
1/1/2004 4/1/2004
8/1/2004 10/1/2004
The difference date (not occupied date) not booking date
The result what I want to get
5/1/2004
6/1/2004
7/1/2004
What I want to say the question:
Give me the date not in reservation dates
Using (query or sql)
Thank you

Michel Walsh said:
Hi,


Did you include the WHERE clause? The WHERE clause should limit the
records to those where the computed date, [Begin Date] + Iotas.Iota is
less than or equal to [End Date]:

... WHERE [Begin Date] + Iotas.Iota <= [End Date]

( I used the wrong inequation, in my first post)


Vanderghast, Access MVP


a said:
Thank you vrey much for your help and answer thank you:
Mr Micherl Walsh ([MVP]):
Next Question:
begin date end date number of days
20/01/2004 25/01/2004 5
Mr Micherl Walsh ([MVP]):
can I returen only number of days only not 1000 record
example for the result:
20/1/2004
21/1/2004
22/1/2004
23/1/2004
24/1/2004
25/1/2004

Thank you very much thank you






Michel Walsh said:
Hi,


You may add an


ORDER BY [Begindate]+Iotas.iota ASC



so that the dates would be ordered together, rather than "presented" at
random...


Hoping it may help,
Vanderghast, Access MVP



I have apply your solution and work correct but with one notes:
the begin date series begin very high (example)
begin date end date
1/1/2004 10/1/2004

the query tell me
expr1:
4/6/2005
5/6/2004
etc

is there any missing data in this expr ( [Begindate]+Iotas.iota AS Expr1)
thank you for your help and answer



Hi,


If you have a Iotas table, one field, iota, with values from 0 to, say,
999,
then


SELECT [Begin date]+Iotas.iota, Company, ID
FROM mytable , Iotas
WHERE myTable.[end date] <= myTable.[Begin date] + Iotas.Iota





To get a Iotas table, make a Ds table, one field, D, with values
Click to expand...
form
0
to
9. In a query, use:

SELECT D.Ds+ 10*D.Ds_1 + 100* D.Ds_2 As Iota
FROM Ds, Ds As Ds_1, Ds As Ds_2


and make a table out of it (make the iota field the primary key).


Hoping it may help,
Vanderghast, Access MVP


thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


You may add an


ORDER BY [Begindate]+Iotas.iota ASC



so that the dates would be ordered together, rather than "presented" at
random...


Hoping it may help,
Vanderghast, Access MVP



a said:
I have apply your solution and work correct but with one notes:
the begin date series begin very high (example)
begin date end date
1/1/2004 10/1/2004

the query tell me
expr1:
4/6/2005
5/6/2004
etc

is there any missing data in this expr ( [Begindate]+Iotas.iota AS Expr1)
thank you for your help and answer



Michel Walsh said:
Hi,


If you have a Iotas table, one field, iota, with values from 0 to, say, 999,
then


SELECT [Begin date]+Iotas.iota, Company, ID
FROM mytable , Iotas
WHERE myTable.[end date] <= myTable.[Begin date] + Iotas.Iota





To get a Iotas table, make a Ds table, one field, D, with values form 0 to
9. In a query, use:

SELECT D.Ds+ 10*D.Ds_1 + 100* D.Ds_2 As Iota
FROM Ds, Ds As Ds_1, Ds As Ds_2


and make a table out of it (make the iota field the primary key).


Hoping it may help,
Vanderghast, Access MVP


a said:
thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


Did you include the WHERE clause? The WHERE clause should limit the
records to those where the computed date, [Begin Date] + Iotas.Iota is
less than or equal to [End Date]:

.... WHERE [Begin Date] + Iotas.Iota <= [End Date]

( I used the wrong inequation, in my first post)


Vanderghast, Access MVP


a said:
Thank you vrey much for your help and answer thank you:
Mr Micherl Walsh ([MVP]):
Next Question:
begin date end date number of days
20/01/2004 25/01/2004 5
Mr Micherl Walsh ([MVP]):
can I returen only number of days only not 1000 record
example for the result:
20/1/2004
21/1/2004
22/1/2004
23/1/2004
24/1/2004
25/1/2004

Thank you very much thank you






Michel Walsh said:
Hi,


You may add an


ORDER BY [Begindate]+Iotas.iota ASC



so that the dates would be ordered together, rather than "presented" at
random...


Hoping it may help,
Vanderghast, Access MVP



a said:
I have apply your solution and work correct but with one notes:
the begin date series begin very high (example)
begin date end date
1/1/2004 10/1/2004

the query tell me
expr1:
4/6/2005
5/6/2004
etc

is there any missing data in this expr ( [Begindate]+Iotas.iota AS Expr1)
thank you for your help and answer



Hi,


If you have a Iotas table, one field, iota, with values from 0 to, say,
999,
then


SELECT [Begin date]+Iotas.iota, Company, ID
FROM mytable , Iotas
WHERE myTable.[end date] <= myTable.[Begin date] + Iotas.Iota





To get a Iotas table, make a Ds table, one field, D, with values
Click to expand...
Click to expand...
form
0
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


SELECT a.[end date]+1 As FromDate, MIN(b.[begin date]-1 As ToDate
FROM myTable As a INNER JOIN myTable As b
ON a.[end date]+2 < b.[begin date]
GROUP BY a.[end date]


should do.


The "b"-set get all the [begin date] where b.[begin date]-1 > a.[end
date]+1 ; taking the minimum of those, for a given a.[end date], returns
the "first next" [begin date] that follow a.[end date].

Note that the ON condition should be:

ON a.[end date]+1 < b.[begin date] -1


I simplified the algebra to

ON a.[end date]+2 < b.[begin date]

so a possible index on b.[begin date] can eventually be use.



Hoping it may help,
Vanderghast, Access MVP



a said:
Thank you very much (((sorry Next Question...))) (Difficult question)
Available dates (you can say table1)
Begin date end date
1/1/2004 10/1/2004
Reservation dates (you can say table2)
Begin date end date
1/1/2004 4/1/2004
8/1/2004 10/1/2004
The difference date (not occupied date) not booking date
The result what I want to get
5/1/2004
6/1/2004
7/1/2004
What I want to say the question:
Give me the date not in reservation dates
Using (query or sql)
Thank you

Michel Walsh said:
Hi,


Did you include the WHERE clause? The WHERE clause should limit the
records to those where the computed date, [Begin Date] + Iotas.Iota is
less than or equal to [End Date]:

... WHERE [Begin Date] + Iotas.Iota <= [End Date]

( I used the wrong inequation, in my first post)


Vanderghast, Access MVP


a said:
Thank you vrey much for your help and answer thank you:
Mr Micherl Walsh ([MVP]):
Next Question:
begin date end date number of days
20/01/2004 25/01/2004 5
Mr Micherl Walsh ([MVP]):
can I returen only number of days only not 1000 record
example for the result:
20/1/2004
21/1/2004
22/1/2004
23/1/2004
24/1/2004
25/1/2004

Thank you very much thank you






Hi,


You may add an


ORDER BY [Begindate]+Iotas.iota ASC



so that the dates would be ordered together, rather than "presented" at
random...


Hoping it may help,
Vanderghast, Access MVP



I have apply your solution and work correct but with one notes:
the begin date series begin very high (example)
begin date end date
1/1/2004 10/1/2004

the query tell me
expr1:
4/6/2005
5/6/2004
etc

is there any missing data in this expr ( [Begindate]+Iotas.iota AS
Expr1)
thank you for your help and answer



Hi,


If you have a Iotas table, one field, iota, with values from 0 to,
say,
999,
then


SELECT [Begin date]+Iotas.iota, Company, ID
FROM mytable , Iotas
WHERE myTable.[end date] <= myTable.[Begin date] + Iotas.Iota





To get a Iotas table, make a Ds table, one field, D, with values form
0
to
9. In a query, use:

SELECT D.Ds+ 10*D.Ds_1 + 100* D.Ds_2 As Iota
FROM Ds, Ds As Ds_1, Ds As Ds_2


and make a table out of it (make the iota field the primary key).


Hoping it may help,
Vanderghast, Access MVP


thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
Click to expand...
 
¹

¹Ú¹®¼ö

a said:
I have apply your solution and work correct but with one notes:
the begin date series begin very high (example)
begin date end date
1/1/2004 10/1/2004

the query tell me
expr1:
4/6/2005
5/6/2004
etc

is there any missing data in this expr ( [Begindate]+Iotas.iota AS Expr1)
thank you for your help and answer



Michel Walsh said:
Hi,


If you have a Iotas table, one field, iota, with values from 0 to, say, 999,
then


SELECT [Begin date]+Iotas.iota, Company, ID
FROM mytable , Iotas
WHERE myTable.[end date] <= myTable.[Begin date] + Iotas.Iota





To get a Iotas table, make a Ds table, one field, D, with values form 0 to
9. In a query, use:

SELECT D.Ds+ 10*D.Ds_1 + 100* D.Ds_2 As Iota
FROM Ds, Ds As Ds_1, Ds As Ds_2


and make a table out of it (make the iota field the primary key).


Hoping it may help,
Vanderghast, Access MVP


a said:
thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
Click to expand...
 
¹

¹Ú¹®¼ö

a said:
Thank you very much (((sorry Next Question...))) (Difficult question)
Available dates (you can say table1)
Begin date end date
1/1/2004 10/1/2004
Reservation dates (you can say table2)
Begin date end date
1/1/2004 4/1/2004
8/1/2004 10/1/2004
The difference date (not occupied date) not booking date
The result what I want to get
5/1/2004
6/1/2004
7/1/2004
What I want to say the question:
Give me the date not in reservation dates
Using (query or sql)
Thank you

Michel Walsh said:
Hi,


Did you include the WHERE clause? The WHERE clause should limit the
records to those where the computed date, [Begin Date] + Iotas.Iota is
less than or equal to [End Date]:

... WHERE [Begin Date] + Iotas.Iota <= [End Date]

( I used the wrong inequation, in my first post)


Vanderghast, Access MVP


a said:
Thank you vrey much for your help and answer thank you:
Mr Micherl Walsh ([MVP]):
Next Question:
begin date end date number of days
20/01/2004 25/01/2004 5
Mr Micherl Walsh ([MVP]):
can I returen only number of days only not 1000 record
example for the result:
20/1/2004
21/1/2004
22/1/2004
23/1/2004
24/1/2004
25/1/2004

Thank you very much thank you






Hi,


You may add an


ORDER BY [Begindate]+Iotas.iota ASC



so that the dates would be ordered together, rather than "presented" at
random...


Hoping it may help,
Vanderghast, Access MVP



I have apply your solution and work correct but with one notes:
the begin date series begin very high (example)
begin date end date
1/1/2004 10/1/2004

the query tell me
expr1:
4/6/2005
5/6/2004
etc

is there any missing data in this expr ( [Begindate]+Iotas.iota AS
Expr1)
thank you for your help and answer



Hi,


If you have a Iotas table, one field, iota, with values from 0 to,
say,
999,
then


SELECT [Begin date]+Iotas.iota, Company, ID
FROM mytable , Iotas
WHERE myTable.[end date] <= myTable.[Begin date] + Iotas.Iota





To get a Iotas table, make a Ds table, one field, D, with values form
0
to
9. In a query, use:

SELECT D.Ds+ 10*D.Ds_1 + 100* D.Ds_2 As Iota
FROM Ds, Ds As Ds_1, Ds As Ds_2


and make a table out of it (make the iota field the primary key).


Hoping it may help,
Vanderghast, Access MVP


thank you
I have 2 dates:
Tables1
Begin date end date Company ID
10/12/2004 20/12/2004 hp 1
15/12/2004 17/12/2004 Microsoft 2
01/01/2004 02/01/2004 dell 3
11/01/2004 12/01/2004 Oracle 4
I want to convert this dates to:
Converted date company ID
10/12/2004 hp 1
11/12/2004 hp 1
12/12/2004 hp 1
13/12/2004 hp 1
14/12/2004 hp 1
15/12/2004 hp 1
16/12/2004 hp 1
17/12/2004 hp 1
18/12/2004 hp 1
19/12/2004 hp 1
20/12/2004 hp 1
15/12/2004 Microsoft 2
16/12/2004 Microsoft 2
17/12/2004 Microsoft 2
01/01/2004 dell 3
02/01/2004 dell 3
11/01/2004 oracle 4
12/01/2004 oracle 4
Is this possible? By using query thank you
Thank you
notes:
you will see this post in query also
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

convert dates to Series 8
Advanced problem using auto-number and datediff to find readmissio 1
Date range overlap 4
WCG Stats Tuesday 01 August 2023 2
WCG Stats Saturday 23 September 2023 3
Autofil on variable rows, delete extract and show remaining rows 3
WCG Stats Sunday 28 May 2023 3
LOOKUP function between two dates in Excel 2007 6

Top