1 A = 2 B = 2 lines of data in the record

  • Thread starter Thread starter Sharlie
  • Start date Start date
S

Sharlie

I am trying to create a system to track consignments. I am stuck on creating
a form that will generate 2 lines of data entry fields when a unique value is
entered.

There are 2 A to every B (for example a truck rego with 2 trailers), so when
I enter B in the form I need to have A1 and A2 with the associated field
headings to be created to enter the data. The idea is the trailers can't be
separated and we need to report on empty trailer movements.

This seems like it should be simple, however I have pretty much no
experience with creating databases and am struggling.
 
Sharlie,

This is a classic data relationship known as one-to-many (or 1:m). In your
case it sounds as though it's one-to-two.

Your database will need two tables: one for tugs (B in your example) and one
for trailers (A). The relationship jargon goes: 'Each tug can have zero to
many trailers.' You should define this relationship once you've completed the
table design and before you move onto form design.

Now you may have heard of main forms and subforms - a mainform (tug) and a
subform (trailers) is the construct you need to manage a tug-trailer set. You
will also need to have some form allowing for the assignment of trailers to
tugs. I know you say that trailers can't be separated; in my experience a
user saying it never happens is tantamount to guaranteeing it will happen
within 24 hours. :-)

I realise this post has given you nothing practical so please reply saying
where you want to begin and I'll try to give some practical help. What
version of Access?

Rod.
 
It is a one to many relationship

One truck has many trailers as far as I can tell

You can create a query to join these two tables.

Then use the wizard to create a form with subforms based on this
query.


For FREE Access ebook and videos click here
http://access-databases.com/ebook
 
I am trying to create a system to track consignments. I am stuck on creating
a form that will generate 2 lines of data entry fields when a unique value is
entered.

There are 2 A to every B (for example a truck rego with 2 trailers), so when
I enter B in the form I need to have A1 and A2 with the associated field
headings to be created to enter the data. The idea is the trailers can't be
separated and we need to report on empty trailer movements.

This seems like it should be simple, however I have pretty much no
experience with creating databases and am struggling.
Click to expand...

If the trailers can't be separated, might it not be suitable to simply
consider a pair of trailers as one object (with two compartments, say, if you
need to track the contents)?

In any case you do NOT need to create empty records prior to filling them in.
If it really is a one to many relationship as suggested, then you can put use
a Form for trucks with a Subform for trailers, and put a bit of code on the
subform's BeforeInsert event to allow only two trailers to be added. The new
records will be created as you start to enter data into them.
 
I will try to make what I am doing a little clearer -

I have a fleet of 'tugs' (to use your terminology) which have two
'platforms' (A and B), each platform can carry a vehicle however the
platforms cannot be separated (1 tug will always have 2 platforms). Each tug
has a unique id and there can be many tugs to a movement. I need to be able
to identify empty platforms moving as well as loaded. Not sure if it is
relevant but I have no way of knowing what the vehicle will be so cannot make
a table for that.

What I really need is for the database to know that 1 tug must have 2
platforms, so every time a wagon is entered into the form there needs to be 2
platforms worth of data (vehicle ID, weight, product, etc) whether empty or
loaded.

I have a table of unique tugs, but I can't work out how to link that as I
have said above.

The version is 2003.
 
Sharlie,

Thanks, it's clearer now. Follow John Vinson's good (as ever) advice. Simply
duplicate each column for the platforms with an identifying prefix or suffix
- 'upper'/'lower'; '1'/'2'; or whatever. Each row of the trailer/platform
table thus has two sets of data.

(It is possible to introduce a third 'platform' table and then each
'trailer' record has two foreign keys, each pointing to a platform record -
but I think this is complicating things unnecessarily.)

The tug (yes my terminology but that's what I thought the generic term is as
used by the transport industry for the bit at the front with the driver :-))
is I suspect detachable - and interchangeable. I also assume a two-platform
unit has some sort of independent identity. I further detect that your
primary interest is the platforms and their loads and not the tugs - 'What
tug is pulling my platforms' rather than 'What platforms are being pulled by
my tug.'

It's a subtle difference that means I would start my user interface design
with the platform form showing say two columns of fields, one for each
platform (or one above the other if that's how they are in real life). The
tug I would select from a list box or combo box. The tug reference is stored
on the platform record as a foreign key. This foreign key is the bound value
for your list or combo box.

You also need to capture some journey information.

How much historical data do you need? Do you need to know what journeys have
been made in the past? Do you need to know what loads have been carried?
Answers to these questions could alter the design to accomodate a history.

Rod
 
Rod,

Now I am going to make more sense, I am actually working with a railway,
this database is to track certain types of wagon loads on a rail service. If
you replace tug with wagon, and each wagon has 2 platforms which can carry a
tank (as in a fuel/chemical tank you would normally see on the road attached
to a prime mover or 'tug' :-)) and I can have up to 3 wagons on a consignment
note, with several wagons on a service. The wagons have unique id's with a
platform A and B.

So what I have is a consignment note with the following -
Con Note number
customer
date
service
origin
destination
customer reference (shipment number supplied by the customer)

Then -
Wagon 1
Platform A
Vehicle ID
Product
Litres
Tonnes
Platform B
Vehicle ID
Product
Litres
Tonnes

I now have an additional complication in that the customer will put platform
A on one consignment number and platform B on another because it has a
different shipment number. So I am thinking a consignment note form with a
subform for the wagon load details and a checkbox to identify empty wagons
(unless there is a way to make each service require an even number of
platforms?). Then create a report/query which will track empty platforms for
a certain time period.

The vehicles being transported are tanks and the movements are invoiced
based on the product they are carrying and the origin of the movement .
Which is my next problem, how do I link the rate to be charged to the origin
and the product type?

Our operations group will need to be able to see historical data to track
'wagon wastage' with a potential to invoice the customer if it gets out of
hand. I presume this is what you mean by historical data?

Very long winded and I do apologise, I think my instincts are pushing me in
the wrong direction on this and I have spent far too much time on the
reporting end of databases with no requirement to understand why my reports
work!

Sharlie
 
Back
Top