Append query

[Jan T.]

I do have a split database with a front end at the hard disk station C: and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update query
that adds one row in my table for each day between Start date and End date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[KARL DEWEY]

Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end date])));

 

[Jan T.]

I am impressed. I thought this was impossible but you made it look very
easy.
Thank you very much!

Yours sincerely
Jan T.

Create a table named CountNumber with field CountNUM containing numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end
date])));

--
KARL DEWEY
Build a little - Test a little

I do have a split database with a front end at the hard disk station C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update query
that adds one row in my table for each day between Start date and End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[Jan T.]

I now have another challenge. I have one table with dates From and To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date to end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that are over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE (((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First table consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all the dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.

I am impressed. I thought this was impossible but you made it look very
easy.
Thank you very much!

Yours sincerely
Jan T.

Create a table named CountNumber with field CountNUM containing numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end
date])));

--
KARL DEWEY
Build a little - Test a little

I do have a split database with a front end at the hard disk station C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update query
that adds one row in my table for each day between Start date and End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[KARL DEWEY]

Try this --
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates
FROM CountDays, TestTable
WHERE CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

--
KARL DEWEY
Build a little - Test a little

I now have another challenge. I have one table with dates From and To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date to end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that are over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE (((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First table consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all the dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.

I am impressed. I thought this was impossible but you made it look very
easy.
Thank you very much!

Yours sincerely
Jan T.

Create a table named CountNumber with field CountNUM containing numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end
date])));

--
KARL DEWEY
Build a little - Test a little


:

I do have a split database with a front end at the hard disk station C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update query
that adds one row in my table for each day between Start date and End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[Jan T.]

When I tried to run the query, I got an error saying something like this:
Cannot have a (kind of) function in
WHERE CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))

What do I do wrong?

Jan T.

Try this --
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates
FROM CountDays, TestTable
WHERE CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

--
KARL DEWEY
Build a little - Test a little

I now have another challenge. I have one table with dates From and To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date to end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that are
over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First table
consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping
periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all the
dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.

I am impressed. I thought this was impossible but you made it look very
easy.
Thank you very much!

Yours sincerely
Jan T.




"KARL DEWEY" <[email protected]> skrev i melding
Create a table named CountNumber with field CountNUM containing
numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end
date])));

--
KARL DEWEY
Build a little - Test a little


:

I do have a split database with a front end at the hard disk station
C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update query
that adds one row in my table for each day between Start date and End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[KARL DEWEY]

I did not test - I think it does not like the plus sign hung out there - try
this --
WHERE (CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))

--
KARL DEWEY
Build a little - Test a little

When I tried to run the query, I got an error saying something like this:
Cannot have a (kind of) function in
WHERE CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))

What do I do wrong?

Jan T.

Try this --
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates
FROM CountDays, TestTable
WHERE CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

--
KARL DEWEY
Build a little - Test a little

I now have another challenge. I have one table with dates From and To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date to end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that are
over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First table
consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping
periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all the
dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.




"Jan T." <[email protected]> skrev i melding
I am impressed. I thought this was impossible but you made it look very
easy.
Thank you very much!

Yours sincerely
Jan T.




"KARL DEWEY" <[email protected]> skrev i melding
Create a table named CountNumber with field CountNUM containing
numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end
date])));

--
KARL DEWEY
Build a little - Test a little


:

I do have a split database with a front end at the hard disk station
C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update query
that adds one row in my table for each day between Start date and End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[Jan T.]

Hm. I am still getting an error. I have pasted this query into Access SQL
view:

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))))
ORDER BY CVDate([TestTable].[From])+[intDays];

?
Jan T.

I did not test - I think it does not like the plus sign hung out there -
try
this --
WHERE
(CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))

--
KARL DEWEY
Build a little - Test a little

When I tried to run the query, I got an error saying something like this:
Cannot have a (kind of) function in
WHERE CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))

What do I do wrong?

Jan T.

Try this --
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

--
KARL DEWEY
Build a little - Test a little


:

I now have another challenge. I have one table with dates From and To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date to
end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that are
over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First table
consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping
periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all the
dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.




"Jan T." <[email protected]> skrev i melding
I am impressed. I thought this was impossible but you made it look
very
easy.
Thank you very much!

Yours sincerely
Jan T.




