Re: access SQL query

On Dec 4, 1:06 pm, turtle <dkaloust...@xxxxxxxxx> wrote:
On Dec 3, 5:28 pm, jonceramic <joncera...@xxxxxxxxx> wrote:





On Dec 3, 2:19 pm, turtle <dkaloust...@xxxxxxxxx> wrote:

I have an access database with three tables.  I want to take all of
the records in a field from one of the tables and update/insert them
into the same exact field in a separate table.  I want to be able to
do this through some kind of update/insert query as opposed to
copying
and pasting each record.  Here is what I have so far and it appears I
have some work to do.

insert into tblShares.LifetimeOfShare
select tblSharesSpread***.LifetimeOfShare
from tblSharesSpread*** inner join tblShares on
tblSharesSpread***.LifetimeOfShare=tblShares.LifetimeOfShare;

Now all of these fields I have are text format, so would that not
allow the inner join function to work?  Should I be trying to
construct an "update" query instead of an "insert into"?

I am really new to this type of stuff and have been trying to piece
together online tutorials.  Any help would be much appreciated.

Turtle, If this is a one-time thing....  i.e., you're just trying to
move the data from one table to another....
Open up a "new" query in "design" view.  Add the table with your
current data in it.  Then, switch the "type" to an append query.  Use
your join criteria in the Criteria box for LifetimeOfShare.  When you
eventually execute (via the red ! icon), this will add the lines from
the current table to the new table.

If it's a thing you want to program to execute for single lines in
your table, then that's a whole different story depending on what
you're doing.  You'll have to be more specific in your problem
definition.  (i.e. tell us what you're trying to do with the data in
real life, rather than how you think the database will execute it)
You can do it a multitude of ways.

Jon- Hide quoted text -

- Show quoted text -

I think Rich is right, there is something wrong with my select
statement because when I try to run that alone it will not return any
results.  Would the join function work with strings?

I want this database to house information regarding our file shares
(size, life time, size, conact information, type of information in
folders, etc).    I have two tables that contain information about
file shares and they are both constructed the same way (have the same
exact fields).  One just contains more information about some of the
records than the other.  I basically want to be able to update the
fields in the master table with the more detailed information that the
smaller table does have.

I want the query to compare the two tables and if the record from the
first table (folder name) matches a record in the same field of the
second table (folder name) it will update a separate field (life time
of folder) in the first table with the information(life time of
folder) from the field in the second more detailed table.

I think I should be trying to tackle an update statement because the
insert will just put this information as new records at the end on my
table.

Does this help?  If not I will try to explain it more.- Hide quoted text -

- Show quoted text -

Turtle, it sounds right - somewhat.

But, it also sounds like you're really wanting either 1 table, with
additional views(queries) of that data.

There should be no reason for you to copy data from one table to
another. Instead, you should be able to query off of a "key" field to
populate a view with the desired information on the fly.

Here's how I would do your example

Table 1: Master table
Folder
Time
Field 1
Field 2
Field 3

Query 1: Select ...
Folder
Time

Query 2: Select...
Folder
Field1
Field3

In fact, if you're displaying the information on a Form, there's
really no need to even do the Queries. Just only choose that data you
wish to see.

If you need to modify the data in one table to be shortened, I'd
consider 2 methods.
1. The "bigger table" method.
Table 1: Master table
Folder
Time
Field 1
Field 1 Abbreviated
Field 2
Field 2 Abbreviated
Field 3
Field 3 Abbreviated

Or, probably prefered.
2. The "on the fly" method.
Use your original Master table, but as you display information,
truncate or abbreviate on the fly.
A. You can do this through the Form or Report with statements like
=left([Field1],20).
B. You can do this through a query.
Query 2: Select...
Folder
Field1Abbr: left([Field1],20)
Field3

Remember, you can always use a Query as a datasource in Access, just
like a Table. This works for forms, reports, drop downs, subforms,
etc.

Best of luck,

Jon

.