UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Cleaning/converting Old Data To Correct Format, Access 2016    
 
   
Zaddicus
post Apr 15 2019, 07:00 AM
Post#1



Posts: 122
Joined: 3-April 19
From: Cardiff


Hello genius' of the interwebs.

What I'm asking here may be simple however I've tried multiple expressions for default value

One function that I'm required to add to the database is the ability to mass import certain files from our old records system. (There are MANY files)

I've created a staging table for the old data to be stored temporarily as I was hoping Access would allow me to program a function to effectively clean up the old data and convert into the new format.

Example: One field I use is [Event]. In all the old records this is a text/string. However in the database event field is numerical as it links to tbl_Events PK, Similar with the field [TicketType] the old data is text/string and the new would be numerical linking to the tbl_TicketTypes PK.

CODE
+------------+---------+---------------+--------------+
|   Event    | EventID |  TicketType   | TicketTypeID |
+------------+---------+---------------+--------------+
| Reading Q2 |       1 | Sponsor       |            1 |
| Reading Q3 |       2 | Chargable     |            2 |
| London Q3  |       4 | Non-Chargable |            3 |
| Reading Q2 |       1 | Chargable     |            2 |
+------------+---------+---------------+--------------+


What I was hoping was possible without VBA coding was Access to lookup the 'Event' field, check tbl_Events and return the 'EventID' to 'EventID' field. This would then allow me to run the Upend query on the ammended/updated data.

I have done several google searches but they all seem to return results that require VBA, which is not the preferred method.

As always, thanks in advance
This post has been edited by Zaddicus: Apr 15 2019, 07:12 AM

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
BuzyG
post Apr 15 2019, 07:19 AM
Post#2



Posts: 417
Joined: 20-September 12
From: Cornwall UK


Why do you not wish to use code to do this?

Using VBA Record sets this would indeed appear to be a simple task.

--------------------
Live to Surf
Go to the top of the page
 
DanielPineault
post Apr 15 2019, 07:22 AM
Post#3


UtterAccess VIP
Posts: 6,671
Joined: 30-June 11



You should be able to use DLookup for this.
The general concept would be along the lines of:

EventId = DLookUp("EventID", "tbl_Events PK", "[TicketType]='" & Event & "'")
Then you could check if you got a proper numerical value or not, if not, it wasn't found (so should it be added, a message displayed, ...).
and VBA is most certainly the way to deal with this, as you can add validation controls and much more to ensure the process works properly.

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Jeff B.
post Apr 15 2019, 07:49 AM
Post#4


UtterAccess VIP
Posts: 10,254
Joined: 30-April 10
From: Pacific NorthWet


I'm not clear from your description. Are you saying that the data in question is 'typed' as text and you want it to be numeric? If so, have you looked into using the CInt() function in a query?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
Zaddicus
post Apr 15 2019, 09:02 AM
Post#5



Posts: 122
Joined: 3-April 19
From: Cardiff


QUOTE
Why do you not wish to use code to do this?

Using VBA Record sets this would indeed appear to be a simple task.


Mostly because some of the old data files have 100K+ rows of information and using VBA to set all the records would probably be a very power hungry process.

I found a way to rectify this by using form field controls with dlookup's and an updater query for the time being.

CODE
have you looked into using the CInt() function in a query?


honestly I'm still not fully confident in using queries yet (Still learning), maybe this is something I can look into in the future.
This post has been edited by Zaddicus: Apr 15 2019, 09:05 AM

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
AlbertKallal
post Apr 15 2019, 07:51 PM
Post#6


UtterAccess VIP
Posts: 2,813
Joined: 12-April 07
From: Edmonton, Alberta Canada


Ok, a few tips and points.

It is a GREAT goal to modify and update the data without writing code. In fact, 9 out of 10 times, you CAN (and should) avoid writing code.

So, we don’t want to avoid VBA because of simply wanting to avoid VBA, but if you CAN avoid code, you will save HUGE amounts of time and effort.

And in most cases you can use the query builder to do the heavy lifting for you.

