Replacing information in a field with a linked table

[KevinS]

I brought in a linked Excel 2000 spreadsheet creating a table using Access
97. I would like to use the Excel table to replace some of the fields in
Table A.

Table A has:
Name
Address
Quality 1 - Text field
Quality 2 - Text field

Excel Table has:
Name
Address
Info1
Info2
Date1
Cost1
Comment1 - text field
comment2 - text field

I would like to replace whatever is in
Table A / Quality 1 with whatever is in
Excel table / Comment1

I tried an update query but it is only good for replacing a name with a name
- not everything in the field.

 

[Clif McIrvin]

That should work.

Please post the SQL from your update query.

 

[KevinS]

I should mention that the fields I am trying to replace are text fields with
255 characters.

What I am trying to do is If this field matches that field then replace the
old comments with the new comments.

Not sure what to look up in the Access books to solve this.

 

[Clif McIrvin]

You said that you have a linked Excel spreadsheet that has the
information you need, and it correlates with the records in TableA.
From your description, everything should work correctly, if you are
building your query properly. Until you post the SQL from your query (so
we can see what you are actually doing) there really isn't anything
anyone here can do to help you.

255 character text fields should cause no trouble. Is the field in
TableA in fact defined as text with a length of 255?

 

[KevinS]

Cliff,
sorry to be so difficult. This is the best I can do.
SELECT [Coming Due].[Comments/ Plan of Action], IntakeMain.[Comments/Plan of
Action]
FROM [Coming Due], IntakeMain;
I want to move everything from Coming Due.[Comments/Plan of Action] to
IntakeMain.[Comments/Planof Action]

Theoretically, if I could place the one field underneath the other in an
Update Query it would be perfect but I can't figure out how to do it.

 

[Clif McIrvin]

Cliff,
sorry to be so difficult. This is the best I can do.
SELECT [Coming Due].[Comments/ Plan of Action],
IntakeMain.[Comments/Plan of
Action]
FROM [Coming Due], IntakeMain;
I want to move everything from Coming Due.[Comments/Plan of Action] to
IntakeMain.[Comments/Planof Action]

Theoretically, if I could place the one field underneath the other in
an
Update Query it would be perfect but I can't figure out how to do it.

First, to show us the SQL for your query it's a simple copy and paste.
In your Query Design grid menu choose View | SQL View then copy and
paste the SQL into your post.

Do you have a join in your query? You need to tell the Jet database
engine how the records in the two tables are related. After you define
the join, invoke the Expression Builder in the Update To: "cell" in the
query design grid to "place the one field underneath the other".

Select the Update To: "cell" and click the wizard icon, or right click
and chose Build... from the context menu.

 

[Clif McIrvin]

Kevin, I forgot a couple things:


Not a problem. This is where you come to find the answers you don't
know. All we ask is that you pay careful attention to our answers (and
questions), and that you try to gain an understanding of how Access /
Jet does things ... don't be afraid to post back with "I'd really like
to do what you suggested, but I simply don't understand what you said.
What do you mean by .... ?"

The other thing is a matter of "housekeeping", related to how Usenet
newsgroups work. Don't change the subject line when replying to a post
so that it is obvious to all which thread the reply belongs to. This
also makes it easier for anyone who might in the future be searching for
information on the same question.

(I just learned something about how Outlook Express re-posts messages.
This post *should* show up in the right thread.)

--
Clif

This is the best I can do.
SELECT [Coming Due].[Comments/ Plan of Action],
IntakeMain.[Comments/Plan of
Action]
FROM [Coming Due], IntakeMain;
I want to move everything from Coming Due.[Comments/Plan of Action]
to
IntakeMain.[Comments/Planof Action]

Theoretically, if I could place the one field underneath the other in
an
Update Query it would be perfect but I can't figure out how to do it.

First, to show us the SQL for your query it's a simple copy and paste.
In your Query Design grid menu choose View | SQL View then copy and
paste the SQL into your post.

Do you have a join in your query? You need to tell the Jet database
engine how the records in the two tables are related. After you define
the join, invoke the Expression Builder in the Update To: "cell" in
the query design grid to "place the one field underneath the other".

Select the Update To: "cell" and click the wizard icon, or right click
and chose Build... from the context menu.

 

[KevinS]

