update column to be a section of another

[MrBologna]

Hello!

I have a few tables in the database I am currently working on. Orders, Parts, and Parts where used.


The Orders sheet has all of the unique info for an order, like the order number and the number of associated jobs, etc.
Each order can have multiple jobs, which are named after the order with an 'E' number at the end to differentiate them.

For example, order number 28682 may have 3 jobs associated with it: 28682E02, 28682E03, and 28682E04. They do not necessaryily start at 0 or 02.

The issue I'm facing is that when I import the parts where used table, each part is linked to its job number, rather than its order number, so I cannot link it to the corresponding order entry.

I would like to create a macro or button or something that will add a new column which is just the digits before the E number for each entry in the parts where used table, and thus link it to the Jobs table.
But I have yet to figure out the best way to go about this.

Please bear with me, as I'm pretty new to Access!

[Bob Fitz]

Perhaps you could use the Left() function to return the 5 left digits, if the order number is always 5 digits, otherwise you might use the InStr() function as well in the expression.

[Micron]

Methinks your db isn't normalized; i.e. orders should be in one table, jobs in another with order id as a common link between them.
For each order (the parent) you'd have several job (child) records; each job record having the order id (usually left of the job number/id but not necessarily the first field).

Maybe do some normalization research?
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

There's also a lot of other stuff you might want to look into, such as naming things, what to avoid, etc. Have those links but don't want to insult you if you're way ahead.

[MrBologna]

Okay, so I need to make a table which links each job number to its corresponding order number.

If I take a list of all of my orders (~18,000 entries) and want to auto-populate their order numbers (~8,500 entries), what would be the easiest/best way to do that?

Something like copying the first 5 digits of the job number into a new column so I can link it to my orders table

[MrBologna]

And on that thought, right now my orders table has a "customer" field that has redundant data. If I create a customers table, how do I then update my orders table to change the customer's name to the ID which is then defined in the customer table?

[Micron]

right now my orders table has a "customer" field that has redundant data.

What does this mean? The data is no longer useful?

If I take a list of all of my orders (~18,000 entries) and want to auto-populate their order numbers (~8,500 entries), what would be the easiest/best way to do that?

The problem for us here is that you have the business knowledge and the current structure of your db and we don't. I think you need to give an overview (not too broad yet not too deep either) of the business at hand (what is this, construction? renovation? parts sales?). The explanation should define everything not blatantly obvious. You might think Order would be obvious, but it's really not treated the same by everyone. Don't use jargon, or if you do, define it and don't assume we know what any term means. Include an explanation of what you have, or better yet, pictures of table relationships if you have created them. Also state the goal so we know what you have as well as where you want to end up.

If you haven't created relationships I'd say don't as I suspect your db isn't ready for that. In lieu of those pictures I'd suggest dumping some data into Excel. Use Find/Replace (e.g. Acme Industries becomes ABC Ltd) if you have to protect information. Make those column labels the names of your proposed table fields. Put your table name in the top left cell of the range for the data. Center the data then copy/paste into your thread and you'll get a nice table you can work with. This is better than an image because it provides data that we may be able to use in addressing the problem. In Excel, it would look something like this:


I decided today that I'm not getting enough accomplished outside of this forum and will need to back off a bit. If no one else jumps in here, I will try to stick with you until you decide this thread is solved, but I need to get to where I understand everything necessary to get you there as quickly as possible. Of course, I welcome other experts to join in as always.

[MrBologna]

Okay, I'll start from the beginning then!
Thanks so much for your assistance by the way.

I started working for a company that designs/produces electrical control panels.
They currently use a collection of massive excel sheets to track all of their parts, orders, jobs, etc.
When I advised them to move towards an ERP system, I got some pushback due to the rigidity that generally comes with SAP.

Our process flow is something like this:

