Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Missing Records-Wrong Query Results (97)

    1. I have created a base query as the foundation for a CrossTab Query.
    2. The base query works fine - the results are what is expected. I have 114 records come back that have a blank FirstName and blank LastName fields.
    3. When I run the Cross Tab query - only 9 records come back with blank fields.

    What - where are the others? There is nothing going on. It is a simple Base query and then using the Base query to create the Crosstab query.

    I hope this is enough info.

    Here is the code for the Base query:
    SELECT tblPeople.MemberID, tblPeople.MemberWholeName, tblPeople.Telephone1, tblPeople.Company, tblPeople.Signature, tblAnnualAppealHistory.Year, tblAnnualAppealHistory.Amount, tblPeople.LastName, tblPeople.FirstName, IIf(IsNull([LastName]),[Company],[LastName]) AS LastNameOrCompany
    FROM tblPeople INNER JOIN tblAnnualAppealHistory ON tblPeople.MemberID = tblAnnualAppealHistory.MemberID
    ORDER BY tblPeople.LastName;

    Here is the code for the Crosstab:
    TRANSFORM Sum(qryAABaseAllMembers.Amount) AS [The Value]
    SELECT qryAABaseAllMembers.FirstName, qryAABaseAllMembers.LastName, qryAABaseAllMembers.Telephone1
    FROM qryAABaseAllMembers
    GROUP BY qryAABaseAllMembers.FirstName, qryAABaseAllMembers.LastName, qryAABaseAllMembers.Telephone1
    ORDER BY qryAABaseAllMembers.LastName
    PIVOT qryAABaseAllMembers.Year;


    Soc

  2. #2
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Records-Wrong Query Results (97)

    I see people have viewed it but no responses. Why? I will provide more info as this mystery baffles me.

    1. 2 linked tables via MemberID. Primary table is tblPeople. Secondary table is tblAnnualAppealHistory.
    2. tblPeople - MemberId, FirstName, LastName, Company, Telephone, etc.
    3. tblAnnualAppealHistory - MemberId(foreign key) Year, Amount, Date.
    4. tblPeople has 1427 records. 114 of those records are the name of a Company and therefore have no First or LastName data in their respective fields.
    5. tblAnnualAppealHistory has 4 entries per record - for years 1997-2000. this is a total of 4X1427= 5708 records.
    6. The base qry - qryAABaseAllMembers has the following fields selected in the design grid: MemberID, FirstName, LastName, Company, Year, Amount,Telephone, and LastNameOrCompany: IIf(IsNull([LastName]),[Company],[LastName]).
    7. The results of this query work. i.e. I get 5708 records.
    8. I created a Crosstab Query on top of the(recordSource) the base query. The selected fields were:FirstNamne, LastName, Telephone - these were the Row Headings.
    8A) the Column headings were: Year. This gave me 4 columns with the Amount donated for each record in the corresponding year.
    9. The results are not accurate. I expected to get 114 records with a blank First and LastName field, with a Telephone number and then the Amount donated for the specific Year.

    Instead I get only 9 records with empty/blank First and LastName fields. It is missing 105 records.

    Any ideas?

    Soc

  3. #3
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Was it Duplicate records?

    I think I figured it out. Is it or does it sound like a CrossTab query will not return duplicate records?
    The company name was the same for a number of records.
    I'm going to run a find duplicates query to do some more checking but was just wondering out loud.

    Soc

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Was it Duplicate records?

    Crosstabs are grouping queries, so no, they don't return duplicate records. That's why the Value only offers you choices like First or Last or Avg, etc.
    Charlotte

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Records-Wrong Query Results (97)

    Your crosstab query groups by FirstName, LastName and Telephone. This will give you one record for every unique combination of those THREE fields. If Telephone is blank on many of the "company" records, they will be grouped together.

    Also, I think you should be grouping on FirstName, LastNameOrCompany, Telephone. This would provide a unique record for each company (where they are not unique on LastName).

Posting Permissions

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