updating only 1 table of a 2-table query

[illuzioner]

Hi,

I have two tables, one with a list of assets and one with a list of prices, setup like this:

tblAssets and tblPrices
--------- -------- ------------------------------
MainSecID ...(other fields) SecID PricingDate Price
^^^^ ^^^ ^^^^
Primary Key Combo Primary keys

They are linked by MainSecID <--> SecID

I'm trying to make a query that will give me all assets and their prices for a particular date, and then another one that will give me one asset's price over a range of dates.

The problem is when I make up the query, adding a new record adds it to both tables, where I only want to add a record into tblPrices for a particular SecID and PricingDate. I don't want to change tblAssets at all from these queries.

Any ideas?

Thanks!
Lou

 

[scriverb]

First of all this statement:
I'm trying to make a query that will give me all assets and their prices for a particular date, and then another one that will give me one asset's price over a range of dates

. . sounds like you are creating two select queries for reporting. Is this correct?

Secondly, do you want to create another query that creates a new record for every record in tblAssets and put that record in tblPrices? This we can do as an Append Query and only one record matching the MainSecID value of tblAssets records will be created in tblPrices along with other data for tblPrices.

INSERT INTO tblPrices ( SecID, PricingDate, Price )
SELECT MainSecID, Date() as PDate, 0.00 as DummyPrice
FROM tblAssets;

I this close to what you want to do?
Bob Scriver

 

[illuzioner]

Hi Bob,

>. . sounds like you are creating two select queries for >reporting. Is this correct?

Yes, 2 select queries:

1) to select all assets' prices on a sepcific date:
________________________________________________________
SELECT tblAssets.MainSecID, tblAssets.AssetName, tblPrices.SecID, tblPrices.PricingDate, tblPrices. Price
FROM tblAssets LEFT JOIN tblPrices ON tblAssets.MainSecID = tblPrices.SecID
WHERE ((tblPrices.ClosePriceDate)=#1/24/2003#)

2) select one assets' prices over a range of dates
_________________________________________________
SELECT tblAssets.MainSecID, tblAssets.AssetName, tblPrices.SecID, tblPrices.PricingDate, tblPrices. Price
FROM tblAssets INNER JOIN tblPrices ON tblAssets.MainSecID = tblPrices.SecID
WHERE ((tblPrices.ClosePriceDate) Between #1/20/2003# And #1/28/2003#);

If I just open these queries in datasheet view and start entering data, it goes into tblAssets as well as tblPrices, whereas I only want tblPrices to be affected. From the first query I want to see every asset and enter prices for a specific date, and from the second I want to see a particular asset and enter prices for any possible date.
I don't want the possibility of changing the AssetName or anything that is in tblAssets.

The problem with the INSERT query is that it is an action query. I am running this through a form (in continuous mode) and when the user types in prices they will go directly into the table.

Is that clearer?

Thanks!
Lou

>Secondly, do you want to create another query that creates >a new record for every record in tblAssets and put that >record in tblPrices? This we can do as an Append Query >and only one record matching the MainSecID value of >tblAssets records will be created in tblPrices along with >other data for tblPrices.

>INSERT INTO tblPrices ( SecID, PricingDate, Price )
>SELECT MainSecID, Date() as PDate, 0.00 as DummyPrice
>FROM tblAssets;

>I this close to what you want to do?

>Bob Scriver