Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27

    Importing several different columns from one spreadsheet into several access tables

    I am looking for an effective way to import data into several different tables from one spreadsheet. Let me explain...



    I have 5 tables with the following fields:

    Each table will have their own fields example:
    tblAccountList
    • AccountName


    tblAccountProfile
    • BillingStreetAddress
    • BillingCity
    • BillingCounty
    • BillingState
    • BillingZipcode
    • MRC (stands for monthly reoccurring charge)


    tblServiceLocationAddress
    • SLStreet
    • SLCity
    • SLCounty
    • SLState
    • SLZipCode


    tblAccountContact
    • FirstName
    • LastName
    • OfficePhone
    • MobilePhone
    • EmaillAddress


    tblEmployeeCount
    • EmployeeCount





    I have one spreadsheet with all the field names for the column headings. When a brand new user sets up their profile inside of Access, I would like them to be able to import their working data with a single button. In other words, instead of my users having to take the data out of this one large spreadsheet (will call it the "master spreadsheet"), and create 5 different spreadsheets containing the respective data that matches the field names, I would like a way to create an "ImportData" button on a form that will allow the user to import this data from the master spreadsheet and let access populate each of the tables respectively.


    As a bonus request...This dataset can and does change periodically, so I would also like a way to do this on an "update" basis as well. The end user may need to "update" their account list with add/remove/change data to the above tables...from time to time. I know this is a much more involved task and probably too big for this forum, and if so...just say so...I will understand.

    Any help is greatly appreciated!!

    Thanks in advance!
    -mgm

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    save as to the same file everytime: c:\temp\File2Import.xlsx
    attach this file as an external linked table: tFile2Import
    build all your append queries to add this data to the internal data tables
    put the queries into a macro: mImportXL


    then the steps will be:
    1. overwrite the file, c:\temp\File2Import.xlsx, with the new data
    2. run import macro: mImportXL
    done

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think your design need some work.

    There are several ways to get the data from Excel into Access:
    1) you could import the SS into a table in access and use queries.code to put the data into the correct tables.
    2) you could use automation to read the data from Excel and write it into Access. (lots of code)
    3) you could write code in Excel and push it into the Access table.

    I would probably choose option #2.



    It would be nice to see your dB and the SS with a little bit of data - use names like Fred Flintstone, Barney Rubble, Jessica Rabbit
    And would also need to know where the column data goes in the Access tables.

    As far as the Access tables, I might start with
    Click image for larger version. 

Name:	Relationship1.png 
Views:	36 
Size:	91.9 KB 
ID:	43818

    You don't need table "tblEmployeeCount" because the total can be calculated (and you don't have an "Employee" table)
    I'm sure these are not the only fields/tables you would need, but without more info about your business, it is the best I can suggest.

  4. #4
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    Thank you for this Steve,

    I agree with you my tables need some work.. In my question, I left out some fields that are in my table...for instance, I have a PrimaryKey and a ForiegnKey in each table with established relationships almost exactly like you have outlined above.

    Yes I also agree with you on the option2 part however, I am not a programmer at all..and any successful projects I work on in Access, I mainly stumble my way through and make it work by looking at templates and other "real programmers" examples

    As far as my DB and SS go....yes I will upload a copy just as soon as I clean it up. Right now, I have a very messy database with lots of "trial pieces and parts" (heh heh) everywhere. Lots of tbl_testing, frm_deletemewhenfinished, qry_trythis....blah blah. It is very messy and cloogy right now. As soon as I get it all cleaned up, Ill upload.

    Thanks again!
    Matt

  5. #5
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    Hey Ranman256,

    Yes..I was thinking about linking an outside spreadsheet and then creating a macro to update the tables and in the end...I may end up doing that...

    I wanted to keep it very simple...less work for my users and less room for user error if I can contain it all within the db itself. SO I am very much on the fence about this...but I certainly agree...that linking an outside file, may be the "quickest way to the happy path."

    Thanks again and BTW...greetings from Kentucky! I noticed you are from the bluegrass state...I am in Louisville...

    Take care,
    Matt

  6. #6
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    Hey Steve,

    I am attaching a link the download the DB. You will find the spreadsheet in the folder called source_data. One note, I forgot to rename a form call ~frm_TEST in the DB. Once everything is said and done, I will rename it to "frm_NaviTab_MyModuleManager."

    https://www.dropbox.com/s/q8dte2crq58iuqo/SAMM.zip?dl=0


    Thanks in advance for any input you can offer!
    Matt





    Quote Originally Posted by ssanfu View Post
    I think your design need some work.

    There are several ways to get the data from Excel into Access:
    1) you could import the SS into a table in access and use queries.code to put the data into the correct tables.
    2) you could use automation to read the data from Excel and write it into Access. (lots of code)
    3) you could write code in Excel and push it into the Access table.

    I would probably choose option #2.



    It would be nice to see your dB and the SS with a little bit of data - use names like Fred Flintstone, Barney Rubble, Jessica Rabbit
    And would also need to know where the column data goes in the Access tables.

    As far as the Access tables, I might start with
    Click image for larger version. 

