Append Query is adding Duplicate/multiple Records for one month

[Khan]

Hi
I am working on student fees collection database. At the end of each month, I create fees challan/records for each student in challan database. 1st and 2nd month it went well but now, if I add new fees challan to challan database the number of entries are double (and checked for next month entries are 4 for each student record) while I want only one entry.

I am using query to take record from students and it's showing 590 records but when I append in challan the numbers are double around 1140.

This is my append query (Sorry for too much detail but I want to show the complete thing, as it worked when all the details were not there but I need them in program):



PARAMETERS [Enter Description] Text ( 255 ), [Enter AdmissionFees] Long, [Enter AnnualExamFees] Long, [Enter StationaryFees] Long, [Enter OtherFees] Long;
INSERT INTO ChallanData ( StudentID, StudentName, FatherName, ClassID, ClassName, Date0, Description, MonthlyFees, AdmissionFees, AnnualExamFees, StationaryFees, OtherFees, CurrentDues, PreviousDues, TotalDues, Balance, LastChallanID, LastPaymentDate, LastChallanAmount, LastChallanPayment, LastChallanBalance )
SELECT DISTINCTROW QryStudentforMonthly.StudentID, QryStudentforMonthly.StudentName, QryStudentforMonthly.FatherName, QryStudentforMonthly.ClassID, QryStudentforMonthly.ClassName, Date() AS Date0, [Enter Description] AS txtfeesHead, Nz([QryStudentforMonthly].[MonthlyFees],0)*Nz([Forms]![CreatMonthlyChallan]![TxtNoOfMonths],1) AS MonthlyFees, [Enter AdmissionFees] AS AdmissionFees, [Enter AnnualExamFees] AS AnnualExamFees, [Enter StationaryFees] AS StationaryFees, [Enter OtherFees] AS OtherFees, Nz([Student].[MonthlyFees],0)*Nz([Forms]![CreatMonthlyChallan]![TxtNoOfMonths],1)+Nz([AdmissionFees])+Nz([annualExamFees],0)+Nz([StationaryFees],0)+Nz([OtherFees],0) AS CurrentDues, QryStudentforMonthly.Balance AS PreviousDues, Nz([CurrentDues],0)+Nz([PreviousDues],0) AS TotalDues, Nz([TotalDues],0) AS Balance, QryStudentforMonthly.LastPaymentChallan, QryStudentforMonthly.LastPaymentDate, QryStudentforMonthly.LastDuesAmount, QryStudentforMonthly.LastPaymentAmount, Nz([LastChallanAmount],0)+Nz([LastPaymentAmount],0) AS LastChallanBalance
FROM QryStudentforMonthly LEFT JOIN ChallanData ON QryStudentforMonthly.StudentID = ChallanData.StudentID
WHERE ((([Enter Description])=[Enter Description]) AND (([Enter AdmissionFees])=[Enter AdmissionFees]) AND (([Enter AnnualExamFees])=[Enter AnnualExamFees]) AND (([Enter StationaryFees])=[Enter StationaryFees]) AND (([Enter OtherFees])=[Enter OtherFees]));

[ranman256]

can you set the base query property to UNIQUE VALUES = TRUE
this will not show duplicates and only add 1.

[ssanfu]

I'm not sure what "Challan" means, but I think you have issues with your table designs. It looks like, from the query, that you have committed "Spreadsheet". That is when you design your tables like Excel spreadsheets.
For example the first 5 fields in the query are
StudentID,
StudentName,
FatherName,
ClassID,
ClassName

With the exception of the first field "StudentID", why are they in a table that has fee data? You didn't post the SQL of the query "QryStudentforMonthly", so I don't know how many tables are in that query.
It would be really nice to see an image of the relationship window....... or even better to have the dB for analysis.


----------------------------------------------------------
You might think about aliasing the table name - you could use

<snip> FROM QryStudentforMonthly As qSM LEFT JOIN ChallanData <snip>

Then you would use

SELECT DISTINCTROW qSM.StudentID, qSM.StudentName, qSM.FatherName, qSM.ClassID, qSM.ClassName, Date() AS Date0,.....

instead of

SELECT DISTINCTROW QryStudentforMonthly.StudentID, QryStudentforMonthly.StudentName, QryStudentforMonthly.FatherName, QryStudentforMonthly.ClassID, QryStudentforMonthly.ClassName, 
Date() AS Date0,

--------------------------------------------
Also, you are missing part of the NZ function

