Results 1 to 7 of 7
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Queries on Top of Queries? (2003)

    Thanks to much help from this forum, qryGoodToMail is a somewhat complex query that I can use as the data source for a Word mail merge document. I've tested it extensively, and it does exactly what I want it to do. The records in qryGoodToMail are a subset of those in tblAllLocated. The fields are a subset of those in tblAllLocated, plus two calculated fields based on the contents of fields in tblAllLocated.

    I want to do just a little bit more.

    I have another query, qryLastContribution, whose source is NOT tblAllLocated. I want to add some of its results to qryGoodToMail. The common field for both tables is Handle. Any Handle appears only once in either table.

    qryLastContribution has one entry for every record in tblAllLocated. Its fields are Handle, Date and Amount. Many of the Date and Amount fields are blank.

    What I want to do is add the Date and Amount fields from qryLastContribution to the results of qryGoodToMail (note that the former has more records than the latter).

    I could probably do this myself if I converted both queries to tables, then did an update query on tblGoodToMail. I've got a feeling I don't need to do that, but I don't know for sure. My book on making queries still hasn't arrived.

    Who can help?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    Re: Queries on Top of Queries? (2003)

    Open qryGoodToMail in design view.
    Select Query | Show Table or click the Show Table button on the toolbar.
    Activate the Queries tab.
    Select qryLastContribution and click Add.
    Close the Show Table dialog.
    Join tblAllLocated to qryLastContributed on Handle.
    Add the Date and Amount fields to the query grid.

    You now have a query that "adds" data from qryLastContribution to qryGoodMail. No need to store Date and Amount in tblAllLocated, unless you have compelling reasons.

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Queries on Top of Queries? (2003)

    Good, but now I have an error message. The names of queries are different and more complicated than the names I used in my example. The error message is:

    The specified field '[Last]' could refer to more than one table listed in the FROM clause of your SQL statement

    There is a Last field in both tblAllLocated and OKqryContributionsDateAndAmountOfLast.

    Here's the SQL view of the query. I've got a feeling that the troublesome "Last" is in the IIF statement:

    SELECT tblAllLocated.Status, tblAllLocated.Subclass, tblAllLocated.HomeCity, tblAllLocated.HSt, tblAllLocated.Address, tblAllLocated.Address2, tblAllLocated.Address3, tblAllLocated.Country, tblAllLocated.WifeNow, tblAllLocated.Email, tblAllLocated.Plankowner, tblAllLocated.Shareholder, tblAllLocated.ServiceClass, tblAllLocated.Service, tblAllLocated.VerifiedBy, tblAllLocated.VerifyDate, [VerifyDate]<#1/1/2006# AS OldOne, tblAllLocated.SolicitationGroup, tblAllLocated.Handle, tblAllLocated.Last, tblAllLocated.Business, tblAllLocated.First, tblAllLocated.Middle, tblAllLocated.Suf, tblAllLocated.Hon, IIf([Middle]="(n)" Or [Middle]="[Unknown]",[Business] & " " & [Last] & " " & [Suf] & " " & [Hon],[First] & " " & [Middle] & " " & [Last] & " " & [Suf] & " " & [Hon]) AS Formal, tblAllLocated.Salutation, tblAllLocated.City, tblAllLocated.State, tblAllLocated.ZIP, tblAllLocated.Phone, tblAllLocated.YearOn, tblAllLocated.RankRateOn, tblAllLocated.Assignments, tblAllLocated.YearOff, tblAllLocated.RankRateOff, tblAllLocated.DOB, tblAllLocated.PostNavyCareer, tblAllLocated.OtherDutyStation01, tblAllLocated.OtherDutyStation02, tblAllLocated.OtherDutyStation03, tblAllLocated.OtherDutyStation04, tblAllLocated.OtherDutyStation05, tblAllLocated.OtherDutyStation06, tblAllLocated.OtherDutyStation07, tblAllLocated.OtherDutyStation08, tblAllLocated.OtherDutyStation09, tblAllLocated.OtherDutyStation10, tblAllLocated.OtherDutyStation11, tblAllLocated.OtherDutyStation12, tblAllLocated.OtherDutyStation13, OKqryContributionsDateAndAmountOfLast.Date, OKqryContributionsDateAndAmountOfLast.Amount
    FROM tblAllLocated INNER JOIN OKqryContributionsDateAndAmountOfLast ON tblAllLocated.Handle = OKqryContributionsDateAndAmountOfLast.Handle
    WHERE (((tblAllLocated.Status)="Living") AND ((tblAllLocated.Subclass) Is Null) AND ((tblAllLocated.Address) Is Not Null))
    ORDER BY tblAllLocated.Status, tblAllLocated.Last, tblAllLocated.Business;
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    Re: Queries on Top of Queries? (2003)

    First determine from which table you want to use the Last field. For example, of you want the field from tblAllLocated, change all non-prefixed occurrences of [Last] to

    [tblAllLocated].[Last]

    If any of the fields Business, Suf, Hon, First or Middle occurs in both tblAllLocated and OKqryContributionsDateAndAmountOfLast too, do the same for their non-prefixes occurrences.

  5. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Queries on Top of Queries? (2003)

    Thanks again.

    I chose a non-technical solution, both for ease of implementation and for freedom from having to remember what I did to the SQL code:

    I decided I didn't really need Last and a few other fields in OKqryContributionsDateAndAmountOfLast, so I just took 'em out. ;-)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    Re: Queries on Top of Queries? (2003)

    If you don't really need them, this is actually a much better solution! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Queries on Top of Queries? (2003)

    It normalises / normalizes my database, too. ;-)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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