Update query in access - based on data in different table

[sheree]

I have 3 tables (amoung a few others) in a small access database. The
tables are as follows:
==
AEReport
--------
AEID (PK)
RptCatelog
GCRCID
PatientID
EvntDate
XAbscess
XAnemia
XAtaxia
(and so on)

==
Event
-------
EID (PK)
Symptom

==
AEtoEvent
-----
AEID (PK)
EID (PK)

The relationships are as such:
Event (one) ---> (many) AEtoEvent
AEReport (one) ---> (many) AEtoEvent
Referential integrity is turned on for both relationships.

Right now, the AEReport table is fully populated. The Event table and
the AEtoEvent table are completely empty (these are just newly added).

What I need to do is insert records into the Event (and by default
also the AEtoEvent linking table) based upon a few true/false fields
in the AEReport table. For example, if XAbscess = True in the
AEReport table, then I want to insert a record into the Event table
with Symptom = Abscess (EID is an autonum, as is the AEID).

I'm just not getting the syntax correct for this, no matter what I
try. Can anyone help with some syntax/code examples of how to do
this?? Thank you so much for helping!

[Larry Linson]

Are you trying to reorganize your tables so that they are normalized? That's
what your description would lead me to believe. If not, then you should be
considering that, as life will be much simpler when you want to use the data
if they are normalized... and the AEReport Table does not seem to be.

Perhaps I am missing something but it would appear that you want to create
one record in the Event table for each symptom, but relate that one
"symptom" record to each AEReport to which it applies, via the AEtoEvent
table... in essence, creating a many-to-many relationship between the
AEReport and Event table.

If that is not what you had in mind, perhaps it would be useful if you would
describe in detail what you are trying to _accomplish_ rather than _how_ you
are trying to accomplish it, and maybe we could make some useful
suggestions.

An Event table with only the Symptom and a generated unique id (the
AutoNumber) repeated for each AEReport which has that Symptom doesn't appear
to me to be useful.

Larry Linson
Microsoft Access MVP

"sheree" <sa***@case.edu> wrote in message
news:f8**************************@posting.google.c om...

I have 3 tables (amoung a few others) in a small access database. The
tables are as follows:
==
AEReport
--------
AEID (PK)
RptCatelog
GCRCID
PatientID
EvntDate
XAbscess
XAnemia
XAtaxia
(and so on)

==
Event
-------
EID (PK)
Symptom

==
AEtoEvent
-----
AEID (PK)
EID (PK)

The relationships are as such:
Event (one) ---> (many) AEtoEvent
AEReport (one) ---> (many) AEtoEvent
Referential integrity is turned on for both relationships.

Right now, the AEReport table is fully populated. The Event table and
the AEtoEvent table are completely empty (these are just newly added).

What I need to do is insert records into the Event (and by default
also the AEtoEvent linking table) based upon a few true/false fields
in the AEReport table. For example, if XAbscess = True in the
AEReport table, then I want to insert a record into the Event table
with Symptom = Abscess (EID is an autonum, as is the AEID).

I'm just not getting the syntax correct for this, no matter what I
try. Can anyone help with some syntax/code examples of how to do
this?? Thank you so much for helping!

[sheree]

My apologies for not being clearer in my description. Yes, what
you've described is *exactly* what I am trying to do. I originally
designed this database incorrectly, and am now trying to normalize it
(as it should be). And, yes, I'm creating a many-to-many relationship
between the AEReport and Event tables -- using the AEtoEvent table as
a linking table only. I want to move all the existing data from the
incorrect structure (all located in the AEReport table) over to these
2 new tables, but I'm not quite sure how to go about this. Once I
have all the data copies over into the new structure, I will be
permanently deleting the XAbscess, ... fields from the AEReport table.
I'd be grateful for any suggestions on the syntax on how to accomplish
this. Thanks for taking the time to help.

"Larry Linson" <bo*****@localhost.not> wrote in message news:<A5*******************@nwrddc01.gnilink.net>. ..

Are you trying to reorganize your tables so that they are normalized? That's
what your description would lead me to believe. If not, then you should be
considering that, as life will be much simpler when you want to use the data
if they are normalized... and the AEReport Table does not seem to be.

Perhaps I am missing something but it would appear that you want to create
one record in the Event table for each symptom, but relate that one
"symptom" record to each AEReport to which it applies, via the AEtoEvent
table... in essence, creating a many-to-many relationship between the
AEReport and Event table.

If that is not what you had in mind, perhaps it would be useful if you would
describe in detail what you are trying to _accomplish_ rather than _how_ you
are trying to accomplish it, and maybe we could make some useful
suggestions.

An Event table with only the Symptom and a generated unique id (the
AutoNumber) repeated for each AEReport which has that Symptom doesn't appear
to me to be useful.

