Referencing CBO's, Relationship loop dilemma

[Anthony.DG]

The setup -
3 Tables: [tblCarriers], [tblTrucks], [tblTicketing](junction table)
1 Form: [frmTicketing]

[tblCarriers](one) has a foreign key linked from [tblTrucks](many).
[tblTrucks](one) has a foreign key from [tblTicketing](many)
[tblTicketing](one) had a foreign key from [tblCarrier](many) but the relationship and field were deleted because it caused a relationship loop.

The problem -
On [frmTicketing] there are 2 combo boxes: (1)[cboCarriers], (2)[cboTrucks].
[cboTrucks] only shows a selection of Trucks depending on what you choice you select from [cboCarriers]
BUT...now that the relationship loop from [tblCarriers] was deleted along with the its foreign key field ([CarrierID])in [tblTicketing] the 2 combo boxes no longer work.

So, I see one solution to immediately is to put back the field [CarrierID] just to store the number as a reference but don't put back the relationship (otherwise I'm putting the relationship loop back) and move forward.
But that in itself creates 2 problems i see.
1.The [CarrierID] field in [tblTicketing] is redundant information that bloats the database
2. The lack of a relationship from [tblTicketing].[CarrierID] to [tblCarriers] makes it possible on [frmTicketing] for [cboCarriers] and [cboTrucks] to have 2 selections that are not actually related to each other.

The question -

I don't know what to do. I want to have my 2 combo boxes so that [cboCarriers] filters the selection for [cboTrucks] but I don't want to store the [CarrierID] field on [tblTicketing]. And I also need to be able to see the name of the Carrier on [frmTicking] when entering data and when it gets printed. I want to have my cake and eat it to without any relationship loops. Or should I leave a relationship loop in, much harm?

Anyone have any solutions or ideas or any additional need information needed to help me figure out what I can do to get what I want?

 

[jdraw]

Tell us about your business in simple, non database terms. Start at the 30,000 ft level and tell us about the Carriers and the Trucks and what Ticketing means in your context.

 

[Anthony.DG]

Tell us about your business in simple, non database terms. Start at the 30,000 ft level and tell us about the Carriers and the Trucks and what Ticketing means in your context.

Material Yard (asphalt, bed rock, base rock, sand, dirt, ect.) with a weigh station . Trucks get weighed empty (tare weight) then weighed before leaving (gross weight) to know how much material they have ( gross - tare = net). We charge by the ton. We have trucks to take material where it needs to go or customers sometimes use their own trucks or contracted trucks to pick up material.

Ticketing is just basically an invoicing displaying Customer name, The Carrier (company that owns the truck that was loaded up), truck number(not licence plate, its a number assigned to truck by the Carrier so numbers could potentially be repeated by another Carrier). Along with Tare, Gross, and Net weight, material type

(This is just a mock up ticket since its not ready for use yet.)

Truck driver reviews ticket(invoice), signs and leaves with material.

 

[jdraw]

I see a number of potential tables in your description, but their use would depend on the scope of your project.

Customer, Material, Loader/operator, Job, Truck, Carrier, Driver, Destination,WeighStation, Weighing, MaterialCost..
I found this older post that does not have a specific answer, but the comments/dialog may be of interest/use to you.
Good luck with your project.

 

[Uncle Gizmo]

I have a bit of experience with haulage, lorries, (USA Trucks) weighbridges, as I ran a sand and gravel business in Newbury. The business sold sand and gravel, road construction materials, concreting Ballast, decorative aggregates, rockery stone etc. I wrote my first MS Access database to run the haulage for the company. Mind you this was many years ago and I've not had much contact with the industry, except to observe how in the UK at least, the prices of aggregates have skyrocketed!

I'm thinking you might need to approach the problem from the other end, from the:-

"Weight Ticket"
Bill of lading, weighbridge ticket, delivery docket, --- I don't know what term you use to describe this document?

What I'm thinking is the ticket that goes with the lorry is an official legal document. That's if I assume correctly that the rules in the USA are practically the same as in the UK.

This document proves that the vehicle is not overloaded, well most of the time!

I think if you think about it from that end, then your database structure will grow up from there if you see what I mean.

So let's assume you have the Weighbridge ticket (that's what I would call it) and you have a customer, now you've got both ends. The customer needs an invoice, and the load needs a ticket, now you need to fill in the blanks.

 

[The_Doc_Man]

I want to have my cake and eat it to without any relationship loops. Or should I leave a relationship loop in, much harm?

There IS such a thing as creating a query that spot-overrides extant relationships. Your carrier/ticket relationship caused a relationship loop. However, if you use the query grid for at least part of this process, you can assert and deassert relationships in it. By default, when you pull two tables in, if they have a high-level (i.e. globally defined) relationship from the Relationships panel, it is shown between those tables. But there is nothing to stop you from deasserting an extant relationship in that query's upper section where the tables and their fields are listed. And there is nothing to stop you from asserting a new (temporary) relationship.

What I'm thinking is that for selection purposes in a combo, you might not need all three tables to be involved. (For the whole problem, obviously yes you do - but I'm talking about the narrow problem of your combo boxes.) For any query involving only two of your tables, you can spot-assert the relationship. Now if you have a query with all three tables, that can't happen because of the loop. But if the loop can't happen because not all parts are involved in this tiny piece of the puzzle, you might be able to muddle through. Just a thought.