Database Design

Lets say I've been charged with creating a database for Consumer Price Indexes for Canada (and its provinces) and the US. There are 12 sources that we extract data from for one of our reports and each source provides data differently. For example, A major bank will provide quarterly CPI data while another source like Statistics Canada will provide monthly values. Statistics Canada will also break down the CPI into all of its little baskets, which we also want but the banks and other sources do not.

My question is, do I make 1 database to accommodate them all (with different data structures) or do I create multiple databases to accommodate each source? In Excel we use workbooks for each source so that is why I have to ask. Please let me know if I have expressed this clearly enough.

Kindest Regards:

Michael

Hard to say without seeing the data, but I think I'd use one database - I'd make separate tables to import each source's data, then set up queries to get the data into the format that you want. I'd assume you'd want it all in the same format, in which case I'd set up one table that would hold the formatted results from all the other tables.

So it'd be something like this:

Major Bank Table --> Query --\
Statistics Canada Table --> Query -->--> Master Table Holding all data.
Other source(s) --> Query --/

I'd clear each source table whenever you get fresh data, import the data into the source table, and then run an append query to format the data and place it into the Master Table.

When I say format, I'm not talking about how the data looks, but rather putting it into what ever organization you want, be it based on dates or whatever.

Does that make sense?

Ok, so a table for each source you say. The way I have my tables set up (soon to change) is:
tblSources

• SourceID(PK)

• Source_Name

• A couple more attributes

tblRegions

• RegionID(PK)

• Region_Name

tblEconomic_Variables

• Eco_Var_ID (PK)

• SourcID(FK)

• RegionID(FK)

• Eco_Var_Name

• Qtr_Val

• Mth_Val

tblEconomic_Variables is where I get confused because of the different data provided from different sources. Based of what you see, should I still create tables for each source?

Well, the different table for each source would just be the table into which the raw data is imported into Access. If the raw data all follows the same format, then you wouldn't need a separate table for each.
If one source gives you five columns of data, but another gives you ten columns, obviously you are going to need different tables to import them into.

This would help then to get your data into tables shown above. If one source provides quarterly data, but another provides only monthly data, you obviously can't use the same query to get them from an imported table to your Economic Variables table.

It does look like you did a good job setting up normalized tables.

I completely understand what you are saying Dave but I have to ask, do you think I should have just one flat table then for each source instead of the three I have? Something like:

tblStatsCan
• Eco_Var_ID(PK)
• VSeriesNumber
• Source_Name
• Region
• Mth_Val

It's very hard to say without seeing your data, and may be subjective anyway. You want to try to minimize the amount of data that is stored in multiple places. If you use one flat table, are your Source Names and Regions going to repeat a bunch of times?
On the other hand, too much normalization, while good in theory, in practice can be a beast to maintain. I created a database about half a year ago where I made it as normal as possible. It was as close to fourth order normal as could be. However, now it takes me forever to maintain and make user requested changes, and I've realized I should have simplified it (and had duplicate data) because the user's wanted changes so often.

Anyway, I'd probably have 1 regions and 1 sources table that holds all regions and sources data for all the different sources. Then I'd have a table for each source where the only thing that table does is temporarily hold the data from the source when you import it into the database. Lastly, I'd have one master table that hold all data permenantly. I'd use separate querys to get the data from the import tables into the master table.

If you want to upload a database, maybe just leave one record (scrubbed of private data) in each table, and I may be able to get a better idea of what you're looking at.

Thanks Dave, I have a database created but the tables are all empty at the moment. I'll need a bit of time to add some data to them but I will.

