linking Excel sheet to access database

Question

Hi All,

I am really new to word of Access, here is what i am trying to do.

i have a master table in access, which is imported from Excel sheet, and its is very huge data for excel to handle, 

I am trying to create a database for my company, what I wanted to do, i get the reports in different excel sheets from various vendors on daily basis, as I said before I have a master sheet which  needs to update from excel on daily basis and run a query to show the required output

How do I do it now.. help me with it,,

Regards

samuel

Answer 1

Simple answer. You LINK to the Excel sheets and run Append queries to add the data to your Master.

However, If you Master table is a single table based on an Excel spreadsheet then it is highly likely that it is not normalized properly. Can you show us what the field structure of the master looks like? Can you give us an idea of what the table info is capturing?

Answer 2

Hey scott,

Thank you for your reply,

what i want to do is.

1. i need to create a master sheet with all the information in the column (Table 1)
2. i will create more tables with different names, for each vendors we have lets say about 15 tables. ( on daily basis we will receive a rate sheet for each items we buy from them, and that rate sheet will have updated rates for items based on the market value )

2.1) here is my task i need to update this rate sheets in access. we get rate sheet in CSV format Or normal Excel.xlsx format from our vendors.

2.2) once i update this new sheets in access i want those updated rates to reflect in master sheet.

3. once master sheet is updated i will run a query to show up the updated price in new table.

if you would like to take look at the file which i am try to create, let me know so that i can share it with you, if you can help me with a email address.

i hope i am bit clear in explaining my task, kindly help me on this, if i dont get this done i will have to loss my job..

Thanks in advance.

have a great day.

Answer 3

First, you need to read up more on Normalization. This is a set of rules that govern the design of a relational database. I see at least three places in your list of goals that show you are planning a denormalized structure. The foundation of an efficient database is the table structure. That needs to be corrected before you go further.

1) i need to create a master sheet with all the information in the column (Table 1)

Access does not have sheets, it has tables. Access is not a spreadsheet but a relational database. Data tables that work fine in Excel do not work in Access because they are not likely to be normalized. 

2) i will create more tables with different names, for each vendors we have lets say about 15 tables. 

Again, you are thinking spreadsheet. Its normal in Excel to create different sheets for different classes of data. Its in correct in Access. You should have ONE vendors table. You definitely do not have multiple tables with the same structure, but different names to indicate different vendors. 

2.2) once i update this new sheets in access i want those updated rates to reflect in master sheet.

And yet again, this is the wrong concept for Access. You do not need a "master sheet". This can be done with a query.

So what I get from your posts is that your task is to take the data files you get from vendors with price changes, input that into Access so the Vendor records reflect the current prices. Very doable, but it needs to be done right. We can certainly help you get it right, but we need more info.

What you need to explain further is what are these prices for? Does each vendor have a list of items that they price? Do you need to maintain a history of prices or just what the current price is? What else is the Access app going to be used for (orders, inventory???). What type of business are you in. Please help us help you.

Answer 4

Thanks for your reply scott,

So what I get from your posts is that your task is to take the data files you get from vendors with price changes, input that into Access so the Vendor records reflect the current prices. Very doable, but it needs to be done right. We can certainly help you get it right, but we need more info.

You are absolutely correct Scott, that is what I wanted to do,

What these prices are for?

Prices are calculated as per call basis, these prices are in Cents and less than a cents.

0.15
0.1338
0.3213
0.2975
0.3123
0.1448
0.0406
0.1723
0.18
0.1888
0.161
0.181
0.1857
0.134
0.9865
0.1655

Does each vendor have a list of items that they price?

Yes we have about 15 vendors with different prices for based on country, below is the screenshot

screenshot 2

Do you need to maintain a history of prices or just what the current price is?

I need to run a query to show the updated price on daily basis.

What else is the Access app going to be used for (orders, inventory???).

Only price inventory,  

below is the main table (in my understanding its my master sheet as per EXCEl)

1st column is out **primary key,**as per the vendor its is called as country code/area code/T

below is how my main table to look like,

What type of business are you in?

We into telecom industry, basically deals with prices on per call basis for all the cites in the country.

if you can help me with a option to send the files so that you have look at the file and guide me accordingly

Answer 5

First you didn't make it clear whether you need a history or not. Second, can you explain what the fields in your Master represent?

You should have a table like this:

tblRates

RateID PK autonumber

VendorID FK

Destinations

Rate

EffDate

CountryCode

Areacode

Currency

When you get the price sheets, you link to them and run an Update query to update tblRates with the current rate.