Normalising incoming data across several tables

[peskywinnets]

To set out my stall, I started with MS Access about a year ago to help me in my online business (I'm a one man business from CEO, to office cleaner...I do all roles - including coding now!).

Back when I started using Access, I simply took a bulk dump of orders each day (from a 3rd party order management system) & imported them into one hulking big table in access ...which I know makes people round these parts wince.

Ok, so the 3rd party product I was using to 'go fetch' my online orders, have hiked their prices to point where I lay on the floor & adopted the foetal position...after sucking on my thumb & pondering my options for a few days, I made the bold decision to try & 'roll my own' replacement for their system. I've bitten off more than I can chew.

Stage 1 is complete (the 'go fetch' my online orders from ebay & Amazon & pipe them into Access in a meaningful way - I had to learn about XML, VBA, & parsing XML using Access VBA etc ...Amazon's API do it significantly different to Ebay's - it was ugly...but it's now pretty much done).

So what I'm (loosely) planning is this 'retrieved order data' ends up in a 'scratch' Access table (going back 30 days in time)...I intend splitting out (normalising) the raw data in this table amongst other tables (customers, invoice, invoice items, inventory etc.), but frankly having started doing this normalising with queries...I'm ending up with a *lot* of queries & it's becoming cumbersome.

For example, just adding to the customer table, needs me to select 'unique' customers from my incoming 'scratch' table, then check if the customer exists in the customers table, if they don't exist then add them to the customers table....and finally add the customer ID back over to the scratch table the .....is my approach wrong? Should I be using VBA vs queries?

 

[jdraw]

Pesky,

I don't think this is a one size fits all sort of thing. I have had many instances where "bulk data" was received. It was multi subject, non normalized and it was necessary to use this data with properly structured existing databases. There were some special/unique things depending on the circumstances.

The approach, as you said, was import to temporary table, then run "queries/procedures" to extract the data and reformat it into designed tables. This often involves verification of codes, number, ranges etc against standard reference tables. Then use these verified staging tables as input to update/append processes. You also need to figure out how you want to handle any/all errors in the verification/reformatting process. Are these saved up and run as a single batch, or are individual transactions OK? If you have several queries and they're getting a little unmanageable, then you might consider using vba and running/executing the queries via code.
In my view you need some good documentation regarding your business and the processes. After all you're a one-man show, so you can't depend on someone else knowing/understanding what is in your head. Some logic diagrams; process descriptions; data flow diagrams; some test utilities to check/rigorously test your changes; table and field descriptions.....
If you have real customers and a business relationship, it is important to be as "professional" as possible/practical.
Commercial packages attempt to do all of the above; have support personnel etc --so since you are basically putting your business into your software and related procedures, you don't want to be guessing "what happens if" when it happens. You want to test (and retest) your code and documentation so, if and when something goes bad, it's a situation you have seen or can test.

You know your business -better than any reader - so you know the requirements. People here can help for specific issues/options.

This is a symptom "I've bitten off more than I can chew". It is your business and you know the risks.

Are there other packages?

Good luck.

 

[peskywinnets]

Thanks for all the input & advice.

Re the 'bitten off more than I can chew.....whilst true, I have until mid January to do the rest .....& things are moving at reasonable speed (thankfully, the worst part is now behind me - 'fetching' the data from Amazon & Ebay via an Access API)...so whilst what I have by then might not be slick/professional looking (that can come later), I should be able to get something deployed (though you'll probably see a fair few threads in the meantime!)

Re 'off the shelf solutions' ...there are solutions aplenty but they all charge a pretty penny & being candid, since they're appealing to the masses, the functionality I need for my particular business just isn't there (it's why i started learning access in the first place...i.e. I export all their order collected order data & manipulate how I need it). But the main reason I don't want to go with an 'off the shelf solution ' is that feeling of being owned ...it's hell to migrate away once you've gone 'all in' (my present provider knows this hence they've just cranked prices massively...& no doubt most are having to tolerate it). Also, I've had a couple of occasions where their servers have gone down & I'm helpless with orders stacking up (or they've introduced a bug that I'#m unable to address & must wait on them)....at least 'rolling my own' I'm in control of everything.

 

[jdraw]

Good luck with your project.
Don't overlook or underestimate the importance of current documentation.
And possibly some backup hardware/server/storage.

 

[static]

Should I be using VBA vs queries?

Depends what you mean really.

A stored query

currentdb.execute "myquery"

is going to be faster than running sql from VBA

currentdb.execute "update mytable set whatever=something where foo=bar"

because it's already compiled (but you wont notice the difference.)

If you mean something like this ...

do until rs.eof
   rs.edit
   rs!field1 = "whatever"
   rs.update
loop

then the answer is an absolute no!

 

[peskywinnets]

If you mean something like this ...

do until rs.eof
   rs.edit
   rs!field1 = "whatever"
   rs.update
loop

then the answer is an absolute no!

Thanks for your input .....no, I'm not doing that (I'm using just standard update queries - but an awful lot of them!!)