QUOTE
What I was hoping was possible without VBA coding was Access to lookup the 'Event' field, check tbl_Events and return the 'EventID' to 'EventID' field.


Yes, you can do this without code, but you also REALLY want to do things this way, as it is oh so much less work!

I been around the block on data migration. I done more of this stuff then most here will ever care to do!

I done huge migrations with VBA code, and done it using no code (SQL).

And my STRONG conclusion based on my experience is to use the query builder when you can.

This is not about speed of data processing, it is about human effort and time.

And to be 100% clear, yes, sometimes you will need some code, but most surprising is how little and how often you don’t!

Ok, so let’s take this problem head on – and use Access to beat the daylights out of this task – and do it with ease and style.

You can sip your cup of tea and do this with great ease!

First:
Send the new events to the event table

You not really shared if the events will exist in the Events tables, or they will not, but no problem!

If they exist, then you can skip this, but I am including this step, since you OFTEN may have to add new ones.

So if events are already assumed to exist, then you can skip this step. However, it will check for you, and add them if they don’t exist.

The trick is to set a unique index on the text description in the target table. So ONLY new event types will be added here.

So, fire up Mr. Query builder, and drop in our staging table.

Now, double click on Event (text) column to send it to the query grid (or drag + drop).

Now, in ribbon, change the query to a append query.

And of course type in tblEvents.

We have this:




Now, just run the query. This will add the event text to the events table. Of course, you have a unique index on the Text description column for the event table, right? (So only new ones would be added).

If the event text exists, then the event will not be added.

As noted, you may well be able to skip the above step, but it still a valuable tip for data migration.

Now, let’s update our EventID column to our Event table (those PK id values).

So, we add the EventID column to our staging table (and I see you have done that already done that in above!!! – good for you!!!).

Ok, fire up Mr. Query builder, drop in our staging table.

Drop in tblEvents.

Now, simply draw a join line from Event (text) to the event text column in the tblEvents.

Drag + drop EventID to the query grid.

From ribbon, change query type to update.

Now, in the update to column just type in:

[tblEvents].[id]

You have this:



And you get great intel-sense to help you here.

You are done – run the query!!!

You now will do the same set of steps for the TicketType.

At this point, I am VERY much betting that you NOW want to send this row of data the tblBookings table.

Once again, new query and drop in staging table. Change to append and choose our booking table.

You can now append the records, but have the EventID column, TicketType column. And likely a few other columns like perhaps name, or comments or whatever. All of these columns can now be sent to the bookings table. (And you send the ID columns, not the text description columns).

You get this:



The REALLY nice part in the above is NOTE HOW the name columns were different. So I am mapping columns, and no code is required here.

So, in staging, we had FirstName, but in production table the column is FirstN.

So this mapping occurs without code – saves lots and lots of time.

The other really cool part?

Well, we can save the queries as we make them, and once they are working, then you now have a set of quires that you can simple run each time to import additional data.

So sure, often you need code, but as above shows, often you don’t.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
Zaddicus
post Apr 16 2019, 02:46 AM
Post#7



Posts: 122
Joined: 3-April 19
From: Cardiff


Albert I must say that is the most in-depth explanation of how to go about this using the query method I have ever seen. It really is great, I've followed the process step by step (Whilst making notes for future queries)

I feel the reason I didn't think about a query was because the data didn't exist on import, didn't cross my mind to use a query or 2 to validate the data and update from the database before importing. Sometimes a fresh perspective really is a great thing.

Thanks greatly for this smile.gif

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
AlbertKallal
post Apr 16 2019, 06:03 PM
Post#8


UtterAccess VIP
Posts: 2,813
Joined: 12-April 07
From: Edmonton, Alberta Canada


Thank you for the kind words.

It is really great - is it not!

