I've got two tables, "Member Contributions" and "Non-
Member Contributions" that I want to automatically dump
their contents into a third table, "All Contributions."
Ummm... No. You probably DON'T want to store all the data in two
tables redundantly in a third table. Consider: a record in any of the
three tables could be edited at any time, making the redundant record
WRONG.
I've established a many to many relationship between the
first two tables through a junction table.
But there IS no relationship between the two tables! A record in the
[Member Contributions] table is by definition from a member; no record
in the Non-Member table CAN POSSIBLY BE related to it, since we know
for a fact that it is a *different* contribution from a *different*
person.
Does anyone have any idea how I can get the two "parent"
tables to dump their info into the junction table?
Instead of having the third table exist at all, use a UNION query to
"stitch together" the two tables - or, perhaps better, create an All
Contributions table, and use two Append queries to fill it; and delete
the Member and Non-Member tables altogether. If you have a Yes/No
field [Member] in the Contributions table you can distinguish which
kind of contribution it is!
A UNION query requires that you go to the SQL window, but it's easy:
create a new query based on [Member Contributions] and view it in SQL
view. Edit it to read something like
SELECT thisfield, thatfield, theotherfield
FROM [Member Contributions]
UNION ALL
SELECT thisfield, thatfield, theotherfield
FROM [Non-member Contributions]
You should be able to just copy and paste the SQL from the initial
query, and edit the tablename.
