Auto Populate fields based on Combo Box selection

[abernut]

Nothing really confidential in it so I will attach it with this post.

You will notice on the Mold table there are two other fields, Cycles_Since_Last_Main and Main_Req_At.

Each time they use a mold it gets used for a certain number of Cycles. They eventually would like to see what molds are getting close to needing maintenance.

Thanks,
Mike

[ssanfu]

I would suggest these two changes:

MoldLog

1. Log_ID (PK- autonumber)
2. Mold_No (FK to table Molds - Long)
3. request_date
4. Log_Date
5. Time_In
6. Time_Out
7. Hours(calculated if you want).. not really needed (and shouldn't be stored in the table)
8. do you need a requested by
9. work done by?
10. comments

[ssanfu]

They have an excel sheet of all of the molds they use

I have already entered about 50% of the data into the Mold table

Since the Molds are in Excel, you can import the data and not have to enter it.... I hope you are not hand entering Mold data

So what I thought would be an easy task is snowballing into a giant poop sandwich.

Welcome to database design and programming!!

It goes easier it you use pen and paper to do the design before starting Access. Then when you have the design, start creating tables and entering a few records.
I cut the paper up to be able to move the fields to different tables...
I've also used white boards....

[abernut]

Starting this on paper is what made me think it would be easy

I tried attaching my pdf version but it is too big.

But it looks just like this.


MoldReq
Work_Ord (Pri Key)
Part_Name (From Mold table)
Part_No (From Mold table)
Mold_No (From Mold table)
Machine_No (Drop Down list to display Press 1 through Press 8)
Date
Material Drop Down list to display PVC or Non-PVC)
Notes
Mold_Installed_By (Either Drop Down list to select the Employee or pulled from a Employee table)
Mold_Pulled_By (Either Drop Down list to select the Employee or pulled from a Employee table)
Cycles
Check_By (Either Drop Down list to select the Employee or pulled from a Employee table)

MoldLog
Log_ID (Pri Key)
Work_Ord (From MoldReq Table)
Mold_No (From MoldReq Table)
Part_Name (From MoldReq Table)
Part_No (From MoldReq Table)
Log_Date
Date_In
Time_In
Time_Out
Hours (Calculated field based on Time_Out - Time_In)
Lube_Used (Drop down list: Donnelly Grease or STP/Donnelly)
Notes
Tips_Tricks
Comp_By (Either Drop Down list to select the Employee or pulled from a Employee table)
Date_Finished
Work_Type (Drop Down list: Repair, New Work, Revision, Maintenance)
Attachment (They may need to attach a jpeg)

Molds
Mold_No (Pri Key)
Part_Name
Part_No
Cylce_Since_Main
Main_req_At


After building the table I thought "Wow this is easy" but then when I tried created the relationships and form entry, I realized why I stuck with Networking

[lzhao7573]

Hi I did some changes on the End of Run form. When you pick a mold number from the drop down list, you can find the related part_name, you can then easily enter it. In order to get the relation between mold number and part number, you need set part number as a bounded filed. You need refresh part_name filed after update mold number. see attached files, hope it helps.

[ssanfu]

MoldReq
Work_Ord (Pri Key)
Part_Name (From Mold table)
Part_No (From Mold table)
Mold_No (From Mold table)

The two fields in red are not needed because the Mold_No (PF) is linked to the table MoldReq via the Mold_No (FK).

MoldLog
Log_ID (Pri Key)
Work_Ord (From MoldReq Table)
Mold_No (From Molds Table)
Part_Name (From MoldReq Table)

Don't store the Hours in the table - you can calculate it at any time.
Part_No (From MoldReq Table)

Fields in red ar not needed.
Mold_No (in Blue) is from the Molds table, not MoldReq table

[abernut]

I removed the columns.

Next I tried setting my relationships.

Is this correct? (See attachment)

[ssanfu]

One Mold can have many MoldReqs
One MoldReq (WO#) can have many MoldLogs

Are these statements TRUE?

[abernut]

I will meet with them tomorrow and list all the one-to-one and one-to-manys

Thanks,
Mike

[abernut]

One Mold can have many MoldReqs
One MoldReq (WO#) can have many MoldLogs

Are these statements TRUE?

One Mold can have many MoldReqs TRUE
One MoldReq (WO#) can have many MoldLogs FALSE (This is a one to one. Each time a Mold is ran they do maintenance on that mold (MoldLog)

[abernut]

I did make some progress on my forms today.
Everything is pulling the way it is supposed to but I may need to start another topic for my next questions (Not sure on the forum rules)

The Molds.Cycles should be a calculated field. Each time someone does a MoldReq there is a place to enter "Cycles Ran". Right now I just have it as a column in the Molds table. They use this to determine when a Mold is due for maintenance. In my Molds table I have a Main_Req_At field. ie... Mold 1077 requires maintenance after 10,000 cycles.