Advice for Road Project Database

[Drumstix]

Hello, I'm new to Access and have been trying to build my first database over the past couple of days. I work for my local transportation authority and I'm trying to design a database to act as a small scale pavement management system.

In my database, I'd like to be able to have a list of current and past roadwork projects, the contractor associated with the project (along with complete contractor contact information), the location of the project (GPS coordinates, route number, district number, chainage), and lab results (compaction, % asphalt, binder grade). Finally, if possible, I'd like to be able to add, as an attachment, data from our automatic road analyzer (ARAN). The road analyzer exports its data to Excel, and I'd simply like to be able to attach the Excel output file to a specific project.

Here's how I've designed my tables:

ProjectTable
ProjectID (Primary Key)
ProjectName (our own internal convention, in format "44-10 TSG" as an example)
DateStarted
DateCompleted
ProjectDescription (a memo note)


ContractorID (Foreign Key)

ContractorTable
ContractorID (Primary Key)
CompanyName
FirstName
LastName
TelNumber
Address
City
Email
Notes

LocationTable
LocationID (Primary Key)
ProjectID (Foreign Key)
GPSLat
GPSLong
Chainage
RouteNumber
District
LocationDescription (memo note)

LabTable
LabID (Primary Key)
SampleNumber (our own internal convention)
ProjectID (Foreign Key)
TestDate
PercentAsphalt
BinderGrade
Compaction

ARANTable
AranID (Primary Key)
ProjectID (Foreign Key)
Operator
TestDate
ARANData (I would like to attach data here, and associate it with a project using the relationships)


Based on what I'm trying to do, have I designed this correctly? There can be multiple projects per contractor, multiple lab tests per project, multiple locations per project, multiple lab tests per project, and multiple ARAN runs per project.

[June7]

Looks good to me.

Although I am not clear what you mean by ARANData and how you would 'attach' data here - is this to be a hyperlink to a PDF document?

What is chainage?

Why multiple locations per project?

[Drumstix]

For ARANData, I guess what I'd like to do is hyperlink to an Excel spreadsheet. The database will be kept on the shared company drive along with all of the ARAN Data. Is this possible?

Chainage refers to the linear length of the project itself.

As for the locations, though it's not common, the projects might be broken up into multiple parts within the original tender. So technically it's part of the same original "project" but has multiple locations and will need multiple lab tests and ARAN runs.

[June7]

Hyperlink is possible.

If the spreadsheet structure is simple enough, could even link to the spreadsheet and display like a table within Access. Then build queries and reports with the link.

Would not have multiple tests if the project was not broken up?

[Drumstix]

If the project were broken up, there would be multiple tests. Usually one per project location, but I don't think the project ever goes beyond two locations, and even then, they're in very close proximity.

I'm struggling to figure out the best way to structure my forms. I'd like for someone with minimal Access knowledge to be able to just boot up a form and enter details for a new project. Will I need subforms? Say, one primary form for general project info, and then multiple subforms within that form (one for locations, one for tests, one for ARAN). And then a separate form for contractor info?

[June7]

Yes, subforms sounds appropriate. Could use a tab control to organize the multiple subforms.