Access reports dropping data included in query

40 pts.
Tags:
Microsoft Access 2003
Windows 7
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
ASKED: November 30, 2012  2:48 AM
UPDATED: November 30, 2012  1:12 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    We need to see the query statement in order to know. There are various possibilities. Please show the statement. -- Tom
    125,585 pointsBadges:
    report
  • JohnKennedy
    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.
    415 pointsBadges:
    report
  • osbornisle59
    What is PK?  I inherited this database and am not that good at it!
    40 pointsBadges:
    report
  • osbornisle59
    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;
    40 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • osbornisle59

    PK is phone number

     

    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following