A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old January 21st, 2009, 07:26 AM posted to microsoft.public.access.gettingstarted
Sharlie
external usenet poster
 
Posts: 3
Default 1 A = 2 B = 2 lines of data in the record

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.


  #2  
Old January 21st, 2009, 09:08 AM posted to microsoft.public.access.gettingstarted
Rod Plastow
external usenet poster
 
Posts: 195
Default 1 A = 2 B = 2 lines of data in the record

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.

"Sharlie" wrote:

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.


  #3  
Old January 21st, 2009, 10:37 AM posted to microsoft.public.access.gettingstarted
troy23
external usenet poster
 
Posts: 55
Default 1 A = 2 B = 2 lines of data in the record

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





On 21 Jan, 06:26, Sharlie wrote:
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. *


  #4  
Old January 21st, 2009, 06:31 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 1 A = 2 B = 2 lines of data in the record

On Tue, 20 Jan 2009 22:26:01 -0800, Sharlie
wrote:

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.


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.
--

John W. Vinson [MVP]
  #5  
Old January 22nd, 2009, 02:05 AM posted to microsoft.public.access.gettingstarted
Sharlie
external usenet poster
 
Posts: 3
Default 1 A = 2 B = 2 lines of data in the record

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.



"Rod Plastow" wrote:

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.

"Sharlie" wrote:

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.


  #6  
Old January 22nd, 2009, 08:51 AM posted to microsoft.public.access.gettingstarted
Rod Plastow
external usenet poster
 
Posts: 195
Default 1 A = 2 B = 2 lines of data in the record

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
  #7  
Old January 23rd, 2009, 12:44 AM posted to microsoft.public.access.gettingstarted
Sharlie
external usenet poster
 
Posts: 3
Default 1 A = 2 B = 2 lines of data in the record

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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:14 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.