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];
Tks
David
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