"KARL DEWEY" <[email protected]> skrev i melding
Create a table named CountNumber with field CountNUM containing
numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end
date])));

--
KARL DEWEY
Build a little - Test a little


:

I do have a split database with a front end at the hard disk
station
C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update
query
that adds one row in my table for each day between Start date and
End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[KARL DEWEY]

You did not say what the error was.
Why do you have CVDate([TestTable].[From])+[intDays] following AS myDates, ?

Use this --
ORDER BY (Min(CVDate([TestTable].[From]))+[intDays]);

--
KARL DEWEY
Build a little - Test a little

Hm. I am still getting an error. I have pasted this query into Access SQL
view:

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))))
ORDER BY CVDate([TestTable].[From])+[intDays];

?
Jan T.

I did not test - I think it does not like the plus sign hung out there -
try
this --
WHERE
(CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))

--
KARL DEWEY
Build a little - Test a little

When I tried to run the query, I got an error saying something like this:
Cannot have a (kind of) function in
WHERE CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))

What do I do wrong?

Jan T.


"KARL DEWEY" <[email protected]> skrev i melding
Try this --
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

--
KARL DEWEY
Build a little - Test a little


:

I now have another challenge. I have one table with dates From and To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date to
end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that are
over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First table
consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping
periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all the
dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.




"Jan T." <[email protected]> skrev i melding
I am impressed. I thought this was impossible but you made it look
very
easy.
Thank you very much!

Yours sincerely
Jan T.




"KARL DEWEY" <[email protected]> skrev i melding
Create a table named CountNumber with field CountNUM containing
numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end
date])));

--
KARL DEWEY
Build a little - Test a little


:

I do have a split database with a front end at the hard disk
station
C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update
query
that adds one row in my table for each day between Start date and
End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[John Spencer]

Well, if you are using an aggregate function in the query, you are going
to need to use Group by elsewhere AND you can't use an aggregate
function in a where clause you have to transfer that into a HAVING
clause. The following should be correct syntax, however it makes little
logical sense.

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
GROUP BY CVDate([TestTable].[From])+[intDays]
HAVING CVDate([TestTable].[From]) + [intDays]
<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

If I understood your posting, you want a query like the following based
on Karl Dewey's first response.

Query1 a list of all possible dates between the earliest and latest date:
SELECT DateAdd("D",IntDays,DMin("FROM","TestTable") as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

Now base a second query on that and your table (TestTable)
SELECT DISTINCT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hm. I am still getting an error. I have pasted this query into Access SQL
view:

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))))
ORDER BY CVDate([TestTable].[From])+[intDays];

?
Jan T.

I did not test - I think it does not like the plus sign hung out there -
try
this --
WHERE
(CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))

--
KARL DEWEY
Build a little - Test a little

When I tried to run the query, I got an error saying something like this:
Cannot have a (kind of) function in
WHERE CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))

What do I do wrong?

Jan T.


"KARL DEWEY" <[email protected]> skrev i melding
Try this --
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

--
KARL DEWEY
Build a little - Test a little


:

I now have another challenge. I have one table with dates From and To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date to
end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that are
over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First table
consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping
periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all the
dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.




"Jan T." <[email protected]> skrev i melding
I am impressed. I thought this was impossible but you made it look
very
easy.
Thank you very much!

Yours sincerely
Jan T.




"KARL DEWEY" <[email protected]> skrev i melding
Create a table named CountNumber with field CountNUM containing
numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end
date])));

--
KARL DEWEY
Build a little - Test a little


:

I do have a split database with a front end at the hard disk
station
C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update
query
that adds one row in my table for each day between Start date and
End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[Jan T.]

Sorry quys, I am still getting errors. Can it be because I have a Norwegian
Access 2000? I tried to substitute the commas with semiColons ( ; ) but that
did not solve my problems.

