Adding minutes to a start time and to display new time

[Mick99]

Hi all, I thought this was easy but I can't seem to work it out. I am trying to have minutes added to a particular start time and have the query display the new time each time there is an increase in minutes. Below is a mockup of what I am after the start time is 11.00am and I would like this time to increase in respect to the minutes.

Cheers Mick

[ranman256]

Append query.
insert into table (start,end) values(start, now())

but where does the time come from?

[John_G]

You are right, it is not particularly easy, because unlike Excel, Access cannot reference other rows in the table or query. It can only deal with the current row, i.e. the current record. So, in your case, it cannot refer to the row above to know what time to add the "Minutes" value to.

What you could do is put the initial data into a temporary table, use VBA to calculate the "Item Time" values, then display the results in a query.

[knarfreppep]

... Access cannot reference other rows in the table or query. It can only deal with the current row, i.e. the current record. So, in your case, it cannot refer to the row above to know what time to add the "Minutes" value to.

Are you sure about that?

What if the table had an ID field so a query dealing with a row with ID n could look back at the row with ID n-1?

[knarfreppep]

Could something be developed from this? -

SELECT A.bytID, A.dtmTime, (SELECT dtmTime FROM tblTest WHERE bytID = A.bytID-1) AS dtmEarlierTime
FROM tblTest AS A;

[John_G]

Are you sure about that?

Yes.

What if the table had an ID field so a query dealing with a row with ID n could look back at the row with ID n-1?

Still no - a query cannot refer to a row within the query. BUT - see below.

SELECT A.bytID, A.dtmTime, (SELECT dtmTime FROM tblTest WHERE bytID = A.bytID-1) AS dtmEarlierTime
FROM tblTest AS A;

Yes, that might be possible. But it is not "looking at the previous row in the query" - it is using a sub-query to get the value, which is possible only because the data structure (sequential ID) allows you to do it.

In your case, you also need a cumulative value (total number of minutes from start), so maybe you can develop something from this. I made a simple table:

ID	X
1	3
2	6
3	1
4	2
5	5
6	7
7	5
8	1
9	3
10	8

Then, using a query with SQL like this:

SELECT A.ID, A.X, (SELECT X FROM Table2 WHERE ID = A.ID-1) AS NextX, nextX+A.X AS NewX, DSum("X","Table2","ID <=" & [A].[ID]) AS Cumulative
FROM table2 AS A;

I get results like this:

ID	X	NextX	NewX	Cumulative
1	3			3
2	6	3	9	9
3	1	6	7	10
4	2	1	3	12
5	5	2	7	17
6	7	5	12	24
7	5	7	12	29
8	1	5	6	30
9	3	1	4	33
10	8	3	11	41

This works fast as long as there not too many rows in the table. You have to find a way to handle the start time; check the dateadd function to generate the other times properly.

HTH

[knarfreppep]

Still no

I think you may be being a tad dismissive.

'Start Time' in the record with ID = 1 is able to be determined with a query similar to the one I suggested ... from that, 'Item Time' in every record can be calculated using its ID, the DateAdd function, and 'Minutes'.

I suspect the whole thing can be done in one query with the user having only to first update 'Start Time' in ID 1 and 'Minutes' and 'Agenda Item' in as many records as are needed for each new meeting.

[knarfreppep]

I suspect the whole thing can be done in one query ...

It might be doable in 1 ... I took 2.

Create tblAgenda (below).

Create qryAgenda_0 -

SELECT A.bytID, (SELECT dtmMeetingStarts FROM tblAgenda WHERE bytID = 1) AS dtmMeetingStarts_A, A.strItemDescription, A.intDurationMinutes, (SELECT Sum(intDurationMinutes) FROM tblAgenda WHERE bytID <= A.bytID) AS intMinuesOffset
FROM tblAgenda AS A

Create qryAgenda -

SELECT IIf([bytID]=1,Format([dtmMeetingStarts_A],"h:nn AM/PM")) AS [Start Time], qryAgenda_0.intDurationMinutes AS Minues, Format(DateAdd("n",[intMinuesOffset],[dtmMeetingStarts_A]),"h:nn AM/PM") AS [Item Time], qryAgenda_0.strItemDescription AS [Agenda Description]
FROM qryAgenda_0
ORDER BY qryAgenda_0.bytID

Run qryAgenda to see this -


For a new meeting, all one need do is add/delete/change tblAgenda records then re-run qryAgenda.

PS I'm not sure about the logic of the Agenda ... I would have thought 'Item Time' should be the item's start time, not its finish time ... the queries can, of course, be modified to 'fix' that.

Table -

[Mick99]

Wow this is looking awsome, I was starting to think mayby it can't be done. You are a legend.

Cheers Mick

[Mick99]

I have built the table and the first query but i'm not sure where to put the SQL code? Also you are correct 'Item Time' should be the item's start time, not its finish time . Not sure if you could upload a copy of the test you did so I can fully understand how you made this work.

Cheers Mick

[June7]

Don't think really applies in this situation but might be of interest http://allenbrowne.com/subquery-01.html#AnotherRecord

[knarfreppep]

if you could upload a copy of the test you did so I can fully understand how you made this work

Here it is ... Agenda.zip

[Mick99]

Thank you greatly I will look to incorporate it today and let you know how I go.

Cheers Mick

PS Thanks Again