Merging Two Completely Different Tables
Greetings,
I couldn't delete this post so, I am just completely modifying it, for anyone who navigated their way back to this and is thinking, "This isn't what I was looking for". lol
I have two tables with completely different data. I want to create a third table which would be made up of the contents of each record in table1, duplicated for each record in table2, so I can add additional formulas, etc for a report.
table1 structure
Name -- StartDate -- FinishDate -- Amount
table2 structure
Period -- BeginDate -- EndDate
Desired merger
Name -- StartDate -- FinishDate -- Amount -- Period -- BeginDate -- EndDate --...
Example output
Name1 -- StartDate -- FinishDate -- Amount -- Period1 -- BeginDate -- EndDate --...
Name1 -- StartDate -- FinishDate -- Amount -- Period2 -- BeginDate -- EndDate --...
Name1 -- StartDate -- FinishDate -- Amount -- Period3 -- BeginDate -- EndDate --...
Name2 -- StartDate -- FinishDate -- Amount -- Period1 -- BeginDate -- EndDate --...
Name2 -- StartDate -- FinishDate -- Amount -- Period2 -- BeginDate -- EndDate --...
Name2 -- StartDate -- FinishDate -- Amount -- Period3 -- BeginDate -- EndDate --...
etc...
The true challenge with this is being able to set this up so the merger of the data in this fashion happens dynamically. Meaning when one or both tables has a record added, modified, or removed, the merged output reflects the changes. I figure this calls for a relationship, especially for the dynamic aspect of things. However, I don't know how to set that up, assuming it's possible to do. If anyone has any suggestions, that would be greatly appreciated. Thank you in advance.