This query returns an Syntax error;
SELECT DateAdd("D",IntDays,DMin("FROM","TestTable") as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

This one, returns an aggregate error;
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
GROUP BY CVDate([TestTable].[From])+[intDays]
HAVING CVDate([TestTable].[From]) + [intDays]
<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

Error says; You have tried to run a query that does not contain the
expression
Min(CVDate([TestTable].[From]))+[intDays] AS myDates as part of an aggregate
function?

Just to repeat the purpose of my query. I do have one table with the records
and a FromDate field and
a ToDate field. Like when a person is absence from work i.e. 4 days. Then I
want the query to return all
the dates that this person was absent. If two persons are absent on the same
dates, the query shall return
the same dates twice for those days (records) when we have overlaping
periods.

Thanks anyway so far!

Jan T.

Well, if you are using an aggregate function in the query, you are going
to need to use Group by elsewhere AND you can't use an aggregate function
in a where clause you have to transfer that into a HAVING clause. The
following should be correct syntax, however it makes little logical sense.

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
GROUP BY CVDate([TestTable].[From])+[intDays]
HAVING CVDate([TestTable].[From]) + [intDays]
<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

If I understood your posting, you want a query like the following based on
Karl Dewey's first response.

Query1 a list of all possible dates between the earliest and latest date:
SELECT DateAdd("D",IntDays,DMin("FROM","TestTable") as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

Now base a second query on that and your table (TestTable)
SELECT DISTINCT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hm. I am still getting an error. I have pasted this query into Access SQL
view:

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))))
ORDER BY CVDate([TestTable].[From])+[intDays];

?
Jan T.

I did not test - I think it does not like the plus sign hung out there -
try
this --
WHERE
(CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))

--
KARL DEWEY
Build a little - Test a little


:

When I tried to run the query, I got an error saying something like
this:
Cannot have a (kind of) function in
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))

What do I do wrong?

Jan T.


"KARL DEWEY" <[email protected]> skrev i melding
Try this --
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

--
KARL DEWEY
Build a little - Test a little


:

I now have another challenge. I have one table with dates From and
To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date to
end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that are
over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First table
consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping
periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all the
dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.




"Jan T." <[email protected]> skrev i melding
I am impressed. I thought this was impossible but you made it look
very
easy.
Thank you very much!

Yours sincerely
Jan T.




"KARL DEWEY" <[email protected]> skrev i melding
Create a table named CountNumber with field CountNUM containing
numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end
date])));

--
KARL DEWEY
Build a little - Test a little


:

I do have a split database with a front end at the hard disk
station
C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update
query
that adds one row in my table for each day between Start date and
End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[Jan T.]

You did not say what the error was.
Why do you have CVDate([TestTable].[From])+[intDays] following AS
myDates, ?

Use this --
ORDER BY (Min(CVDate([TestTable].[From]))+[intDays]);

--
KARL DEWEY
Build a little - Test a little

Hm. I am still getting an error. I have pasted this query into Access SQL
view:

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))))
ORDER BY CVDate([TestTable].[From])+[intDays];

?
Jan T.

I did not test - I think it does not like the plus sign hung out there -
try
this --
WHERE
(CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))

--
KARL DEWEY
Build a little - Test a little


:

When I tried to run the query, I got an error saying something like
this:
Cannot have a (kind of) function in
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))

What do I do wrong?

Jan T.


"KARL DEWEY" <[email protected]> skrev i melding
Try this --
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

--
KARL DEWEY
Build a little - Test a little


:

I now have another challenge. I have one table with dates From and
To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date
to
end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that
are
over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First
table
consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping
periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all
the
dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.




"Jan T." <[email protected]> skrev i melding
I am impressed. I thought this was impossible but you made it look
very
easy.
Thank you very much!

Yours sincerely
Jan T.




"KARL DEWEY" <[email protected]> skrev i
melding
Create a table named CountNumber with field CountNUM containing
numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter
end
date])));

--
KARL DEWEY
Build a little - Test a little


:

I do have a split database with a front end at the hard disk
station
C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update
query
that adds one row in my table for each day between Start date
and
End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[KARL DEWEY]

Try this --
SELECT (Min(CVDate([TestTable].[From]))+[intDays]) AS myDates,
FROM CountDays, TestTable
GROUP BY (CVDate([TestTable].[From])+[intDays])
HAVING (CVDate([TestTable].[From]) + [intDays])
<=Max(CVDate([TestTable].[To]))
ORDER BY (Min(CVDate([TestTable].[From]))+[intDays]);

--
KARL DEWEY
Build a little - Test a little

Sorry quys, I am still getting errors. Can it be because I have a Norwegian
Access 2000? I tried to substitute the commas with semiColons ( ; ) but that
did not solve my problems.

