none
VBA, query for linking two tables with a boolean true value for mail merge with a form letter RRS feed

  • Question

  • Mail merge

    I’m working with Office 2016 and developing a teaching system.  I’ve two tables shown below

    Table: tbFPA_Assignment_with_data

     

    Table: tblTeachTT

    I need to do a mail merge with those fields (e.g. F2_1B1, F2_2A2, F2_3A1, ..., etc.) with True value in table of “tbFPA_Assignment_with_data” and linking them to a table “tblTeachTT” to get values from DtTeach and SSubGp.

    For example,

    Field F2_2A1 in table “tbFPA_Assignment” has a true value, I need to link it to the table “tblTeachTT” and get its corresponding value of “29-Dec-16” from DtTeach and “9A” from SSubGp from “tblTeach”

    Is there any VBA and query methods to accomplish it.

    Thank you in advance.

    NT100

     

    Friday, December 2, 2016 6:41 AM

All replies

  • Hello,

    And what's the relation to SSAS = SQL Server Analysis Services, the topic of this forum? Is that a fact/Dimension table from SSAS?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, December 2, 2016 7:02 AM
  • The problem is that your tbFPA_Assignment_with_data table is very badly designed.  By having multiple columns of Boolean data type it is 'encoding data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1).  This requires that all data be stored as values at column positions in rows in tables, and in no other way.

     

    What you appear to have is a binary many-to-many relationship type between the entity type modelled by tblTeachTT, of which TsRef appears to be a candidate key, and some other entity type of which T_Ref is a candidate key.  The correct way to model a binary relationship type like this is by means of a third table which resolves it into two one-to-many (unary) relationship types.  This table would thus contain two foreign keys T_Ref and TsRef, each referencing the keys of the two tables which model the related entity types.

     

    You'll find an example of how to automatically recast an incorrectly designed table like this into a correct model as UnencodeColumns.zip in my public databases folder at:

     

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

     

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly. 

     

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

     

    This little demo file illustrates the methodology for doing this in two scenarios, one where the values are Boolean, as in your case, the other where they are quantitative values.

     

    With a correct model it is then simply a matter of joining the three tables in a query.


    Ken Sheridan, Stafford, England

    Friday, December 2, 2016 11:47 AM
  • I've redesigned  tbFPA_Assignment_with_data table (new table name: tblFPA_Assignment) as follows

    Any further suggestions.

    Thank you for your help.

    Tuesday, December 6, 2016 7:02 AM
  • Hi NT100,

    Could you share us your source data and target result with column names in Excel? It might be easy to make a test that we could try to create the table in Access with the data.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 8, 2016 1:44 AM