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" <sah18@case.edu> wrote in message
news:f898c1d7.0407090404.1e853f68@posting.google.c om...[color=blue]
> 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" <bouncer@localhost.not> wrote in message[/color]
news:<A5jHc.40258$qw1.32903@nwrddc01.gnilink.net>. ..[color=blue][color=green]
> > Are you trying to reorganize your tables so that they are normalized?[/color][/color]
That's[color=blue][color=green]
> > what your description would lead me to believe. If not, then you should[/color][/color]
be[color=blue][color=green]
> > considering that, as life will be much simpler when you want to use the[/color][/color]
data[color=blue][color=green]
> > 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[/color][/color]
create[color=blue][color=green]
> > 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[/color][/color]
would[color=blue][color=green]
> > describe in detail what you are trying to _accomplish_ rather than _how_[/color][/color]
you[color=blue][color=green]
> > 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[/color][/color]
appear[color=blue][color=green]
> > to me to be useful.
> >
> > Larry Linson
> > Microsoft Access MVP
> >
> > "sheree" <sah18@case.edu> wrote in message
> > news:f898c1d7.0407081106.666d0d2f@posting.google.c om...[color=darkred]
> > > 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![/color][/color][/color]
