Critical Many to Many Relationship?

[cap.zadi]

Hey there,

I would like to call experts opinion for this type of data structure to manage in best way and for easy data entries?

Over View:

This is a process stage where a Product is received on carrier and process involving people and machines and then loaded on other carrier to move next stage.

Data field will be like that:

TblMain

TrnDate
TrnNo PK
Shift FK to tblshift
Machine FK to tblemachines
Operator FK to tblPeople
Product FK to tblProducts

* Those above data fields just changed once a shift or operation.

TblSmain

ResultNo PK
TrnNo FK to tblMain
Time
UnloadedC A Dryer Car used as carrier to bring products with unique no.
GoodQty Products are good from the car. can be many
RejectQty products reject from the car while inspection. can be many
LoadedonC A other carrier where goodQty is loaded only.


* A "loadedonC" carrier is filled by many "UnloadedC" carriers. The carriers identification is just numeric values like 20, 04 on both ends.

* if there is a rejectQty so need to indentify the reasons for rejects.
like if rejectQty is 10 units so may out of it 5= Reason1 and 5=Reason2


* There are more than 30 resultNo's under one transaction in one entry so need to look for best design for data entry point of view as well.

* while data entry if there is rejectqty filled in so can a new form popup where can put the details with reasons of rejects?


will be waiting for the best design advise.




thanks

zee

[June7]

You say GoodQty and RejectQty 'can be many'? These are numeric fields with a quantity value for how many products were good or bad. One or the other or both fields could have a value. Not strictly normalized but maybe good enough. This records quantity, do you need to know exactly which products were 'good' or 'reject'?

Yes, can popup a form or use subform.

[cap.zadi]

well,Sorry here. many means can be more than 1 like 10, 20, 30 and same for rejects but need to record rejects reasons.

As i describe in main table. all those products and machines are bound to one transaction per shift, per machine and per product. so we carry more than 30 results under this on transaction.


i have one old designed form where the main table is used as main form and the results data is recorded in sub form as much as result we enter but issue is:

i want to upgrade the structure and the reasons are important to link with each reject qty.

plz advise?

thanks

[June7]

If you don't need to know which products were 'good' or 'reject' can have a Comment field in the result table. Otherwise, have another table for comments with the result table ID as a foreign key.

[cap.zadi]

well, yes sir need another table but i am still confuse here. can you sketch some structure for me if dont mind plz? how it will work and how can be easiest data entry form?

[June7]

tblComments
CommentID (PK)
ResultID (FK)
ProductID (if this is needed)
Notes

There can be many Notes records for each ResultID, one for each product included with a Result if you wish.

Easiest data entry might be a form/subform arrangement.

Don't know how to explain more simply. It is just another related table. Just as TblMain and TblSMain are related.

[cap.zadi]

well, ok sir will arrange the tables and revert you soon if there is any problem.

[cap.zadi]

Sir, please find here the attached DB Sample.

if u try to enter data from "FrmPrs" and when come to the subform and if u put some qty in RejectQty and will find a floating form for reasons and enter the detail of rejects.

the only issues is the link key "BatchNo" frm tblPRS1 to tblPRS2 is not filled up in tblPRS2.

please advise where is mistake?


thanks

zee

[June7]

You would have to use code to grab the BatchNo value from frmPRS1 and set value of BatchNo in frmPRS2. One way is in the Load or Current event of frmPRS2. I only know how to do with VBA code. I tried once with a macro and couldn't make it work.
If IsNull(Me!BatchNo) Then Me!BatchNo = Forms!frmPSR1!BatchNo

An alternative is to set frmPRS1 as Single Record view and make frmPRS2 a subform of frmPRS1.

Another approach is to leave frmPRS1 as Datasheet and put frmPRS2 as a subform on frmPRS. The 'sideways' linking of frmPRS2 to frmPRS1 is tricky but I have seen it done. See http://www.access-programmers.co.uk/...ad.php?t=45857

[cap.zadi]

Sir, good day. it is great to go through this article and understanding the relationship of two sub-forms.

now i must have to choose one condition:

- can the 2nd sub-form can work with its floating appearance when there is RejectQty>0
-if it cant so must go with the same procedure as in this article but then need to setup some conditions where this sub-form just appear or display on main form when there is RejectQty>0.

please advise?

thanks

zee

[June7]

No, subform could not have 'floating' appearance. You can control visibility of the subform container control. So in the main form OnCurrent event, set visibility property of the container control based on the RejectQty value.

[cap.zadi]

thanks sir.

[cap.zadi]

Dear Sir,

please find here the DB.

thanks

[cap.zadi]

sorry this is dab for searchform. wrong msg. thanks

[June7]

You do not need assistance?