This query returns an Syntax error;
SELECT DateAdd("D",IntDays,DMin("FROM","TestTable") as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

This one, returns an aggregate error;
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
GROUP BY CVDate([TestTable].[From])+[intDays]
HAVING CVDate([TestTable].[From]) + [intDays]
<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

Error says; You have tried to run a query that does not contain the
expression
Min(CVDate([TestTable].[From]))+[intDays] AS myDates as part of an aggregate
function?

Just to repeat the purpose of my query. I do have one table with the records
and a FromDate field and
a ToDate field. Like when a person is absence from work i.e. 4 days. Then I
want the query to return all
the dates that this person was absent. If two persons are absent on the same
dates, the query shall return
the same dates twice for those days (records) when we have overlaping
periods.

Thanks anyway so far!

Jan T.

Well, if you are using an aggregate function in the query, you are going
to need to use Group by elsewhere AND you can't use an aggregate function
in a where clause you have to transfer that into a HAVING clause. The
following should be correct syntax, however it makes little logical sense.

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
GROUP BY CVDate([TestTable].[From])+[intDays]
HAVING CVDate([TestTable].[From]) + [intDays]
<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

If I understood your posting, you want a query like the following based on
Karl Dewey's first response.

Query1 a list of all possible dates between the earliest and latest date:
SELECT DateAdd("D",IntDays,DMin("FROM","TestTable") as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

Now base a second query on that and your table (TestTable)
SELECT DISTINCT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hm. I am still getting an error. I have pasted this query into Access SQL
view:

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))))
ORDER BY CVDate([TestTable].[From])+[intDays];

?
Jan T.



"KARL DEWEY" <[email protected]> skrev i melding
I did not test - I think it does not like the plus sign hung out there -
try
this --
WHERE
(CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))

--
KARL DEWEY
Build a little - Test a little


:

When I tried to run the query, I got an error saying something like
this:
Cannot have a (kind of) function in
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))

What do I do wrong?

Jan T.


"KARL DEWEY" <[email protected]> skrev i melding
Try this --
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

--
KARL DEWEY
Build a little - Test a little


:

I now have another challenge. I have one table with dates From and
To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date to
end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that are
over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First table
consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping
periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all the
dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.




"Jan T." <[email protected]> skrev i melding
I am impressed. I thought this was impossible but you made it look
very
easy.
Thank you very much!

Yours sincerely
Jan T.




"KARL DEWEY" <[email protected]> skrev i melding
Create a table named CountNumber with field CountNUM containing
numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end
date])));

--
KARL DEWEY
Build a little - Test a little


:

I do have a split database with a front end at the hard disk
station
C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update
query
that adds one row in my table for each day between Start date and
End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[John Spencer]

First query is missing a closing parentheses.

SELECT DateAdd("D",IntDays,DMin("FROM","TestTable")) as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

Second query should drop the DISTINCT if you want dates reported
multiple times.


SELECT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Sorry quys, I am still getting errors. Can it be because I have a Norwegian
Access 2000? I tried to substitute the commas with semiColons ( ; ) but that
did not solve my problems.

This query returns an Syntax error;
SELECT DateAdd("D",IntDays,DMin("FROM","TestTable") as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

This one, returns an aggregate error;
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
GROUP BY CVDate([TestTable].[From])+[intDays]
HAVING CVDate([TestTable].[From]) + [intDays]
<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

Error says; You have tried to run a query that does not contain the
expression
Min(CVDate([TestTable].[From]))+[intDays] AS myDates as part of an aggregate
function?

Just to repeat the purpose of my query. I do have one table with the records
and a FromDate field and
a ToDate field. Like when a person is absence from work i.e. 4 days. Then I
want the query to return all
the dates that this person was absent. If two persons are absent on the same
dates, the query shall return
the same dates twice for those days (records) when we have overlaping
periods.

Thanks anyway so far!

Jan T.

Well, if you are using an aggregate function in the query, you are going
to need to use Group by elsewhere AND you can't use an aggregate function
in a where clause you have to transfer that into a HAVING clause. The
following should be correct syntax, however it makes little logical sense.

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
GROUP BY CVDate([TestTable].[From])+[intDays]
HAVING CVDate([TestTable].[From]) + [intDays]
<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

If I understood your posting, you want a query like the following based on
Karl Dewey's first response.

Query1 a list of all possible dates between the earliest and latest date:
SELECT DateAdd("D",IntDays,DMin("FROM","TestTable") as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

Now base a second query on that and your table (TestTable)
SELECT DISTINCT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hm. I am still getting an error. I have pasted this query into Access SQL
view:

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))))
ORDER BY CVDate([TestTable].[From])+[intDays];

