Setting up my main sheet

[BmD8907]

Hello,



I am brand new to access and I am creating a database for my company that will pull in smart codes from other companies when they transition to our system and would give out the proper information. I am trying to find out the best way to lay out the first page.

the plan is to put a smart code which will be 20-50 characters. the first 20 will always be there and I can break it down in excel. I dont know if I should have it broken down into the access file, or can i paste the smart code in the first cell and have it extract the information like I would in excel in the following cells in the same row, example 7hcsgr8100c would break down into 5 cells 7 - HCS - GR8 - 100 - C. or since each of the items between the dashes would be information that runs through each list I would put in access should I just leave it all together so just put 7hcsgr8100c and have access break it down based on parameters instead of separating it? I want to make this as simple as possible with as little human input from everyone else. I want everything be automated from the first cell.

Thank you in advance

[Welshgasman]

Firstly databases do not have cells. Tables have fields. Forms and reports have controls, which show the fields or calculations.
If for any reason that code would need to be split to find anything, like from an area or subdivision, then split it at source. If you are never going to need that, then keep it as one field.
Do not mush something together that you need to split all the time depending on where some character is. If the first 20 will always exist, then that would likely be in its own field.
It is easier to concatenate than it is to split, if the data can vary.

[BmD8907]

The problem is I am breaking down their part numbers that already exist, it is already mashed together I need to break it down it down from their part number and run the material / type of item / special information / dimensions and so on and convert it into our description / part number. I can break everything out of the part number I just didnt know if I should have it broken down or would it be better to just run the query from the part itself. in excel I would seperate the part number into multiple cells and then run things like vlookup to pull up what the code means in our version. So if it would be easy / better to just have access break everything down instead of me having multiple fields that are the break down of the item then thats what im trying to find out. I added a visual to make sure I am making better sense, I want to be able to paste whats in cell a2 and then how in the following columns it breaks down the part into the sections. Essentially I want to do the same thing in access but i wasnt sure if I needed to set it up the way it is in excel where it breaks it down, or if I just use A2's information and then let access break everything down into the multiple lists in the background. Also if helps basically whats in each column currently i plan to run through my list of abbrevations to come up with what our system uses for our smart code and then it would spit out a new description / part number and any errors or information associated. as this will be a database of tens of thousands of parts unless there is a better way to handle this. Access would be better than excel due to file size.

[Welshgasman]

I would do it the same way you did in excel.Create a dedicated function to do that.

[ArviLaanemets]

It looks for me, you have 2 different tasks here.
1. Creating a database, which allow you to manage part info;
2. Inserting this info into your database as easily as possible.

About database
You need a table like
tblItems: ItemID, ItemCode, InchMet, Des, Fam, Material, Diam, Leng, Fin, ...
, and a form, where you can select an item, display all properties of the item, and do whatever you need with this info.

About reading the part info into your database, it depends how you get this info and how you will insert it.

1. In case you enter this info manually (typing it, or using copy-paste):
You enter the attribute code string into unbound control on form, and then run a procedure (e.g. clicking a button for this), which reads the code string in, splits it and saves splitted info to variables, and then updates form controls with those variables. You can allow the procedure to be called only for new record. In case the procedure is not restricted for existing records, it will update the active record.

2. In case you have attribute code string (and any additional info) stored in some external table (like Excel workbook) which Access can have linked:
You link the external table into your database. Then you create a procedure, which reads row-wise the attribute code, splits it, and checks tblItems for existence of same info there. When the item is new, then the procedure inserts a new row into tblItems and fills it. When the item was present in your database, you can either update the info, or simply skip to next row. As final step, the procedure may clear all (now old) info from linked table.

3. In case the new item info is in any form Access is able to read in (e.g. text or html file, Excel workbook, etc.), you can create a procedure, which asks for import file, reads info from import file into array, processes the read info, and as end result, saves new item info into some array (and in case there is a need for it, saves updating info into another one). After the whole import file is processed, the procedure updates the tblInfo with info from final array(s).

[NoellaG]

It seems you have it all setup in Excel. Why do you want to move to Access? Is Excel getting to slow with to much data? Do you have to put in the same data several times?
If you decide to do so, the first action should be analyse the way you want to work, which data do you need? What is the input and what output do you want?
What is the final goal (example: create a catalogue, fast search possibilities for multiple users, ...)
With that information you can decide on a data model: tables, primary keys, relations, indexes, ... .
Only after that you should look at creating the application, forms, reports, functions, actions like breaking down information.

[BmD8907]

It looks for me, you have 2 different tasks here.
1. Creating a database, which allow you to manage part info;
2. Inserting this info into your database as easily as possible.

About database
You need a table like
tblItems: ItemID, ItemCode, InchMet, Des, Fam, Material, Diam, Leng, Fin, ...
, and a form, where you can select an item, display all properties of the item, and do whatever you need with this info.

About reading the part info into your database, it depends how you get this info and how you will insert it.

1. In case you enter this info manually (typing it, or using copy-paste):
You enter the attribute code string into unbound control on form, and then run a procedure (e.g. clicking a button for this), which reads the code string in, splits it and saves splitted info to variables, and then updates form controls with those variables. You can allow the procedure to be called only for new record. In case the procedure is not restricted for existing records, it will update the active record.

