473,224 Members | 1,446 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,224 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 1996
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...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

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.