?
Jan T.



"KARL DEWEY" <[email protected]> skrev i melding
I did not test - I think it does not like the plus sign hung out there -
try
this --
WHERE
(CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))

--
KARL DEWEY
Build a little - Test a little


:

When I tried to run the query, I got an error saying something like
this:
Cannot have a (kind of) function in
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))

What do I do wrong?

Jan T.


"KARL DEWEY" <[email protected]> skrev i melding
Try this --
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

--
KARL DEWEY
Build a little - Test a little


:

I now have another challenge. I have one table with dates From and
To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date to
end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that are
over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First table
consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping
periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all the
dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.




"Jan T." <[email protected]> skrev i melding
I am impressed. I thought this was impossible but you made it look
very
easy.
Thank you very much!

Yours sincerely
Jan T.




"KARL DEWEY" <[email protected]> skrev i melding
Create a table named CountNumber with field CountNUM containing
numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter end
date])));

--
KARL DEWEY
Build a little - Test a little


:

I do have a split database with a front end at the hard disk
station
C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update
query
that adds one row in my table for each day between Start date and
End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[Jan T.]

Well, now I can run the first query but it treats CountDays as a Parameter.
When changing to SQL Design-mode, I have the field; [CountDays].
Running the query returns a Parameter Input Box. If I write 1 and OK it
returns appr. 778 records with dates... ?

Jan T.

First query is missing a closing parentheses.

SELECT DateAdd("D",IntDays,DMin("FROM","TestTable")) as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

Second query should drop the DISTINCT if you want dates reported multiple
times.


SELECT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Sorry quys, I am still getting errors. Can it be because I have a
Norwegian
Access 2000? I tried to substitute the commas with semiColons ( ; ) but
that
did not solve my problems.

This query returns an Syntax error;
SELECT DateAdd("D",IntDays,DMin("FROM","TestTable") as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

This one, returns an aggregate error;
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
GROUP BY CVDate([TestTable].[From])+[intDays]
HAVING CVDate([TestTable].[From]) + [intDays]
<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

Error says; You have tried to run a query that does not contain the
expression
Min(CVDate([TestTable].[From]))+[intDays] AS myDates as part of an
aggregate function?

Just to repeat the purpose of my query. I do have one table with the
records and a FromDate field and
a ToDate field. Like when a person is absence from work i.e. 4 days. Then
I want the query to return all
the dates that this person was absent. If two persons are absent on the
same dates, the query shall return
the same dates twice for those days (records) when we have overlaping
periods.

Thanks anyway so far!

Jan T.

Well, if you are using an aggregate function in the query, you are going
to need to use Group by elsewhere AND you can't use an aggregate
function in a where clause you have to transfer that into a HAVING
clause. The following should be correct syntax, however it makes little
logical sense.

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
GROUP BY CVDate([TestTable].[From])+[intDays]
HAVING CVDate([TestTable].[From]) + [intDays]
<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

If I understood your posting, you want a query like the following based
on Karl Dewey's first response.

Query1 a list of all possible dates between the earliest and latest
date:
SELECT DateAdd("D",IntDays,DMin("FROM","TestTable") as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

Now base a second query on that and your table (TestTable)
SELECT DISTINCT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Jan T. wrote:
Hm. I am still getting an error. I have pasted this query into Access
SQL view:

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))))
ORDER BY CVDate([TestTable].[From])+[intDays];

?
Jan T.



"KARL DEWEY" <[email protected]> skrev i melding
I did not test - I think it does not like the plus sign hung out
there - try
this --
WHERE
(CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))

--
KARL DEWEY
Build a little - Test a little


:

When I tried to run the query, I got an error saying something like
this:
Cannot have a (kind of) function in
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))

What do I do wrong?

Jan T.


"KARL DEWEY" <[email protected]> skrev i melding
Try this --
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

--
KARL DEWEY
Build a little - Test a little


:

I now have another challenge. I have one table with dates From and
To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date
to end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that
are
over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First
table
consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping
periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all
the
dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.




