Design/Structure Ideas for Data Entry form with multiple levels

[KRISTIRG]

I thought I had a direction I was going then yet another criteria arose and I'm just having a hard time picturing it. I've also just been staring at it for too long.
In the attached pic is what I had but now there can be more than 1 customer per stop so the subform on the right won't work. The Add Fees button opens another subform to enter those #'s and OpenArgs the LoadID, StopNumber and CustomerID.
What I need:
Data entry of a Load -
LoadDetails - LoadID (autoNumber), Date,Truck, Company, Driver, EndMileage (subtract current odometer from truck table to get LoadMiles, this also calculates LoadCost)
Stops - Each Load can have 1 to ? Stops. There can be multiple customers per stop, each has # pounds their carrying, Notes field for each.
Fees - Each Stop can have 1 - ? fees that go with it for oversized freight or layovers or whatever, each fee has a $ value that needs entered.

These all go to tblLoads, tblStops (autonumber field, LoadID, StopNumber, CustomerID, Pounds), and tblStopFeeDetails (autonumber, LoadID, StopID, CustomerID, FeeID, Amount)

I'm just not seeing it working how I have it, the stop number has to stay the same and let me enter another customer. A subfrm for just StopNumber seems like a waste. Just looking for ideas. Like I said, staring too long, can't see the forest at all anymore...Lol

 

[LarryE]

Please take a screenshot of your relationships design window and post the picture in a response. It's no use trying to help on this unless we can see what the design and relationships look like to begin with.

 

[plog]

By focusing on forms you are attacking this the wrong way mentally. Put forms aside and focus on your tables/fields. Forms are the last element to work on in an Access database.

These all go to tblLoads, tblStops (autonumber field, LoadID, StopNumber, CustomerID, Pounds), and tblStopFeeDetails (autonumber, LoadID, StopID, CustomerID, FeeID, Amount)

