Storing data from linked tabels in to mater

[Gofaster]

Hi

Now I will try and explain this as best I can. In a data base that kind of grew bigger than it was ever ment to I have a set of master tabels in a _be file. Thats fine it all works ok the problem I now have is the operators enter data using forms and basically using a combo box store the primary key field for that set of data into the main tabel. then in querry etc I bring it all back together...
I now have a problem were the data in the linked tables may be subject to change (not the primay key though) so I now need to store all the fields from the sub tabels into the main data tabel and still do this using the combi box in the data entry form using only the primay key as the look up, must be posible but How????

 

[willir]

Gofaster

I don't think your problem is storing data in the main table. ("I now need to store all the fields from the sub tabels into the main data tabel and still do this using the combi box&quot

I suspect what you need to do is use a subform. The main form displays info from your main table, and the subforms, which are embedded in your main form, depict the information from your supporting tables. Typically, data in the subform is linked to the main form using the primary key. Frequently, in a one-to-many relationship, the primary key is stored on the supporting tables as a foreign key.

A typical example would be the invoice number on an invoice "header" table -- the invoice number would be called the primary key. This invoice number would then be stored on the invoice detail table -- all items on the invoice detail would include the invoice nuber -- the invoice number on this table is called the foreign key.

 

[Gofaster]

Willir

Thanks for the reply ok heres more detail I am basically calculating times not that that matters, at the moment I store a part number as a primary key, in this related table is description but critically a time value. its these values that now need to change frequently so I need to store time and description into the main table but I only want the clerk to get that value from the combi box with the part number combi. I have to be a bit carefull there are a lot of other tables and about 25,000 records that I don,t want the mods to affect if I can.

 

[willir]

GoFaster

Basically, more specific details would be helpful, but here is a stab at it.

If I understand you correctly, you have a main table that uses the part number as the primary key.

In a second table, the same part number is used as the foreign key -- this is how the tables are linked. If this statment is not true, then there will be problems. You have to be able to link the tables together.

The second table tracks times. And you want to perform a time calcuation that is to be stored on the main table.

I am still a tad confused, but I see two types of scenarios.

Let's say you are running an auto maintenance shop or garage. The part number in this case is an work order number. The customer brings in a car, the mechanics perform various tasks on the car, and you want to sum up the time spent hours spent working on the car for billing purposes.

This scenario is fairly easy. Since the work detail is linked to the work order, the operator can only see the detail records specific to the main work order. A button on the main form can calculate the sum of time spent on the detail orders and store or display it.

Okay scenario two...
Same garage, but now a second table is used to provide rates for various tasks. You have to select a job code and the code provides the cost of the task.

If your needs are simply, then the user can select the task, and the rate or job cost is used to populate the current record. Here I would use the combo box to find the job code. A second combo box would use the same query, but display the rate instead of the job code. The rate would then be used to populate the rate field in the current table. An event for "after change" would be used to trigger the process. The job code combo box would not be bound. The second combo box used for getting the rate could be bound or unbound to the current form depending on whether you want to perform calcualtions or not, or offer the operator some control on the rate to be used.

Hope the scenarios were close to what you needed.

Richard

 

[Gofaster]

willir

Ok I think I see what you are getting at in stage 2 but I am not sure its quite what I need.

Heres more detail I have a main data entry form, various tables linked to look up data from combo boxes. The important bit, I look up a part number through a combo box from my part details table i.e find part abc, this part may have many operation and all the related data to that operation/part is stored in this table, if its operation2 then its abc-2 etc. I now store that part number into my main data table. When I report I bring all the data together using the part number as primary key. Then use the time stored in the part details table for an operation to do my calculations.
I now need to change the time value that is stored in the parts detail table, and may need to change it frequently.
But I also need to have history.
So when the clerk does the data input using the form I still need to use part number as the look up in the combo box, but now I need to store the part number field and the time field from the part details table into my main data table, and I want to do this automatically i.e by only looking up the part number/operation from a combo, once only, further the clerk is not permitted to change time values for a particular operation.
This will then give me a permenant record of the set time for that part at a given date.

I suppose what I realy need is a bit of code in the background to do this i.e when the part is selected read that value and force an automatic update to the the main data table to write in the second field I want stored from my part details table into my main table.

Regards

ROn

 

[willir]

Ron

I think I have a better understanding.

You have a master table. You also have a parts table. The part table not only describes the part, but also descibes any tasks involved with the part, and assoicated number of hours for the task.

You want the culumative hours to be stored on the master table.

Before proceeding, you may wish to review a process called normalization when designing a database... I post a couple of links after I have finished here.

What you seem to be suggesting that the master table is linked to the parts table in two ways. 1) by the part number; 2) the number of hours for the tasks. You really only need the one link. By using this link, you can grab the other bit of information.

I am assuming that your combo box for the part number is working.

Create a second combo box, or copy the part number combo box. Open up the properties. For the row source, select your parts table. Then open up the query builder by clicking on the "..." button.

Select the part number for the first column.

In the second, build an expression to sum up the hours. For the "field" entry, it could look like [task1_min]+[task2_min]
Select your parts table for the table source.

Toggle to the Datasheet view by right clicking on the title bar for the query builder to make sure you are getting what you want.

Exit the query builder. Save when prompted. This query is now bound to the second combo box.

Back to the proerties for the second combo box....
The bound column should be "1" - the part number. On this same tab, set Enabled to No, and lock to Yes. This way, users will not be able to edit the value.

Go to the format tab on the properties box. Column count should be 2. Column width should be 0";0.7". The 0" zero column width will hide the first column, and the user will only see the time / hours.

That should do it.

If your tables are different, then more information will be required.

Also, I suspect you may be having some problems due to some design issues.