Update Query Not Working

[Missy]

Here is the code: UPDATE [Results Table] SET [Results Table].ResultID =
Import.ResultID, [Results Table].Q1 = Import.Q1, [Results Table].Q1T =
Import.Q1T, [Results Table].Q2 = Import.Q2, [Results Table].Q2A = Import.Q2A;



This is just a portion of the code. I import the data from excel and put it
in the import table. Now on the results table I do have an id that is not
populated in the import table since it is an autonumber format. I have tried
append and update queries and I am having no luck. I thought I had this
working before.

This is what I want
All the records in the import table I want added to the existing records in
the Results Table. Then I will delete the import table.

 

[KARL DEWEY]

Unless you only have one record in each table Access will not know what
record is to be updated from any record of the import. The two tables have
to be correlated.

 

[John Spencer]

If you want to add records, you need to use an APPEND query

INSERT into [Results Table] (ResultID, Q1, Q1T, Q2, Q2A)
SELECT Import.ResultID, Import.Q1, Import.Q1T, Import.Q2,Import.Q2A
FROM Import

If [Results Table].ResultID is an autonumber field, then remove it from
both portions of the query.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Missy]

What are you saying? I can not do an update without a relationship?

Unless you only have one record in each table Access will not know what
record is to be updated from any record of the import. The two tables have
to be correlated.
--
KARL DEWEY
Build a little - Test a little

Here is the code: UPDATE [Results Table] SET [Results Table].ResultID =
Import.ResultID, [Results Table].Q1 = Import.Q1, [Results Table].Q1T =
Import.Q1T, [Results Table].Q2 = Import.Q2, [Results Table].Q2A = Import.Q2A;



This is just a portion of the code. I import the data from excel and put it
in the import table. Now on the results table I do have an id that is not
populated in the import table since it is an autonumber format. I have tried
append and update queries and I am having no luck. I thought I had this
working before.

This is what I want
All the records in the import table I want added to the existing records in
the Results Table. Then I will delete the import table.

 

[Missy]

Ok, Well I was importing via a macro into access. Then I want to merge the
new data with the existing table. Now in the results table (existing table)
I have an ID that is setup for auto number field. I am trying to make this
process as automated as possible. I started with the data in an email but
the import from exchange does not work correctly it shows too many fields. I
will keep plugging away at this. Thanks

That's right!

You need to have a common field that you can join otherwise the records in
one table have no corresponding record in the other table.

It would be like have a list of fruit and a list of prices and then saying
to apply the correct price to each fruit.

--
KARL DEWEY
Build a little - Test a little

What are you saying? I can not do an update without a relationship?

Unless you only have one record in each table Access will not know what
record is to be updated from any record of the import. The two tables have
to be correlated.
--
KARL DEWEY
Build a little - Test a little


:

Here is the code: UPDATE [Results Table] SET [Results Table].ResultID =
Import.ResultID, [Results Table].Q1 = Import.Q1, [Results Table].Q1T =
Import.Q1T, [Results Table].Q2 = Import.Q2, [Results Table].Q2A = Import.Q2A;



This is just a portion of the code. I import the data from excel and put it
in the import table. Now on the results table I do have an id that is not
populated in the import table since it is an autonumber format. I have tried
append and update queries and I am having no luck. I thought I had this
working before.

This is what I want
All the records in the import table I want added to the existing records in
the Results Table. Then I will delete the import table.

 

[KARL DEWEY]

That's right!

You need to have a common field that you can join otherwise the records in
one table have no corresponding record in the other table.

It would be like have a list of fruit and a list of prices and then saying
to apply the correct price to each fruit.

--
KARL DEWEY
Build a little - Test a little

What are you saying? I can not do an update without a relationship?

Unless you only have one record in each table Access will not know what
record is to be updated from any record of the import. The two tables have
to be correlated.
--
KARL DEWEY
Build a little - Test a little

Here is the code: UPDATE [Results Table] SET [Results Table].ResultID =
Import.ResultID, [Results Table].Q1 = Import.Q1, [Results Table].Q1T =
Import.Q1T, [Results Table].Q2 = Import.Q2, [Results Table].Q2A = Import.Q2A;



This is just a portion of the code. I import the data from excel and put it
in the import table. Now on the results table I do have an id that is not
populated in the import table since it is an autonumber format. I have tried
append and update queries and I am having no luck. I thought I had this
working before.