Ok Dave I thought I would take a different approach to showing you my data. I have uploaded an excel file with the data that is extracted from Statistics Canada (don't worry, all data is public). The very last sheet ("Other Sources(BMO)") is from one of the banks we extract from. As you will see, they are very different. My thinking right now is to have a database specifically for data from Statistics Canada, another database for banks, and then a final database for 4 other forecasting agencies. What do you think?

That sounds good, but are you going to be able to compare data between the databases? Or is that not your aim?

The aim of the user is two fold. 1, create corporate quarterly economic analysis reports, and 2, do compare analysis for inter-departmental reports.

I don't know why but uploaded this database makes me feel like a nervous nelly! I've never created a database before that didn't have to do with student enrollments in university or playing with Northwind. At any rate, here it is Dave (or anyone else). I have two tables, a Regions table and a Statistics_Canada_CPI table. I have populated the Regions table but now I need to populate the Statistics_Canada_CPI table but as seen in the previous Cdn&ProvCPI.zip file that I uploaded, there is a lot to load. My question now becomes, how do I do this?

Sorry, I didn't have time to look at this yesterday, but I should today. What is the variable name in the Statistics_Canada_CPI table going to hold? is that Column B in the spreadsheets? How would you determine the region for all tabs BEFORE MBCPI?

Hi Dave and thanks for your patience. You are right, the variable name is from column B in the spreadsheets. The region for all tabs before MBCPI would be RegionID 1, Canada.

Is the setup of my tables off? Something seems a miss to me but being new to this, I cannot pinpoint what I am feeling on this.

I think it looks ok. I'll set up how I would import the data real quick, then you can compare it to how you were planning on doing it and see what works better and why.

I was worried that I would have to enter each value individually so your way will be best I'm sure. I tried to import the data but the Primary key index in table Statistics_Canada_CPI wouldn't auto index doing it that way.

Ok, here is how I would do it. It may not be the fastest or best way, but using this method has certainly worked for me.

First task is to get your Excel data in a format that is friendly for Access to import and work with. I created this simple macro to do that. Put this macro on a standard module in your workbook, and save the workbook. Don't run the macro, Access will run it for you.

Please Login or Register  to view this content.

Ok, I tried like crazy to upload the database, but I can't seem to do it. Here is the added code:

Please Login or Register  to view this content.

I had to use the Excel tab names to identify which province the data was coming from, so I had to add a table to link the tab names with the province ID numbers, see a screenshot. I also added a table to temporarily hold the imported data, as I described earlier. This captured all the data in your workbook except the Other Source (BMO) data. You'll want to either alter the Excel macro or define a new Access import to help you get that info into Access. I've also commented the Access data pretty well so you should know exactly what is happening at each step.

Here is the Query:

Please Login or Register  to view this content.

Let me know if you have any questions.

Hi Dave, I've been working on this for a bit now and keep getting a "Type Mismatch" when trying to run the query you provided. I know what that means and found some differences in the tables and changed them but I still get the error and everything appears proper (to me) but obviously I am missing something. At any rate, I re-uploaded the file. I am going to study this and also read a couple of books that I have to try and figure this out but I don't know if I will solve this little problem before you can respond. Thanks.

Michael

There is just so much to learn!

There is no data in your tbl_Import table. You need to run the Access code to populate the tbl_Import table with data.

Hi Dave, I just figured that out and the code is running right now

Because there is so much data being transferred, this taking a while! I am excited right now You are awesome!

You know you are a geek when you get excited over something like this!

The following line from the Access Sub is popping an error (field 'F1' doesn't exist in destination table 'tbl_Import.'

Please Login or Register  to view this content.

and I don't know how to rectify that.

What's taking so long is the Excel code. It has a lot of sheets, rows, and columns to loop through. One thing to keep in mind is that Excel VBA errors, when the code is called from Access, won't jump out at you like they usually do. If you do encounter an Excel error, you will probably want to see the workbook so you can fix it. You need to use the immediate window in the Excel's vbe and type:

Please Login or Register  to view this content.

This will make the Excel application visible. If you were to terminate the code in Access before the Excel application was closed, there would be an invisible Excel application running on your computer. This has happened to me many times, and you have to use the Task Manager, Processes tab to close it.

Originally Posted by Mordred

I don't know how to rectify that.

Check your Excel file: the front sheet should be the ForImport sheet, and there should be five columns, set up the same as the five columns in your import table.

Originally Posted by davegugg

Check your Excel file: the front sheet should be the ForImport sheet, and there should be five columns, set up the same as the five columns in your import table.

So I need to creae a sheet called ForImport with the 5 columns?

Yes, there are 5 columns there and the sheet was already created by your code.

The code will not get past the error in the outlook module

Please Login or Register  to view this content.

When I ran it with your latest database you posted, I found the Canism header on the Exce rate sheet did not match the Cansim field name in the table. Those have to match. The workbook you posted earlier says Cansim, so we should stick with that. I've edited the Excel code in post #17 above. Try putting that new code in and see if it works.

Originally Posted by davegugg

You need to use the immediate window in the Excel's vbe and type:

Please Login or Register  to view this content.

This will make the Excel application visible. If you were to terminate the code in Access before the Excel application was closed, there would be an invisible Excel application running on your computer. This has happened to me many times, and you have to use the Task Manager, Processes tab to close it.

I don't know how to access the immediate window while the files a) originally close and b) when the code is running because I cannot access it while the code is running.

Well, I don't know if you need to use that now, but if Excel encounters and error, you will be given the option to Debug. Once you do that, you will have access to the immediate window.

Was this knowledge of yours learned through trial and error or have you buried your nose in many books to know this? Probably both I guess.

Mostly trial and error. I have the wrox Access 2000 Programming Book, which is an excellent reference. I also have a great teacher here at work.

Well, its taking a lot longer now and so far there have been no errors so mayhap its going to work! I have a book called Hands-On Microsoft Access:A Practical Guide to Improving Your Access Skills but it doesn't go over any code except for a bit of SQL. I think it may be a little too basic for my needs and I may get my manager to buy me something a little more...appropriate for me.

I don't know how the newer wrox books are, but this one is outstanding. Very readable, very in-depth, very thorough. I'd strongly recommend it. The only thing about instructional books nowadays is you can usually find anything quicker on the net than you can in a book.

Error: Type mismatch in expression

Please Login or Register  to view this content.

is the new error.

Alright, we have to get this done before the weekend. Hurry Dave, find the answer!

LoL, no worries Dave!

I wish I could analyze this and help figure out the problem but until I get a few of these under my belt you are alone (and I'm very lucky to have your help)!

The line I gave you with the error is 3 away fromteh end Almost there!

There are also over 540 Type Conversion errors that have been created in their own table.

Found it! In the query design view, you have the Regions table linked to the wrong place on the tbl_RegionTransfers. Regions.Region_Abbreviation should be linked to tbl_RegionTransfers.Region.

So SQL should look like this:

Please Login or Register  to view this content.

Also, in the Access code, switch this line:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

Originally Posted by Mordred

There are also over 540 Type Conversion errors that have been created in their own table.

When I worked on the spreadsheet you posted earlier, I only had 4...

K, trying it now!

Dave, you are a genius and I could kiss you (but I won't)!

However, the Cansim isn't loading the Vseries, it is loading number values for some reason but it is so darn close it's not even funny!

Ah, the problem is with the Excel macro. I didn't notice that the V number is in column 4 on one sheet, and column 3 for the rest of them. My previous code tried to always grab it from column 4. Try this instead:

Please Login or Register  to view this content.

I think this is kind of a sloppy fix, but as long as your template stays the same it will run the fastest.

The other way would probably be to search and find the Cansim cell on each sheet, then refer to the column it is in when finding the v numbers. If you Cansim column ends up switching a lot on future spreadsheets, you would want to use that method instead. Let me know and I can show you how I'd set it up.

I wonder if named ranges would make a difference. I tried on the weekend to rework the Excel Sub but to no avail. For the data extraction from the source, I use countable cansim-named ranges as well as countable dates-named ranges. Regarding the cansim for instance, the ranges are named MBHCansim1, MBHCansim2,...MBHCansim21. Dates = MBHDates1,MBHDates2,...MBHDates21. Using the MBHCansim's I was able to load all of the cansim numbers onto the "ForImport" sheet with the following code

Please Login or Register  to view this content.

As you can see, this loads the Cansim name, the Variable name, and the Region name but now I am stuck trying 1)understand your code here

Please Login or Register  to view this content.

and how it can be applied to what I have done. Is there middle ground?

Originally Posted by davegugg

Ah, the problem is with the Excel macro. I didn't notice that the V number is in column 4 on one sheet, and column 3 for the rest of them.

That is because of how the data comes in on the first sheet, there is one extra column added to the table to the left of Cansim. It cannot be removed on that one sheet (user requirements) but yet it is not necessary in the database (user requirements). I can have it removed I guess and tell the user that it has to be in order to create consistency within the workbook.

For this workbook, and considering that it is now being dumped into a database, should I extract all the source data straight into the "ForImport" without populating all the other sheets? Once it is in the database, the running of my other code for extraction seems redundant. I think I just answered my own question.

Alright, I removed the one column and am using your original code. Hopefully this works. Thanks to you I have learned that there is no room, at all, for inconsistencies when dumping mass amounts of data into Access from Excel.

This seems strange to me, In the Statistics_Canada_CPI table, from row 1635 to 40319, the cansim numbers still do not exist, even though they do in tbl_Import. tbl_Import is properly loaded from excel so that part is now 100% but the final dump is slightly off. I looked in the SQL statement but it seems to be alright. Any clue? Should I re-upload?

Regarding the use of named ranges, I'm not very good at that, so I'm afraid I can't really weigh in on whether or not they could work better. Basically, you need to get all the data into those five columns on the ForImport sheet. How you do it doesn't matter too much, aside from the fact that some ways will take longer to run than others.

Here is the code with comments to explain:

Please Login or Register  to view this content.

All the j's you see are the current column that is being looped through. All the i's are the current row being looped through.

I don't know why the cansim numbers would not exist, you can upload your db if you are not able to figure it out, and I'll see if I can.

I guess I'll have to try and figure this one out on my own because I cannot upload the file (zipped it is 7,000 + KB while not zipped it is 45,000 + KB).

Thank you for the explanation of the code by the way.

Heck ya I think you've hit pay-dirt with this Dave and thank you. What I did was I deleted all of the records in the Statistics_Canada_CPI table and then re-ran the qry_TransferImport query. Now everything has been added properly. The autonumber is at 400,000 + but that is because of all the times I have run the query. If I remember my basic studies from scholl, that cannot be changed (unless I start from scratch right?). Thanks a whole bunch Dave, I couldn't tap your scales enough to convey my appreciation.

Now comes the learning regarding clever querying.