"Jan T." <[email protected]> skrev i melding
I am impressed. I thought this was impossible but you made it look
very
easy.
Thank you very much!

Yours sincerely
Jan T.




"KARL DEWEY" <[email protected]> skrev i melding
Create a table named CountNumber with field CountNUM containing
numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter
end
date])));

--
KARL DEWEY
Build a little - Test a little


:

I do have a split database with a front end at the hard disk
station
C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update
query
that adds one row in my table for each day between Start date
and End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[Jan T.]

Tried this. I tried to translate th Error message:
SELECT -part of sentence contains a reserved word or argument missing or
misspelled, or has the
wrong separator (? separator like comma, semicolon, period and so forth...)

I really can not see what is wrong.

- - - -
I would like to make my sample table a little smaller what periods concerns.
This, just to make it
easier to write what the query should return.

Then my query should return the following dates:

1 #01/02/09#
1 #01/03/09#
1 #01/04/09#
1 #01/05/09#
1 #01/06/09#
1 #01/07/09#
2 #01/06/09#
2 #01/07/09#
2 #01/08/09#
2 #01/09/09#
3 #01/20/09#
3 #01/21/09#
3 #01/22/09#
3 #01/23/09#

Do you get that on your Access?


Jan T.

Try this --
SELECT (Min(CVDate([TestTable].[From]))+[intDays]) AS myDates,
FROM CountDays, TestTable
GROUP BY (CVDate([TestTable].[From])+[intDays])
HAVING (CVDate([TestTable].[From]) + [intDays])
<=Max(CVDate([TestTable].[To]))
ORDER BY (Min(CVDate([TestTable].[From]))+[intDays]);

--
KARL DEWEY
Build a little - Test a little

Sorry quys, I am still getting errors. Can it be because I have a
Norwegian
Access 2000? I tried to substitute the commas with semiColons ( ; ) but
that
did not solve my problems.

This query returns an Syntax error;
SELECT DateAdd("D",IntDays,DMin("FROM","TestTable") as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

This one, returns an aggregate error;
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
GROUP BY CVDate([TestTable].[From])+[intDays]
HAVING CVDate([TestTable].[From]) + [intDays]
<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

Error says; You have tried to run a query that does not contain the
expression
Min(CVDate([TestTable].[From]))+[intDays] AS myDates as part of an
aggregate
function?

Just to repeat the purpose of my query. I do have one table with the
records
and a FromDate field and
a ToDate field. Like when a person is absence from work i.e. 4 days. Then
I
want the query to return all
the dates that this person was absent. If two persons are absent on the
same
dates, the query shall return
the same dates twice for those days (records) when we have overlaping
periods.

Thanks anyway so far!

Jan T.

Well, if you are using an aggregate function in the query, you are
going
to need to use Group by elsewhere AND you can't use an aggregate
function
in a where clause you have to transfer that into a HAVING clause. The
following should be correct syntax, however it makes little logical
sense.

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
GROUP BY CVDate([TestTable].[From])+[intDays]
HAVING CVDate([TestTable].[From]) + [intDays]
<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

If I understood your posting, you want a query like the following based
on
Karl Dewey's first response.

Query1 a list of all possible dates between the earliest and latest
date:
SELECT DateAdd("D",IntDays,DMin("FROM","TestTable") as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

Now base a second query on that and your table (TestTable)
SELECT DISTINCT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Jan T. wrote:
Hm. I am still getting an error. I have pasted this query into Access
SQL
view:

SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates,
CVDate([TestTable].[From])+[intDays]
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))))
ORDER BY CVDate([TestTable].[From])+[intDays];

?
Jan T.



"KARL DEWEY" <[email protected]> skrev i melding
I did not test - I think it does not like the plus sign hung out
there -
try
this --
WHERE
(CVDate([TestTable].[From])+[intDays])<=Max(CVDate([TestTable].[To]))

--
KARL DEWEY
Build a little - Test a little


:

When I tried to run the query, I got an error saying something like
this:
Cannot have a (kind of) function in
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))

What do I do wrong?

Jan T.


"KARL DEWEY" <[email protected]> skrev i melding
Try this --
SELECT Min(CVDate([TestTable].[From]))+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
CVDate([TestTable].[From])+[intDays]<=Max(CVDate([TestTable].[To]))
ORDER BY CVDate([TestTable].[From])+[intDays];