Nz([Student].[MonthlyFees],0)*Nz([Forms]![CreatMonthlyChallan]![TxtNoOfMonths],1)+Nz([AdmissionFees])+Nz([annualExamFees],0)+Nz([StationaryFees],0)+Nz([OtherFees],0) AS CurrentDues,

And Welcome to the forum...

[Khan]

can you set the base query property to UNIQUE VALUES = TRUE
this will not show duplicates and only add 1.

Thanks for response.

Tried it in some other program, it worked but not working here, may be due to different structure.

[Khan]

Thanks for a thorough look!

Your queries answered:

I'm not sure what "Challan" means, but I think you have issues with your table designs. It looks like, from the query, that you have committed "Spreadsheet". That is when you design your tables like Excel spreadsheets.
For example the first 5 fields in the query are
StudentID,
StudentName,
FatherName,
ClassID,
ClassName

Challan means "Payment Receipt".

You are right about the extra fields, i copied all the data from Spreadsheets that's why there are columns:
StudentID,
StudentName,
FatherName,
ClassID,
ClassName

I planned to refine after completing all the options.

With the exception of the first field "StudentID", why are they in a table that has fee data? You didn't post the SQL of the query "QryStudentforMonthly", so I don't know how many tables are in that query.
It would be really nice to see an image of the relationship window....... or even better to have the dB for analysis.

I will upload pics related to the queries and other images (taking some time to understand pic upload at the forum).

----------------------------------------------------------

You might think about aliasing the table name - you could use

<snip> FROM QryStudentforMonthly As qSM LEFT JOIN ChallanData <snip>

Then you would use

SELECT DISTINCTROW qSM.StudentID, qSM.StudentName, qSM.FatherName, qSM.ClassID, qSM.ClassName, Date() AS Date0,.....

instead of

SELECT DISTINCTROW QryStudentforMonthly.StudentID, QryStudentforMonthly.StudentName, QryStudentforMonthly.FatherName, QryStudentforMonthly.ClassID, QryStudentforMonthly.ClassName, 
Date() AS Date0,

NOTED: Good Suggestions, I will do it when refine in the end.
--------------------------------------------

Also, you are missing part of the NZ function

Nz([Student].[MonthlyFees],0)*Nz([Forms]![CreatMonthlyChallan]![TxtNoOfMonths],1)+Nz([AdmissionFees])+Nz([annualExamFees],0)+Nz([StationaryFees],0)+Nz([OtherFees],0) AS CurrentDues,

NOTED: Thanks, I corrected it and will check the same at other places, but even after that the problem is there.

And Welcome to the forum...[/QUOTE]

[Khan]

Following are the Images showing number of records 593 but making payment receipts/Challan 1140 entries.



QryStudentforMonthly is taking bio data and totaling/Summing dues from student record.

[ssanfu]

The images helped a little...

QryStudentforMonthly is taking bio data and totaling/Summing dues from student record.

But I don't understand why you are using that query.
Why are the ClassID/ClassName and the Fees in the Students table. They are not attributes of a student.


You should research "Normalization".

Would you describe "What" you are trying to do/ how you would do it manually - no computer/database jargon?
And maybe post a copy of your dB? Without sensitive data.

[Khan]

The images helped a little...


But I don't understand why you are using that query.
Why are the ClassID/ClassName and the Fees in the Students table. They are not attributes of a student.

Because these fields are from SPREADSHEET, I imported to Ms ACCESS. Already told you, that I will refine/Normalize them after completing the options.

QryStudentForMonthly:

SELECT DISTINCT Student.StudentID, Student.StudentName, Student.FatherName, Student.ClassID, Student.ClassName, Student.MonthlyFees, Student.AdmissionFees, Student.AnnualFees, Student.StationaryFees, Student.OtherFees, Student.OpgBalance, Sum(ChallanData.CurrentDues) AS SumOfCurrentDues, Sum(ChallanData.PreviousDues) AS SumOfPreviousDues, Sum(ChallanData.TotalDues) AS SumOfTotalDues, Sum(ChallanData.PaidAmount)

AS SumOfPaidAmount, Sum(Nz([student]![opgbalance],0)+Nz([challandata]![currentdues],0)-Nz([challandata]![paidamount],0)) AS Balance, Student.Active, Student.LastPaymentChallan, Student.LastPaymentDate, Student.LastDuesAmount, Student.LastPaymentAmount

FROM Student LEFT JOIN ChallanData ON Student.StudentID = ChallanData.StudentID

