Using a table to fill an other table

  • Thread starter Thread starter jvdn
  • Start date Start date
J

jvdn

Hi,

I have a set of 3 tables that are related.

The first table is a summary.

due to performance I have to change the columns in the summary table.

With data from the 2 other tables.
the primary key of the summary table is referenced from both other
tables.


I have added the columns to the summary table

What i have to do is to update the data by using min/ max function on
columns of the first of the two other tables.
And a sum on two columns of the second of the other tables.

Like:
UPDATE Table1 set StartTime = (Select min(Table2.StartTime) where
Table1.Id = Table2.Id Group By Table2.Id) ;

Or something like that.

I can't use code to achieve this I need to solve it in SQL no VB or
Extern C,C++ C# is posible.

I hope that my problem is clear.

Regards

jvdn.
 
If you are saying that you have "detail" table rows, you don't need a
"summary" table. That's what queries are for.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
NO Code will make this tough. IN Access, you can never use any aggregate
query in an update query except in the where clause.

Your choices are to use the VBA Domain aggregate functions (DMIN) or to run
a series of queries.

Populate a separate table with the results of the minimum time for table2
INSERT INTO Table3 (ID,StartTime)
Select Table2.Id, min(Table2.StartTime)
FROM Table2

Now use that table to do the update to table1
UPDATE Table1 INNER JOIN Table3
ON Table1.Id = Table3.Id
SET Table1.StartTime = [Table3].[StartTime]

The basic question is do you really need to do this at all? You could
normally just use the table2 in your master query. Or use a Subquery in
your master query
 
first of all thangs for your responce.

The reason for my Summery table is the fact that using the summery
table saves me 800 ms. (milliseconds) time geting the data. That data
is not changing any more . From the summary table takes 141 ms. In
stead of usting the master detail takes 940 ms. There for I do want to
use the summery table.

So timing is the master key word. The two tables Connected tables have
also a master slave relation.

So in my "Summery" table I have 1 record in my table2 I have 2 - 20
related records and in the table 3 I have 1 -16 related records to each
record in table2. On all of the I have to use min max or sum fonctions.

That is why I add some "Summery" data to the Table1.

I have now a query (view) this should work like that table.

UPDATE Tbl_Order o INNER JOIN OrderTrackTrace ott ON o.OrderId=
ott.OrderId SET o.Eggs = ott.Eggs;


UPDATE Tbl_Order o INNER JOIN OrderTrackTrace ott ON [o].[OrderId]=
[ott].[OrderId] SET [o].[Eggs] = [ott].[Eggs];

Running both in Access results in the Question to fill the parameter
value of [ott].[Eggs]

And this one
UPDATE Tbl_Order AS [Order] INNER JOIN OrderTrackTrace ON Order.OrderId
= OrderTrackTrace.OrderId SET [Order].Eggs = [OrderTrackTrace].[Eggs];

result in the same Question with [OrderTrackTrace].[Eggs] value.

give me the value of [Table3].[StartTime]

I hope You knows wat gows wrong
 
jvdn schreef:
first of all thangs for your responce.

The reason for my Summery table is the fact that using the summery
table saves me 800 ms. (milliseconds) time geting the data. That data
is not changing any more . From the summary table takes 141 ms. In
stead of usting the master detail takes 940 ms. There for I do want to
use the summery table.

So timing is the master key word. The two tables Connected tables have
also a master slave relation.

So in my "Summery" table I have 1 record in my table2 I have 2 - 20
related records and in the table 3 I have 1 -16 related records to each
record in table2. On all of the I have to use min max or sum fonctions.

That is why I add some "Summery" data to the Table1.

I have now a query (view) this should work like that table.

UPDATE Tbl_Order o INNER JOIN OrderTrackTrace ott ON o.OrderId=
ott.OrderId SET o.Eggs = ott.Eggs;


UPDATE Tbl_Order o INNER JOIN OrderTrackTrace ott ON [o].[OrderId]=
[ott].[OrderId] SET [o].[Eggs] = [ott].[Eggs];

Running both in Access results in the Question to fill the parameter
value of [ott].[Eggs]

And this one
UPDATE Tbl_Order AS [Order] INNER JOIN OrderTrackTrace ON Order.OrderId
= OrderTrackTrace.OrderId SET [Order].Eggs = [OrderTrackTrace].[Eggs];

result in the same Question with [OrderTrackTrace].[Eggs] value.

give me the value of [Table3].[StartTime]

I hope You knows wat gows wrong
Click to expand...

UPDATE Tbl_Order AS [Order] INNER JOIN OrderTrackTrace ON Order.OrderId
= OrderTrackTrace.OrderId SET [Order].Eggs =
[OrderTrackTrace].[TotalEggs];


I found my first error tha Column name was wrong dumb dumb dumb,

But I get the error message that the action should be done on a query
that can be modified ?? or somtning like that it is in Duch so i'm
trying to translate it as goed as posible
The OrderTrackTrace is a Query that is gethering the summery
information for me.

But changing the query in a (tmp)table did the trick.

Thanks a lot.
 
Back
Top