calculate date query with date interval

[MLHab306]

Hi

I am a newbie to access 2000. I am trying to create a query to calulate a new date + interval. I have done several searches of the Tek-Tips with different key words before asking this.

I would like the user to be able to enter a start date and interval ie 14 and let access calculate appts for the rest of the year or for the next year.
I tried to [Date_Last_done]+[Intervalofappt] AS datenextAppt1, [dateNextAppt1]+[Intervalofappt] AS datenextAppt2, etc. When I do this I get prompted for each datenextappX. I included the sql to help.

This does not need to be linked to any table. I just want to be able to do simple calculations. I would output the results to a form, also not linked to a table, in datasheet format listing each datenextapptX and the user would manually enter dates in another table.

SELECT Customers.LastName, Customers.IntervalOfAppt, Date_last_done.Date_last_done, Date_last_done.DateNextAppt, [Date_Last_done]+[Intervalofappt] AS datenextAppt1, [dateNextAppt1]+[Intervalofappt] AS datenextAppt2, [dateNextAppt2]+[Intervalofappt] AS datenextAppt3, [dateNextAppt3]+[Intervalofappt] AS datenextAppt4,
etc ...
[dateNextAppt30]+[Intervalofappt] AS datenextAppt31, [dateNextAppt31]+[Intervalofappt] AS datenextAppt32
FROM Customers LEFT JOIN Date_last_done ON Customers.CustomerID = Date_last_done.Customer_ID
ORDER BY Date_last_done.Date_last_done, Date_last_done.DateNextAppt, [Date_Last_done]+[Intervalofappt], [dateNextAppt1]+[Intervalofappt], [dateNextAppt2]+[Intervalofappt], [dateNextAppt3]+[Intervalofappt], [dateNextAppt4]+[Intervalofappt],
etc...
[dateNextAppt29]+[Intervalofappt], [dateNextAppt30]+[Intervalofappt], [dateNextAppt31]+[Intervalofappt];

Thank you for your suggestions
Mark

 

[RichUK]

There doesn't seem any logic to use a function to project dates and then get the user to enter them manually into a table. I can give you a function to project the dates but it will write them to a table, you can of course delete them after if you wish. Post back if your interested.

 

[dneufarth]

SELECT adate AS Expr1, ainterval AS Expr2, dateadd("d",ainterval,adate) AS x1 , dateadd("d",ainterval,x1) as x2
FROM table1;

should handle the date math.


Dave

 

[MLHab306]

Hi

Thank you for responding. The logic behind this was in a query I have the field Date_Last_done for the client. The client may have been seen 1 times all the way up to 52 times in the last year, or time period. The only way I was able to figure out for the year or time period was to repeat the equation + 1. When I created the above query I ended up with X columns across the datasheet. Some of the fields have little or no date while building to lots of date. After I posted I created a form which displayed only one of each field DateNext1, DateNext2, etc. I am very open to all suggestions. I am willing to try whatever is suggested, so I can decide which one will do the best job for me.

I started with thread 181-166116. Using an update query. I do not understand completely about the update query and what would happen if the user ran it repeatedly. Also my concern is that the user would run it repeatedly and change the dates or the interval by 1 or 2 or ? days and ruin the whole schedule for everyone. I hope this better explains why I wanted the user to enter in manually.

Mark

 

[MichaelRed]

You should start with (by) understanding data structures (e.g. Tables). Design a data structure (one or more tables) which include the 'necessary and sufficient' information in a normalized from. Once you see the and understand this, proceed to review SQL, so you can understand what an update query is and how it will affect the data structure. In this latter process, you will also discover other query types and processes which will limit the changes caused by executing various queries.

I do not mean to be harsh, but from the question and YOUR responses, it may be a while before you are ready to really participate in these forums. Your question shows a distinct lack of understanding of even the most basic issues of Ms. Acceess and relational databases. I would advise you to visit your local bookstore and find an introductory text on Ms. Access. Read it and perform the exercises. Get past the novice / beginner tremors and then retur here for advice on more advanced techniques.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MLHab306]

I appreciate your honesty. I agree I need to learn more. I learned before I ask for help to check out the threads to see if my questions have been asked before. I found that the same question is asked repeatedly but with different responses which is very educational.

Before I even went on line I asked all my computer friends with MCSE and CNE etc for help, but no one had ever dealt with access. I have become best friends with Access Help.

Most of the books that I have seen either are aimed at the beginner or at the advanced expert or are so poorly written with little on no examples, so I don't have one yet. I started with a text book and class to get this far.

Mark