GROUP BY Student.StudentID, Student.StudentName, Student.FatherName, Student.ClassID, Student.ClassName, Student.MonthlyFees, Student.AdmissionFees, Student.AnnualFees, Student.StationaryFees, Student.OtherFees, Student.OpgBalance, Student.Active, Student.LastPaymentChallan, Student.LastPaymentDate, Student.LastDuesAmount, Student.LastPaymentAmount
HAVING (((Student.Active)=True));

You should research "Normalization".

SURE, after this issue, and adding EDIT options.

Would you describe "What" you are trying to do/ how you would do it manually - no computer/database jargon?
And maybe post a copy of your dB? Without sensitive data.

Process:
* Student Record
* Challan/Fees payment receipts (generate at the beginning of each month for each student by totaling their previous dues and current dues).
* When parent comes for payment, Challan/Fees payment receipts is marked with date and amount he paid. (dues, fees defaulters, etc. are calculated from this data).

I hope, I explained the process.

Now, My databases:
* I have STUDENT DB for students record and CHALLANDATA DB for monthly fees payment receipts.
* Next I am making Monthly fees receipt with total current and previous dues (UNPAID) for all STUDENTS from STUDENT DB at the beginning of each month and store that data in CHALLANDATA DB, they both are joined.
* As soon as a parent comes to pay the receipt, I Enter Challan Number and receive the the amount (some times full & some times whatever they pay).
* Daily reports, Fees defaulters, etc. reports are generated from this data.

* At the beginning of each month, i make challan, by calculating: opening Balance FROM Student, Previous Dues (Total all current dues from CHALLANDATA), Current Dues (this monthly fees) and Minus them from all payments from ChallanData (in QryStudentForMonthly).

The problems is here, The append query above is making two fees payment receipts for each student in place of one. (which is increasing when i check by making challan/fees payment receipts for coming months).

[ssanfu]

I planned to refine after completing all the options.

Because these fields are from SPREADSHEET, I imported to Ms ACCESS. Already told you, that I will refine/Normalize them after completing the options.

Any time spent fixing the problem now will be wasted because once you start the normalization process, everything will need to be changed - tables, queries, forms, reports and code.


Without your dB to test, I would suggest checking the query "QryStudentForMonthly" to be sure that the correct records are returned.

This is untested :
Create a new query named "qryStudentCount"

SELECT Student.StudentID, Student.StudentName, Student.FatherName, Student.ClassID, Student.ClassName, Student.MonthlyFees, Student.AdmissionFees, Student.AnnualFees, Student.StationaryFees, Student.OtherFees, Student.OpgBalance, Student.Active, Student.LastPaymentChallan, Student.LastPaymentDate, Student.LastDuesAmount, Student.LastPaymentAmount
FROM Student WHERE Student.Active = True ORDER BY Student.StudentID;

How many records are returned?
Is the number of DISTINCT Students correct?


Now create another NEW query named "qryTestStudentforMonthly":

SELECT DISTINCTROW qSM.StudentID, qSM.StudentName, qSM.FatherName, qSM.ClassID, qSM.ClassName, Date() AS Date0, Nz([qSM].[MonthlyFees],0)*Nz([Forms]![CreatMonthlyChallan]![TxtNoOfMonths],1) AS MonthlyFees, [Enter AdmissionFees] AS AdmissionFees, [Enter AnnualExamFees] AS AnnualExamFees, [Enter StationaryFees] AS StationaryFees, [Enter OtherFees] AS OtherFees, Nz([Student].[MonthlyFees],0)*Nz([Forms]![CreatMonthlyChallan]![TxtNoOfMonths],1)+Nz([AdmissionFees])+Nz([annualExamFees],0)+Nz([StationaryFees],0)+Nz([OtherFees],0) AS CurrentDues, qSM.Balance AS PreviousDues, Nz([CurrentDues],0)+Nz([PreviousDues],0) AS TotalDues, Nz([TotalDues],0) AS Balance, qSM.LastPaymentChallan, qSM.LastPaymentDate, qSM.LastDuesAmount, qSM.LastPaymentAmount, Nz([LastChallanAmount],0)+Nz([LastPaymentAmount],0) AS LastChallanBalance FROM QryStudentforMonthly As qSM LEFT JOIN ChallanData ON qSM.StudentID = ChallanData.StudentID ORDER By qSM.StudentID

How many records are returned?


Edit the query "qryTestStudentforMonthly" and replace the query name "QryStudentforMonthly" with "qryStudentCount" so it looks like

