How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask NewITZone Your Own Question

NewITZone
NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
53509759
Type Your Microsoft Office Question Here...
NewITZone is online now
A new question is answered every 9 seconds

I need a 3 excel spreadsheets converted to a single MS

Customer Question

I need a 3 excel spreadsheets converted to a single MS access database, can you do this?
Submitted: 6 months ago.
Category: Microsoft Office
Expert:  Jason Jones replied 6 months ago.

Hello,

Yes, I can most definitely do this for you. May I ask for the Excel spreadsheets to be uploaded?

Please, upload it on the following page and give me the link or just upload it on this page: http://www.megafileupload.com/

Thank you,

Jason

Customer: replied 6 months ago.
OK, ill upload the spreadsheets, but i need some advice on creating the database, I want to basically have a single place to store all my customer info, including their purchase history. So to keep the fields manageable, i read that separate tables could be used for say, customer name and addess, email, phone, and another table for customer ID, purchased equipment, etc, and these can be made relational, is this still within your scope?
Customer: replied 6 months ago.
Lastly, I want this to be collaborative for 5 - users, ( we have been attempting to do this with excel and dropbox, which is less than ideal) so either Office 365 or using OneDrive to host the back end files
Customer: replied 6 months ago.
We basically have 3-4 people who are constantly adding and editing these sheets, so I hope to avoid conflict by using a database rather then excel + dropbox, to make it possible for multiple users to edit simultaneously ( not the same record obviously) not sure the difference between all the MS options in this regards,
Expert:  Jason Jones replied 6 months ago.

Thank you for the clarification. Once I receive the documents, I will begin.

Customer: replied 6 months ago.
I uploaded them both ( only 2 not 3) 30 min ago, I can send a dropbox link if you did not get them
Expert:  Jason Jones replied 6 months ago.

Hello,

You have to give me the download links. I do not see the downloads links on this page.

Thank you,

Jason

Customer: replied 6 months ago.
8
Expert:  Jason Jones replied 6 months ago.

I see two excel documents. Was there only two or were there three?

Expert:  Jason Jones replied 6 months ago.

There are a multitude of different tabs in the documents. Which tabs/sheets do you want import into the MS Access Database and how do you want the database setup? Please, give me a few examples.

Customer: replied 6 months ago.
the tabs on the document INTERNAL are just to separate out the different customer types, based on customer ID, which made it easier to sort through when using Excel, the tabs can go away on that spreadsheet
Customer: replied 6 months ago.
The other shreadsheet I will split up, its basically customer name , email and mailing address, combined wiht billing info, the billing info should be a separate database table but linked, the same with the tab called sharefile, let me sort that out first
Expert:  Jason Jones replied 6 months ago.

Is there confidential information on those documents? The license key item appears as though it may be. Please, unshare the Dropbox documents, if they are confidential.

Customer: replied 6 months ago.
Not confidential, but I'd rather the customer contact information not be made public or shared, but it's not PII in any way, shall I unshare those?
Expert:  Jason Jones replied 6 months ago.

Yes, please unshare them as I have already downloaded them.

Customer: replied 6 months ago.
OK doing that now, I am re-doing the second database with all the customer contact info as well, to remove duplicated fields or un-needed info, so please wait on that one.
Expert:  Jason Jones replied 6 months ago.

Thank you.

Customer: replied 6 months ago.
Hi Jason, Im ready with the other spreadhseets if you are avialable
Expert:  Jason Jones replied 6 months ago.

Yes, I am available.

Customer: replied 6 months ago.
Hi Jason, firedrill for another project, im going to have to wait until Monday, but I can upload at least one or two other spreadsheets tomorrow, woudl you prefer to do this on Monday ?
Expert:  Jason Jones replied 6 months ago.

Hello,

I work seven days a week, everyday, so we can continue on Monday or your choice of time.

-Jason

Customer: replied 3 months ago.
Hi JasonI finally have the excel spreadsheets up to date, there are 2 of them, can I upload them some place, then have you take a look and make some recommendations on setting them up as the starting point for an Access database, we are using the Office 365 version of Access and want to host the back -end in the cloud so multiple people can work on it
Expert:  Jason Jones replied 3 months ago.

Hello,

Please, upload the documents.

Please, be forewarned that the site is not secure:
- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"

Customer: replied 3 months ago.
I just discovered a few more errors I need to correct, will send tomorrow, sorry for the fire drill, also is there a secure way I can send you the files, such as ShareFile, where I can control the times downloaded?
Expert:  Jason Jones replied 3 months ago.

