Missing Records-Wrong Query Results (97)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote