Append Query

[f123 via AccessMonster.com]

hi all, am working in database, where i have table with [StartDate] &
[EndDate].
also, there is query where i calculate [Rank] "number format" field.

Next I want to make a update query that adds one row in subform in the main
form for each day between Start date and End date based on [Rank].

example:
[StartDate]: 01/01/2009
[EndDate]: 31/12/2009
[Rank]: 30

Results in the suborm:
31/01/2009
29/02/2009
.....
.....
31/12/2009
in other words, make auto fields for the dates based on [Rank]

What would be the best way to do this?

Appriciate your help.
What would be the best way to do this?

Appriciate your help.

 

[Noëlla Gabriël]

Hi there,

I don't think this can be done with a query. You'll need to write a VBA
procedure to do this. I don't see exactly whet the Rank parameter does.
Does it state the interval between 2 dates? So if [Rank] = 30, you always add
30 days to the previous date?
By the way, 29-2-2009 doesn't exist...

 

[KARL DEWEY]

Next I want to make a update query that adds one row in subform in the main
form for each day between Start date and End date based on [Rank].

The query would not be an update but an append query. It would not add rows
to subform but to a table that would be displayed in your subform.

I do not see how Rank would be used here. What doe the Rank value have to
do with the dates being appended?

To append those dates create a table named CountNumber with number field
CountNUM with numbers from 0 (zero) through your maximum value.

INSERT INTO Table1 ( Date1 )
SELECT CVDate([StartDate])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([StartDate])+[CountNUM])<=CVDate([EndDate])));

 

[f123 via AccessMonster.com]

thanks for your reply, the [Rank] would be used as number of days to be added
to the date
for example,if the [StartDate] is 01/01/2009 & [EndDate] is 31/12/2009 &
[Rank] is 30 that mean the query must insert 12 dates between each one is 30
days

if the [Rank] is 20, that mean the query must insert 18 dates between each
one is 20 days...and so on.

i tried your solution, but i think its append all the dates between
[StartDate] & [EndDate].

any help or other advice on this

The query would not be an update but an append query. It would not add rows
to subform but to a table that would be displayed in your subform.

I do not see how Rank would be used here. What doe the Rank value have to
do with the dates being appended?

To append those dates create a table named CountNumber with number field
CountNUM with numbers from 0 (zero) through your maximum value.

INSERT INTO Table1 ( Date1 )
SELECT CVDate([StartDate])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([StartDate])+[CountNUM])<=CVDate([EndDate])));

 

[John W. Vinson]

thanks for your reply, the [Rank] would be used as number of days to be added
to the date
for example,if the [StartDate] is 01/01/2009 & [EndDate] is 31/12/2009 &
[Rank] is 30 that mean the query must insert 12 dates between each one is 30
days

if the [Rank] is 20, that mean the query must insert 18 dates between each
one is 20 days...and so on.

i tried your solution, but i think its append all the dates between
[StartDate] & [EndDate].

any help or other advice on this

You can do this with the help of a little auxiliary table. I'll usually have a
table named Num, with one primary key long integer field N, filled with values
from 0 to 10000 or so (it's still a tiny table at that size).

You can run an Append query based on Num (and any other table that you need,
say for foreign keys); use a criterion on N of

< [Rank]

to select exactly [Rank] rows from Num, and a calculated date field of

DateAdd("d", [N], [StartDate])

 

[KARL DEWEY]

Try this --
INSERT INTO Table1 ( Date1 )
SELECT CVDate([StartDate])+[CountNUM]*[Rank] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([StartDate])+[CountNUM]*[Rank])<=CVDate([EndDate])));

thanks for your reply, the [Rank] would be used as number of days to be added
to the date
for example,if the [StartDate] is 01/01/2009 & [EndDate] is 31/12/2009 &
[Rank] is 30 that mean the query must insert 12 dates between each one is 30
days

if the [Rank] is 20, that mean the query must insert 18 dates between each
one is 20 days...and so on.

i tried your solution, but i think its append all the dates between
[StartDate] & [EndDate].

any help or other advice on this

The query would not be an update but an append query. It would not add rows
to subform but to a table that would be displayed in your subform.

I do not see how Rank would be used here. What doe the Rank value have to
do with the dates being appended?

To append those dates create a table named CountNumber with number field
CountNUM with numbers from 0 (zero) through your maximum value.

INSERT INTO Table1 ( Date1 )
SELECT CVDate([StartDate])+[CountNUM] AS [My Dates]
FROM CountNumber
WHERE (((CVDate([StartDate])+[CountNUM])<=CVDate([EndDate])));

 

[f123 via AccessMonster.com]

Thanks john, i have tried your solution but i dinot get what i want,

I think I have to do somekind of counter in an append
query and using the dateAdd function to create the Dates required based on
the number of days[Rank] i have to add but dont know how to do this.

i tried this but i think its need arrangment:

Private Sub Form_Timer()
Dim dt As Date 'Loop controller
Dim rs As DAO.Recordset 'Table to append records to.


If DateDiff("m", Me.StartDate, Me.EndDate) > 1 Then
dt = Me.StartDate
Set rs = OpenRecordset("Start Query", dbOpenDynaset, dbAppendOnly)
With rs
Do While dt <= EndDate
.AddNew
!Schd = dt
.Update
dt = DateAdd("d", [Rank], dt)
Loop
End With
rs.Close
End If
Set rs = Nothing

End Sub

i dont know whats wrong, i got an error :
Set rs = OpenRecordset: Not Defined

any help