Larry Linson
Microsoft Access MVP

"sheree" <sa***@case.edu> wrote in message
news:f8**************************@posting.google.c om...

I have 3 tables (amoung a few others) in a small access database. The
tables are as follows:
==
AEReport
--------
AEID (PK)
RptCatelog
GCRCID
PatientID
EvntDate
XAbscess
XAnemia
XAtaxia
(and so on)

==
Event
-------
EID (PK)
Symptom

==
AEtoEvent
-----
AEID (PK)
EID (PK)

The relationships are as such:
Event (one) ---> (many) AEtoEvent
AEReport (one) ---> (many) AEtoEvent
Referential integrity is turned on for both relationships.

Right now, the AEReport table is fully populated. The Event table and
the AEtoEvent table are completely empty (these are just newly added).

What I need to do is insert records into the Event (and by default
also the AEtoEvent linking table) based upon a few true/false fields
in the AEReport table. For example, if XAbscess = True in the
AEReport table, then I want to insert a record into the Event table
with Symptom = Abscess (EID is an autonum, as is the AEID).

I'm just not getting the syntax correct for this, no matter what I
try. Can anyone help with some syntax/code examples of how to do
this?? Thank you so much for helping!

[Larry Linson]

I'm sorry that my situation is such that I can't provide details in this
post, but basically, create an entry in the Event table for each of the
(few) possible events. Then create a query with a calculated field for the
event name based on the X... field, join the Query and the Event Tables on
that Field and the Event Name, make the join "all from AEReport and only
those that match from Event", pull the two IDs into the grid, check to make
sure it is returning what you expect, then convert the Query to an Append
query to add the paired IDs to the AEToEvent table.

Larry Linson
Microsoft Access MVP
"sheree" <sa***@case.edu> wrote in message
news:f8**************************@posting.google.c om...

My apologies for not being clearer in my description. Yes, what
you've described is *exactly* what I am trying to do. I originally
designed this database incorrectly, and am now trying to normalize it
(as it should be). And, yes, I'm creating a many-to-many relationship
between the AEReport and Event tables -- using the AEtoEvent table as
a linking table only. I want to move all the existing data from the
incorrect structure (all located in the AEReport table) over to these
2 new tables, but I'm not quite sure how to go about this. Once I
have all the data copies over into the new structure, I will be
permanently deleting the XAbscess, ... fields from the AEReport table.
I'd be grateful for any suggestions on the syntax on how to accomplish
this. Thanks for taking the time to help.

"Larry Linson" <bo*****@localhost.not> wrote in message

news:<A5*******************@nwrddc01.gnilink.net>. ..

Are you trying to reorganize your tables so that they are normalized? That's what your description would lead me to believe. If not, then you should be considering that, as life will be much simpler when you want to use the data if they are normalized... and the AEReport Table does not seem to be.

Perhaps I am missing something but it would appear that you want to create one record in the Event table for each symptom, but relate that one
"symptom" record to each AEReport to which it applies, via the AEtoEvent
table... in essence, creating a many-to-many relationship between the
AEReport and Event table.

If that is not what you had in mind, perhaps it would be useful if you would describe in detail what you are trying to _accomplish_ rather than _how_ you are trying to accomplish it, and maybe we could make some useful
suggestions.

An Event table with only the Symptom and a generated unique id (the
AutoNumber) repeated for each AEReport which has that Symptom doesn't appear to me to be useful.

Larry Linson
Microsoft Access MVP

"sheree" <sa***@case.edu> wrote in message
news:f8**************************@posting.google.c om...

I have 3 tables (amoung a few others) in a small access database. The
tables are as follows:
==
AEReport
--------
AEID (PK)
RptCatelog
GCRCID
PatientID
EvntDate
XAbscess
XAnemia
XAtaxia
(and so on)

==
Event
-------
EID (PK)
Symptom

==
AEtoEvent
-----
AEID (PK)
EID (PK)

The relationships are as such:
Event (one) ---> (many) AEtoEvent
AEReport (one) ---> (many) AEtoEvent
Referential integrity is turned on for both relationships.

Right now, the AEReport table is fully populated. The Event table and
the AEtoEvent table are completely empty (these are just newly added).

What I need to do is insert records into the Event (and by default
also the AEtoEvent linking table) based upon a few true/false fields
in the AEReport table. For example, if XAbscess = True in the
AEReport table, then I want to insert a record into the Event table
with Symptom = Abscess (EID is an autonum, as is the AEID).

I'm just not getting the syntax correct for this, no matter what I
try. Can anyone help with some syntax/code examples of how to do
this?? Thank you so much for helping!

[sheree hemphill]

Thank you so much for your time and suggestions. You've been very
helpful!
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!