Linking two tables based on market location
I apologize in advance if this is a stupid (and long) question.
My ultimate goal is to generate a properly formatted .csv which lists meeting information in chronological order for use in another process.
I am starting from scratch. Right now I have two tables: MBM (which lists which Market [defined by state and county] will need meetings generated) and MR (which contains all the meeting data [dates, times, locations, including state and county]). The trick is that sometimes multiple counties are treated as a single Market in the MBM. There is a field (Combo_County) which designates whether two or more counties need to be combined as a single Market. If so, this field lists of all the relevant counties within the state separated by a semicolon. If not, this field is blank.
So logically, it should work like this:
1. Is there data in Combo_County in the MBM?
If yes, pull all the MR records that match the (combo) Market in MBM into a third table (I think). If no, pull all the MR records that match the (single) Market in MBM into a third table (again, I think).
2. In the third table(?), order each set of MR records that have been grouped by Market (single or combo) by Location (sometimes there are multiple locations within the same county), then by Date, then by Time. Then combine all of the ordered meetings for each Market into a single record (basically: Location; Meeting1; Meeting2; Meeting3...).
Seems simple, but I'm still trying to figure out how to link the MBM and MR tables. Maybe we should take it a step at a time. How can I evaluate the MR records against the MBM Markets to group them in a third table? Any thoughts on effectively linking the two tables would be greatly appreciated.