Update query from a data file

[Travis]

I've programmed a button on a form to import data from a .csv and put
the information into a table called "tblImportedPrices".

The data being imported is price and performance information for
managed funds.

I have another table, "tblInvestments", which has fields I would like
to update using the data residing in tblImportedPrices.

Both tables share common IDs, the standard industry codes for each
fund. These IDs are unique.

The other information sitting in tblImportedPrices includes fund names,
buy and sell unit prices and performance over various time frames.
There are fields in tblInvestments which I wish to update with this
data.

The values in tblImportedPrices will change as I import different lists
of funds, but the columns will always point to the same types of data.
There will only be limited numbers of funds in the tblImportedPrices
table, I've set it up to get prices only for the funds I'm working with
in that portfolio, so only a minority of funds in tblImportedPrices
will ever be updated in any one update query.

Unless there is data corruption or something, there shouldn't ever be a
fund in tblImportedPrices which isn't in tblInvestments.

First of all, do I set the relationships between tblImportedPrices and
tblInvesments to draw a line between every corresponding field... or
just the common IDs.

And secondly, I can't figure out how to do the update query. I tried a
few things but my skills are not good in this area and I got completely
wrong results.

Any help would be appreciated!

Travis

 

[Jeff Boyce]

Travis

I'm sure what you mean (see below)

First of all, do I set the relationships between tblImportedPrices and
tblInvesments to draw a line between every corresponding field... or
just the common IDs.

Are you talking about a query, drawing the joins between the tables, or
about relationships, using the relationship window? You don't need to use
the relationship window.

And secondly, I can't figure out how to do the update query. I tried a
few things but my skills are not good in this area and I got completely
wrong results.

It isn't clear if you are keeping a static (approx. "current") set of data
in your Investment table, or if you wish to keep a history. If the former,
you'd only need to:
* create a query, add both tables
* join on shared ID field
* select the fields from the Investment table you wish updated
* change the query type to Update
* show which fields (from the import table) or values you wish to have
your Investment table fields updated to

Different matter if you are trying to maintain a history -- you need a third
table.

 

[Travis]

Travis

I'm sure what you mean (see below)

Are you talking about a query, drawing the joins between the tables, or
about relationships, using the relationship window? You don't need to use
the relationship window.

First time I ran the query wizard to get a query started it came up
with an error message saying there was no relation between the two
tables, I had to go to relationships and set them.

Since actually all of the fields in tblImportedPrices correspond to a
field that I want to update in tblInvestments I wasn't sure if I should
join just the IDs or draw lines between everything.

I joined all, the result was a messy query that could only be viewed in
SQL because the designer couldn't handle it.

It isn't clear if you are keeping a static (approx. "current") set of data
in your Investment table, or if you wish to keep a history. If the former,

Current only, though keeping a history is something I'm planning on
doing eventually. Right now I'm just trying to enable the basic
features before I get into the stuff I'd like to do some day. Once
I've got history I'll be able to do all kinds of complex stuff like
calculating and charting portfolio performance over time, taking into
account transaction activity etc.

But that's all in the future....

you'd only need to:
* create a query, add both tables
Ok.
* join on shared ID field
Done.

* select the fields from the Investment table you wish updated

You mean click on them in the table to bring them into the query, but
don't click show? Done.

* change the query type to Update
Easy...

* show which fields (from the import table) or values you wish to have
your Investment table fields updated to

Click on all the fields, but since its an update query now there is no
"show" to click...

I'm going to guess I'm supposed to type [tblImportedPrices].[the
appropriate imported prices table field name] in the "Update to" field
for each field of the investment table that I want to update

<run>

.... Cool!

Thanks for your help Jeff.

Travis