Please help build this expression

[Guest]

I have a database of order numbers, transaction date, transaction type(enter,
change or delete) order amount, and other fields. The transaction date field
will have the date the order was entered or changed. The database contains a
new row for each change and the transaction date of that change. So my table
can/will have multiple rows for each order number based on the number of
changes. For example, when an order is entered it will create a row in the
table. Each time a change is made to the order it will create a new row in
the table. So one row will be for the entry of the order with the recorded
data, another row will be for the changes. The only difference between the
two rows, that I am concerned about, will be the transaction date and the
order totals.

What I am trying to get is a new table that will have the order number with
the original entered date and the current order amount regardless of how many
times it has been changed.

So my database looks like this:

Ord# Trans. Date Trans Type Amount
1234 01/15/07 Entered 100.00
1234 02/20/07 Changed 175.00

I want a new table that will just have this:
Ord# Trans. Date Trans Type Amount
1234 01/15/07 Entered 175.00

Can anyone help me out with this?

Thanks in advance for your help.

 

[Guest]

I think I probably explained this to difficultly and made it more complicated
than it may be.

What I would like to basically do is have the transaction date for the entry
of the order be the date for every row in the table for that order number.

Hope this makes it easier to understand.

Thanks

 

[Guest]

Subqueries would be simpler but try these queries. MarkM used as table name.

MarkM_Date_Min ---
SELECT MarkM.[Ord#], Min(MarkM.Trans_Date) AS MinOfTrans_Date
FROM MarkM
GROUP BY MarkM.[Ord#];

MarkM_Date_Max ---
SELECT MarkM.[Ord#], Max(MarkM.Trans_Date) AS MaxOfTrans_Date
FROM MarkM
GROUP BY MarkM.[Ord#];

MarkM_Type_ ---
SELECT MarkM.[Ord#], MarkM.Trans_Date, MarkM.Trans_Type
FROM MarkM INNER JOIN MarkM_Date_Min ON (MarkM.[Ord#] =
MarkM_Date_Min.[Ord#]) AND (MarkM.Trans_Date =
MarkM_Date_Min.MinOfTrans_Date);

MarkM_Amount ---
SELECT MarkM.[Ord#], MarkM.Trans_Date, MarkM.Amount
FROM MarkM INNER JOIN MarkM_Date_Max ON (MarkM.[Ord#] =
MarkM_Date_Max.[Ord#]) AND (MarkM.Trans_Date =
MarkM_Date_Max.MaxOfTrans_Date);

MarkM_Type_Amount ---
SELECT MarkM_Amount.[Ord#], MarkM_Type.Trans_Date, MarkM_Type.Trans_Type,
MarkM_Amount.Amount
FROM MarkM_Amount INNER JOIN MarkM_Type ON MarkM_Amount.[Ord#] =
MarkM_Type.[Ord#];