Append query

[Jan T.]

I finally got it to work right! Yess!!!
I made two queries. The first one is this:

qMain:

SELECT TestTable.TestID, TestTable.From, TestTable.To,
([TestTable].[To]-[TestTable].[From]) AS NumDays
FROM TestTable
ORDER BY ([TestTable].[To]-[TestTable].[From]);

Then I had the tool for making my second query wich I saved as
qSub;

SELECT [qMain].[TestID], [qMain].[From]+[intDays] AS ActualDates
FROM qMain, CountDays
WHERE ((([qMain].[From]+[intDays])<=[qMain].[From]+[qMain].[Numdays]));

It now returns all the dates for each period in one table, the TestTable.

I would never have started trying to write this query in the first place if
it wasn't
for you guys. I would not have come up with this solution just by my self.
So thank you very much for helping out!!!

Have a nice day!!

Sincerely
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
'====================================================