Results 1 to 7 of 7
  1. #1
    Wilkema is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Posts
    3

    Creating database

    Hello,



    I've searched the forums and haven't found anything (maybe I overlooked what I'm looking for). I'm looking to pull data from multiple excel docs that have the same format into an access database and then have specific data in the database spit out onto a metric in excel. The attachment is the metrics but I've added the sheets "KC", "Texas", "St. Paul", and "Denver" for convenience. Those 4 tabs are examples of the docs I would need the data pulled from, imported into a database, and exported onto the "Executive Dashboard", "PDC Dashboard", and "Quality Dashboard" sheets. The data I'm looking to pull is "Units Processed", "% LFD", % LFR", % Non-Conformance's, "#1 Dealer for LFD/LFR" and "#1 Dealer for non-conformance's. The sheets "KC", "Texas", etc are never ending, so for example, every time a new "KC" excel doc is created and saved into a specific folder I need access to automatically pull the data from that doc and export it into the metrics. Please let me know if this is possible, I'm completely new to access.

    Thank youPDC Metrics 12-9-16.zip

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    1st youd import all your separate excel files into access into a single table. Then you can run queries on the table on a single state.
    you can also pull your metrics, units, %this, %that.

    IF you can, get away from creating State excel sheets and enter all data directly into the database.
    if you cannot, then you would build an importing macro....
    save the workbook to a generic name like File2import.xlsx
    attach the excel file as a table.
    build a query to append the excel data to the main table.
    put this query in a macro.

    so the workflow would be:
    1. every new wb, save the workbook and overwrite the same File2Import.xlsx
    2. run the import macro.
    done.

  3. #3
    Wilkema is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Posts
    3
    Unfortunately I won't be able to get away from using excel. Also, you said to overwrite the previous file but I'm unable to do that. I need to have all these separate excel docs for each load I receive in. So, I would need access to pull data for each new wb.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Wilkema,
    The Zip attachment in Post#1 only has a shortcut in it - no files...


    Do you have an Access file already?

  5. #5
    Wilkema is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Posts
    3
    Quote Originally Posted by ssanfu View Post
    @Wilkema,
    The Zip attachment in Post#1 only has a shortcut in it - no files...


    Do you have an Access file already?
    Hm, must of clicked the wrong file. I've attached the correct file. I don't have an Access file yet(Access is on our companies main server, and I'm awaiting access to that server. Should have it by Monday)

    PDC Metrics 11-9-16.zip

  6. #6
    Craigp is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Posts
    3
    I think the first thing which you need to do, is to decide whether to just Link the excel files to Access or whether to import the information fully. If the information is not going to change within the Excel file, then importing is most likely the best option. The second thing, is to ensure that all column names are the same across all files and there is a column, which clearly identifies the various states, in each excel file, to ensure you can still reports across states once in Access. (These Column Names will then match Access column names, including State field) Each time you add a new excel table to the folder, you will have to unfortunately have to execute a link/import action as the name of the excel file will be different. This will have to be manually done, unless as suggested you are able to use a set name for each of the excel files per state such as File2ImportTexas.xlsx, where you can then set an automated link/import process.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have a lot of work ahead of you.

    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.
    ------------------
    Bad field name: "RA#" (has the hash mark")
    Good/better field names: "RANum", "RA_Num", "RA_Number"

    Bad field name: "Bagged Y/N" (has space and slash")
    Good/better field names: "BaggedYN", "Bagged_YN", "Bagged_Y_N"
    ------------------

    Read up on Database Design Basics:
    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    Normalization Parts I, II, III, IV and V
    http://rogersaccessblog.blogspot.ca/...on-part-i.html


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


    Here is a link to tutorials at RogersAccessLibrary that you should work through to familiarize yourself with the design process. Each tutorial will take 45-60 minutes.
    You will learn by working through the process, and what you learn can be used with any data base.
    Getting your tables and relationships designed and tested to meet your requirements is the most critical part of database.
    http://www.rogersaccesslibrary.com/forum/forum46.html

    You might try watching some free video tutorials by Dr.Daniel Soper that will help you with the concepts.
    Intro to Database
    The Relational Model
    Data Modelling and the ER Model

    Then, using pencil and paper, cardboard, whiteboard, etc, design your tables. Designing outside of Access will save you time....

    "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.

    ------------------
    Quote Originally Posted by Wilkema View Post
    <snip> I don't have an Access file yet(Access is on our companies main server, and I'm awaiting access to that server. <snip>)
    How many users will there be?
    Do they have or will Access be installed on their computer?

    If Access is only installed on the server, you should install the Access Runtime Version on EACH users computer.
    See http://hitechcoach.com/index.php?opt...untime-version
    and http://accesshosting.com/difference-...access-runtime

    ------------------
    Quote Originally Posted by Wilkema View Post
    so for example, every time a new "KC" excel doc is created and saved into a specific folder I need access to automatically pull the data from that doc and export it into the metrics.
    Please expand on what you mean by "automatically".
    How often do you get "New" excel docs?


    Questions about the Excel workbooks:
    1) On the "PDC Dashboard", there is Kansas City, then under that heading: St. Paul, Denver, Brenham and Kansas City. What are St. Paul, Denver, Brenham and Kansas City? Distribution centers??

    For the tabs: KC, Texas, St.Paul and Denver:
    2) What is the data in A1:C6?
    3) What does the workbook name look like?
    4) Is each workbook for one plant/City/month?
    5) Are the hidden columns needed? (K:M and O:Q)

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

Similar Threads

  1. Replies: 2
    Last Post: 06-20-2016, 07:06 PM
  2. need help creating database
    By johnnyb in forum Access
    Replies: 1
    Last Post: 02-18-2013, 10:10 AM
  3. Creating new database.
    By rjurke in forum Database Design
    Replies: 5
    Last Post: 09-28-2011, 04:55 PM
  4. Creating new database
    By rjurke in forum Access
    Replies: 10
    Last Post: 09-23-2011, 04:59 AM
  5. Help with creating a database
    By ITChevyUSSNY in forum Access
    Replies: 0
    Last Post: 07-31-2009, 05:48 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