If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Merging Qry into Table
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 |
#2
|
|||
|
|||
Merging Qry into Table
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 |
#3
|
|||
|
|||
Merging Qry into Table
On Thu, 20 May 2010 02:13:01 -0700, StuJol
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] |
Thread Tools | |
Display Modes | |
|
|