Re: [MS_AccessPros] Report not giving complete data

 

Khalid,

I would not add records to tables before there is information to store in them. As per my previous response, use joins that select all records from one of the tables. For instance if you have customers and orders tables and you want to return all of the customers regardless of if they have placed an order your query SQL would be something like:

SELECT Customers.CustomerID, Customers.CompanyName, Customers.ContactName, Customers.ContactTitle, Orders.OrderDate

FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Note the LEFT JOIN.

Regards,

Duane Hookom

Minnesota

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, August 2, 2017 2:47 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Report not giving complete data

 

Duane,

Thank you for replying and telling the expected possibilities.

Yes you are right. Your first observation is correct, there some records that don't have matching/related records in other tables.

This apparently shows that when a new member is added in Form "Add New Member", field "MemberID" should be added in all related tables. Am i right ?

If this is the cause, how it could be attained?

For Form "Add New Member" i have following Before Update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim msg As String

    msg = msg & "Do you want to Save this record?"

    If MsgBox(msg, vbYesNo, "SAC Pakistan Guide - Confirmation!") = vbYes Then

        DoCmd.Save

        If Me.MembershipExpireDate < Now Then

            Me.SocietyLeavingReason = "Dormant"

            Me.SocietyLeavingDate = MembershipExpireDate

            Me.Working = False

            LabelWorking.BackColor = RGB(255, 0, 0)

            LabelWorking.Caption = "Removed Member:"

            Me.LastUpdate = Now

        End If

        If IsNull(MembershipExpireDate) Or MembershipExpireDate > Now Then

            Me.SocietyLeavingReason = ""

            Me.SocietyLeavingDate = ""

            Working = True

            LabelWorking.BackColor = RGB(0, 9900, 0)

            LabelWorking.Caption = "Active Member:"

            Me.LastUpdate = Now

        End If

    Else

        Me.Undo

    End If

End Sub

~~~~~~~~~~~~~~~~~~~~~~

The form may be updated many times as its data may be edited/modified or missing entries are filled.

Please suggest how to manage it.

Regards,

Khalid

 

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

I expect there might be some records that don't have matching/related records in other tables. Have you tried looking at the full query records without the membership type filter? Have you tried changing the joins to LEFT or RIGHT joins?

Regards,

Duane Hookom

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, August 1, 2017 1:19 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Report not giving complete data

 

Hi all,

I have a report "List of Members For Printing Directory" having Record Source "List of Members Selective Membership Type Query" its SQL is:

SELECT [Members].[MembershipType], [Members].[MemberID], [Members].[GenderStatus], [Members].[Salutation_Detail], [Members].[SurName], [Members].[GivenName], [Members].[GivenName] & " " & [SurName] AS MemberName, Members.MembershipStartDate, tblCNIC.[CNIC Number], Members.DesignationSOC, tblJob.OrganizationDesignation, tblJob.Organization, tblAddress.[Postal Address], tblContactNumbers.TelephoneOffice, tblContactNumbers.TelephoneResidence, tblContactNumbers.FaxNo, tblContactNumbers.MobileNo1, tblContactNumbers.MobileNo2, tblContactNumbers.FaxNo, tblElectronicContacts.MemberEmail, Members.Working, tblAddress.MainCityName

FROM ((((Members INNER JOIN tblAddress ON Members.MemberID = tblAddress.MemberID) INNER JOIN tblCNIC ON Members.MemberID = tblCNIC.MemberID) INNER JOIN tblContactNumbers ON Members.MemberID = tblContactNumbers.MemberID) INNER JOIN tblElectronicContacts ON Members.MemberID = tblElectronicContacts.MemberID) INNER JOIN tblJob ON Members.MemberID = tblJob.MemberID

WHERE (((Members.MembershipType)=[Forms]![Dialog Members Directory Report]![cboMembershipType]) AND ((Members.Working)=True))

ORDER BY Members.GivenName, [Members].[GivenName] & " " & [SurName];

The Unbound Dialog Box "Dialog Members Directory Report" to open the report has Unbound Combo Box "cboMembershipType" its Row Source is:

SELECT DISTINCT [Membership Type].MembershipType, [Membership Type].MembershipTitle

FROM [Membership Type]

WHERE ((([Membership Type].MembershipType)<>"TM"))

ORDER BY [Membership Type].MembershipType;

Report is not giving or showing any error, i'm unable to trace why some data is missing from report.

Help required please.

(I've submitted this topic as it is most urgently required by my boss for printing of Society's Member Directory.)

Regards,

Khalid

 

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___