Access reports dropping data included in query
My primary key for my database is based on phone numbers. I have created a query which includes anyone that has an outstanding balance. The query includes last names that are the same which I see. I have 3 people with the last name of Thomas, all have different first names. I created a report from this information but when I open and print it is only including the first Thomas, the other 2 are being dropped. Not included at all. What am I doing wrong. Any help would be appreciated.
Software/Hardware used:
windows 7
Software/Hardware used:
windows 7
ASKED:
November 30, 2012 2:48 AM
UPDATED:
November 30, 2012 1:12 PM
Answer Wiki:
This could be caused by grouping based on last name?
Actually when You Group them by Lastname then It will check the Oustanding balances for all the names with similar lastname and assume to be one person who has the arrears. I think that why you are having these kind of problems. Do not Group By Lastname;U'd rather group by Pk. Try again and consult further if there is still a problem.
All Answer Wiki Contributors:
JohnKennedy 
405 pts. ,
ArkieV 15 pts. ,
Michael Tidmarsh 11,410 pts. ,
osbornisle59 40 pts.
405 pts. ,
ArkieV 15 pts. ,
Michael Tidmarsh 11,410 pts. ,
osbornisle59 40 pts. To see all answers submitted to the Answer Wiki: View Answer History.
We need to see the query statement in order to know. There are various possibilities. Please show the statement. — Tom
I think u should not use SELECT *, distinct(Lastname) FROM tablename …..;this but if it would be easier to help someone who given
the sql statement. If you have used the basic Ms Access Query, then you
need to specify; it is also easy to execute directly.Just use something like this; Select * From Tablename Where Tablename.Fieldname<>0 Group By Phone No.
What is PK? I inherited this database and am not that good at it!
SELECT [Tracking Information].LastName, [Tracking Information].FirstName, [Tracking Information].HomePhone, [Tracking Information].Balance, [Tracking Information].LastPayment, [Tracking Information].LastPayAmt, [Tracking Information].Notes, Sum([Tracking Information].Balance) AS [sum of balance]FROM [Tracking Information]GROUP BY [Tracking Information].LastName, [Tracking Information].FirstName, [Tracking Information].HomePhone, [Tracking Information].Balance, [Tracking Information].LastPayment, [Tracking Information].LastPayAmt, [Tracking Information].NotesHAVING ((([Tracking Information].Balance)<>0))ORDER BY [Tracking Information].Balance;
First, remove the HAVING clause. There is the possibility that the other two groups of ‘Thomas’ names calculate a balance of zero. If they show up when that HAVING clause is gone, verify their balances and decide if you want them included or not. After that, the other possibility seems to be that those rows also have the same FirstName, HomePhone, Balance, LastPayment, LastPayAmt and Notes; but you already said the have different FirstNames. — Tom
PK would be the Primary Key. I don’t see much point in grouping by the primary key, especially if you want to group by LastName (unless LastName is the PK). — Tom
PK is phone number