I thought it was a really good idea to share this approach. I recent taught this approach to a fresh computing science graduate from university, and he loved it. (He did the slicing and dicing from the SQL studio, but it was an access front end system, and SQL back end. So, another tip was I found the SQL studio even better at this process. (But at end of day, in Access or SQL studio – it works equally well).

And sharing the approach with long time Access developers also tends to bring smiles.

I dare say that bonus of the “field” mapping without code is also a great feature of this approach. In code you have to read a zillion columns, and that is painful.

When you “finally” hooked up all the PK/FK, and you can finally append the record right into the system (such as my booking table), or say some project system, it really is amazing.

As I stated, I done tons and tons of data migrations, and I wish someone had taught me the above.

In our case, with a messy code approach, it would be about 5 days to migrate. With the SQL approach, it was 1 day of work.

And, of course, it turned out we had to migrate all over again – and again having saved the query steps made the re-migration a far better experience.

Last but not least?

Well, sometimes you will need to write code, but in our last migration, we even avoid having to do that.
We did not write ANY code with this approach!

As I stated, it not that we need or want to avoid code, but WHEN we can such as with the above approach, then it far less work.

I likely should take the notes etc. here and turn this into an article. It not an earth shattering approach but it is an approach that I wish I knew about years ago!

Good luck!

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 
WildBird
post Apr 17 2019, 12:18 AM
Post#9


UtterAccess VIP
Posts: 3,562
Joined: 19-August 03
From: Auckland, Little Australia


I have done lots of migrations, and find code invaluable. While you may be able to save some queries and run these, it is far easier to have them run by code. I am doing a migration at the moment, and have code for transformations (so within the queries themselves), for accessing data (we get data from a few different systems), and code for outputting files (1 system requires a spreadsheet of their design be populated), code for a splash screen so users can see what is happening, code for handling any errors, code for encryption of files (personal data, files need to be encrypted, and of course unencrypted), code for auditing, code for emailing of files, code for cleaning up data.

So if it is a very basic migration, with no transformations, and a very limited number of tables, then it might be viable to not use code. Have never seen a system in 25 years this basic though that didn't require some code :-)



--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
AlbertKallal
post Apr 17 2019, 04:03 AM
Post#10


UtterAccess VIP
Posts: 2,813
Joined: 12-April 07
From: Edmonton, Alberta Canada


This will all come down to what one means by reformatting data, and transforming data.

If the input data simply needs to be sent out to multiple related tables, and you have to map out fields with different names etc., then the above technique described works fine.

If by data transformation, you have say:

PersonName: Albert D. Kallal

And you need:
FirstName: Albert
Middle : D
LastName : Kallal

Then yes you will need code.

So, for the sake of this discussion, sending out of staged data to multiple related tables does not require code.

And, EVEN in the case in which re-formatting (as opposed to re-mapping), then that code should be run on the staging table, and NOT during the sending of the data to related tables.

The reason is your reformatting code will not deal with any (or as many) related tables in that code. Thus your reformatting code is going to be simplified.

I would be quite surprised if you can post sample data that can’t be mapped out using the above approach and concept.

I would 100% agree that code to re-format such data will and is often required, but no code should be required for sending this data out to the relational model.

And as for putting the series of quires in some code routine to be run? Well, this issue comes down to a one time data migration as opposed to some kind of weekly or daily importing of data.

I don’t think anyone considers an import of data the same concept or idea as a data migration project. The terms are often interchanged, but a one-time data migration project is not the same thing as a recurring import even if that import is a complex process. (the major difference is a one-time as opposed to recurring. If this is a reoccurring import, then of course extra time will be spent using some code, but for a one time, not so much effort is required.

I doubt you can post sample data that can’t be re-mapped and sent out to a relational model using the above approach. And the reason for this is that the data can be represented as normalized, or de-normalized, but “logically” such data can be represented by the (legal) relational model, else it is impossible to import such data!!!


So, I think for this discussion, the concept of re-formatting data (requiring code) as opposed to taking data and remapping columns and a single row out to multiple tables is a good concept to keep in mind.

And I still suggest that reformatting occur in the staging table(s), and the sending of the data out to the relational model should can and should use the above approach.

You wind up writing less code, and you VBA code will not be dealing with multiple related tables in most cases.

I suppose it depends on how the incoming data is (multiple tables of data, or a de-normalized row of data?). Even in this case of multiple tables, I again find the outlined approach will save time and effort.


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
kallal@msn.com
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th June 2019 - 11:36 PM