This is what I want
All the records in the import table I want added to the existing records in
the Results Table. Then I will delete the import table.

 

[KARL DEWEY]

Do the two tables have a field that has common data? If so then you can use
a JOIN.
Post some sample data from both tables.
--
KARL DEWEY
Build a little - Test a little

Ok, Well I was importing via a macro into access. Then I want to merge the
new data with the existing table. Now in the results table (existing table)
I have an ID that is setup for auto number field. I am trying to make this
process as automated as possible. I started with the data in an email but
the import from exchange does not work correctly it shows too many fields. I
will keep plugging away at this. Thanks

That's right!

You need to have a common field that you can join otherwise the records in
one table have no corresponding record in the other table.

It would be like have a list of fruit and a list of prices and then saying
to apply the correct price to each fruit.

--
KARL DEWEY
Build a little - Test a little

What are you saying? I can not do an update without a relationship?

:

Unless you only have one record in each table Access will not know what
record is to be updated from any record of the import. The two tables have
to be correlated.
--
KARL DEWEY
Build a little - Test a little


:

Here is the code: UPDATE [Results Table] SET [Results Table].ResultID =
Import.ResultID, [Results Table].Q1 = Import.Q1, [Results Table].Q1T =
Import.Q1T, [Results Table].Q2 = Import.Q2, [Results Table].Q2A = Import.Q2A;



This is just a portion of the code. I import the data from excel and put it
in the import table. Now on the results table I do have an id that is not
populated in the import table since it is an autonumber format. I have tried
append and update queries and I am having no luck. I thought I had this
working before.

This is what I want
All the records in the import table I want added to the existing records in
the Results Table. Then I will delete the import table.

 

[Missy]

Actually I got this to work now. The only thing I have to do is change the
ResultID field which is blank on the import table to number format. I am
currently trying to see if I can do this via a macro. Since I have the macro
that does the import then the update and then it deletes the imported table.
If someone knows how to change the format of the field either in a macro or
SQL please let me know.

UPDATE [Results Table] RIGHT JOIN Import ON [Results Table].ResultID =
Import.ResultID SET [Results Table].Q1 = Import!Q1, [Results Table].Q1T =
Import!Q1T, [Results Table].Q2 = Import!Q2, [Results Table].Q2A = Import!Q2A,
[Results Table].Q2T = Import!Q2AT, [Results Table].Q3 = Import!Q3, [Results
Table].[Q4-1] = Import![Q4-1], [Results Table].[Q4-2] = Import![Q4-2],
[Results Table].[Q4-3] = Import![Q4-3], [Results Table].[Q4-4] =
Import![Q4-4], [Results Table].[Q4-5] = Import![Q4-5], [Results Table].[Q4-6]
= Import![Q4-6], [Results Table].[Q4-7] = Import![Q4-7], [Results
Table].[Q4-8] = Import![Q4-8], [Results Table].Q4T = Import!Q4T, [Results
Table].Q5T = Import!Q5, [Results Table].[DealerName:] = Import![Dealership
Name], [Results Table].[ContactName:] = Import![Contact Name], [Results
Table].[Title:] = Import!Role, [Results Table].PhoneNumber = Import!Phone,
[Results Table].[Email:] = Import![Email Address], [Results Table].[User:] =
Import!User;

If you want to add records, you need to use an APPEND query

INSERT into [Results Table] (ResultID, Q1, Q1T, Q2, Q2A)
SELECT Import.ResultID, Import.Q1, Import.Q1T, Import.Q2,Import.Q2A
FROM Import

If [Results Table].ResultID is an autonumber field, then remove it from
both portions of the query.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Here is the code: UPDATE [Results Table] SET [Results Table].ResultID =
Import.ResultID, [Results Table].Q1 = Import.Q1, [Results Table].Q1T =
Import.Q1T, [Results Table].Q2 = Import.Q2, [Results Table].Q2A = Import.Q2A;



This is just a portion of the code. I import the data from excel and put it
in the import table. Now on the results table I do have an id that is not
populated in the import table since it is an autonumber format. I have tried
append and update queries and I am having no luck. I thought I had this
working before.

This is what I want
All the records in the import table I want added to the existing records in
the Results Table. Then I will delete the import table.