Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report using SQL (A2K)

    The following query is able to display the records when viewing it as a displayed query.
    However, when I try to use the query as a report source where I want to show the two versions of Inv_Mst_Part_No,
    [qry Inventory - Equivalents].[qry Inventory - Master].Inv_Mst_Part_No and
    [qry Inventory - Equivalents].[qry Inventory - Master_1].Inv_Mst_Part_No

    I get the following message. I would have thought that by identifying the two different Masters, that this would have alleviated this problem.

    The specified field 'Inv_Mst_Part_No' could refer to more than one table listed in the FROM clause of you SQL statement.

    SELECT [qry Inventory - Equivalents].Part_Equiv_MstrSysKey1, [qry Inventory - Equivalents].Part_Equiv_MstrSysKey2, [qry Inventory - Equivalents].Inv_Issued_UserId, [qry Inventory - Equivalents].Inv_Mst_BinLoctn, [qry Inventory - Equivalents].[qry Inventory - Master].Inv_Mst_Part_No, [qry Inventory - Equivalents].[qry Inventory - Master_1].Inv_Mst_Part_No, [qry Inventory - Equivalents].Inv_Mst_Part_Type
    FROM [qry Inventory - Equivalents];

    The variations on my problem that I fould in the forum didn't quite fit my situation. I'd appreciate any help on this.
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report using SQL (A2K)

    Try

    SELECT [qry Inventory - Equivalents].Part_Equiv_MstrSysKey1, [qry Inventory - Equivalents].Part_Equiv_MstrSysKey2, [qry Inventory - Equivalents].Inv_Issued_UserId, [qry Inventory - Equivalents].Inv_Mst_BinLoctn, [qry Inventory - Equivalents].[qry Inventory - Master].Inv_Mst_Part_No AS Master_Part_No, [qry Inventory - Equivalents].[qry Inventory - Master_1].Inv_Mst_Part_No AS Master_1_Part_No, [qry Inventory - Equivalents].Inv_Mst_Part_Type
    FROM [qry Inventory - Equivalents];

    You can then place controls bound to Master_Part_No and Master_1_Part_No in your report.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report using SQL (A2K)

    Hans,

    Thanks again for looking in and saving my programming butt. I noticed the obvious change to the SQL in that you add "AS Master_Part_No and AS Master_1_Part_No. Was or is there any way that that coding could have been implemented in the regular, non-SQL format of the query.
    I had tried Dummy:Part_No, etc. etc. and got nowhere. Never having actually worked in SQL itself, this is a new area for me to forage through.
    A day without a new Hans gem is a day without coding success.
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report using SQL (A2K)

    You can define an alias for a field in design view too. For example, click in the field [qry Inventory - Master].Inv_Mst_Part_No and insert Master_Part_No: in front of it, so that it looks like

    Master_Part_No: [qry Inventory - Master].Inv_Mst_Part_No

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report using SQL (A2K)

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> WOW! Mucho Gracias!
    Cheers,
    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •