Here's a weird one for you:
I'm trying to populate all instances of a linking field for a many:many relationship. However as there are currently 3000+ records with that field blank.
The process would be simplified significantly if I could compare [tableParticipants].[EventDate] with ANY matching instance in [tableEvents].[EventDate], but I'm not really sure it's possible to do this and have an updateable recordset, which is the point behind this exercise.

I can get the two tables in question to show up, but if I try to include the linking table (so that I can update EventID), I get "ambiguous outer joins" problems. I've tried changing the join types every way I can think of.

Here's how the tables are linked:
tableParticipants (1:n via ParticipantID) to tableDevices
tableDevices (ambiguous because data is not full, via EventID) to tableEvents
and a temporary join for this Unmatched Query:
tableEvents (Left Join via StartDate->EventDate) to tableParticipants

I'm sure it's this three-way situation that is causing the trouble, but I'm not smart enough to figure out what to do about it.

Thanks,
David R

For the puposes of populating the missing EventID's you need to temporarily break the link between tableEvents!EventID and tableDevices!EventID You said you have about 3000 tableDevices records needing an EventID. If there is more than one event record per day then you'll corrupt your data with misaligned ID fields in your devices table. If this is likely to be a problem you would need to add another field to your temporary Participant/Event link.

Well, that got it to show me the query with all three tables present, however it is not updateable.

Here's the current SQL statement:
SELECT CFS_Events.EventID, CFS_Events.EventName, CFS_Events.StartDate, CFS_Events.Location, tableDevices.EventID, tableParticipants.EventDate, tableParticipants.ParticipantID, tableDevices.ParticipantID
FROM (CFS_Events LEFT JOIN tableParticipants ON CFS_Events.StartDate = tableParticipants.EventDate) LEFT JOIN tableDevices ON tableParticipants.ParticipantID = tableDevices.ParticipantID
WHERE (((tableParticipants.EventDate)=[CFS_Events].[StartDate]));

I again caution you about the case of multiple events per day. If this is the case, the query below will update your blank tableDevices.EventID values, but the data will be incorrect. I would suggest making a copy of your devices table, running this query and then examine the data for accuracy. Here it is:

UPDATE (CFS_Events LEFT JOIN tableParticipants ON CFS_Events.StartDate = tableParticipants.EventDate) LEFT JOIN tableDevices ON tableParticipants.ParticipantID = tableDevices.ParticipantID SET tableDevices.EventID = [CFS_Events]![EventID];

Good Luck.

Oh, I know that's a risk. For the old data I'm concerned about, there's no risk of having two events the same day because the event records are incomplete - most of those older events only consist of a day. If we can later clarify some of them as two events, it'll be a simple thing.

Thanks for the update query. It's always something new here.

David R

Here was the final answer to this part of the puzzle.
UPDATE CFS_Events INNER JOIN tableDevices ON CFS_Events2.StartDate = tableDevices.DateReceived
SET tableDevices.EventID = [CFS_Events]![EventID];

However, now the second part rears its head, and I'm not sure how to phrase it.
I need to populate CFS_Events with 'dummy' events for all the dates listed under tableDevices.DateReceived but NOT already accounted for in CFS_Events.StartDate.

To clarify:
We now have 5000 devices listed, but only 790 of them are associated with being given at events in the database. For the other 4000-odd devices, the EventID field is still blank because there's nothing to match to. What I am trying to create is an Append Query that will fill in the blanks.
This gave me what I believe is a Cartesian Product (492k records):
SELECT CFS_Events2.StartDate
FROM tableDevices, CFS_Events2
WHERE (((CFS_Events2.StartDate)<>[tableDevices].[DateReceived]));

when I removed the relationship between the two tables, however since they're linked via the Date fields or the EventID fields, leaving the relationship in resulted in 0 rows.

Am I completely off base here? I realize it's late in the day, but my boss FINALLY realized the need for normalizing this part of the database (I know, Pat, I know...she doesn't design anything, anymore!)

David R




[This message has been edited by David R (edited 02-28-2002).]

David,

Could you not include your table twice and form a join between the two primary keys then enter 'Is Not Null' under the date field from the second table. this should result in a recordset with only null dates from your first instance of the table.

Then run your update query from a query based on the above.

I am on track????


Ian

[This message has been edited by Fornatian (edited 03-01-2002).]

I think the difficulty lies in understanding exactly HOW badly unnormalized this database was. This part of the structure currently looks like this:
tableParticipants:
ParticipantID (PK)
...
EventName (text)
EventDate (Date/Time)
EventLocation (text)
EventType (Lookup field to standard list, text)

That's it. One event per person, independently entered.

The associated (1:M) table tableDevices looks like this:
DeviceID (Autonumber PK)
ParticipantID ( Long Integer, FK)
DeviceName (Lookup list, text)
DateReceived (Date/Time)
DeviceNotes (Text, 255)
EventID *(Long Integer, Unused)

Then the (as of yet) COMPLETELY unrelated table in a separate scheduling database, CFS_Events:
EventID (Autonumber PK)
EventName (Text)
EventDate (Date/Time)
EventLocation (Text)
EventType (Lookup field to standard list, text)

Does it make more sense now? I'm trying to remove the section on events in the existing tableParticipants, where it doesn't belong, and put it in a M:M relationship with CFS_Events through the table tableDevices, since everyone gets a device...at an event! Logical, no?
Have I mentioned that my boss doesn't do database design anymore?

What's compounding the problem, and my current hurdle, is that for over 4/5 of the devices, there is no information as of yet in the CFS_Events table to link to. My boss started the databases back in May 2001, and the Events and Devices were actually occurring back to 1998. So I'm trying to populate either this table or a 'dummy' table (to keep from flooding the real CFS_Events table) with 'dummy' events to link to. If a dummy table is feasible I'll union it with the real one for this association.

As for your question, if I understand what you're asking (linking tableDevices.EventID with CFS_Events.EventID and check for nulls), I can't because the former is not populated yet, except for the 790 that I have an event to link to. Events simply do not exist in the table prior to May 2001. Now, I know they exist because I have a DateReceived in tableDevices, and if all I have to go on is a date and approximate location, that will work. But I need to pull all those unaccounted for DateReceived's into CFS_Events.

*Takes a breath*
Thanks in advance,
David R

[This message has been edited by David R (edited 03-01-2002).]

David R,

*I really have a headache now*

Can you send me a small example that I can fry my head with???

Ian

A sample is on its way.
Remember...you asked for it!

David R