Adding Many Records

  • Thread starter Thread starter Stephen
  • Start date Start date
S

Stephen

I have a database of Customers, Customer Products,
Orders, Orderdetails, etc.

In Customer Products there are records specific to that
customer which contain among other fields,
ProductNumber, ProductName, ProductSell,
ProductCost,EffectiveDate,...

I want to create a new record (copying the old record)
with som slight changes.
i.e Existing record is:
1234, Desk60x30, 299.99, 220.00, 08/25/03
I want to creat a new record that is:
1234, Desk60x30, 399.99, 320.00, 04/15/04

I have formulas that will rais the sell price X%, and the
cost by $X.XX and I want the new record to have an
effective date of today.

This sounds like an Append Query to me, but I'm not quite
sure how to accomplish it.

Thanks in advance.

Stephen
 
Hi,


INSERT INTO myTable(ItemID, ItemDesc, ItemPrice, EffectiveDate)
SELECT a.ItemID, LAST(a.ItemDesc), 1.10 * LAST(b.ItemPrice), Date()
FROM myTable As a INNER JON myTable As b
ON a.ItemID=b.ItemID
GROUP BY a.ItemID, a.EffectiveDate
HAVING a.EffectiveDate=MAX(b.EffectiveDate)


The concept is to get the SELECT statement, then to make the insert. To get
the record associated to the maximum value of EffectiveDate, by ItemID, I
use one of the technique mentioned at
http://www.mvps.org/access/queries/qry0020.htm

Hoping it may help,
Vanderghast, Access MVP
 
Back
Top