Append query

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.

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


"Jan T." wrote:

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.

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" skrev i melding
news
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


"Jan T." wrote:

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.

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." 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" skrev i melding
news
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


"Jan T." wrote:

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.

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


"Jan T." wrote:

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." 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" skrev i melding
news
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


"Jan T." wrote:

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.

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" 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


"Jan T." wrote:

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." 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" skrev i melding
news 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


"Jan T." wrote:

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.

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


"Jan T." wrote:

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" 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


"Jan T." wrote:

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." 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" skrev i melding
news 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


"Jan T." wrote:

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.

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" 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


"Jan T." wrote:

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" 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


"Jan T." wrote:

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." 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" skrev i melding
news 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


"Jan T." wrote:

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.

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


"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" 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


"Jan T." wrote:

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" 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


"Jan T." wrote:

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." 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" skrev i melding
news 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


"Jan T." wrote:

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.

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" 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


"Jan T." wrote:

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" 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


"Jan T." wrote:

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." 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" skrev i melding
news 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


"Jan T." wrote:

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.