How to insert Mastertable value into child table?

[digitalbalu]

I am having Two tables



Master: RyotNo (Primary Key), CategoryNo,RyotName, Address, City, BankAccNo

Child: RyotNo(Foreign Key), TDate(PrimaryKey), LorryNo(PrimaryKey), Weight(PrimaryKey), CategoryNo(PrimaryKey)

If I insert a RyotNo in Child table, its categoryID must also be stored in Child table CategoryNo.
Plz help me. Thanks in advance.

[June7]

Why do you need the CategoryNo in child table? How can it be a primary key in the child? It can be retrieved by linking the tables on the RyotNo pk/fk fields.

[digitalbalu]

Dear June7, I am doing a sugarcane Agency project assigned by X.
At very first X told that he want to enter Load details from multiple customers, he strictly told Date, LorryNo, Weight should not come again for any customers.
I designed,
Ryot(Customers) Table as RyotNo, RyotName, Address, City, AccNo
LoadDetails Table as RyotNo(FKey), TDate(PKey), LorryNo(PKey), Weight(PKey), Rate, Amount(Weight*Rate), AdvancePaid, Nett(Amount-AdvancePaid)
and its working fine for me. I delivered the DB too.

But today X told that he manages 3 types of customers in single load,
1. Drivers (The lorry drivers of that load)
2. Farmers ( They are the owners of the Sugarcane)
3. Labours (They are harvesting the sugarcane in that field)
Now the X's condition becomes Date, LorryNo, Weight for the Particular group of customers should not come again.
ie.. If a driver gives 6/4/2012, TN32AR5868, 18.580 then it should not re entered by any drivers, but can be entered by Farmers & Labours

This is the reason Why I have Date, LorryNo, Weight, CategoryNo as primary in LoadDetails(Child). I want use the single table to store all these. This is the exact reason for that. If any corrections in the DB, please suggest me. Thanks in advance...

[June7]

Oh, I see now, compound unique ID.

Is data entry in forms or directly on table (I hope forms are used)?

Set value of the child CategoryNo based on value of master CategoryNo. The trick is figuring out what event to trigger this. Then need to handle situation if the entries are duplicates of existing record. Perhaps a 'Save Record' button for the child form.

[digitalbalu]

Yes. I am using forms for data entry. I give u the old db here. Please analyze it for current modification. X always require report in the specified format. I use LorryNo's default value is current time format in hhmmss bcoz, the customers sometimes get only payment from X. Due to the db size is >= 500 KB. I upload it in 4Shared. thanks in advance..

SugarCane.accdb

[June7]

That site wants me to download software before I can download the file. Don't want to download software. Box.com does not do that.

Try attaching to post. Follow instructions at bottom of my post. Up to 2mb zip allowed.

[digitalbalu]

Thanks for the info. The db is now in http://www.box.com/s/bd9e51871ffb7435057d

[digitalbalu]

The db is ....
SugarCane.zip

[June7]

Need field in Transactions for the Category. Do you want it to be a number and save 1, 2, 3 or text and save Farmer, Labor, Driver? Include it as part of the compound PK. Now if user tries to enter record that duplicates this compound key, Access will popup error message and record will not save.

The two tables have a relationship defined in the Relationships builder, hence the + on the parent table.

Field name VechileNo is misspelled.