Updating problem

[roedelfroe]

Hi everybody,

I've got following problem:

In our company we receive an order information, which we fill in "Table 1" and an Invoice information, which we receive in "Table 2". In a view, information of both tables should be merged together.

And here my problem begins:

Example:

Table 1:

Orderno. OtyDelivered DeliveryNo. PriceperItem Timestamp
1234567 10 001 5.05 9:26
1234567 9 002 5.10 11:30
1234567 21 003 5 14:25
1234567 10 004 5 15:05
1234567 10 005 5 15:05
1234567 10 006 5 15:10

Table2:

Orderno. QtyDelivered InvoiceNo. PricePerItem
1234567 10 001 5.05
1234567 21 002 5
1234567 10 003 5
1234567 9 004 5.10
1234567 10 005 5
1234567 10 006 5

View1 should be shown like this:

Orderno QtyDelivered PriceperItem TimeDelivery
1234567 10 5.05 9:26
1234567 21 5 14:25
1234567 10 5 15:05
1234567 9 5.10 11:30
1234567 10 5 15:05
1234567 10 5 15:10

To make it clear, I just have to create this view.

I see no problem to put in the TimeDelivery value from Table 1 as long as QtyDelivered and PriceperItem and Orderno are unique. But what should I do if they aren't? As you can see, the invoicenumber is not included in the view, therefore it is possible to put in ANY TimeDelivery value in equal rows. But every TimeValue in Table1 which exists must be at the end in the view.

To come back to the example above, how should I fill the rows in the View, where Orderno, QtyDeliverd and PriceperItem are the same, but the DeliveryTime isn't?

Any hints?

Thank so much for your help!

Roland

 

[LKBrwnDBA]

You can't without the invoice# in table 2.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb