source to target

Is it possible to use source to target in Microsoft Access?
vbnetcoderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't understand what you mean. Can you explain further?
vbnetcoderAuthor Commented:
Basic ETL stuff. Within access i have to take stuff from a staging table, transform it (lookups, verify format etc) and then load it into a target table that can be reported on.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Ah ...  then if you're asking if Access has any builtin tools that would do that, the answer is no - you have to build them yourself.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

vbnetcoderAuthor Commented:
hmmm that is what i figured.  How do you suggest i do this sort of ETL? I am planning on creating a bunch of queries that i will call from the VBA
PatHartmanCommented:
Queries are the simplest solution unless you have some complex multi-record processing to handle.
vbnetcoderAuthor Commented:
Most of the stuff is pretty simple.... verifying that required fields have data, making sure certain fields have valid data where i will verify in a lookup table (for example US states), concatenation of fields etc.  I think there might be some stuff that is more complex then that but that sums it up pretty good i think.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Queries and basic code logic is what I typically use, regardless of the platform. If you just have a handful of rules - no more than 10 - 15 - then often you just write code blocks that handle these. If you have more than that, I'd consider setting up a table to hold the rules, along with a method to order, prioritize, and set their severity level (along with what happens if they fail the tests - do you abort, or just continue and report, etc).
vbnetcoderAuthor Commented:
OK great. That helps.
Jeffrey CoachmanMIS LiasonCommented:
It is not clear if you need help with any of the specific coding, (or if I am understanding your question correctly)
...But, in any event, ...here are two very simple examples of some validation loops:
'Checks for null values
Dim strMsg As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT ID, YourRequiredField, YourStateField FROM YourTable WHERE Isnull(YourRequiredField)")
rst.MoveFirst
Do Until rst.EOF
    strMsg = strMsg & vbCrLf & "ID: " & rst!ID & ", is missing data."
    rst.MoveNext
Loop
MsgBox strMsg

Open in new window

'Checks for non-matching values
Dim strMsg As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT YourTable.ID, YourTable.YourRequiredField, YourTable.YourStateField FROM YourTable")
rst.MoveFirst
Do Until rst.EOF
    If IsNull(DLookup("State", "tblStates", "State=" & "'" & rst!YourStateField & "'")) Then
        strMsg = strMsg & vbCrLf & "ID: " & rst!ID & "'s value of: " & "'" & rst!YourStateField & "'" & " is invalid or missing "
    End If
    rst.MoveNext
Loop
MsgBox strMsg

Open in new window


Obviously you can do things like; load a table with the results, ...etc

JeffCoachman
vbnetcoderAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for vbnetcoder's comment #a40986926

for the following reason:

ty
vbnetcoderAuthor Commented:
ty
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.