2. In case you have attribute code string (and any additional info) stored in some external table (like Excel workbook) which Access can have linked:
You link the external table into your database. Then you create a procedure, which reads row-wise the attribute code, splits it, and checks tblItems for existence of same info there. When the item is new, then the procedure inserts a new row into tblItems and fills it. When the item was present in your database, you can either update the info, or simply skip to next row. As final step, the procedure may clear all (now old) info from linked table.

3. In case the new item info is in any form Access is able to read in (e.g. text or html file, Excel workbook, etc.), you can create a procedure, which asks for import file, reads info from import file into array, processes the read info, and as end result, saves new item info into some array (and in case there is a need for it, saves updating info into another one). After the whole import file is processed, the procedure updates the tblInfo with info from final array(s).

So I will receive it as the complete part number and my job will be to essentially seperate it so that I can recreate it in our format and description. So I will receive the code that is 20-50 characters long and I will will need to extract all the data from the part number to recreate it. So the thing im basically trying to figure out is whats the best way to add the information to access to extract the appropriate information and rebuild this. As our company is rather large we are converting some of our other companies to our sales system. They use Prophet 21 but will move over to SAP. the less information they manually input the less issue we have in the end. This will not fully eliminate input but will be able to cover a majority of parts and keep things consistent.

[BmD8907]

It seems you have it all setup in Excel. Why do you want to move to Access? Is Excel getting to slow with to much data? Do you have to put in the same data several times?
If you decide to do so, the first action should be analyse the way you want to work, which data do you need? What is the input and what output do you want?
What is the final goal (example: create a catalogue, fast search possibilities for multiple users, ...)
With that information you can decide on a data model: tables, primary keys, relations, indexes, ... .
Only after that you should look at creating the application, forms, reports, functions, actions like breaking down information.

So what you are seeing in the small snip it from above is for 1 line. i will be using this for potentially up to 100,000 different lines or more. But that line of just breaking down the information the excel file is too large already at 26MB. I havent introduced most of the tables to the excel file yet. This isnt a catalogue but a conversion file

so I will be provided with

7HCS2100C200ZT - Tells me this is a 7=inch HCS=hex head cap screw 2=Grade 2 100c= 1 inch diameter coarse 200=2 inches long zt=zinc trivalent and either I would have access break everything down from the full code or if i need to break it down into each part as I stated above. I want everything ran through the many lists that I would need to and change this code into 7000016200 and then provide a new description saying SCR-HCS-GR2-(ZT)-1_8X2

this one is a very very easy basic one that I can provide, some of these will be way more complicated as well as eventually I would need to put some information and have access try to extract data but thats a beast im not ready for yet, im trying to take care of the easy simple items. Also instead of spending months of time just staring at a first version of the file I can paste all their part numbers into the system and spit all the data out in a matter of seconds/minutes. but also this can keep track to make sure their data is correct with corrections and then we can make matches in our system much faster. this will honestly save months of work. and the last go live I was given only 6 months for a relatively small company of like 50,000 parts, I cant imagine the amount of time for their transition. I state 100,000 codes but honestly we could be looking at up to 500,000 codes and weeding out duplicate parts that the companies arnt even aware of.

Now another reason I want to move to access because of the large set of information that will be stored in the background as well, but also because from what I understand that I can have access tell my why something errored out so if I didnt have HCS loaded in my database it could in some way tell me that it couldnt find that information as its not in the appropriate table instead of just providing me with #VALUE or something. this data is not to store the part numbers just large conversions for companies, consistency for companies, and also a reference tool if needed for my team. One that I would tailor to be used for both their conversion and for us to use in our system. as of right now though the focus is on company transitions.

[davegri]

They use Prophet 21 but will move over to SAP. the less information they manually input the less issue we have in the end.

If your company uses SAP, why don't you use the conversion tools provided by SAP to convert to SAP standards?

[Micron]

im basically trying to figure out is whats the best way to add the information to access to extract the appropriate information and rebuild this.

If you have not studied and db normalization, that is where you must start if you're going to do this in Access. If you build tables like you would do for spreadsheets, that will be a big mistake. It might be ok to just take table suggestions and run with them but the danger in that might be that they are incomplete due to missing information as to what the db needs to do. One task (splitting data) probably does not encompass everything you need. As far as that goes, a pic showing only one sample row isn't much to go on, and as far as I can see, you didn't give an example of the output. When splitting strings, some sort of logic must prevail in order to get consistent results. I don't see what that is yet. Last but not least, it may very well be that every piece of that split string after the first section might very well belong in it's own table but not enough info to say one way or another.

[NoellaG]

so, if I understand the situation well, you need a kind of a datapump that takes the input data from several companies, breaks it down in several bits and translates the information so you can compare it with the data in your existing system and correct the data there.
If you want to do the checks, you need the new data input + the data that already exist in your system, so your system will need to link to it. If you can't link , you're looking at maintaining 2 systems operating side to side.
You talk about handling 500.000 codes, so Access won't handle this efficiently, you will be needing something like SQL server.
IMO: you can use Excel to convert every new batch of codes you receive, and talk to the developers of your system which format they need to import the data in your system. You can make Excel templates for each customer, so if something changes you only need to change it in 1 place. Once the batch codes is split into the correct format for your system, you can import it and let your application do the work.
Only if you can link to the information in your system, you can already do, batch by batch, the clean up of doubles before sending the results.