Report with Multiple Grouping and specific range on 1 group

[jnielsen]

Hi All. New to the forums and I've ran into some trouble with a project i'm working on.

I have a project that has a road with several segments that make up the road. I also have some records that are right of way distances by milepost that need to fall within each section and display under that segment of road. I think what I need to do is something with the grouping but I cant figure out what i'm doing wrong. I've attached a sample database with the information and the report I would like to setup as well. Also attached is what the report would look like.

Currently it seems to work except it shows multiple copies of records under each segment and they don't fall within the segment for each road.

I'm not an expert but I have played around with access for awhile now and then. Any pointers or ideas on how to get it to work correctly?

Thank you,

Jason

[June7]

Hi, Jason

I work for a state DOT agency and deal with road data as well.

The 'duplicate' records is because each table has multiple records for each road. When joined in query on the Road Number fields each record of each table must join to each record of the same road in the other table (a Cartesian join): 4 records for Beet Rd in ROW and 3 records for Beet Rd in ROADLOG means 12 records for Beet Rd in the query when pulling in the BMP and EMP fields from both tables.

Access is not 'spatially aware'. It has no idea that ROADLOG segments go with specific ROW sections: Blalock Dr has 4 ROW sections of varying widths and 2 ROADLOG sections. How will Access know that the ROADLOG section 0.0000 to 0.7400 should associate with ROW section 0.0000 to 1.2490?

And back with Beet Rd: there is a ROADLOG segment 0.0000 to 0.8300 but that length in ROW is broken in 4 sections starting with 0.0760 (with a gap between ID's 16 and 17).

Then it appears not all ROADLOG segments have a corresponding ROW section.


Advise no spaces or special characters/punctuation (underscore is exception) in names.