Merging Qry into Table

  • Thread starter Thread starter StuJol
  • Start date Start date
S

StuJol

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
 
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;
 
first record of the
table
Click to expand...

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.
 
Back
Top