--
KARL DEWEY
Build a little - Test a little


:

I now have another challenge. I have one table with dates From and
To.
In my second table I have numbers from 0 to 700.
Below I had to write parameters to query all dates from start date
to
end
date.
Now I want to query all the dates for all records in my TestTable.
However, I don't know how to get the dates from the periods that
are
over-
laping each other. Se my sample query:

SELECT CVDate([TestTable].[From])+[intDays] AS myDates
FROM CountDays, TestTable
WHERE
(((CVDate([TestTable].[From])+[intDays])<=CVDate([TestTable].[To])))
ORDER BY CVDate([TestTable].[From])+[intDays];

I have two tables that is the source data for the query. First
table
consist
of the following fields and data:

Table: TestTable
TestID From To
1 #01/02/09# #01/10/09#
2 #01/07/09# #01/13/09#
3 #01/20/09# #01/23/09#

The query will not return the dates for periods that is overlaping
periods
like
TestID 1 and TestID 2.

I kind of understand that, but how can I have my query return all
the
dates
based on every record in my TestTable Table?

Thank you very much for any help!

Yours sincerely
Jan T.




"Jan T." <[email protected]> skrev i melding
I am impressed. I thought this was impossible but you made it
look
very
easy.
Thank you very much!

Yours sincerely
Jan T.




"KARL DEWEY" <[email protected]> skrev i
melding
Create a table named CountNumber with field CountNUM containing
numbers
from
0 (zero) through your maximum spread.
Use the SQL below in your append query --
SELECT CVDate([Enter start date])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([Enter start date])+[CountNUM])<=CVDate([Enter
end
date])));

--
KARL DEWEY
Build a little - Test a little


:

I do have a split database with a front end at the hard disk
station
C:
and
the backEnd, data tables, on a shared folder.

Now I want to make a form where the user should be able to put
in a Start Date and an End Date. Next I want to make a update
query
that adds one row in my table for each day between Start date
and
End
date.

What would be the best way to do this?

Appriciate your help.

Regards
Jan T.

 

[John Spencer]

Head slap, bang head against wall.


SELECT DateAdd("D",IntDays,DMin("FROM","TestTable")) as PossibleDate
FROM CountDays
WHERE CountDays.IntDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

This assumes that intDays is the field name and CountDays is the table
name. Hope I got it right this time.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Well, now I can run the first query but it treats CountDays as a Parameter.
When changing to SQL Design-mode, I have the field; [CountDays].
Running the query returns a Parameter Input Box. If I write 1 and OK it
returns appr. 778 records with dates... ?

Jan T.

First query is missing a closing parentheses.

SELECT DateAdd("D",IntDays,DMin("FROM","TestTable")) as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

Second query should drop the DISTINCT if you want dates reported multiple
times.


SELECT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jan T.]

Yesss! You got it right this time!!!
You shall know that I am so greatful and impressed of what you're doing and
the patience you show is awesome. Thanks a million! This actually works
fine.

I returns appr. 44 records I think, but that is before I have started on the
2. query. (My first query I saved as qryDates). Tomorrow I will try to
put in the right substitutes (words) in the next step:

SELECT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate

My guess is that I substitute [AboveSavedQuery] with [qryDates], right?
And then you are using q1? Not quite sure where that comes from but
right now it is midnight in Norway so I am going to bed. Good night
and I will be back to morrow. Again thank you all so much for helping!

Sincerely
Jan T.

- - - - - -

Head slap, bang head against wall.


SELECT DateAdd("D",IntDays,DMin("FROM","TestTable")) as PossibleDate
FROM CountDays
WHERE CountDays.IntDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

This assumes that intDays is the field name and CountDays is the table
name. Hope I got it right this time.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Well, now I can run the first query but it treats CountDays as a
Parameter.
When changing to SQL Design-mode, I have the field; [CountDays].
Running the query returns a Parameter Input Box. If I write 1 and OK it
returns appr. 778 records with dates... ?

Jan T.

First query is missing a closing parentheses.

SELECT DateAdd("D",IntDays,DMin("FROM","TestTable")) as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

Second query should drop the DISTINCT if you want dates reported
multiple times.


SELECT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

Yes, you need to substitute the name of your query for the name I used.