• Receive a sales Order, create entry in 'Orders' database (Example Order #: 28258)
• Create a job for each item on the order (example jobs for above order: 28258E02, 28258E03)
• Engineering team designs each job and creates bills of material (for each job) consisting of all the job's required parts
• This BOM is then imported into 3rd party software which is used for purchasing and inventory management
  
  • Some of the data I am using comes from an export function within this 3rd party software
  
  
• The state of each job is then tracked throughout its life:
  
  • Are all the parts here? (possibly integrate some inventory management here in the future, but for now can be a checkbox)
  • Is the CNC machining program done?
  • Has it been machined?
  • Has it been built?
  • Has it been wired?
  • Has it been tested?
  • Has it been shipped?

The following data's primary location will be in the Access database, will need to be easily backed up and somewhat difficult to create issues in:

• Orders
• Job status

The following data is exported from other software, and will be held for reference within the Access database (being re-exported and imported as needed to keep the database up to date):

• Parts
• Parts where used

My task is now basically to design an access database which will handle the following procedures:

• Search 'Parts' database
• Search 'Orders' database
• Search 'Parts where used' database (a list of every part used in each and every job we've done, like a collection of all the BOMs)
• Create new entry in 'Orders'
• track & modify the status of a job
• See which jobs need machining/need to be built/need to be tested

Attached you will find screenshots of the general layout of the orders table, the parts table, and the parts where used table, respectively.







I have already created some form & query combinations that worked for the search functions, but I'm pretty sure I'll need to overhaul the entire database structure so those will likely need to be re-written.
Right now I am using a password protected back end with all the tables in it, and a distributable front end with all the forms and queries.

Let me know if there's any information I missed.

From what I have gathered so far, I believe I will need the following tables?

• Customers
• Orders
• Jobs (containing status fields)
• Parts
• Parts where used

[June7]

Assuming customer names in Orders table are consistently spelled and there are not multiple customers with same exact name, replacing the name with ID is a simple UPDATE action.

If you don't already have a Customers table, extract data from Orders to new Customers table.

SELECT DISTINCT Customer INTO Customers FROM Orders;

Then create an autonumber primary key field in Customers.

Add a field in Orders for the numeric customer ID foreign key. Build a SELECT query that joins Orders to Customers. Switch it to UPDATE. Define field to update. The query designer will help build the SQL which should look like:

UPDATE Orders INNER JOIN Customers ON Customers.Customer = Orders.Customer SET CustomerID_FK=CustomerID;

After all looks good, delete Customer field from Orders.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. No or Num would be better than #.

[Micron]

Did you review the normalization links? If so, I don't think you entirely understand the concept if what you're showing seems OK to you now. It can depend on other details about the business or data sources you have to deal with that aren't part of my understanding at present, but to do this well, you need to think in terms of entity, attributes, and entity relationships. Here's one example that I think I can hang my hat on, so to speak: "Programming Required" is not an attribute/characteristic of the entity "Orders". It is an attribute of the job, thus it doesn't really belong where you have it. Same might be said for other fields in the orders table such as Materials or Complete (but what is "complete" - the order or the job?). How you do or don't apply this thinking will dictate what tables you will have. For example, if you came to the decision that quote attributes don't belong in the orders table (quotes are different entities are they not? I think yes, but you are there and I am here) then quote date doesn't belong in orders.

When an order comes in there should be fields pertinent to the attributes of the order. So how then do you relate the order to any quote that might have spawned it? You add a "linking" field that builds a bridge to tblQuotes. Maybe you're asking "if quotes are their own entity, why put the quote ID or quote number in the orders table?". The answer is that quote id is the linking field to tblQuotes. What you don't put in the orders table is the quote date, who created it, the quote amount, etc. Get it? From your earlier post I see no evidence of how you can link an order to a job. So do you put the order id in the job table or the job number in the order table? The order exists first, yes? Ideally I think we try not to go back and alter those records, so if you put the order ID in the job table, you don't have to go back and update the orders table. You can "relate" all order info to the job. In the end, you build a sort of spider web that ties everything together.

Here's a possible list of tables I imagine for this:

Orders
OrderItems: multiple panels or whatever makes up the order
Jobs: build panel ABC, build switch XYZ
JobTask: to build panel - 1)drill holes 2)install boards 3)install breakers 4)apply labels
TaskStatus: Scheduled/Active/Fini
JobStatus: Scheduled/Active/Complete/Closed
Parts?
TaskParts: in my world, the Job (Work Order) didn't have a parts list - the task did
Customer
Quote
PO
Shipping?

Because you have special characters in your field names I'm going to throw in a few more links. Strongly suggest you review them.
Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

****How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

Re ****
Have meetings and document what you get agreement on that the db needs to support and why it should be built. Identify where it will save time/money. Start at the high level, then go away and plan according to the suggestions in that link. Suppose you all agree that it needs to support relating orders to jobs to parts while not allowing unauthorized entry, and that's about it. If someone demands financials, you have the right to try to stick with the plan because of the huge project bloat that will be.

Have follow up meetings to show progress and answer questions you may have and add detail to the scope if need be. Once you have an agreement, build towards the final product. One of the biggest problems with such a project is that without agreement in principle at first, and on details later, you will get requests that derail what you've done. You might have to say "I'll consider that for version 2" - unless it's the boss. If it's a must-have, you didn't do the first part well enough. Many such projects turn out bad because of the lack of forethought.

Hope that keeps you busy for a while. As an afterthought, there might be data models here that give you some pointers.

[MrBologna]

The sceenshots I uploaded were of un-manipulated data straight from our current system, since I decided to restart from scratch.

One thing I think I should clarify on is the difference between orders and jobs.
To an engineer, the order is the whole job. They receive the order folder, and each E-number (job) within that order is basically a different part of the system they are designing.
So say there are 2 jobs in one order. One is for a main control panel, which has programmable components, and the other is a local control station (a 4" x 8" box with 3 buttons on it).
This order is defined as needing programming, even though one of its jobs does not, because the system as a whole needs to be programmed to function correctly.
The main reason jobs are tracked separately is for production purposes.

Regardless,
I am currently going through and editing my tables and data, but I have a question.

What is the best way to approach a field that has very few options?