UPDATE ComingDue2 INNER JOIN (Staff INNER JOIN ((Staff AS Staff_1 INNER JOIN
(((C_Checklist RIGHT JOIN (PCCases INNER JOIN OpenCases ON PCCases.CaseID =
OpenCases.IntakeChild.CaseID) ON C_Checklist.CaseID =
OpenCases.IntakeChild.CaseID) INNER JOIN IntakeChild ON
OpenCases.IntakeChild.CaseID = IntakeChild.CaseID) INNER JOIN IntakeMain ON
PCCases.CaseID = IntakeMain.CaseID) ON Staff_1.StaffID =
IntakeMain.Supervisor) INNER JOIN CompltDB2 ON IntakeMain.CaseID =
CompltDB2.CaseID) ON (Staff.StaffID = IntakeMain.PCW) AND (Staff.StaffID =
CompltDB2.StaffID)) ON ComingDue2.ASCI = CompltDB2.ASCI >>>>SET
IntakeMain.[Comments/Plan of Action] = [ComingDue2]![Comments/ Plan of
Action]<<<< These are the two that are driving me crazy!
WHERE (((IntakeMain.ReferDate)<=Date()) AND ((C_Checklist.Certype) Is Null));



--
Is it the times or the Zeitgiest?

Cliff,
sorry to be so difficult. This is the best I can do.
SELECT [Coming Due].[Comments/ Plan of Action],
IntakeMain.[Comments/Plan of
Action]
FROM [Coming Due], IntakeMain;
I want to move everything from Coming Due.[Comments/Plan of Action] to
IntakeMain.[Comments/Planof Action]

Theoretically, if I could place the one field underneath the other in
an
Update Query it would be perfect but I can't figure out how to do it.

First, to show us the SQL for your query it's a simple copy and paste.
In your Query Design grid menu choose View | SQL View then copy and
paste the SQL into your post.

Do you have a join in your query? You need to tell the Jet database
engine how the records in the two tables are related. After you define
the join, invoke the Expression Builder in the Update To: "cell" in the
query design grid to "place the one field underneath the other".

Select the Update To: "cell" and click the wizard icon, or right click
and chose Build... from the context menu.

 

[Clif McIrvin]

I don't see anything obviously wrong with the SQL.

If you change the query to a select query and show both fields in
question do you see what you expect?

What are the properties of the two fields in question in the respective
table design views?

I tried an update query but it is only good for replacing a name with
a name
- not everything in the field.

What exactly do you mean? Unless this is different between A97 and A2003
(I have no experience with any version other than A2003) replacing
"everything in the field" is precisely what an update query does.

--
Clif

UPDATE ComingDue2 INNER JOIN (Staff INNER JOIN ((Staff AS Staff_1
INNER JOIN
(((C_Checklist RIGHT JOIN (PCCases INNER JOIN OpenCases ON
PCCases.CaseID =
OpenCases.IntakeChild.CaseID) ON C_Checklist.CaseID =
OpenCases.IntakeChild.CaseID) INNER JOIN IntakeChild ON
OpenCases.IntakeChild.CaseID = IntakeChild.CaseID) INNER JOIN
IntakeMain ON
PCCases.CaseID = IntakeMain.CaseID) ON Staff_1.StaffID =
IntakeMain.Supervisor) INNER JOIN CompltDB2 ON IntakeMain.CaseID =
CompltDB2.CaseID) ON (Staff.StaffID = IntakeMain.PCW) AND
(Staff.StaffID =
CompltDB2.StaffID)) ON ComingDue2.ASCI = CompltDB2.ASCI >>>>SET
IntakeMain.[Comments/Plan of Action] = [ComingDue2]![Comments/ Plan of
Action]<<<< These are the two that are driving me crazy!
WHERE (((IntakeMain.ReferDate)<=Date()) AND ((C_Checklist.Certype) Is
Null));

 

[KevinS]

When I put the two fields sid by side in a query two things happen.
1- I get a message that I cannot group on memo or OLE object
2 - When I get rid of the 'Total' row then I get all the repeat records.
Normally, i would only get one record.

When I put IntakeMain.[Comments/Plan of Action] in the update query
Comments/Plan of Action --- Field
IntakeMain -- Table
[ComingDue2]![Comments/ Plan ofAction] -- Update to
[Comments/ Plan ofAction] -- Criteria

When I run this update query I get the message: "Operation must use an
update query".
What does this mean? Any questions?
KevinS
--
Is it the times or the Zeitgiest?

I don't see anything obviously wrong with the SQL.

If you change the query to a select query and show both fields in
question do you see what you expect?

What are the properties of the two fields in question in the respective
table design views?

I tried an update query but it is only good for replacing a name with
a name
- not everything in the field.

What exactly do you mean? Unless this is different between A97 and A2003
(I have no experience with any version other than A2003) replacing
"everything in the field" is precisely what an update query does.

--
Clif

