Calculated Field

[ssanfu]

MoldReq
1. Work_Ord(Pri Key)
2. Mold_No
3. Material
4. Date

"Date" is a reserved word. Better would be "WO_Date" or "Request_Date"

11. Attachment

What are the attachments?

5. Time_In


6. Time_Out

Can the Time in / Time out span multiple days?

[abernut]

"Date" is a reserved word. Better would be "WO_Date" or "Request_Date"

I have changed it to Req_Date

What are the attachments?

During our meeting they said that they sometimes need to attach a drawing. I included this field and told them they could scan the drawing, save it as a .jpg, and add it as an attachment. I hope I told them correctly.

Can the Time in / Time out span multiple days?

Yes

[ssanfu]

During our meeting they said that they sometimes need to attach a drawing. I included this field and told them they could scan the drawing, save it as a .jpg, and add it as an attachment. I hope I told them correctly.

Saving the pictures in Access will cause tremendous bloat. I would save the pictures to a folder and use a hyperlink to view/access them.

Can the Time in / Time out span multiple days? Yes

Won't you need Date In / Date Out fields to be able to calculate or know when they came in for maint and when they left?

[abernut]

How does this look so far:


I have the following one-to-many

• Molds.Mold_No -> MoldReq.Mold_No
• Materials.Material_Type -> MoldReq.Material_Type
• Lubricant.Lube_Type -> MoldLog.Lubricant_Type
• MaintType.Maint_Type -> MoldLog.Maint_Type

I also have a one-to-one for

• MoldLog.Work_Ord -> MoldReq.Work_Ord

I am not sure how to set up a relationship between the Employees table and all the places it is need on the other tables.

[abernut]

Saving the pictures in Access will cause tremendous bloat. I would save the pictures to a folder and use a hyperlink to view/access them.

Not a problem. They have their own folder on my file server. I will just create subfolder for access files.

Won't you need Date In / Date Out fields to be able to calculate or know when they came in for maint and when they left?

True, I will add the changes to the table so it will resemble:
Date_In
Time_In
Date_Out
Time_out

[ssanfu]

In your relationship pic, you have "MoldLog.Work_Ord" as the PK. "MoldLog.Log_ID should be the PK for MoldLog table.


"MoldReq.Work_Ord" (PK) -->> "MoldLog.Work_ord" is the FK

[abernut]

In your relationship pic, you have "MoldLog.Work_Ord" as the PK. "MoldLog.Log_ID should be the PK for MoldLog table.


"MoldReq.Work_Ord" (PK) -->> "MoldLog.Work_ord" is the FK

I think I tried that but every time I tried creating the relationship it recognized it as an indeterminate type.

[abernut]

Here is the new relationship structure.


I am still not sure how I link the Employees table to the MoldReq and MoldLog table.

Mike

[ssanfu]

What are the field types for

MoldReq.Work_Ord (PK)

MoldLog.Work_Ord (FK)

------------------
I use an autonumber field 99% of the time to link fields. I have seen where a text field is used for a PK field. I have read it can be done but it is not as efficient as using a Long Int.

[abernut]

What are the field types for

MoldReq.Work_Ord (PK)

MoldLog.Work_Ord (FK)

------------------
I use an autonumber field 99% of the time to link fields. I have seen where a text field is used for a PK field. I have read it can be done but it is not as efficient as using a Long Int.

I would like to use autonumber but the naming convention they use contains letters.

[rpeare]

There is nothing preventing you from using autonumber as the PK and having a separate field for your internal numbering system.

[abernut]

Thanks,
I should probably do the same with the Molds.Mold_No then.
Create a Molds.MoldID that is a autonumber and set that as the PK.

[abernut]

Does this now look accurate?


I am still not certain on how to set the relationship up between the Employees table and the MoldReq and MoldLog tables.

In the MoldReq table there are three places in which the Employee information is required. Do I just create a One-To-Many for each field?

[abernut]

I must really be over thinking this.
I am getting ready to make my first form so the user can enter a Mold Request.

The information required from the user:
Work Order Number
Mold Number
Machine Number
Material
Comments
Who was it installed by
Who pulled it
How Many Cycles
Who checked it.

I am using the form wizard, so it prompts me for what Fields I want to use from the MoldReq table.
The following information now resides within another table (Mold_No, Material_Type, Machine_No, Mold_Installed_By, Mold_Pulled_By, Checked_By) but they are still fields in the MoldReq table.

Do I need to use the fields from the other table in the form, if so how does it update the MoldReq Table?

[ssanfu]

Don't base the form on a table, use a query. Create a query that joins the two (or more) tables - add the required fields to the query design grid. The query will be the record source for the form. When the wizard asks for a table or query, select the query.
(All of my forms as a query as the record source.)


Try it and post back....
(and post your current dB design if you want)