Results 1 to 4 of 4
  1. #1
    kozzy is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Oct 2016
    Posts
    2

    Seeking assistance with database creation for my company

    Hello everybody. I've developed an idea for a database that would save people at my company a lot of time and frustration. The only problem is I'm spending a lot of time and getting frustrated while trying to build the dang thing. It seems like it should be a rather simple and straight forward database so hopefully someone can lead me in the right direction.



    I have somewhere in the ballpark of 25-30 large excel files, all containing over 30K part numbers along with various details and descriptions pertaining to each one. All of these files have the same column headings. Some of these files will contain the same part number, but with a different description depending on which file it is coming from. What I wish to do is create a form with a single text box in which a user inputs a part number(s) and then have the desired field of information drawn from all of the excel files in which the part exists.

    I know this is going to involve importing all of the excel files into Access as tables, which is as far as I have gotten. From here I'm not sure where to go or what needs to be done. If anyone could assist by suggesting ways in which this can be done, or directing me to resources where I can learn this, that would be fantastic! I've been looking all over the internet to find such a thing but haven't come across anything yet.

    Thank you

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I wish to do is create a form with a single text box in which a user inputs a part number(s) and then have the desired field of information drawn from all of the excel files in which the part exists.
    So you want to create a search form that will return info for a specific part number.

    Lets start with questions:

    Why 30 different Excel files with the same (more or less) data?
    Shouldn't the same part number in all workbooks have the same description?
    How often does the Excel workbooks get updated/changed?
    Who does the updating/changes?
    How many columns are in the workbook?

    Could you post a workbook or a workbook with 20 -30 rows of data? Zip it then post it.

    ------------------------------------------------------------


    I stole the following from a post by orange - it is better than anything I could write:
    ---
    "I recommend you create a narrative in simple terms about your proposed database and application. You don't start building a database by identifying a Form with 3 buttons. Now, that may very well be your initial user interface, but that is not the database. It is not as easy as many think to clearly describe WHAT your business is/will be. Consider the "things" involved(entities), the particular characteristics of each(attributes) that are important for your business and how these things relate to one another (relationships). After you have the narrative, you can use pencil and paper (or whiteboard or cardboard or ...) to develop and refine a data model. Create some test data and vet your model."
    <snip>

    <snip>
    "I see too many people, who have the latest HW and Access, jumping in head first think the software will build the database. They have multiple issues and can't access the data required for X and/or Y, and typically it is an issue of basic table and relationship design. Their next "rationalization" is " I've got too much invested to go back and correct the design...". Don't get yourself in that predicament.

    You have to do the analysis, build a plan, set priorities......Since you have the business, you know the processes better than anyone, so analysis should be second nature-- and providing you are familiar with database concepts -- designing the database should be an iterative process and a good learning experience for you. The data model and testing/vetting will result in a blueprint for your database. Review, test, adjust, and repeat until you get the results you expect. People are here and willing to help."
    ---


    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.

    Do not use spaces, punctuation or special characters in object names.
    Do not use look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.

    "Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

  3. #3
    kozzy is offline Novice
    Windows XP Access 2010 64bit
    Join Date
    Oct 2016
    Posts
    2
    Ssanfu - I thank you for your reply and apologize for my vagueness about the scenario. To answer your questions:

    1 & 2. There are so many different excel files because each file is created and owned (maintained) by a particular manufacturing plant. Most Plants will have the same notes for each part, but there will also be plants that give unique descriptions. At my company, we regularly have to extract all of these different descriptions for each part and compile them to send to suppliers. Some parts may be maintained at 1 plant while others at 15 plants. Which is why it is extremely inefficient filter through all of the files.

    3 & 4. The data in the files originate from quality notes created at each individual plant. The excel files are extracted from SAP monthly. So every month I would (or have an intern) take the files and update the database to ensure current data is available.

    5. After extracting the raw data from SAP there are 15 columns. I trim the fields down to only 3 columns which are the only ones I'm concerned with. The material number, the plant number, and the quality notes.

    I could post a sample of data tomorrow when I have access to it.
    Last edited by kozzy; 10-20-2016 at 06:56 AM.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm spending a lot of time and getting frustrated while trying to build the dang thing.
    What do you have so far? If you have a dB, would you post it?

    Sounds like simple requirements - sounds like you basically want to have a search form. I think the challenging part will be deciding if a part number needs to be appended or updated each month.


    When you search, do you want just the part number that matched a specific plant or the part number from all plants?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 01-31-2015, 06:03 PM
  2. Seeking guidance with session database
    By melliott1712 in forum Access
    Replies: 5
    Last Post: 10-06-2014, 12:21 AM
  3. ACCESS Novice Seeking Form Assistance
    By aliup98 in forum Access
    Replies: 6
    Last Post: 07-29-2013, 07:11 PM
  4. Seeking someone to help me build my own database
    By Meat4grinder in forum Access
    Replies: 7
    Last Post: 08-11-2012, 10:04 PM
  5. Database for Company's Output
    By cg98721 in forum Database Design
    Replies: 1
    Last Post: 06-15-2011, 07:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums