Prev: Final Results
Next: Switchboard in Access 2007
From: StuJol on 20 May 2010 05:13
Using Access 2003 i have a table named 'Trip TimePeriod' which looks like

Minute TimePeriod Total
1 00:01:00 0
2 00:02:00 0
3 00:03:00 0
4 00:04:00 0
5 00:05:00 0
...60 01:00:00 0

has 60 records representing 60 minutes. Data to be used for a pivotchart to
show the total number of trips for 60 minutes. the table is to be used as a
template to get me my 60 minute time scale even with null totals.

i have a qry which give me my data named 'Alarms - Totals After Trip - 60
Minutes', which looks like

Event Type State Date TimePeriod Total
ALARM ACT/UNACK 29/04/2010 14:10:00 1
ALARM ACT/UNACK 29/04/2010 14:15:00 2
ALARM ACT/UNACK 29/04/2010 14:17:00 1
ALARM ACT/UNACK 29/04/2010 14:18:00 1
ALARM ACT/UNACK 29/04/2010 14:22:00 1
ALARM ACT/UNACK 29/04/2010 14:38:00 2
ALARM ACT/UNACK 29/04/2010 14:41:00 1
ALARM ACT/UNACK 29/04/2010 14:52:00 6
ALARM ACT/UNACK 29/04/2010 14:53:00 2
ALARM ACT/UNACK 29/04/2010 14:54:00 5
ALARM ACT/UNACK 29/04/2010 14:55:00 2
ALARM ACT/UNACK 29/04/2010 14:57:00 1
ALARM ACT/UNACK 29/04/2010 14:58:00 5
ALARM ACT/UNACK 29/04/2010 14:59:00 1
ALARM ACT/UNACK 29/04/2010 15:03:00 2
ALARM ACT/UNACK 29/04/2010 15:05:00 4
ALARM ACT/UNACK 29/04/2010 15:07:00 3

i need to put the first record of the qry into the first record of the
table, so whatever the timestamp of the qry, it is represented as the first
record in the table. i then need to add the rest of the qry into the table
using the timestamp. so where no data exists in the qry for a time period,
then the table remains at 0 total

From: KARL DEWEY on 20 May 2010 11:08
Try these tw queries ( UNTESTED ) --
qryAlarms --
SELECT [Event Type], [State], [Date], [TimePeriod], [Total], [TimePeriod] -
((DatePart("h", [TimePeriod]) * 60) + DatePart("n", [TimePeriod])) +1 AS
Display_Minute
FROM Alarms;

SELECT [Trip TimePeriod].*, qryAlarms.*
FROM [Trip TimePeriod] LEFT JOIN qryAlarms ON [Trip TimePeriod].Minute =
qryAlarms.Display_Minute
ORDER BY [Trip TimePeriod].Minute;

--
Build a little, test a little.


"StuJol" wrote:

> Using Access 2003 i have a table named 'Trip TimePeriod' which looks like
>
> Minute TimePeriod Total
> 1 00:01:00 0
> 2 00:02:00 0
> 3 00:03:00 0
> 4 00:04:00 0
> 5 00:05:00 0
> ..60 01:00:00 0
>
> has 60 records representing 60 minutes. Data to be used for a pivotchart to
> show the total number of trips for 60 minutes. the table is to be used as a
> template to get me my 60 minute time scale even with null totals.
>
> i have a qry which give me my data named 'Alarms - Totals After Trip - 60
> Minutes', which looks like
>
> Event Type State Date TimePeriod Total
> ALARM ACT/UNACK 29/04/2010 14:10:00 1
> ALARM ACT/UNACK 29/04/2010 14:15:00 2
> ALARM ACT/UNACK 29/04/2010 14:17:00 1
> ALARM ACT/UNACK 29/04/2010 14:18:00 1
> ALARM ACT/UNACK 29/04/2010 14:22:00 1
> ALARM ACT/UNACK 29/04/2010 14:38:00 2
> ALARM ACT/UNACK 29/04/2010 14:41:00 1
> ALARM ACT/UNACK 29/04/2010 14:52:00 6
> ALARM ACT/UNACK 29/04/2010 14:53:00 2
> ALARM ACT/UNACK 29/04/2010 14:54:00 5
> ALARM ACT/UNACK 29/04/2010 14:55:00 2
> ALARM ACT/UNACK 29/04/2010 14:57:00 1
> ALARM ACT/UNACK 29/04/2010 14:58:00 5
> ALARM ACT/UNACK 29/04/2010 14:59:00 1
> ALARM ACT/UNACK 29/04/2010 15:03:00 2
> ALARM ACT/UNACK 29/04/2010 15:05:00 4
> ALARM ACT/UNACK 29/04/2010 15:07:00 3
>
> i need to put the first record of the qry into the first record of the
> table, so whatever the timestamp of the qry, it is represented as the first
> record in the table. i then need to add the rest of the qry into the table
> using the timestamp. so where no data exists in the qry for a time period,
> then the table remains at 0 total
>
From: John W. Vinson on 20 May 2010 11:14
On Thu, 20 May 2010 02:13:01 -0700, StuJol <StuJol(a)discussions.microsoft.com>
wrote:

> first record of the
>table

Tables HAVE NO ORDER.

A table does not *have* a "first record", not in any way that's of any use to
you. If you're concerned about the order of records you must - no option, no
choice! - use a Query sorting the table by the value of one or more fields.

You'll need a different approach which doesn't depend on assuming an order of
records in the table.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Final Results
Next: Switchboard in Access 2007