Database Design.

[cap.zadi]

Hey there,



I am working on project to create a database with the following information.

tblSortingInfo
TrnNo (AutoNumber) PK
TrnDate (Sorting Date)
ShiftID (ComboBox)
InchargeID (ComboBox)
MachineID (ComboBox)
OperatorID (ComboBox)
ProductID (ComboBox)
Note

* This is the General part of data sheet which remains same till shift or machine is not changed.

tblSortingResultsInfo
ResultNo (Autonumber) PK
TrnNo FK
PrdnDate
kCarID (ComboBox)
Timestarts
PassedQty
RejectQty (If not null, so there will be some reasons)
ScrapQty
Note

* this is part in which results entered.


tblRreasonsInfo (Reasons for each reject value)
ResultNo FK
RejectQty
RreasonID (ComboBox)
Note

* This is the part which required when there is reject qty and same will be prepared for ScrapQty.

so here now my question is:

1. The 1st two parts are working fantastic with relation.
2. How the 3rd part will be linked to the 2nd part and if there is RejectQty so this part should appear in a new window to enter and then exit and same will be managed for ScrapQty.

Please advise.

Thanks

Zee

[rpeare]

Personally I would not have a reject reason table. at a minimum you would have to have 3 fields for it (unique key/autonumber field, foreign key to your tblSortingResultsInfo and the actual reason code). If you just have a reject reason on your tblSortingResultsInfo then you are only storing one additional field.

Unless of course you are trying to provide for the possibility of multiple rejection reason codes. If that's the case then your tblReasonInfo table should have these fields

ReasonID (autonumber)
ResultNo (FK)
RejectPortion (number) (don't use reject QTY twice for two different values)
ReasonID
Note

If you're using a bound form/subform for your data entry you will likely have to create a sub-subform (a subform off of your subform) to handle the data entry and only make the sub-subform visible if the RejectQTY is >0 (you can do this in the AFTER UPDATE event or ON EXIT event). The other option is to set up you data entry subform like a datasheet where you could just hit the + sign on the far right to open up data entry for scrap detail.

[cap.zadi]

Thanks man, Yes it is true, i should not use RejectQty field twice. Yes this is the case always if there is 20 Units rejects so i need to enter the reasons for the 20 which can be like wise:
1. Clay Crack = 10 Units
2. Glaze Jump = 5 Units
3. So. Crack = Units

Let me try in this way and will be back with results.

[cap.zadi]

Hello Man,

Thanks for ur support and it is working fantastic. just have little problem when i create the scrap subform and link to the resultid (tblsortingresultinfo).

and please advise some more techniques to furnish it.

in same way i have around 7 more prodnd process which will estabilsh later in same database.

*Note. I am going to attach my this database for ur review and advise.

thanks

zahid

[rpeare]

Just make your frmScrapInfo a subform of your subform (and establish the link). In this example I made the sub-subform a continuous sheet.