Name:	Relationship1.png 
Views:	36 
Size:	91.9 KB 
ID:	43818

    You don't need table "tblEmployeeCount" because the total can be calculated (and you don't have an "Employee" table)
    I'm sure these are not the only fields/tables you would need, but without more info about your business, it is the best I can suggest.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by mgmercuio View Post
    ....Right now, I have a very messy database with lots of "trial pieces and parts" (heh heh) everywhere. Lots of tbl_testing, frm_deletemewhenfinished, qry_trythis....blah blah. It is very messy and cloogy right now.....
    Sounds like you are peeking over my shoulder.. except I use copies of the dB and when it (the code/form/whatever) works, then copy paste/ import into the most recent copy of the development dB.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So I looked at your dB and made a few changes.
    EVERY code module (Form Class modules and Standard modules) should have these two lines as the first lines
    Code:
    Option Compare Database
    Option Explicit

    I looked at your SS, but these is no data in it.
    I was wanting to see what data needs to be transferred from which cell into what field in which Access table.

    Only the SS SAMM-TableStructure.xlsx will open.
    All of the other SS are named like "~$2021_Account_List.xlsx" and won't open. All files should be closed when adding them to a Zip file.


    BTW, you should use only letters and/or numbers (exception is the underscore) in object names. If there is a form, report, query that I am working on or is temporary, I use a prefix of zz. Instead of "~FormC", I would use "zz_FormC".

  9. #9
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    Steve,

    I apologize. I goofed. The link I shared with you (and everyone else ) was a folder and files that was not the cleaned up version. This explains why you couldn't open some of the files.

    Anyway, I have "re-uploaded" the correct folder and data set. It also contains the sample data.

    https://www.dropbox.com/s/cwp6kh77pe...Files.zip?dl=0

    Again, my apologies for the error.

    Matt





    Quote Originally Posted by ssanfu View Post
    So I looked at your dB and made a few changes.
    EVERY code module (Form Class modules and Standard modules) should have these two lines as the first lines
    Code:
    Option Compare Database
    Option Explicit

    I looked at your SS, but these is no data in it.
    I was wanting to see what data needs to be transferred from which cell into what field in which Access table.

    Only the SS SAMM-TableStructure.xlsx will open.
    All of the other SS are named like "~$2021_Account_List.xlsx" and won't open. All files should be closed when adding them to a Zip file.


    BTW, you should use only letters and/or numbers (exception is the underscore) in object names. If there is a form, report, query that I am working on or is temporary, I use a prefix of zz. Instead of "~FormC", I would use "zz_FormC".

  10. #10
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Matt,
    Your question in this thread is
    Quote Originally Posted by mgmercuio View Post
    I am looking for an effective way to import data into several different tables from one spreadsheet.
    .
    .
    .
    I have one spreadsheet with all the field names for the column headings. When a brand new user sets up their profile inside of Access, I would like them to be able to import their working data with a single button. In other words, instead of my users having to take the data out of this one large spreadsheet (will call it the "master spreadsheet")
    You provided the dB, but where is the "master spreadsheet"???


    I'm still struggling with your table/relationships design. Did you draw the tables/relationships on paper, cardboard, whiteboard, driveway with chalk ? Have you tried adding data to see if the design is viable?
    The PK/FK fields should NOT have a default value of zero (providing. the PK field type is autonumber) - Autonumbers start with 1, so a FK field value would result in an orphaned record IF RI was not set.

    You have 4 Multi-Value fields (MVF) - I would strongly suggest getting rid of them and use tables.

  11. #11
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    Quote Originally Posted by ssanfu View Post
    Matt,
    Your question in this thread is

    You provided the dB, but where is the "master spreadsheet"???
    Steve...did the file structure not come through in the link?? I uploaded the entire folder structure and inside the SAMM folder is a directory called "source_data." Do you not see a spreadsheet in there called "SAMM-TableStructure.xlsx"? If not, I will upload it again. This spreadsheet has the table design, form design (including the path to the record sources for each), the layout of the form design and the sample data.

    Quote Originally Posted by ssanfu View Post
    I'm still struggling with your table/relationships design. Did you draw the tables/relationships on paper, cardboard, whiteboard, driveway with chalk ? Have you tried adding data to see if the design is viable?
    Again, the design is in the spreadsheet inside of ..\SAMM\source_data\SAMM-TableStructure.xlsx

    Quote Originally Posted by ssanfu View Post
    The PK/FK fields should NOT have a default value of zero (providing. the PK field type is autonumber) - Autonumbers start with 1, so a FK field value would result in an orphaned record IF RI was not set.

    You have 4 Multi-Value fields (MVF) - I would strongly suggest getting rid of them and use tables.
    I agree, the PK/FK fields should start at 1 and will do so with the correct data loaded in. Right now, you are seeing my "hodge/podge" records I threw in there and because of all my testing and creating "test-pieces and parts", the record numbers got jumbled. Again, as soon as I load in the correct data, they will start at 1.

    Finally, the 4 MVF fields are in there only as place holders. I will create tables to use as lookup tables once I get the correct data. (I am waiting on some info from my team)

    Anyway, I hope this all makes sense and thank you for your effort. As I stated before, I am no DB programmer and I "stumble" my way through creating this database. I am a quick learner, but I do not have a full grasp on "best practices" for database design. If I did, it would be much easier.

    Regardless, I do appreciate your help!

    Thanks,
    Matt

  12. #12
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yes. I can open SAMM-TableStructure.xlsx.... however, it is not much help at this point.

    1st problem:
    Quote Originally Posted by mgmercuio View Post
    I have one spreadsheet with all the field names for the column headings. When a brand new user sets up their profile inside of Access, I would like them to be able to import their working data with a single button. In other words, instead of my users having to take the data out of this one large spreadsheet (will call it the "master spreadsheet")
    Not easy, but do-able. But I would have to know/see the "master spreadsheet" - is there ONE master spreadsheet that ALL users fill out? If each user has their own "master spreadsheet" is there one worksheet or many?
    Just a lot of VBA code for verification and error handling.


    Quote Originally Posted by mgmercuio View Post
    ...I agree, the PK/FK fields should start at 1 ...
    There should be NO default value for PK/FK fields. Access handles the autonumber incrementing and the FK field should be what the PK field value is. The autonumber value is what it is.


    2nd problem:
    Need to get the tables/relationships issues straightened out. Good to know you are not going to use the MVFs.
    There are fields that are not necessary (IMO) in some tables. Maybe because I haven't got a clue what the purpose of the dB is.

    example:
    What constitutes a "Resource"? There is a "ResourcesID" FK in the table "tblAccountContacts", but there is also a "AccountID" FK in the table "tblResources"???? Is a resource for an AccountList? Or is it for an Account Contact?
    And there are FirstName/LastName fields in the table "tblResources".



    Pretend ! am a 10 year old that knows nothing about databases. Using simple, plain English, explain what you are trying to accomplish. No database jargon. So far, I think it has something to do with stores (iHop, Texas Roadhouse -Mmmmm!) but that is about it.


  13. #13
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    Ok so here it is in a very long winded explanation. Ill try to keep it as clear but brief as possible...

    We are a Internet Provider. Our customer base is quite large and because it is so large, our customers are broken down into different groups we call "verticals."

    My team's particular vertical is the Gov/Ed vertical. Meaning we sell to only Government and Educational facilities. By the way, we are "sellers." It is our company's term for salespeople.

    We use salesforce (SF) companywide to track customer sales, orders, installs, and some billing. I do not know what your knowledge level is of SF, but as you said, I will treat you like you have absolutely no knowledge of it. Anyway, ALL of the information I am trying to manipulate in my DB is found inside of SF (which is basically an online database built for salespeople). Anyway, SF does do everything that I am trying to do...but it is so spread out over many pages and many many buttons to click...plus it is very slow at times. It takes a while for pages to load sometimes. This coupled with the fact that our sales team takes data from SF and each one of us (a team of 8 sellers on my team), then takes that data applys various techniques to produce the same thing. Each week, we are responsible for various sales reporting tasks (to report to our leadership) as well as engage with our client base, "flip-rocks" and make sales.

    The company employees many different groups of teams to help with sales, order processing, technical support, financing, and many many others... There is also an internal SF team that manages SF and loads all the information in the SF database. From there the data is divided into the various verticals. Still yet from there, the customer base is broken down into each seller's "Account Module." The account module is the meat and potatoes of each seller's toolset. It contains everything you wanted to know about a particular customer in your base. But again, you have to flip through many pages and wait out long wait times for page loads to get your info.

    This brings me to the purpose of my database. I want each seller on my team to have the ability to export data from SF regarding their individual module and be able to work with it on a local database on their machine. Local DB = faster processing = easier client management = better reporting and forecasting = better prospecting = MORE SALES !

    Now like I said, SF has all the info needed...but the exporting of the data is a bit "cloogy." Meaning, I can get the data needed, but it requires exporting 3 or 4 different data sets to combine into 1 giant "Master Spreadsheet." Hence...your master spreadsheet you are talking about.

    For instance, in one export, I am able to export...

    1.Account Name
    2.Territory Parent Account Name
    3. Address
    4. Phone
    5.Fax
    6. Total MRC (Monthly Recurring Charge)
    7. Account Type
    8. Account Sub Type
    9. Sales Channel

    Let me talk a little bit about those last three categories...
    In our Gov/Ed" vertical (also referred to as Sales Channel), we have a few account types as well as sub-types...
    example:
    Account Type: Federal, State, County, City, Local, Higher Education (the universities and colleges), K-12 Schools
    In each of these account types the are also sub types...
    example: Fire, EMS, Police, Executive, Administrative, Maintenance, Medical, Developmental.

    The problem with these 3 categories is, this information is NOT exportable out of SF. Thus..the reason WHY I had the MVF field names in the tables. I have not figured out a way to get this into the SAMM db without manually setting up a lookup table. However, since there really isn't too many of these categories and given the fact that they hardly ever change...I think a lookup table will be ok.


    Ok back to the exports...
    another export I can get...

    Account Contact Person
    1. Address
    2. Phone,Mobile,Fax
    3. Email
    4. Title i.e. CIO, CFO, IT Dude or Dudette, Maintenance, etc....
    5. Role i.e. Authorized User on the account, Technical Person on the account, building contact on the account... etc.
    NOTE - In the "Role" category...each customer can and does usually engage an outside contractor at some point. This person or entity could be an IT Consultant, or Business Analyst, or System Integrator, or even a Project Manager that company has authorized them to act in their behalf. This is also where the term "RESOURCE" is defined. This is considered a customer facing external resource.

    While I am on the subject of "RESOURCES", let me also say, that my company also has internal "resources" that my fellow team members use everyday to facilitate the sale process. Remember when I said "The company employees many different groups of teams to help with sales, order processing, technical support, financing, and many many others... ?" These other departments are called "boundary partner." Example, I have 2 Sales Engineers that support me when crafting a solution to go pitch to one of my customers. These Sales Engineers are boundary partners and what I am calling an "Internal Resource." We also have finance people, Managed Service people, Construction people, and what we call "Sales Support" people. These are teams of people that their sole job is to help the seller with the backend work of making the sale.

    Anyway, the pint I am trying to make here is there is a need for a "resource" table...and maybe even a "boundary partner" table (now that I think about it a little more...). The "Resource" table or the "Boundary Partner" Table ... or whatever it should be called...or maybe there should be two separate tables...needs to be attached to the AccountProfile table.

    Another export I can get...

    1. Service Location Address (some accounts may have 100's or even thousands of service locations, while others may only have 1 or 2 )
    2. Products being used at each location. For instance we sell Phone, Internet, Managed Services, etc... So each location could have a "product" attached to it.

    Yet another export would have...
    1. Sales forecasts
    2. Current Opportunities
    3. Past Opportunities
    4. Client Documents

    Ok...so...this brings me to the SAMM_DB. I want to be able to take each of those exports and import that data in the SAMM db so our sellers can use the data to manage their account module. With all of this data, the seller can see at a click of button....How many service locations XYZ company has, what products they are currently using, what products we can sell them (a heat map of sorts)....Craft email marketing campaigns and perform sales touches to those customers easily and effectively. Use the SAMM DB on a daily basis, to make very specific and strategic sales touches to stay in front of their customers...more specifically stated...to make sales touches at the right time to the right customer in the right circumstance at the right moment.

    Other examples would include tracking which client has signed a 5 year contract that is expiring in 6 months and is coming up for renewal...or look at map and see where the majority of "unsold" products are...and create strategies to sell to that area... or generate a forecasting report for our leadership on a weekly basis.

    Trach customer issues...and become a valued "partner" for the customer by reducing these issues...

    To manage the sellers account module in my SAMM db, I was going to setup the following tables:

    tblAccountNames - Originally I was only going to have this table would contain the account name, because I was planning to use the table to create a form so I could use it to have the end use click an account name and it would populate 4 tabs on a navigation tab page all the records about said account.

    tblAccountProfile - This table would include Billing address (main address of account), phone, fax, Territory Management Parent Account Name (most of our accounts belong to a parent account name called TMParent Name), the total monthly spend for the account (MRC), the account type, subtype and sales channel. Basically a snapshot of the account

    tblAccountContacts - This table holds the contact person on the account, address, phone, email.

    tblServiceLocations - Defines all the service location addresses for each account

    tblServices - This table holds all the products we sell to our customers.

    tblResources - This table would hold all the customer resources as well as our internal company resources as well as external resources.

    I am also planning to setup a "Switchboard" with links to the following:

    1. MyModuleManager form in the database would be setup with the account name list on the right hand side as a way to list all accounts alphabetically so the end user could click the name of the account which will fire a macro or VBA script that would :
    a. Load Account Profile info on on tab
    b. Load the service location(s) for the account on the Service Location Tab
    c. Load the resource info for the account on the Resource tab
    d. Load the opportunities for the account on the Opportunities tab.

    2. "MyBusinessManager" form that will manage all the internal company specific data.
    3. "MyReportingManager" form that will manage all reports needed and do forecasting.
    4. "MyProfileManager" - manages all the personal info about the seller.
    5. "Setup New Seller Profile" = To setup a new seller upon first time loading of the DB


    I can go on and on...but I think you are getting the picture here...

    The key take-aways are...

    1. I have a tremendous amount of data that I can export from SF...but I have to do it in bits and pieces.
    2. I want to be able to get this data into the SAMM db effectively.
    3. Once everything is loaded in the SAMM db, I want to make it as easy as possible for the sellers to manipulate the data for their specific module and use it to increase sales touches, prospecting, and sales.

    Like I said earlier, this is a lot of info and it is probably clear as mud..but I hope it gives you somewhat better idea of what I am trying to do here...

    Thanks for your advice Steve...I do really appreciate it.

    Thanks,
    Matt

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry to jump in so late Matt and Steve, but have you considered accessing your Salesforce data live using ODBC?

    https://support.microsoft.com/en-us/...c-c6ac3c58f5a0

    What you're describing is very doable in an Access db, I would skip the step to consolidate the individual exports into a "master spreadsheet" and use a "master folder" instead where you would place the latest exports (csvs) for each of the Salesforce datasets then loop through all the files in the folder and import them. But this will be a static snapshot and all data entry will need to happen in Salesforce anyway, so why not try to get a live link to it.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    mgmercuio is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2020
    Posts
    27
    Vlad,

    I had not thought about using the ODBC connector. I will have to get with ou SF folks and se if it is possible. However, I do know that the copy f Access I am running personally n my laptop is Access2016...I do not know what 365plan we ar using or even if we have a plan? These are all questions that I will investigate.

    Thanks for the tip!
    Matt


    Quote Originally Posted by Gicu View Post
    Sorry to jump in so late Matt and Steve, but have you considered accessing your Salesforce data live using ODBC?

    https://support.microsoft.com/en-us/...c-c6ac3c58f5a0

    What you're describing is very doable in an Access db, I would skip the step to consolidate the individual exports into a "master spreadsheet" and use a "master folder" instead where you would place the latest exports (csvs) for each of the Salesforce datasets then loop through all the files in the folder and import them. But this will be a static snapshot and all data entry will need to happen in Salesforce anyway, so why not try to get a live link to it.

    Cheers,
    Vlad

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-20-2019, 11:38 AM
  2. Replies: 4
    Last Post: 03-16-2018, 12:37 AM
  3. Importing via VBA from Excel Spreadsheet into MS Access 2013
    By cdixon102419607 in forum Import/Export Data
    Replies: 2
    Last Post: 08-17-2017, 09:28 PM
  4. Replies: 1
    Last Post: 05-28-2014, 10:59 PM
  5. Importing Excel 2007 spreadsheet into Access 2002
    By jhjr in forum Import/Export Data
    Replies: 1
    Last Post: 06-17-2010, 02:05 PM

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