Merging data from different data streams into a single ordered table

[JonathanOz]

I am building a database that takes data, in the form of text files, from a travel demand model, and combines records to create aggregate statistical tables needed by decision makers. Here's the problem: the source data are in two different forms depending on the type of roadway being described. In one source, all of the information for travel through a single location along the route in both directions of travel are given in a single record. In the other source, data is separated by direction of travel and special lanes so that travel through any individual location along the route could be represented by as many as four different records.

I figured out how to get the data out of the travel demand model, put them into two tables, and created a series of queries that aggregated the data into statistics for each TCR segment that our bosses want. Trouble is, while the data is all nice and good, it is also in two tables, and one cannot simply append one dataset to the end of the other. Each route is made up of the TCR segments, and the data must be listed in TCR Segment Order without regard for what type of roadway makes up the TCR segment. I can of course export it to Excel sort them the way I want in Excel and then reimport them into Access, but that defeats the intent to keep this thing simple for the end user.

[June7]

Data can be manipulated however you want to get whatever you want. The how might involve queries, VBA, temp tables.

Really need to see examples of source data (the two tables) and desired output.

[JonathanOz]

Data can be manipulated however you want to get whatever you want. The how might involve queries, VBA, temp tables.

Really need to see examples of source data (the two tables) and desired output.

Managed to squeeze the data down to less than 2 MB. SCAG 2008 2035 TCR Database 2014 Edition_2014-10-10.zip.

I frankly admit, I tend to do Access by the seat of my pants. The input tables are under the Input Tables Tab. The two that start with "SCAG 2008 2035..." are the ones.
The tabs in the navigation bar for Segment Manipulation and Aggregation for Conventional Highways and Freeways take the information from the travel demand model. Under two separate sets of queries, I attempted to normalize the traffic flow data so that traffic flow volumes given for different time periods are normalized to reflect a single hour in each time period. Weighted average traffic flow volumes are then calculated for each segment in each direction.
The next step with each of the data streams is to calculate the specific statistics that people want, such as directional split (the percentage of vehicle traveling on the freeway. The is done in the queries under the tab for Calculate Freeway Segment Statistics and the tab for Calculate Conventional Highway Segment Statistics. I took a couple of the queries and converted them to tables in the Generate Report tab. I then created the two "Concept Summary Table YRYR" for Freeways and for Conv Hwy. These are two tables that should be contained. Currently, the only data in the tables are for State Route 118. When completed, there'll be data for 24 different state routes.

I hope this explanation makes any sense at all, and that the data makes it clearer. Any help is appreciated.

[June7]

The two source tables are somewhat different in structure. If they were combined to one table there would be a lot of empty cells.

However, the two "Concept Summary Table YRYR" queries seem identical. There is minor variation in field names but are they really the same type of data?

I am not clear on what the issue is. Do you want the two Concept queries to be presented as one?

[JonathanOz]

Yes, the two "Concept Summary Tables" are essentially identical, but they get processed separately because the tables that generate them are different, hence the processing is different. Essentially, you've got it, I'd like to bring these two tables into one with the ability to sort them in Route Number, TCR Segment Number order for the purpose of generating reports without regard for whether the segment is a conventional highway or a freeway.

[June7]

Options:

1. all records into one Concept Table

2. UNION query

[JonathanOz]

With the help of a couple of You Tube! videos, managed to implement a Union. Worked very nicely!