Q1 is just an alias for the query. You could just use the name of your
query everywhere I had Q1. Using the alias in a query allows you to
have several instances of the same query or table. It also allows
simplifying referring to long complex query or table names.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Yesss! You got it right this time!!!
You shall know that I am so greatful and impressed of what you're doing and
the patience you show is awesome. Thanks a million! This actually works
fine.

I returns appr. 44 records I think, but that is before I have started on the
2. query. (My first query I saved as qryDates). Tomorrow I will try to
put in the right substitutes (words) in the next step:

SELECT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate

My guess is that I substitute [AboveSavedQuery] with [qryDates], right?
And then you are using q1? Not quite sure where that comes from but
right now it is midnight in Norway so I am going to bed. Good night
and I will be back to morrow. Again thank you all so much for helping!

Sincerely
Jan T.

- - - - - -

Head slap, bang head against wall.


SELECT DateAdd("D",IntDays,DMin("FROM","TestTable")) as PossibleDate
FROM CountDays
WHERE CountDays.IntDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

This assumes that intDays is the field name and CountDays is the table
name. Hope I got it right this time.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Well, now I can run the first query but it treats CountDays as a
Parameter.
When changing to SQL Design-mode, I have the field; [CountDays].
Running the query returns a Parameter Input Box. If I write 1 and OK it
returns appr. 778 records with dates... ?

Jan T.



"John Spencer" <[email protected]> skrev i melding
First query is missing a closing parentheses.

SELECT DateAdd("D",IntDays,DMin("FROM","TestTable")) as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

Second query should drop the DISTINCT if you want dates reported
multiple times.


SELECT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Jan T.]

I finally got the query to run. However, there is one problem left.
The query does not return ActualDates more than one time. That
is it only returns uniqe dates and I want those periods that overlaps
each other to show the ActualDates for each record.

Say first period was September the 1st to Sep the 3rd.
The second period was Sep the 2nd to Sep the 4th.
Then my query should return the following dates:

ActualDates
#09/01/08#
#09/02/08#
#09/03/08#
#09/02/08#
#09/03/08#
#09/04/08#

This is because I will make statistics or reports for employees showing
number of days of absence per person.

Jan T.

Yes, you need to substitute the name of your query for the name I used.

Q1 is just an alias for the query. You could just use the name of your
query everywhere I had Q1. Using the alias in a query allows you to have
several instances of the same query or table. It also allows simplifying
referring to long complex query or table names.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Yesss! You got it right this time!!!
You shall know that I am so greatful and impressed of what you're doing
and
the patience you show is awesome. Thanks a million! This actually works
fine.

I returns appr. 44 records I think, but that is before I have started on
the
2. query. (My first query I saved as qryDates). Tomorrow I will try to
put in the right substitutes (words) in the next step:

SELECT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate

My guess is that I substitute [AboveSavedQuery] with [qryDates], right?
And then you are using q1? Not quite sure where that comes from but
right now it is midnight in Norway so I am going to bed. Good night
and I will be back to morrow. Again thank you all so much for helping!

Sincerely
Jan T.

- - - - - -

Head slap, bang head against wall.


SELECT DateAdd("D",IntDays,DMin("FROM","TestTable")) as PossibleDate
FROM CountDays
WHERE CountDays.IntDays <=
DMax("To","TestTable")-DMin("FROM","TestTable")

This assumes that intDays is the field name and CountDays is the table
name. Hope I got it right this time.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Jan T. wrote:
Well, now I can run the first query but it treats CountDays as a
Parameter.
When changing to SQL Design-mode, I have the field; [CountDays].
Running the query returns a Parameter Input Box. If I write 1 and OK it
returns appr. 778 records with dates... ?

Jan T.



"John Spencer" <[email protected]> skrev i melding
First query is missing a closing parentheses.

SELECT DateAdd("D",IntDays,DMin("FROM","TestTable")) as PossibleDate
FROM CountDays
WHERE CountDays <= DMax("To","TestTable")-DMin("FROM","TestTable")

Second query should drop the DISTINCT if you want dates reported
multiple times.


SELECT q1.PossibleDate as ActualDates
FROM [AboveSavedQuery] as q1 INNER JOIN TestTable
On q1.PossibleDate >= TestTable.FROM
AND q1.PossibleDate <=TestTable.To
ORDER BY q1.PossibleDate



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================