UPDATE ComingDue2 INNER JOIN (Staff INNER JOIN ((Staff AS Staff_1
INNER JOIN
(((C_Checklist RIGHT JOIN (PCCases INNER JOIN OpenCases ON
PCCases.CaseID =
OpenCases.IntakeChild.CaseID) ON C_Checklist.CaseID =
OpenCases.IntakeChild.CaseID) INNER JOIN IntakeChild ON
OpenCases.IntakeChild.CaseID = IntakeChild.CaseID) INNER JOIN
IntakeMain ON
PCCases.CaseID = IntakeMain.CaseID) ON Staff_1.StaffID =
IntakeMain.Supervisor) INNER JOIN CompltDB2 ON IntakeMain.CaseID =
CompltDB2.CaseID) ON (Staff.StaffID = IntakeMain.PCW) AND
(Staff.StaffID =
CompltDB2.StaffID)) ON ComingDue2.ASCI = CompltDB2.ASCI >>>>SET
IntakeMain.[Comments/Plan of Action] = [ComingDue2]![Comments/ Plan of
Action]<<<< These are the two that are driving me crazy!
WHERE (((IntakeMain.ReferDate)<=Date()) AND ((C_Checklist.Certype) Is
Null));

 

[Clif McIrvin]

When I put the two fields sid by side in a query two things happen.
1- I get a message that I cannot group on memo or OLE object
2 - When I get rid of the 'Total' row then I get all the repeat
records.
Normally, i would only get one record.

I evidently don't know enough about SQL to have recognized the fact that
you are Grouping. By definition, grouping results in a non-updateable
recordset. To be able to update, you need to be processing individual
records.

When developing an update query, I *always* begin by defining a SELECT
query and verifying that I am getting the data I expect .... only after
carefully confirming that my select query is doing what I intend do I
unleash an update query.

Step one: Back up your database in case soemthing doesn't work quite
right.
Step two: Build a Select query that gives you the *exact* results you
want.
Step three: Attempt to manually modify the fields in question in the
datasheet view of your Select Query. If that fails, re-work your query
until you have an updateable recordset. If you have trouble with this
step post back -- or search the archives for making an updatable
recordset.
Step four: Make an update query using your select query as the
recordsource and verify that it works properly.

After you have tested and verified everything you could consolidate
everything into one query if that's the way you want it --- I tend to
use nested querys a lot because it's easier for me to grasp the concepts
of "do one thing at a time".

I don't know if this applies to this situation or not, but using "poison
characters" in field names makes me nervous -- [Comments/Plan of Action]
may or may not be causing trouble.

--
Clif

When I put IntakeMain.[Comments/Plan of Action] in the update query
Comments/Plan of Action --- Field
IntakeMain -- Table
[ComingDue2]![Comments/ Plan ofAction] -- Update to
[Comments/ Plan ofAction] -- Criteria

When I run this update query I get the message: "Operation must use an
update query".
What does this mean? Any questions?
KevinS
--
Is it the times or the Zeitgiest?

I don't see anything obviously wrong with the SQL.

If you change the query to a select query and show both fields in
question do you see what you expect?

What are the properties of the two fields in question in the
respective
table design views?

I tried an update query but it is only good for replacing a name
with
a name
- not everything in the field.

What exactly do you mean? Unless this is different between A97 and
A2003
(I have no experience with any version other than A2003) replacing
"everything in the field" is precisely what an update query does.

--
Clif

UPDATE ComingDue2 INNER JOIN (Staff INNER JOIN ((Staff AS Staff_1
INNER JOIN
(((C_Checklist RIGHT JOIN (PCCases INNER JOIN OpenCases ON
PCCases.CaseID =
OpenCases.IntakeChild.CaseID) ON C_Checklist.CaseID =
OpenCases.IntakeChild.CaseID) INNER JOIN IntakeChild ON
OpenCases.IntakeChild.CaseID = IntakeChild.CaseID) INNER JOIN
IntakeMain ON
PCCases.CaseID = IntakeMain.CaseID) ON Staff_1.StaffID =
IntakeMain.Supervisor) INNER JOIN CompltDB2 ON IntakeMain.CaseID =
CompltDB2.CaseID) ON (Staff.StaffID = IntakeMain.PCW) AND
(Staff.StaffID =
CompltDB2.StaffID)) ON ComingDue2.ASCI = CompltDB2.ASCI >>>>SET
IntakeMain.[Comments/Plan of Action] = [ComingDue2]![Comments/ Plan
of
Action]<<<< These are the two that are driving me crazy!
WHERE (((IntakeMain.ReferDate)<=Date()) AND ((C_Checklist.Certype)
Is
Null));