Trouble making links between tables once imported from Excel (1 Viewer)

Chrisp87 · Aug 22, 2019

Hi everybody

I am new to Access and have been asked to take an Excel workbook with several tabs (each with around 10 fields) and put that data into an Access database instead. To make my question easier to understand, I have attached an example of the Excel template, rather than me butchering the explanation by trying to write it out instead!

I have looked around the forum and elsewhere for an answer and have not found one yet - so I am sorry if it is here somewhere and I haven't seen it.

Where I am running into trouble is that, although each of the county worksheets in the spreadsheet has fields in common (State, county) etc., they are not unique.

I am wondering what is the best way to set up an Access database with this excel data. I assume the tables can be linked and some duplication between each of them removed as the counties and states and product hierarchies are the same in each table. This is why it is supposed to be a better way of storing the data than in Excel.

Hopefully somebody can help. Let me know if this needs explained further. Thanks so much!

theDBguy · Aug 22, 2019

Hi. Welcome to AWF! Is this a one-time exercise? Meaning, are you trying to convert an Excel workbook into an Access database?

The_Doc_Man · Aug 22, 2019

The question asked by my esteemed colleague theDBguy is important. How often will this have to be done? A once-off or a repeatable process?

I'm going to suggest that you read up on a certain topic. You have admitted being new to Access and further that you are coming from Excel. This typically leads to issues that are easily addressed if you just study a couple of topics.

First, Access is a relational database, which works best when you have normalized tables. So study this topic: "Normalization". If you use this forum's SEARCH feature (menu bar near top of page, 3rd from right), read articles on normalization. There is also the idea of searching the web for articles. If you do so, you must search for "database normalization" because there are similarly named topics in chemistry, politics, statistics, and a few other broad-brush disciplines.

The reason I suggest your reading topic is because you showed us a sample of data that is almost guaranteed to be ripe for normalization. Your first three tabs appear to have the same structure and at least a few of your later tabs have similar structures to each other (though different from the first tabs). While not always guaranteed to require some type of merging and coalescence, similarly structured tables that seem to share a common purpose often DO lead to some normalization efforts.

Chrisp87 · Aug 23, 2019

Thanks both for the quick reply!

Yes it’s a one time exercise, and I’m converting the excel workbook into an Access database. I have the tables into access, though currently the database is far larger than its equivalent in Excel - which is why I am trying to make it a relational database. Otherwise I would assume there would be no advantage in storing the tables without relationships in Access vs. storing them in excel?

isladogs · Aug 23, 2019

Yes you definitely need to use normalised tables in Access.
This recent thread https://www.accessforums.net/showthread.php?t=77963 includes a useful approach to converting your tables using Excel Power Query / Get and Transform feature which is available in versions 2010 and later.
See posts 9-13 in that thread

Chrisp87 · Aug 23, 2019

Thanks. PowerQuery does work - as per the thread you directed me to. I am able to unpivot so that the multiple year columns become one, and their values go in the next column.

Am I right in saying that needs to be the case before I can normalise the tables in Access using the 'table analyser'?

Problem I have run into here though is that the number of rows is 3 million+ in my actual dataset once unpivoted - which is more than I can get onto an excel sheet before importing to Access.

arnelgp · Aug 23, 2019

I did a Remove duplicate on your first sheet and found none.
that means your sheet is normalized.
only thing to do is convert it to table in access.
it is up to you if you want to break the table into smaller ones, ie, county, state, product1, product2..., etc.
on my opinion its better to leave it as is.
it is much easier to update, than having many tables to update.

isladogs · Aug 23, 2019

Answering on my phone so can only give a partial answer.

Excel has a row limit of just over 1 million though Power Query can handle more. So you could try exporting direct from PQ (untested) or split your original data into 3 or more batches (possibly use the range selector for this)

There are other methods of dealing with transposed data but this is probably the simplest in my opinion

Sent from my iPhone using Tapatalk

Chrisp87 · Aug 23, 2019

Ok thank you arnelgp - so you think best to leave as is - and just directly import to Access in the same structure as it is in Excel?

Thanks,

Chris

isladogs · Aug 23, 2019

Disagree with arnelgp.
Just because there are no duplicates does not mean the data is normalised.
Your data is fine for Excel but totally unsuitable for use in Access in its present format.

Sent from my iPhone using Tapatalk

arnelgp · Aug 23, 2019

if it will make your updating painless, why not.
you will need a lot of code when you break it into a "Normalized" way.
first you need a way to link each small tables.
then the updating? how?

CJ_London · Aug 23, 2019

I agree with Colin.

