Mimicking real life "spreadsheet" form how to split into separate lines (1 Viewer)

My knowledge of access is a bit above beginner however I have only learned what I needed to do for the most part so my knowledge is spotty. Our company is currently running access 2003.

I am currently revamping a production report for a company. Essentially they are moving liquids around and turning them into various definable products, which are all going into separate tanks.

Unfortunately the main idea that was stressed is that the input forms should mimic the current hand printed forms.

With this I run into some issues. Instead of setting up a table with a TO and a FROM column like the current sheet is I have a table that uses negatives to register the FROM a positive to register the TO.

However in order to mimic the sheet that is currently used (a printed off excel) I will have to split the data up. I am unsure of how to tell access to create a new line when they are filling out the form.

The only thing conceptually I can think of is to have 2 sub forms in a window both in datasheet view. However the issue is I need a subset of data to link the two lines of data to show they were one movement.

Is there an easier way than the sub form?
How would I get a "behind the scenes" solution to link the two transactions?

Thanks for your help, if you need any further data let me know.

A few thoughts...
- I wouldn't let the design of a printed sheet currently in use inform you too strongly about how to solve a problem at a conceptual level.
- It seems compelling to me that "TO = -FROM". I'd expect you to have a table of locations, maybe tanks, and whether they are origins or destinations--to or from--will be a function of what direction the goods are travelling, in or out.
- I'd expect you'd need a system like in accounting where a transaction must balance. For any given transaction, the amount subtracted from one or more locations must equal the amount delivered to one or more other locations.
- So thinking out loud here, I'd expect to see a transaction table, a location table and an intermediate linking table, maybe transactionDetail. Each record in the detail table connects a transaction to a location by volume, positive or negative, of the transferred liquid. And all detail record volumes that belong to a single transaction should sum to zero--balanced
- Does that make any sense?

There definitely is a tank location table however we cannot uniquely identify them as Source or Destination as the location is usually operating at 90% capacity therefore volume is stored where ever there is room.

Your third point is our main issue of contention, because the tanks are stored in a yard we can have up to 300 to 400 meters of piping in between the two tanks. The operator monitoring the flow only has one thing to measure by which is tank gauges. The operator enters the starting and ending gauge of both tanks for the transfer therefore due to volume within the line and rounding issues with gauges the transactions will not necessarily balance. Through our financial system we generally only use the volume that is being put into the "TO" or destination tank however from an operations stand point both volumes need to be monitored.

I have not thought of an intermediate linking table which seems like it would solve the issue however, I still cant wrap my head around I would identify the unique transfers happening within the day.

Thanks for your help