EMERGENCY! How can I update one table from another in a select que

[Bob Dancer]

I have two table with one common factor: all policies in Table 1 (1000
records) have matches in Table 2 (90,000). I have them in a select query but
just found out I cannot update through query. I had already removed the
orginal key numbers thinking I could use the individual policy numbers found
in both tables. There are no duplicates in the policy field.

I am unable to make any updates to the records while under the select
query. I need to be able to edit the query in a Form and make sure these
updates are made to both tables. An additional problem may evolve. When I
first formated the tables I made sure each had the same columns. For
example, Table A had information in Column 1 while Table B had none. Visa
Versa, table B had information in Column 2 while Table A-column 2 is empty.
I have created a select query that shows informatiopn from both. I found out
afterwards that I couldn't make any updates in the select query. My goal is
to set up a form that the President can use to update and access information
without having to look at the tables or the query.

By now I don't know if what I am doing is even possible although it seems
like it should be to me. Help me I'm under a time crunch.

 

[KARL DEWEY]

Try a form/subform for Table1/Table2 and set the Master/Child links to the
Policy Number.

 

[Klatuu]

You don't update with a select query, you need to change it to an update
query.

But the root of the problem is you two table design. This is a common
mistake. Many people use a second table for Archive or History data thinking
it will improve performance. It usuall does not and only creates problems
like you are experiencing. The correct design is to include a field in your
table that designates the record as current or archive. Then use queries to
filter the records returned based on whether you want only current, archive,
or, in some cases, both.

 

[Bob Dancer]

I'm not sure if I understand. If Table a, Column 1 has info and Table B,
Column 1 is empty (this woulb the duplicate I created myself), should I
delete Column 1 of Table 2 and do the query afterwards? I have 40 columns in
each table and both have the exact column structure except only one of each
duplicate pair has information. Obviously I created empty columns thinking
that it would be necessary for query. If I removed these "dummy columns" and
left only the columns which had information along with the columns in both
tables that have a common denominator (ex. Policy number and/or name) will
this work? Once this is done how would I make a query to access this?

 

[Klatuu]

I'm not sure I fully understand your question.
What is different about the two tables?
What is column 1 in table b has data and column 1 in table a?
Are you wanting to remove duplicate records?

 

[John W. Vinson/MVP]

I'm not sure if I understand. If Table a, Column 1 has info and Table B,
Column 1 is empty (this woulb the duplicate I created myself), should I
delete Column 1 of Table 2 and do the query afterwards? I have 40 columns in
each table and both have the exact column structure except only one of each
duplicate pair has information. Obviously I created empty columns thinking
that it would be necessary for query. If I removed these "dummy columns" and
left only the columns which had information along with the columns in both
tables that have a common denominator (ex. Policy number and/or name) will
this work? Once this is done how would I make a query to access this?

I think you ARE misunderstanding how queries work.

Relational databases use the "Grandmother's Pantry Principle": "A
place - ONE place! - for everything, everything in its place".

If you have a Table or Orders, then the table should contain *only*
fields for information about Orders. If you have a table of Products,
that table should contain *only* contain fields for information about
products. If you have an OrderDetails table detailing which products
comprise each order, it should have only a link to the Orders table -
the orderID - and one to the Products table - the ProductID; it should
not include say the ProductName or the CustomerID.

You certainly do NOT need to include blank columns in a table in
anticipation of that table being used later in a Query.