Zydeceltico
Registered User.
- Local time
- Today, 06:02
- Joined
- Dec 5, 2017
- Messages
- 843
Hi All -
I work as a QC and Field Inspector for a roll forming company. Airports and Stadiums around the US and some outside of the US utilize our product in the ceilings. We have a special and expensive tool that we supply to the subcontractors of our customers to assist in the installation of our product. Currently we have twenty tools (they are all identical and uniquely numbered with a two-digit serial; 01 - 20). I am responsible for contacting the customer to identify the best jobsite contact to send the tool too. As most of these jobs can take months to install, I have a need to be able to accurately track where they are at any given time and who to contact to ship them or have them returned to our shop.
We do not charge for usage of the tool. It is costed into the jobs. Our jobs all have a discrete, unique identifier (TE5850, TE910, TE6140 - the TE is a constant and the 4-digit suffix increments by 1(one) as sales contracts are finalized).
The typical order of operations is that I see a job coming on the radar that requires this tool. I contact the customer a week in advance of the first delivery of our product to their jobsite. The purpose of this initial contact is to identify the jobsite erector (usually a different contact than the customer - but not always). Then I call the jobsite contact and acquire a firm shipping address. Then I ship the tool. I also get an estimate of how long the erector thinks their install will take so I can set a date to call to remind them to return the tool.
Attached is my initial thought about DB design to track all of this.
I am hoping to be able to accomplish the following:
1. From a form, enter a new job, customer contact info, and estimated date of first product delivery and any notes.
2. From a form, enter the jobsite contact information - which again may or may not be the same as customer contact. We have many repeat customers and they have many repeat subcontractors. This almost always happens weeks or months after the initial "new job entry."
3. From a form, enter shipping to jobsite details, UPS tracking, date of shipment, etc.
4. From a form, enter return shipping details.
5. Run a report that shows all upcoming jobs within a user-defined period by Date.
6. Run a report that shows how many and which tools are in the field and how many are on hand at the shop.
7. Run a report that shows all jobs in the field with "Date Shipped" in order to assess contacting jobsite contact to have tool returned.
Given all of that, does my attached DB appear to be initially designed to accomplish/accommodate my needs? Specifically the relationships and FKs?
And then, I should probably repost over on "Forms" to get some guidance on how to approach my data entry/edit forms.
Thank You So Much,
Tim
I work as a QC and Field Inspector for a roll forming company. Airports and Stadiums around the US and some outside of the US utilize our product in the ceilings. We have a special and expensive tool that we supply to the subcontractors of our customers to assist in the installation of our product. Currently we have twenty tools (they are all identical and uniquely numbered with a two-digit serial; 01 - 20). I am responsible for contacting the customer to identify the best jobsite contact to send the tool too. As most of these jobs can take months to install, I have a need to be able to accurately track where they are at any given time and who to contact to ship them or have them returned to our shop.
We do not charge for usage of the tool. It is costed into the jobs. Our jobs all have a discrete, unique identifier (TE5850, TE910, TE6140 - the TE is a constant and the 4-digit suffix increments by 1(one) as sales contracts are finalized).
The typical order of operations is that I see a job coming on the radar that requires this tool. I contact the customer a week in advance of the first delivery of our product to their jobsite. The purpose of this initial contact is to identify the jobsite erector (usually a different contact than the customer - but not always). Then I call the jobsite contact and acquire a firm shipping address. Then I ship the tool. I also get an estimate of how long the erector thinks their install will take so I can set a date to call to remind them to return the tool.
Attached is my initial thought about DB design to track all of this.
I am hoping to be able to accomplish the following:
1. From a form, enter a new job, customer contact info, and estimated date of first product delivery and any notes.
2. From a form, enter the jobsite contact information - which again may or may not be the same as customer contact. We have many repeat customers and they have many repeat subcontractors. This almost always happens weeks or months after the initial "new job entry."
3. From a form, enter shipping to jobsite details, UPS tracking, date of shipment, etc.
4. From a form, enter return shipping details.
5. Run a report that shows all upcoming jobs within a user-defined period by Date.
6. Run a report that shows how many and which tools are in the field and how many are on hand at the shop.
7. Run a report that shows all jobs in the field with "Date Shipped" in order to assess contacting jobsite contact to have tool returned.
Given all of that, does my attached DB appear to be initially designed to accomplish/accommodate my needs? Specifically the relationships and FKs?
And then, I should probably repost over on "Forms" to get some guidance on how to approach my data entry/edit forms.
Thank You So Much,
Tim
Attachments
Last edited: