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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|