Form to create records in multiple related tables

[OzTrucker]

Hello, complete Access & forum beginner here so please forgive me in advance for any transgressions I'm about to make relating too...well anything.



NOTE: I am going to need ongoing help building this DB & would be happy to discuss remuneration for anyone (expert) that could provide ongoing assistance throughout the project (hope I'm allowed to say that here...again sorry if not)?

I have a longhaul car carrying company which to date I have managed on a Excel spreadsheet with a lot of conditional formatting & logic built into the background. But my company is experiencing continued growth & I recognise I need a better way. A long time ago I had a background in Mathematics/Physics so I understand boolean logic, have done some very limited programming in the past but am keen to have a crack at building a simply DB to (for a start at least) handle bookings, pick-ups, deliveries & manifesting.

The problem includes these main facets:

Recording a booking as it comes in which once confirmed I am calling a Job, a Job includes the vehicle details, pick-up (PUP) & delivery (DEL) details. We us local fleet operators to perform both PUPs & DELs as well as performing some ourselves with longhaul semis when required. I then need to be able to allocate the jobs to a manifest which would have the trip details including driver, tractor, trailer, departure date, arrival date, trip (i.e. from-to), etc. I have a lot of one-off customers sourced from internet loading boards which mean 60-70% of the time a new job requires a new Job contact as well as a new PUP & DEL contact & addresses.

So I have designed my tables & created relationships & can allocate jobs to a manifest & can make all of this work (by which I mean I can run queries that would eventually become my manifests & local PUP/DEL runsheets for subcontractors) by jumping between numerous tables in the required order however if I give this to my admin girl she'll quit on the spot so now I need to create my data entry forms which is where I come undone & have no idea where to start. I can create basic "new" forms (e.g. New Vehicle, New PUP, New DEL, New Contact, etc.) but anything more than that...no idea.

So I have the following main tables with all PKs being auto-numbers & all many-to-one relationships dealt with through "assignment" intermediary tables containing an autonumber field & the two FK fields which combined create a unique foreign key (e.g. tbl_AssignManifestJobs has ID (Autonumber), Manifest_ID (PK&FK) & Job_ID (PK&FK). I'm not sure if that's the best way to address this?!): Main tables of interest being:

tbl_Jobs: Contains Job details with fields Job_ID (PK), Booked (Date), Contact_ID (FK), Vehicle_ID (FK), PUPType_ID (FK), PUP_ID (FK), DELType_ID (FK), DEL_ID (FK)

tbl_Vehicles: Contains vehicle details with fields Vehicle_ID (PK), Ready (Date), Make_ID (FK), Model_ID (FK), VIN (Text), rego (Text), reference (Text), Leg_ID (FK), Rate (Currency), Notes (Long Text).

tbl_JobPup: Contains pickup (PUP) details with fields PUP_ID (PK), PUPLocalFleet_ID (FK), PUPContact_ID (FK), PUPAddress (FK), PUPDepot_ID (FK), PUPDate (Date), InPUPDepotDate (Date), PUPRate (Currency), PUPVehicleSurvey (Attachment), Complete (Yes/No)

tbl_JobDel: Contains delivery (DEL) details with fields DEL_ID (PK), DELLocalFleet_ID (FK), DELContact_ID (FK), DELAddress (FK), DELDepot_ID (FK), InDELDepotDate (Date), DELDate (Date), DELRate (Currency), DELProofOfDelivery (Attachment), Complete (Yes/No)

tbl_Manifests: Contains the trip details with fields Manifest_ID (PK), Lane_ID (FK), Truck_ID (FK), Trailer_ID (FK), Employee_ID (FK), Depart (Date), Arrive (Calculated Date).

Obviously there are numerous supporting tables including separate tables for things such as (Addresses, Businesses, Contacts, Employees, Make, Model, Leg, Lane, Truck, Trailer, etc.).

The next hurdle that I believe I need to overcome is in relation to creating a form that allows me to enter the Job details which requires entering a new vehicle, PUP & DEL at the same time which in turn may require up to 3x new contacts & 2x new addresses.

Any & all guidance, suggestions, recommendations, advice, etc. graciously & humbly appreciated. I'm a longhaul trucker, so please bear that in mind...thank you.

[ranman256]

use a master form (single record)
in it is 1 subform to show the related records.
Also add a TAB control so you can swap out the 1 subform for the various other tables. (this save memory rather than having a subform for every table show)

clicking a tab would swap out the source of the subform. thus infinite subforms.

[Micron]

Seems that manifest_jobs is a junction table. So what would happen if a customer had 2 vehicles to be moved? You will have to treat that as 2 jobs, yes? If you are not and will not be, then I see a problem with

the two FK fields which combined create a unique foreign key (e.g. tbl_AssignManifestJobs has ID (Autonumber), Manifest_ID (PK&FK) & Job_ID (PK&FK)

It looks to me as if tblVehicles has several fields which are not attributes of a vehicle and may not belong there, Date being one of them.
I'm not seeing a need for pickup and delivery tables if those aspects could be attributes of a job, but then I don't understand the intricacies of the business.

You have a large and somewhat complicated project in all. Makes me wonder how you could find the time to build this and run a business at the same time. You must not be getting a whole lot of sleep!

[orange]

I suggest you post a copy of your existing tables and relationships - be sure to expand tables to show all fields.
It may be helpful to you and readers if you could create/describe some sample scenarios in simple English.
For example, creating a new contract to pickup Vehicle A and deliver it to location X. Tell us via example how Lane fits?
Engaging a carrier for a long haul delivery (possibly with multi vehicles and different delivery points)?
Some typical transactions so we can get some appreciation of the range of transactions.

See my stump the model for ideas.