Results 1 to 13 of 13
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Remove "aliases" from queries

    Experts:



    Attached database uses a dual listbox (on form) which is linked to three queries.

    Two of the three queries (Q50A_BilletsToWorkingGroups; Q50C_BilletsToWorkingGroups) use **aliases** for the tables?

    Does anyone know how to remove the aliases (and use actual table names) w/o losing the form's existing ability to move items across the two listboxes?

    If so, what's the syntax of the 2 queries "Q50A_BilletsToWorkingGroups" & "Q50C_BilletsToWorkingGroups" w/o aliases?

    Thank you,
    EEH
    Attached Files Attached Files

  2. #2
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    You can't avoid alias of qm "table".
    In Design View, select the "tables" and just replace the aliases in Property Sheet with the names that you want.

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    I tried replacing the SQL below

    Code:
    SELECT q.WorkingGroupIDpk, 
           q.WorkingGroup, 
           qm.WorkingGroupIDfk
    FROM T11_WorkingGroups AS q 
    LEFT JOIN 
    (SELECT T10_JunctionTable_BWG.BilletIDfk, 
            T10_JunctionTable_BWG.WorkingGroupIDfk 
    FROM T10_JunctionTable_BWG 
    WHERE T10_JunctionTable_BWG.BilletIDfk=Forms![F50_BilletsToWorkingGroups]!BilletIDfk) AS qm 
    ON q.WorkingGroupIDpk = qm.WorkingGroupIDfk
    WHERE (((qm.WorkingGroupIDfk) Is Null));
    with

    Code:
    SELECT T11_WorkingGroups.WorkingGroupIDpk, 
           T11_WorkingGroups.WorkingGroup, 
           T10_JunctionTable_BWG.WorkingGroupIDfk
    FROM T11_WorkingGroups  
    LEFT JOIN 
    (SELECT T10_JunctionTable_BWG.BilletIDfk, 
            T10_JunctionTable_BWG.WorkingGroupIDfk 
    FROM T10_JunctionTable_BWG 
    WHERE T10_JunctionTable_BWG.BilletIDfk=Forms![F50_BilletsToWorkingGroups]!BilletIDfk)  
    ON T11_WorkingGroups.WorkingGroupIDpk = T10_JunctionTable_BWG.WorkingGroupIDfk
    WHERE (((T10_JunctionTable_BWG.WorkingGroupIDfk) Is Null));
    I must assume I made a mistake somewhere in the conversion... upon executing, I receive the error "Syntax error in JOIN operation". What am I missing?

    Thanks,
    EEH

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    "mq" cannot just be replaced with a just table name because "mq" is a query (sub query ie a "virtual table").

    So I created a new saved query named "qryT10_J_BWG". The SQL for this query is
    Code:
    SELECT T10_JunctionTable_BWG.BilletIDfk, T10_JunctionTable_BWG.WorkingGroupIDfk
    FROM T10_JunctionTable_BWG
    WHERE (((T10_JunctionTable_BWG.WorkingGroupIDfk)=[Forms]![F50_BilletsToWorkingGroups]![WorkingGroupIDpk]));
    Then I deleted the sub query SQL from the query "Q50C_BilletsToWorkingGroups" and replaced every instance of "mq" with "qryT10_J_BWG"
    Code:
    SELECT T01_Billets.BilletIDpk, T01_Billets.RA_BIN AS RA_BIN_NUMBER, T01_Billets.RA_Billet_Title, qryT10_J_BWG.BilletIDfk
    FROM T01_Billets LEFT JOIN qryT10_J_BWG 
            ON T01_Billets.BilletIDpk = qryT10_J_BWG.BilletIDfk
    WHERE (((T01_Billets.RA_BIN)          Like "*" & [Forms]![F50_BilletsToWorkingGroups]![Search2] & "*") AND ((qryT10_J_BWG.BilletIDfk) Is Null)) OR 
          (((T01_Billets.RA_Billet_Title) Like "*" & [Forms]![F50_BilletsToWorkingGroups]![Search2] & "*") AND ((qryT10_J_BWG.BilletIDfk) Is Null));

  5. #5
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Paradoxically, Access accepts a table name as a query alias.

    SQL def for "Q50A_BilletsToWorkingGroups":
    Code:
    SELECT T11_WorkingGroups.WorkingGroupIDpk, T11_WorkingGroups.WorkingGroup, T10_JunctionTable_BWG.WorkingGroupIDfk FROM T11_WorkingGroups 
    LEFT JOIN (SELECT T10_JunctionTable_BWG.[BilletIDfk], T10_JunctionTable_BWG.[WorkingGroupIDfk] FROM T10_JunctionTable_BWG 
    WHERE T10_JunctionTable_BWG.BilletIDfk=Forms![F50_BilletsToWorkingGroups]!BilletIDfk)  
    AS T10_JunctionTable_BWG 
    ON T11_WorkingGroups.WorkingGroupIDpk = T10_JunctionTable_BWG.WorkingGroupIDfk
    WHERE (((T10_JunctionTable_BWG.WorkingGroupIDfk) Is Null));
    SQL def for "Q50C_BilletsToWorkingGroups":
    Code:
    SELECT T01_Billets.BilletIDpk, T01_Billets.RA_BIN AS RA_BIN_NUMBER, T01_Billets.RA_Billet_Title, T10_JunctionTable_BWG.BilletIDfk
    FROM T01_Billets LEFT JOIN (SELECT T10_JunctionTable_BWG.[BilletIDfk], [T10_JunctionTable_BWG].[WorkingGroupIDfk] FROM T10_JunctionTable_BWG 
    WHERE T10_JunctionTable_BWG.WorkingGroupIDfk=Forms![F50_BilletsToWorkingGroups]!WorkingGroupIDpk)  
    AS T10_JunctionTable_BWG 
    ON T01_Billets.BilletIDpk = T10_JunctionTable_BWG.BilletIDfk
    WHERE 
    (((T01_Billets.RA_BIN) Like "*" &  [Forms]![F50_BilletsToWorkingGroups]![Search2] & "*") 
    AND ((T10_JunctionTable_BWG.BilletIDfk) Is Null)) 
    OR (((T01_Billets.RA_Billet_Title) Like "*" & [Forms]![F50_BilletsToWorkingGroups]![Search2] & "*") 
    AND ((T10_JunctionTable_BWG.BilletIDfk) Is Null));
    In my system, both of them works well.

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    ssanfu - thank you for chiming in... I appreciate the assistance, but ultimately I will have to go with accesstos' solution.

    accesstos -- your solution is superb! Thousand thanks for helping me out on this one. I truly appreciate it.

    <br>

    I am wondering if I could follow up w/ a question (slightly beyond the original post). If you prefer, I will gladly open a new post. Please let me know either way. Anyhow, here it is...

    1. Attached .zip file contains two databases. "Version1" (v01) includes the two updated queries (w/o aliases) and the form works great. Fantastic!
    2. Now, database "Version 2" (v02) is a copy of v01 but includes two additional tables: [T00_JunctionTable_OBS] & [T01_StaffMembers]

    Here's how I envision the process:
    - Given the updated queries w/o aliases, it was easier to tie table "Staffmembers" (having employees) into the query #3.
    - Basically, in the form, it would be great to see which employees (assigned to a billet/job) should be assigned to a working group.
    - Keep in mind, not all of the seven existing billets/jobs have an employee though. Thus, in this example database, position "Scheduling Assistant" and "Vice President" are vacant. The form ** prior to any WG assignments ** displays the information properly.
    - However, if I were to select all 7 billets/jobs in the left listbox and then move them into the right listbox, the two (2) vacant position disappear from the view in the right listbox.

    My question:
    How should the queries and/or VBA modified so that I can show the employees assigned to a billet/job which will help making when making assignments to working groups? Naturally though, I don't want to lose the vacant position in the form view.

    Thank you for any additional assistance in advance.

    Cheers,
    EEH
    Attached Thumbnails Attached Thumbnails Image1.JPG   Image2.JPG  
    Attached Files Attached Files

  7. #7
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi EEH!

    If I have understood correctly, you have to change the type of joins in the query of the right listbox as below:

    Click image for larger version. 

