473,287 Members | 1,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Update query in access - based on data in different table

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!
Nov 13 '05 #1
4 2000
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!

Nov 13 '05 #2
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!

Nov 13 '05 #3
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!

Nov 13 '05 #4

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!
Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
2
by: jquest | last post by:
Hi Again; I am improving my database and have to overcome some original design mistakes. I have 3500 records in the table and I have had to insert new fields to track things I originally didn't...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
11
by: Siv | last post by:
Hi, I seem to be having a problem with a DataAdapter against an Access database. My app deletes 3 records runs a da.update(dt) where dt is a data.Datatable. I then proceed to update a list to...
2
by: Brett | last post by:
My database has 2 tables: Table1 & Table2. If a field is not null on a record in table2, then the not null fields in table1 that correspond to the records in table1 needs to be updated to match the...
1
by: Vinod Sadanandan | last post by:
A Roadmap To Query Tuning ============================ For each SQL statement, there are different approaches that could be used to retrieve the required data. Optimization is the process of...
8
geolemon
by: geolemon | last post by:
I'm trying to perform an update, and I can't avoid this error - I've tried this query what must be 5 different very fundamentally different ways now. Arg. I used to be a DBA in large DB2 and SQL...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.