Thank you

Customer: replied 3 months ago.
Hi Jason, I have the spreadsheet, can I send you a link to download it?
Expert:  Jason Jones replied 3 months ago.

Yes, please.

Customer: replied 3 months ago.
Here is the ShareFile link,https://aidd.sharefile.com/d-sb6890cf551f41cfa
Expert:  Jason Jones replied 3 months ago.

Thank you. I have the document. Please, explain to me now what exactly you need done to this document.

Expert:  Jason Jones replied 3 months ago.

If this document has any confidential information in it, please remove it from the server.

Customer: replied 3 months ago.
I want to use this as the base table to start an Access database for all our customer data, the anchor points are the customer numbers and the license keys- you helped us create them about 6 months ago - we have another spreadsheet that has detailed customer info - addresses , email etc. I want to be able to add other tables to this, but right now we are in the process up changing a lot of the KEY information on this sheet, and it is impossible to do on DropBox with Excel, since only one person can work on it at a time, hence, in Access we cal collaborate. There are tabs in this just to help us keep organized but other than the notes, all the tabs should be about the same. I guess can you look this over and tell me if there are any problems with the way we have it organized to add more tables later with additional infomation
Expert:  Jason Jones replied 3 months ago.

As it stands, your data looks well organized and for this reason, you shouldn't have any issues importing the data into an access database. At this point, what do you want me to do with this? Do you want me to create the access database for you and import the data?

Customer: replied 3 months ago.
Could you do that, and add the following 2nd spread sheet as a table in the same database,This is the detailed customer infohttps://aidd.sharefile.com/d-sf8e992e02544e5eb
Customer: replied 3 months ago.
One last thing, I just checked, on the tab 'customers that have not replaced' on INTERNAL the columns should have the same headings as the tab 'replacement keys,
Name Customer # Original Key Replacement Key Please note any actions herethe most important being Column E is Original Key and column F is Replacement Key
Expert:  Jason Jones replied 3 months ago.

I am sorry, but I will not be able to do this for you.

For this reason, I am opting out of the question and allowing another expert the chance to help.

The next expert will see all that is on this page, so there will be no need to repeat anything.

When another expert picks this up, you will be notified by email.

Thank you for your patience.

- Jason

Expert:  The-PC-Guy replied 3 months ago.

hi, i see the last person you were working with was unable to help.

Since I do not have any of the files, I will need to see those to be able to help.

Also note I do charge additional fees @$75 per 1/2 hour. I do not know how long this might take until I see the data and what you want to do.

Please have some patience you may have to re-explain a few things.

Now I am going to assume that once the DB is set up, you will only import this information once, and then when you get it all in access start adding any new information directly in the DB skipping excel all together. The reason for this is that the importing depending on how the tables are set up can be quite complicated.

also for collaboration, remember that even though the db is shared that only one person can work on any given table at a time as access locks it when someone is working on it, and it will only become available once that person closes it.

Customer: replied 3 months ago.
Hi jasonwhat happened? I thought you could do this?
Expert:  The-PC-Guy replied 3 months ago.

my name is ***** *****

as i said

hi, i see the last person you were working with was unable to help.

Since I do not have any of the files, I will need to see those to be able to help.

Also note I do charge additional fees @$75 per 1/2 hour. I do not know how long this might take until I see the data and what you want to do.

Please have some patience you may have to re-explain a few things.

Now I am going to assume that once the DB is set up, you will only import this information once, and then when you get it all in access start adding any new information directly in the DB skipping excel all together. The reason for this is that the importing depending on how the tables are set up can be quite complicated.

also for collaboration, remember that even though the db is shared that only one person can work on any given table at a time as access locks it when someone is working on it, and it will only become available once that person closes it.

Customer: replied 3 months ago.
Hi PC guy, thanks for taking over, I was not aware of the additional charges, I will have to check first so please bear with me
Expert:  The-PC-Guy replied 3 months ago.

ok, no problem.

The additional charges would just be to compensate me for my time. And of course the original cost of the question would be deducted from that total.

Anyway, just get back to me when you are ready to proceed.