Name:	F50_BtoWG_Query.JPG 
Views:	35 
Size:	36.7 KB 
ID:	39340

    or, if you prefer in SQL view:
    Code:
    SELECT T01_Billets.BilletIDpk, T01_Billets.RA_BIN AS RA_BIN_Number, T01_Billets.RA_Billet_Title, T01_StaffMembers.LastName, T10_JunctionTable_BWG.WorkingGroupIDfk
    FROM T01_Billets INNER JOIN ((T10_JunctionTable_BWG LEFT JOIN T00_JunctionTable_OBS ON T10_JunctionTable_BWG.BilletIDfk = T00_JunctionTable_OBS.BilletIDfk) LEFT JOIN T01_StaffMembers ON T00_JunctionTable_OBS.StaffMemberIDfk = T01_StaffMembers.StaffMemberIDpk) ON T01_Billets.BilletIDpk = T10_JunctionTable_BWG.BilletIDfk
    WHERE (((T10_JunctionTable_BWG.WorkingGroupIDfk)=[Forms]![F50_BilletsToWorkingGroups]![WorkingGroupIDpk]))
    ORDER BY T01_Billets.RA_BIN;
    Cheers,
    John

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    accesstos -- YES, YES, YES!!! The modified SQL for right listbox works great.

    Please forgive me for asking one final question:


    Right now, the two vacant positions (Scheduling Assistant and Vice President) do *not* show a last name in the 3rd column (either in left or right listbox). Is there any way the "blank last names" could be replaced with, e.g., "[Vacant]" in the SQL for the right listbox and, I think the query, for the left listbox? If that's possible, what would the new SQL be?


    Again, I truly thank you for your help thus far!!!


    Cheers,
    EEH

  9. #9
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    EEH, you're welcome!

    About your question, just set the Format property of LastName in T01_StaffMembers to @;@;"Vacant"
    or, if you want, in addition, a color indication:@;@;[Red]"Vacant"

    Good luck with your project!

    Cheers,
    John

  10. #10
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    John... that's is absolutely beautiful how easily you solved this question. Very much impressed!!!

    Thousand thanks!!!!!

    EEH

  11. #11
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    John - again the form for billets to working groups work great. I now replicated the same process for "billets to UJTL".

    As I'm getting a VBA error, I posted the updated database and description at the following URL:
    https://www.accessforums.net/showthr...630#post436630

    Would love to have you have a look at identify where I missed making changes in the copying process of queries, form, and/or VBA.

    Thank you,
    EEH

  12. #12
    Normapow is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    1

    Remove "aliases" from queries

    I have a few userobjects bound to C objects. In a few instances, I need to be able to remove them completely from GameMonkey and C at the same time. I had planned on just throwing something in the destructor to accomplish this, but Im unsure what I can use in GM to remove the actual userobject and thus nullify any references?Anybody know what I have to do? ;-)

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Normapow,

    First, welcome to the forum.
    Next, you have posted your question under someone else's thread. (BTW, this is known as "HI-jacking" a thread). You will probably not get any responses because very, very few people will see your post.
    You need/should to start you own thread, in an appropriate forum.

    At this point, I am not sure what your question has to do with MS Access....

    Good luck.....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-08-2018, 08:18 AM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 4
    Last Post: 04-03-2015, 05:53 PM
  4. Replies: 2
    Last Post: 02-27-2015, 10:03 PM
  5. Replies: 4
    Last Post: 07-12-2014, 02:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums