Tables - Can I? (1 Viewer)

This is my very 1st post - probably of quite a few!
Before I start my first database I would like to know the answer to this question if anyone can help and I am sure its pretty basic.

I have various excel spreadsheets going back to 2008 when I started itemising some expenditure data I start a new sheet every year. However during the period 2008 - 2017 I may have added in new information into different columns than I previously collected at the beginning back in 2008.

My question is this:-

Can I upload all of these files into individual tables and then be able to create some sort of relationship between them that will allow me to extract the data I may be looking for?

2008 - may have columns A-H
2007 - May have columns A-M

I know I cannot append the table as each spreadsheet may have extra columns etc that don't look the same as the one and when I have tried it throws all sorts of errors.

I wonder should I get all the spreadsheets exactly correct before uploading?

Thanks

You may do that or you may have a temp table with the additional colimn you have and import everything. Your final table will only have the basic column you need.

Often times importing is messy: external data is rarely ever cleaned up the way we really like. You can take any approach really: clean it all up in excel first, them import, or import into temporary tables, clean those up, then move it to final tables, etc.

My general preference is to import the unscrubbed data into Access in temporary tables, use queries to clean it up, then move it along to it's final destination. If I have an existing database that's already a fairly solid project and it's a one-time import, I'll often create a separate Access DB file specifically for this (just so as to not clutter my main file).

You can do a lot of nice scrubbing with queries: even more if you can write raw SQL (which I highly recommend, though tough for beginners to swallow). What you can't do in queries alone, some basic VBA functions to do things like parse out phone numbers and strip unwanted characters out of fields, validate email address and discard junk data, etc. Which is to say, Access is a great tool for this job (we work in some fairly sizable projects with SQL Server backends and all sorts of various applications in .NET connecting to it, and Access is still my go-to tool for one-offs of this type).

Cheers,

Good advice for the process above, I just want to hammer home the overriding idea:

Ultimately you should have only 1 table that houses all years' data.

As part of the process that may mean that you load individual years' data to their own table temporarily, but in the end and moving forward it should all get into 1 table.

Jack

I know exactly what you mean about the importing being a bit messy.

That's whats got me asking this question as nothing so far has seemed straight forward and the old brain matter is just getting going again!!

And I'm trying not to get too frustrated.

When you make a temporary table to sort out and make your corrections etc. Do you then just copy and rename that table to its final place when you have finished?

For the purpose of this first database I might just tidy up the excel files so everyone matches the titles and fields So I can then append in the future to the table.

Thanks as that's saved me a bit of time getting started.

Thanks for your help and advice much appreciated.

Tracey

That's not quite the process, no... what plog mentioned is very important: the final table should be normalized (if you're not familiar with the concept, that'd be square 1: read up a bit on it).

In a normalized table, you will not have one table per year: rather you will have one table that houses everything, then you will use queries to get data per year.

So, with that said, the final destination tables (one table with all related information, for all years), often looks very different from the temporary tables.

The temporary tables tend to take the shape of the data being imported: each temp table will basically match whatever excel info you're bringing in. Your job then is to use queries to shape it correctly for inserting into the final, normalized table.

This can be done with a fairly simple insert query. I don't use the Access query designer, so not sure how to show it there, but if you can follow a tad bit of SQL, consider the following:

a) make a query based off your imported, unscrubbed data. The query will scrub the data and shape it so it can fit into your final table. Something like this:

(or whatever).

Save that query as "scrub1" let's say. Now, scrub1 reads from your temp table, cleans everything up and gives you a nice result.

b) take that scrub1 result and insert it into your final table:


Then your data is moved to the final table, and you can discard the temp table and scrub1 query, and import the next year: rinse and repeat.

Again though I'll reiterate: plog's mention of all yearly data going to a single table is highly important. Please do look into normalization and understand the concepts as it is extremely important, and failure to do so will cause you months of wasted time and effort (not to sound like too much of a miser, but it is that important). Here's a bit of ref: https://dymeng.com/put-stuff-where-it-belongs/

Cheers

TTbelle said:

For the purpose of this first database I might just tidy up the excel files so everyone matches the titles and fields So I can then append in the future to the table.

Click to expand...

Despite my last post (which covers end to end), this sounds like a sane approach. Sometimes migrations can be huge jobs (I have a brother in law that's been working on one for Unilever for two years), and taking it in chunks like this can keep us from getting overwhelmed. Just be careful you don't start developing the rest of the Access application against this "middle" state of data.

Thanks Jack again for this sound advice given I will make a note of everything said and plod on as they say shouldn't take me to long to make everything OK as I'm fairly reasonable on excel. Lets just see shall we how it goes!!

I usually link to the external file and then use an append query to clean up the data and append it to the permanent table. I do use the intermediate temp table occasionally when I have more clean up to do than can easily be done with a query. The trick with this is to import the data into a temp database so you don't clutter up your FE or BE with temp tables. Remember Access cannot recover space from deleted objects unless you compact the database. Whether you import rows to a temp table and then delete the rows or delete and recreate a table, the ultimate problem is the same, Access acquires new space causing the database to grow each time you do this. Compacting cleans up the debris and shrinks the database back to its pre-import size.

One alternative not previously mentioned; Have you thought about the design of your table(s) as the end state first? Decide what you need to track, what is required, what are parent/child records, and what you will need for your outputs.

Once you know what you want to achieve you can start looking at how to import into this structure. Think of it as "I'm trying to build the cart (access database) BEFORE I go get the horses (excel spreadsheets) to pull it." Yes, this is one time to put the cart before the horse.