I don't know what the right way is, but I know that is wrong. If tblStops has LoadID in it and tblStopFeeDetails has StopID it does not also need LoadID. You've got circular references in there (CustomerId shouldn't be in both either). You've got a mess that is only obsfucated by focusing on forms. You need to focus on your data and normalize it properly before moving on (Reports are actually the next step, not forms).

My suggestion is to post a sample database or take a screenshot of your Relationship Tool and post it here so we can flesh out the proper way to structure your data.

 

[KRISTIRG]

I don't like the way the drivers and trucking companies are set up but couldn't really figure out another way to be able to look up a driver by their Company and by the Load.

 

[plog]

Can you expand the tables so we can see all the fields in the tables please

 

[KRISTIRG]

The cut off one is tblTrucks if you can't tell.

 

[June7]

Probably better, easier, faster to just provide db for analysis.

 

[KRISTIRG]

1. Each company has multiple drivers and trucks
2. They come together for multiple load dates
3. Each load date has multiple loads
4. Each load has multiple stops
5. Each customer may have multiple stops
6. Each customer and stop come together for an invoice

Is this correct?

Almost, trucks have nothing to do with trucking company, only drivers do.
A trucking company has many drivers.
A date can have many loads.
A load can only have 1 truck, date, driver, trucking company, perPoundRate, Miles and Cost.
A load has multiple stops.
A stop can have multiple customers.
A customer can have multiple stops.
A stop can have multiple fees.
Fees can have multiple customers.
A customer can have only 1 value for Pounds.

Which is why I have LoadID as FK in the Stops and Fees Tables, so I can calculate charges on total pounds and I have reports on Loads, Drivers and Companies.
CustomerID is in Fees and Stops tables because from the invoicing side, that's done by customer and all of those charges are totaled by LoadID, then stop, then itemized fees.

It's odd but I think of it like this.....Each Load is a trip that a specific truck takes, which only one person can drive that only works for one company. A trip lasts for so many miles.
When we take trips we stop, sometimes a lot. So the truck picks up stuff from customerA in MA, CustomerB in OH, then stop 3 in KS has a problem so he has to stay over and that costs $100. So since CustA&B have X pounds each on the truck, they each get to pay for that stay over. Then CustA gets their stuff dropped off in CO so then stop 5 in CA has only CustB stuff (pounds) on the truck.

IDK how the heck these companies keep track of all this. Lol. I feel like their customers probably get overcharged a LOT.

 

[GaP42]

There can be multiple customers per stop,

This is not supported by your current model. Stop-Customer is M:M if this is the case. Resolve by introducing a CustomerStop intersection table using 1:M for Customer-CustomerStop and Stop-CustomerStop.
You can then have the ability to have multiple Customers for each Stop

 

[LarryE]

1. Each company has multiple drivers and trucks
2. They come together for multiple load dates
3. Each load date has multiple loads
4. Each load has multiple stops
5. Each customer may have multiple stops
6. Each customer and stop come together for an invoice

Is this correct?

 

[KRISTIRG]

This is the copy I've been playing with so pay no mind to some of my "experiments", but you'll get where I'm trying to go.
The data entry of the loads and the invoicing are the sole purpose of the Db. One is looked at from the load side, the other by the customer side. they aren't a very large company at all so I'm really not too concerned about storing extra things I shouldn't be, I'm just trying to get it all to tie together from both angles. (Without using concatenated ID fields if I can help it)

 

[KRISTIRG]

1. Each company has multiple drivers and trucks
2. They come together for multiple load dates
3. Each load date has multiple loads
4. Each load has multiple stops
5. Each customer may have multiple stops
6. Each customer and stop come together for an invoice

Is this correct?

Except for the trucks. They are owned by this company not the trucking company. They're a separate entity all together. A load can only be hauled by one truck. The tblTrucks gets updated with the new miles added on and that's reported on when they need serviced or something is expiring on them. They're basically they're own little bubble that only is concerned with how many loads their ID shows up on and how many miles they drove.

 

[KRISTIRG]

This is not supported by your current model. Stop-Customer is M:M if this is the case. Resolve by introducing a CustomerStop intersection table using 1:M for Customer-CustomerStop and Stop-CustomerStop.
You can then have the ability to have multiple Customers for each Stop

Exactly! I just figured that criteria out today and that is when I got stuck and decided I needed some ideas. This project has been down 4 dead end paths so far so I'm not looking at it very clearly. Should step back for a day and regroup but I just need to be done! Lol.

I was trying to decide if I could just handle this on the form side without the need to add a table. The stops table has it's own PK that really doesn't matter in this situation. The stops tbl can have the same StopNumber and CustomerID, that really doesn't matter. I could put LoadID=2, StopNumber=4 6 times with 6 different customer numbers. Same with the Fees table. Then when I report on the Load info, it's just going to sum all those with the same LoadID no matter how many customers/stops. When I report/invoice from the customerID side, same thing, it's just going to look for that value in each. I realize it's not the most normalized or proper structure, but neither is this scenario.
Or is my brain just fried?

 

[GaP42]

was trying to decide if I could just handle this on the form side without the need to add a table. The stops table has it's own PK that really doesn't matter in this situation. The stops tbl can have the same StopNumber and CustomerID, that really doesn't matter. I could put LoadID=2, StopNumber=4 6 times with 6 different customer numbers. Same with the Fees table. Then when I report on the Load info, it's just going to sum all those with the same LoadID no matter how many customers/stops. When I report/invoice from the customerID side, same thing, it's just going to look for that value in each. I realize it's not the most normalized or proper structure, but neither is this scenario.
Or is my brain just fried?

You need the table if you are going to be able to do what you want it to ... your path where there are multiple stop records for the same stop is fraught with issues requiring special processing. Definitely fried Normalisation is a fundamental part of the process that can only be ignored with full awareness of consequences and potential down-the-track re-work.
StopFeeDetails relates to Stops through LoadiD - how will you determine which Fee applies to which Stop? Something amiss with that relationship - Stop Number is there but which Stop record if the there are multiple records with the same Stop/Load?
if StopCustomer is introdcues then you can definitively assign a StopFeeDetails to that table - not the Stop table.

 

[KRISTIRG]

The way the queries are set up now is to match the LoadID, StopNumber and CustomerID from the tblStopFeesDetail and tblStops. Then I either group them by LoadID only if it's to report from that side, or by CustomerID if it's from the invoicing side. The Load reports don't look at what customer it was or specific fees, just the Sums. And then the invoice side goes from customer to all the loads they were part of, to stops and those charges then the individual fees as sub line items.
Then I can also report on individual types of fees, cumulative stop details, etc.

It's definitely a lot to rework but I've already done it so many times....why not? Lol.

I still can't see how I'm going to set up the Data Entry for them though. I guess 3 subforms. One with just stop#, then one for just Customer and pounds then one with just the fees. Something along those lines anyway.

 

[KRISTIRG]

I have an additional table setup now to just combine customer and stop number. So if anyone has any ideas on my original question of how to set up the data entry I would love to hear them! Nothing I've tried has worked. Using tb;CustomerStop as the go between as a subform, I set it to default as stopnumber 1 if the LoadID doesn't appear in the stop table yet, put a button on it to go to the next stop number, but the subforms aren't recognized for some reason. And it all looks like a mess so there's that.

 

[MarkK]

if anyone has any ideas on my original question of how to set up the data entry I would love to hear them!

If your tables are not sufficient to model the problem accurately, then it is too soon to design data entry.

 

[KRISTIRG]

If your tables are not sufficient to model the problem accurately, then it is too soon to design data entry.

Huh. Well besides adding the tblCustomerStop with it's 2 fields of CustomerID and StopID I'm not sure at all what else I need to do. Honestly, I'm not even sure how to utilize that table since everything is set up the way I was doing it. But I'm trying to populate the new table. Right now I'm just doing it by hand a table at a time to get a few rows in there at least. But I would like to be able to populate the way it will be done so I can accurately test it. And hopefully move on to the next requirement.
I went back through this thread and didn't see any other suggestions. That's disappointing, expected, but disappointing.

 

[LarryE]

Except for the trucks. They are owned by this company not the trucking company. They're a separate entity all together. A load can only be hauled by one truck. The tblTrucks gets updated with the new miles added on and that's reported on when they need serviced or something is expiring on them. They're basically they're own little bubble that only is concerned with how many loads their ID shows up on and how many miles they drove.

OK I guess all of us were walking into the theatre in the middle of the movie. These ACCESS application designs rely on the business operation workflow process. I don't think we know what that is. So please describe what business you are in and exactly what kinds of data you wish to keep track of. At this point, we are a long way from creating forms, queries or anything else because nothing will work properly unless the design and relationships are properly developed first. And then you need to add enforcing referential integrity. You have not done that.

If you own the trucks, then who are these trucking companies and how do they relate to your business? I am sorry for your disappointment. Sometimes it is difficult for outsiders like us to exactly understand your business processes in this forum. For example, from what you described earlier, I came up with this initial design:

I didn't add all the required fields in each table, and it is probably not correct at this point, but maybe you can take a look and understand how these applications are designed. I have attached the file itself so you can create your own design. There is no test data in any of the tables, so just re-create the relationships as you require.