Kamis, 11 Februari 2016

[MS_AccessPros] Generalising a compare-two-tables tool.


A pretty common task I guess is to identify for two tables with the same structure:

  • which rows (for each table) are mismatched on the primary key; and/or
  • which rows (matched on the primary key), differ on other fields.

An example "DoubleEntry.mdb" exists in the Files section of this group (it addresses the second above).

I have a set of queries to do this job for my case.

However, I want to generalise them.  In my case, the source of my second table will change from time to time. 

My second table comes every quarter as a .csv file.  My first will generally be the same table (or query) in my Access database.

I've identified two places where I could introduce variability:

(i) using aliases.  My SQL at present uses a short name for both tables.  I can re-associate the second table by changing its "AS" referent. 

(ii) using the linked table association.  I have linked my .csv with a name "UK_NSW_Affiliates_Staging", which has bears no relationship to the path or name of the file itself.

Years back, in a similar situation, I think I used VBA to re-string the SQL so that the alias referent (the object of "AS") was alterable via user selection or input.

I'm just wondering whether I can save myself that effort.  The thought I had at one time was to re-assign the linked table name, but I can't see a straightforward way to do that.  (My gut feeling is that it's a bit wrong-minded, anyway.  I shouldn't be relying on the link manager when I might want to compare two native tables at some other time.)

Maybe the question is: what's the neatest way to achieve this end?  If I can avoid re-stringing SQL, good.  If I can't, what's best there?  Put a token in the SQL text property and replace it at will?  Anything  you can share - abstract or concrete - appreciated!

Sample Query:

(UK_C is an alias, currently assigned to UK_NSW_Affiliates_Staging.
UK_NSW_Affiliates_Staging is a linked table, which points to a .csv file.

I could have multiple linked tables, and alter the referent of the alias UK_C.

Alternatively, I could keep this SQL unaltered, and instead change where "UK_NSW_Affiliates_Staging" links.

I'm now biased towards the former, for reasons explained above. 
But is there a more elegant solution to repointing this table than searching and replacing the SQL "UK_NSW_Affiliates_Staging" within "UK_NSW_Affiliates_Staging AS UK_C" ?
And, if not , do you elegant code within this context?)


SELECT UK_C.MemberKey, NSW_C.[Member Key], UK_C.Organisation, NSW_C.[Corporate entity], UK_C.fullname, UK_C.Address1, UK_C.Address2, UK_C.Address2b, UK_C.Address3, UK_C.PostCode, NSW_C.[Street 1], NSW_C.[Street 2], NSW_C.Suburb, NSW_C.State, NSW_C.InvoicingStreet1, NSW_C.InvoicingStreet2, NSW_C.InvoicingSuburb, NSW_C.InvoicingState, NSW_C.InvoicingPcode, UK_C.Phone, NSW_C.[Home ph], UK_C.Mobile, NSW_C.Mobile, UK_C.Email, NSW_C.email, NSW_C.InvoicingEmail, UK_C.Country, NSW_C.Country
FROM UK_NSW_Affiliates_Staging AS UK_C INNER JOIN TrueMembersExtendedCorporate AS NSW_C ON UK_C.MemberKey = NSW_C.[Member Key];




Posted by: david@powell-au.net
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)



Tidak ada komentar:

Posting Komentar