Take the 'Design_Software' attribute of an order. It has 3 possibilities: AutoCAD (A), Zuken (Z), or neither (N/A) (for field service jobs or whatever).
Does this warrant decomposing into another table? or is this type of simple redundant data ok?

If I were to follow my instincts I would just leave it as a short text field and give the user a 3-option drop down on the input form, but then again I don't know what I'm doing, so.

[MrBologna]

And is there a reason why I should be using autonumber for the primary key? yes it is very simple, but since job and order numbers are unique, could I use those as primary keys instead?
What about something that's not a number, say, a 3-character shortened customer name as a primary key?

[June7]

It is possible to use non-autonumber as primary key. I have done that. Some have strong arguments against but it can be done. For one, indexing on alpha values is slower.

Recommend not using a shortened customer name - too much chance of duplicates.

Review https://www.accessforums.net/showthread.php?t=23329

[CJ_London]

Probably not a problem for you but just be aware that 28682E02 is actually a number described in scientific notation.

?28682E02
2868200

?28682E03
28682000

?28682E2
2868200

?28682E12
2.8682E+16

this can be a problem when moving data between access and excel or the other way round.

[Micron]

Yes, I would
a) NOT use combined order numbers and job numbers in one field.

b) consider either a table or value list for limited choices. It matters not that you'd only have 3 records in a table. Maybe 2 years from now, you'd have to add another option. Which would you rather do, add 1 record to that table or redesign all the forms that only have 3 options in a value list for every such control? Planning = efficiency. What you should not do is have multi value fields, but you read the info and get it, right?

c) Customer gets bought out & there is a name change. Now what? Never use potentially volatile data as PK fields; not even in a short form. I worked for years within a system that used 4 character alpha codes as PKs and there was never an issue. However, there was a hard and fast rule - never re-use for somewhere else, never delete (only obsolete) and never duplicate. Can work just fine. I believe that the difference in speed by indexing on them (if that's true) can't be easily measured. It's very common to index on most kinds of data types, but should never be done on long text (memo) fields. I see no reason why not to have an autonumber as a pk for customer, but I would make the name field an index with no dupes allowed (i.e. unique) or a composite PK. If you ever end up with 2 customers with the same name, you could always add another field to the index as long as the combination of them would be unique.

c) so maybe you don't need to break the job down into tasks as I imagined, but it seems you still need a one to many relationship between (1) order and (N) jobs. Now would be the time to consider if a job should be broken down into tasks, even if that takes 10 seconds to say no. That level may be in your supporting systems when it comes to ordering parts or whatever those systems do. Drilling down to the task level can be a powerful thing, but might be too much for your situation.

d) where you might run into a bit of effort is the generation of E suffixes. It can be a tricky exercise in a multi user environment and would depend on who might be interacting with what at the same time. If you and I can input jobs on an order but not on the same order, then you should be able to get E01, E02... without too much trouble. But we are able to add jobs to the same order at the same time and you are creating 02, the next for me is 03. You create another and it will be what? Depends on whether or not you save that suffix at the beginning or end of the job creation. If at the beginning, your next will be 04. What will you think then - what happened to 03? No biggie, just confusing maybe. So save it at the end. As mentioned, your next will be 03, and you go to save it but I beat you to it. If you designed correctly, you will raise an error when trying to save a duplicate 03. All is not lost, but I'm rambling and will end by saying this is an example of what I was saying earlier. Planning is key, but don't just assume you can figure out how to do something. In this case, you'd research your notion to auto generate sequential data and would find it has the potential to go wrong.

e) consider dropping the alpha in favor of numeric. Are there jobs on the order that are not engineering? If not, why add that complexity when you can avoid the exponent problem (good one, Ajax) and other issues, such as sorting. Text numbers don't sort well. Text 11 comes before 22 so no problem, right? Text 111 also comes before 22. Now you have a problem. Number data type is always easier to deal with in Access than text numbers, which is what you'd have with E22.

[MrBologna]

Thanks for this, you guys have already been a huge help on this project!

I've recreated all of my data import steps and table modifications (work in progress) in VBA and SQL scripts that allow for quickly importing and updating the data in my tables.
However, I am having issues with inconsistencies from within our accounting software, keeping me from creating relationships between my fields.
Is there a good way to find all of the fields that don't match up?

Say i'm trying to connect my Order_No fields between my orders and jobs (now re-named Jobs and Assemblies, respectively, a smart move based on your confusion)
They are all numeric in the Jobs table, because that only has the jobs that com from purchase orders, but some of the entries in our accounting software (imported into the Assemblies table) don't exist in Jobs because they are a warranty job (order_No ends with -w) or something of the like.
I thought about fixing the inconsistencies by hand, but there are over 6,000 entries in assemblies and almost 9,000 in Jobs.
I thought about just looking for any order that has a '-' in it, but there are others that are typos and whatnot.

Is there a good way to select all of the Order_No's from Assemblies which don't exist in Jobs?
Something like: SELECT FROM Assemblies WHERE Order_No DOES NOT EXIST IN Jobs.Order_No

I know the terminology is pretty confusing at this point, I apologize for that.