heather001
Registered User.
- Local time
- Today, 11:21
- Joined
- Jul 5, 2006
- Messages
- 15
Hi all,
I've used these forums immensely when I need help with my databases. Thank you for all the help you've provided so far. I'm hoping I can get some recommendations for my next database project.
I'm getting ready to design a database that uses an ODBC connection to link to our ERP software to retrieve current work order information. The reason I'm building the database is because the software doesn't allow us to adequately track product through a couple of our work centers that have a non-standard production process. The database will allow us to assign the work orders to run on different machines and provide a schedule for the operators to work from.
Using an append query, I'm pulling all open production work orders on a daily basis. This part I have working like a charm.
What I need to consider is how to reflect changes to a work order in the ERP system so the changes are reflected in the database when the append query runs.
The tables and fields in my database are as follows:
***tblWO***
WO#
Due Date
Quantity
Part Description
Customer Name
***tblJobProgress***
WO#
OprSequence
OprDescription
WorkCenter
So for instance, a new work order, let's say 333222 is put up today with a due date of 7/31/06. My append query will catch the work order when it runs tonight and add it to the database. Then let's say the due date gets moved to 8/5/06. I want to reflect this change in the database.
Any changes in [tblJobProgress].[OprSequence] would also be captured.
I'm not sure the best method to go about doing this.
I've seen databases such as this designed that dump all the contents each night and perform a new table query, however, if the status of the work order changes from open to closed, then it won't be included and any data input for that work order will be lost. But including closed work orders could be overkill.
Although I've been told by our Operations manager that history in this database isn't necessary, I would like to figure a way to keep enough history "just in case". If I can make this work with this database, I have another database I can apply this to where history is crucial.
What methods would you recommend to accomplish this?
TIA~
I've used these forums immensely when I need help with my databases. Thank you for all the help you've provided so far. I'm hoping I can get some recommendations for my next database project.
I'm getting ready to design a database that uses an ODBC connection to link to our ERP software to retrieve current work order information. The reason I'm building the database is because the software doesn't allow us to adequately track product through a couple of our work centers that have a non-standard production process. The database will allow us to assign the work orders to run on different machines and provide a schedule for the operators to work from.
Using an append query, I'm pulling all open production work orders on a daily basis. This part I have working like a charm.
What I need to consider is how to reflect changes to a work order in the ERP system so the changes are reflected in the database when the append query runs.
The tables and fields in my database are as follows:
***tblWO***
WO#
Due Date
Quantity
Part Description
Customer Name
***tblJobProgress***
WO#
OprSequence
OprDescription
WorkCenter
So for instance, a new work order, let's say 333222 is put up today with a due date of 7/31/06. My append query will catch the work order when it runs tonight and add it to the database. Then let's say the due date gets moved to 8/5/06. I want to reflect this change in the database.
Any changes in [tblJobProgress].[OprSequence] would also be captured.
I'm not sure the best method to go about doing this.
I've seen databases such as this designed that dump all the contents each night and perform a new table query, however, if the status of the work order changes from open to closed, then it won't be included and any data input for that work order will be lost. But including closed work orders could be overkill.
Although I've been told by our Operations manager that history in this database isn't necessary, I would like to figure a way to keep enough history "just in case". If I can make this work with this database, I have another database I can apply this to where history is crucial.
What methods would you recommend to accomplish this?
TIA~