Customer: replied 3 months ago.
OK we are good, I can send you the ShareFile link to look at the two spreadsheets I want to start with, I can probably firgure out the rest as I go along - got the book - but I just want to make sure the initial inport is correctIn the Excel sheet - Database, if there is any info that needs to be split up - like first name, last name, address, can you help set that up too? There will be a few more tables that I will want to import and add later but these two are the start
Customer: replied 3 months ago.
Here are the fileshttps://aidd.sharefile.com/d-s59da3a70ade419bb
Customer: replied 3 months ago.
Im going to be AFK for 30-40 min, can you take a look at the files and let me know then, and I can answer any other questions that you might have?
Customer: replied 3 months ago.
Hi are you still there?
Expert:  The-PC-Guy replied 3 months ago.

looked that the link, but it says there are no files.

Customer: replied 3 months ago.
Sorry I forgot to upload them, they are there nowhttps://aidd.sharefile.com/d-s1fe3bc9512c440fb
Expert:  The-PC-Guy replied 3 months ago.

ok, got the files, so please tell me what you want this DB to do

Customer: replied 3 months ago.
THis will be the start of our CRM database, customer names, numbers, license keys, emails, update type, and eventually equipment typeThe 'anchor points' are the customer Codes and License Keys, each system that uses our software has a unique key and that is how we need to track them.These two spreadsheets are the start, I want to be be able to add more tables in the future
Expert:  The-PC-Guy replied 3 months ago.

i'm looking at the data and I am a little confused. I assume you would have 1 table of customer information, then another table of keys,

but I am not entirely sure how they relate, it seams from the data that the same info is on like 10 different sheets, or maybe it is different names, I didn't look at it too carefuly.

But you have 2 columns current and new key. Does this mean you want to have 2 columns in the DB, or does this need to keep track of all keys historically for example if customer A has 10 keys there would need to be another table that would list all 10 of customer As keys.

Customer: replied 3 months ago.
All the tabs are there just to make look up easier, they sort the types of customers. Here is a photo of how I whiteboarded the desired end result. 75% of our customers only have one system/one key. the rest have multiples. I will need a column added to the INTERNAL sheet, next to the customer code, that is SYS, which will serve to differentiate which system for a given customer has which key, the values in this will be something like PC1 or TAB1 ( PC or Tablet)So Customer Name - CAS-001 --TAB1 -- (key)The column with Current Key - Replacement key is only important for the next 3-4 weeks ( and why I need the database ) since as you can see, a large number of our customers are getting their old keys replaced and trying to keep track of this using excel and dropbox is not working - once all the old keys have been replaced, I will delete that extra column - but for now I need itAs we replace the keys, if a customer had a replacement key, we have been cutting and pasting the replacement into the current key column, so if there is no replacement, it means either it has already been replaced of did not need to be.
Expert:  The-PC-Guy replied 3 months ago.

so what is the ultimate functionality going to be.

You just want to set up a bunch of tables and manipulate data in excel?

Do you want to have entry forms in the db?

are you going to need to create reports?

The way I see this happening, is you will have one table for customer data. With some kind of customer ID#, if you do not already have ID#s for each customer maybe we do autonumber.

Then a second table for keys, that is a 1-many. So each customer can have 1 key or 10 thousand it doesn't matter. If you want to keep track of the most recent, then maybe a timestamp field, then setup a report that will select only the most recent date for each customer.

As far as updating the old keys with new keys, we could set up a query to do that, It would be best to wait until you have changed them all though, then run the query once.

Customer: replied 3 months ago.
Ultimate goal is to STOP using excel for this and just have everyone use Access, I will create web forms, I just want to make sure there are no conflicts or issues when I initially set up the database, I want to be able to add tables later with more info, all tied to the customer number, but want to be able to look up things, like addresses and emails, by customer nameWont need the 1-many as we hope to get granular enough to have a value for SYSTEM, like, PC1 or TAB3, after the customer code, each with a unique key
Expert:  The-PC-Guy replied 3 months ago.

so this is going to be web based? Not just in access?

Customer: replied 3 months ago.
I plan to host the .mdb file in either Dropbox or OneDrive and allow multiple tech support clients with Access locally to edit, and with a web form for the sales team
Expert:  The-PC-Guy replied 3 months ago.

ok, my appoligies. Since I am totally lost at this point, I will open your question to others.

Customer: replied 3 months ago.
Forget the comment, yes, web based
Expert:  Bruce Wilner replied 3 months ago.

I can do this for you.

However, there's no magic wand to wave.

I first have to have a good look at your spreadsheets so as to understand what you're trying to make permanent and how such things are related to one another.

I presume what you're looking for is table definitions, ideally a script that with automatically create and populate your new Access database?

Related Microsoft Office Questions