Help ASAP: Update and Calculation query not working

D

DDTrx2004

My database contains patient information. I have two tables. One main table
with all the patients' records and various info/fields, and then another
table with a subset of patients and different info/fields. I tried to run an
update query so the information on the subset would be tranfered to the
appropriate rows for those patients in the main table. Below is a
description of what I tried, but it did not work. I keep getting an enter
parameter box and the query will not run.

-In the query I joined the two tables by patient number and transplant date,
since this info is in both tables, and I assume this will let the porgram
know which records to update.
-I made the query of the fields that contained the information to be copied
to the main table. I changed the table name under the info to be copied from
the subset table name to the main table where it is to be copied.
-I used the [tablename].[fieldname] so the query would know what info to
update and from where.
-When I try to run the query I keep getting an enter parameter box and I
don't know why.

The enter parameter box also keeps showing up when I try to do calculations
in other queries using the [] expression for subtracting fields.

Any help will be greatly appreciated.

Thank you
 
D

Dave

It sounds like you don't really have an update query, and by changing the
table name you are causing Access to prompt you for information it doesn't
have; it's hard tell without seeing the actual SQL statement.

If you start with a select query that uses the two tables joined as you want
them, you should then select the fields from the table you want updated and
then change the query type to Update. At that point, you should see the 3rd
row in the query design grid labelled "Update To". Use that row to enter the
field names from the source table under the appropriate fields from the
destination table. If the tables have fields with the same names, that is
where you will have to qualify the source field names; if not, the field
names themselves should be enough.

You should end up with a SQL statement that looks something like

UPDATE Table1 INNER JOIN Table2 ON Table1.PatientID = Table1.PatientID SET
Table1.Field1 = FirstField;

or if the tables have fields with the same name

UPDATE Table1 INNER JOIN Table2 ON Table1.PatientID = Table1.PatientID SET
Table1.Field1 = Table2.Field1;
 
J

John Vinson

My database contains patient information. I have two tables. One main table
with all the patients' records and various info/fields, and then another
table with a subset of patients and different info/fields. I tried to run an
update query so the information on the subset would be tranfered to the
appropriate rows for those patients in the main table. Below is a
description of what I tried, but it did not work. I keep getting an enter
parameter box and the query will not run.

-In the query I joined the two tables by patient number and transplant date,
since this info is in both tables, and I assume this will let the porgram
know which records to update.
-I made the query of the fields that contained the information to be copied
to the main table. I changed the table name under the info to be copied from
the subset table name to the main table where it is to be copied.
Click to expand...

Storing the same information in two tables is VERY RARELY either
necessary or wise. What information are you trying to copy and why?
-I used the [tablename].[fieldname] so the query would know what info to
update and from where.
-When I try to run the query I keep getting an enter parameter box and I
don't know why.
Click to expand...

You'll get a parameter prompt if the bracketed text string is not the
name of a field in any table in the query.
The enter parameter box also keeps showing up when I try to do calculations
in other queries using the [] expression for subtracting fields.

Any help will be greatly appreciated.
Click to expand...

Please open your query in SQL view and post it here. It would help to
indicate which fields are from which table, and which field is
generating the prompts.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top