SELECT DISTINCTROW qSM.StudentID, qSM.StudentName, qSM.FatherName,  qSM.ClassID, qSM.ClassName, Date() AS Date0,  Nz([qSM].[MonthlyFees],0)*Nz([Forms]![CreatMonthlyChallan]![TxtNoOfMonths],1)  AS MonthlyFees, [Enter AdmissionFees] AS AdmissionFees, [Enter  AnnualExamFees] AS AnnualExamFees, [Enter StationaryFees] AS  StationaryFees, [Enter OtherFees] AS OtherFees,  Nz([Student].[MonthlyFees],0)*Nz([Forms]![CreatMonthlyChallan]![TxtNoOfMonths],1)+Nz([AdmissionFees])+Nz([annualExamFees],0)+Nz([StationaryFees],0)+Nz([OtherFees],0)  AS CurrentDues, qSM.Balance AS PreviousDues,  Nz([CurrentDues],0)+Nz([PreviousDues],0) AS TotalDues, Nz([TotalDues],0)  AS Balance, qSM.LastPaymentChallan, qSM.LastPaymentDate,  qSM.LastDuesAmount, qSM.LastPaymentAmount,  Nz([LastChallanAmount],0)+Nz([LastPaymentAmount],0) AS  LastChallanBalance FROM qryStudentCount As qSM LEFT JOIN ChallanData ON qSM.StudentID = ChallanData.StudentID ORDER By qSM.StudentID

How many records are returned?

[Khan]

Thanks for suggesting and giving time, hope the issue will be solved soon.

Any time spent fixing the problem now will be wasted because once you start the normalization process, everything will need to be changed - tables, queries, forms, reports and code.

I will surely do it just after getting rid of this issue, thanks for motivation (it will take the same time as to make new program).

Without your dB to test, I would suggest checking the query "QryStudentForMonthly" to be sure that the correct records are returned.

This is untested :
Create a new query named "qryStudentCount"

SELECT Student.StudentID, Student.StudentName, Student.FatherName, Student.ClassID, Student.ClassName, Student.MonthlyFees, Student.AdmissionFees, Student.AnnualFees, Student.StationaryFees, Student.OtherFees, Student.OpgBalance, Student.Active, Student.LastPaymentChallan, Student.LastPaymentDate, Student.LastDuesAmount, Student.LastPaymentAmount
FROM Student WHERE Student.Active = True ORDER BY Student.StudentID;

How many records are returned?
Is the number of DISTINCT Students correct?

573 Records, and that's the right number.

Now create another NEW query named "qryTestStudentforMonthly":

SELECT DISTINCTROW qSM.StudentID, qSM.StudentName, qSM.FatherName, qSM.ClassID, qSM.ClassName, Date() AS Date0, Nz([qSM].[MonthlyFees],0)*Nz([Forms]![CreatMonthlyChallan]![TxtNoOfMonths],1) AS MonthlyFees, [Enter AdmissionFees] AS AdmissionFees, [Enter AnnualExamFees] AS AnnualExamFees, [Enter StationaryFees] AS StationaryFees, [Enter OtherFees] AS OtherFees, Nz([Student].[MonthlyFees],0)*Nz([Forms]![CreatMonthlyChallan]![TxtNoOfMonths],1)+Nz([AdmissionFees])+Nz([annualExamFees],0)+Nz([StationaryFees],0)+Nz([OtherFees],0) AS CurrentDues, qSM.Balance AS PreviousDues, Nz([CurrentDues],0)+Nz([PreviousDues],0) AS TotalDues, Nz([TotalDues],0) AS Balance, qSM.LastPaymentChallan, qSM.LastPaymentDate, qSM.LastDuesAmount, qSM.LastPaymentAmount, Nz([LastChallanAmount],0)+Nz([LastPaymentAmount],0) AS LastChallanBalance FROM QryStudentforMonthly As qSM LEFT JOIN ChallanData ON qSM.StudentID = ChallanData.StudentID ORDER By qSM.StudentID

How many records are returned?

1146 Records, that's the issue, it should be 573.

This number is closed to total records in ChallanData DB ie. 1151

[mike60smart]

Hi
Can you upload a zipped copy of the database?

[Khan]

Hi
Can you upload a zipped copy of the database?

I am trying to upload ZIP file but after Choose file & Upload nothing happens here. Is there any restriction to upload file or is there any specific place to upload files on this forum?

[Khan]

I have solved the issue.
While checking, I JOINED my QryStudentForMonthly with ChallanData in Append Query, which was not needed. I just deleted the join in Append Query and now it's working fine.

Thank you very much guys for helping me to critically check my work. Special thanks to ssanfu for giving time to analyze my poor work and suggest.

I wish to upload this database after completing NORMALIZATION for review and criticism, would anyone of you can guide me, if it is possible here in this forum and if yes, where should I post it?