Although you could use the data 'as is' it will be cumbersome and relatively slow plus you will be into redesigning when you add a new year. You haven't explained what you are going to do with the data but you may also find some calculations difficult and excruciatingly complicated using an excel data structure in a relational database.

Your example data is not very clear since it is pretty much the same data in each table but in principle I assume that 'sales county' is effectively an extract from 'sales national' basing state and county names on the region and district values?

Better to create the tables as required then import and append.

Better still may be to go back to the original source data and import that instead.

arnelgp · Aug 23, 2019

if you save the table as is, your update query (one field) is something like:

Code:

'table1 is your imported sheet sheet1 is the new sheet you want to get the updat
update table1 set field1 = sheet1.field1
from table1 as t1 
   left join sheet1 as t2
on t1.[region] = t2.[region], 
   t1.[district] =  t2.[district], 
   t1.[state] = t2.[state], 
   t1.[county] =  t2.[county]
   t1.[product hierarchy 1] = t2.[product hierarchy 1],
   t1.[product hierarchy 2] = t2.[product hierarchy 2]
   t1.[product hierarchy 3] = t2.[product hierarchy 3];

on a "Normalized", you will build a bigger query just for one field to get updated.
you'll need a lot of "Joins" to the smaller table (region, county, product, etc..)

Chrisp87 · Aug 23, 2019

Thanks guys for your suggestions. It is a one time thing - and the tables are supposed to have the same structure in terms of geographies and hierarchies.

I split the first one out into 5 blocks and unpivoted the years. I attached what I have done for the first (county sales) tab. Does this look right?

I then imported the first block, and appended the other 4 and the record count checks out.

I guess my question now is - is having the tables in this format any better than what I had before?

@arnelgp - are you saying that the tables were fine the way they were because they are easier to update?

Thanks again for the suggestions so far. Really appreciate it.

Chrisp87 · Aug 23, 2019

Although with the tables imported and appended the file is more than 1GB in size.... which seems pretty unusual?

CJ_London · Aug 23, 2019

suggest compact and repair - will probably reduce the size. It depends on what you are going to do with the data but you may want to index certain fields for filtering and sorting purposes otherwise you will have a slow performance.

Normalising the data will probably reduce your file size by 70-80% and significantly increase performance.

So whether to normalise or not - it's your app, you decide.

Chrisp87 · Aug 23, 2019

I would just like to have the database structured in the most efficient way - and if it keeps the file size down, then that's even better.

Now that I have my seperate year columns into one, and values into another column - can anyone suggest what is the best way to split the table using the 'analyse table' tool, based on the revised template I attached in the last post?

Thanks!

Chris

CJ_London · Aug 23, 2019

Before making suggestions, the data you have imported is from Sales_County_Norm? What about stock, orders and returns?

Also, I note each state has county1, county2 etc - is this valid data or do the county names vary state by state?

isladogs · Aug 23, 2019

You’ve made it clear this is a one off operation so far better to spend a little more time now and do it right.
Normalising will reduce file size as already stated. It will also make ALL your queries easier to construct and faster to run.
Not doing so will mean you continually need to modify tables and queries to add extra columns e.g. for each additional year which will add significant time and effort unnecessarily

As I’m not in front of a computer I’m unable to view your Excel file or advise on specific fields at the moment

As already stated it’s your database and your choice on how to proceed. I hope you make the right choices for the long term.

Good luck

Sent from my iPhone using Tapatalk

arnelgp · Aug 23, 2019

this is what you need to do:
1. make a backup of your excel first.
2. from your template.xlsx, copy sales_county_norm to a new sheet.
3. select the new sheet.
4. highlight region column up to productivity hierarchy 3 column.
5. on the ribbon, data->remove duplicate.
6. this will be your Master table to be exported/imported to access.
7. from access, import this sheet.
8. now import the Template.xlsx as "Link table"
9. create the crosstab query.

I have made a sample db for you.
Link your own Template.xlsx by going to the Ribbon->External Data->Link Table Manager.
check the sales_county_norm.
check also, "Always prompt for new location"
find your Template.xlsx.

after successfully updating the link, run the two queries.

Trouble making links between tables once imported from Excel (1 Viewer)

New member

Attachments

I’m here to help

Immoderate Moderator

New member

MVP / VIP

New member

..forever waiting... waiting for jellybean!

MVP / VIP

New member

MVP / VIP

..forever waiting... waiting for jellybean!

Super Moderator

..forever waiting... waiting for jellybean!

New member

Attachments

New member

Super Moderator

New member

Super Moderator

MVP / VIP

..forever waiting... waiting for jellybean!

Attachments

Similar threads

Users who are viewing this thread