UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Cross-table Query, Access 2016    
 
   
manansafdar
post Mar 25 2020, 02:51 AM
Post#1



Posts: 14
Joined: 28-July 19



Hi,

I have a table "tblStudentFeeCollection" that keeps records of collected fee from students, I have a query "qryDueAmountWithClassReport" that show the "Due Fees" for students.

I have created a query "qryDueCollection&Balance", it should show how much is due fee for each student, how much is collected and how much is balance for a specific Academic Year and month but it is not displaying the records as per requirement and either shows duplicate records or show records of "tblStudentFeeCollection" only.

Any support will be appreciated.

Please find attached snippet and Database for your reference.

Thanks,
This post has been edited by manansafdar: Mar 25 2020, 03:07 AM
Attached File(s)
Attached File  Capture.PNG ( 20K )Number of downloads: 10
Attached File  AsianEducators_V33_Use__09.13.zip ( 1.51MB )Number of downloads: 5
 
Go to the top of the page
 
RJD
post Mar 26 2020, 07:00 PM
Post#2


UtterAccess VIP
Posts: 10,496
Joined: 25-October 10
From: Gulf South USA


Hi: It took a while to figure out what you had done with your db design. I am still not entirely sure I understand. However, I rearranged [qryDueCollection&Balance] for you to look at and see if this addresses your issues. It links tables differently, and links by three fields rather than one, so that month and year values line up and do not repeat.

I also corrected the year indicator in tblAcademicYears as one had a space in it.

See if this moves you toward a solution, of if we need more information and explanation to help you. Plus, check to make sure you have isolated a single academic year in all tables in the queries.

HTH
Joe

Attached File(s)
Attached File  AsianEducators_V33_Use__09.13_Rev1.zip ( 1.46MB )Number of downloads: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
manansafdar
post Mar 27 2020, 06:35 AM
Post#3



Posts: 14
Joined: 28-July 19



Hi Joe,

Thank you so much for sparing time to look into DB. I actually had lost hope that I will get support from this forum but I am very thankful for your input.

What you have done has resolved the issue and it is now working as I inteded.

Really appreciate your support.

:-)
This post has been edited by manansafdar: Mar 27 2020, 07:19 AM
Go to the top of the page
 
RJD
post Mar 27 2020, 07:08 AM
Post#4


UtterAccess VIP
Posts: 10,496
Joined: 25-October 10
From: Gulf South USA


You are very welcome. I am glad that resolved your issue. thumbup.gif

We seem to have slowed down recently due to the COVID-19 disruption. But we are still here and ready to assist

Regards
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
manansafdar
post Mar 27 2020, 07:23 AM
Post#5



Posts: 14
Joined: 28-July 19



Hello Joe,

I hope for everyone to stay safe from this virus.

Since you have spent sometime to understand the DB design, I have another issue that I am unable to resolve, can you kindly look into it?

As of now, when fee is collected for any student, if the student does not pay full dues I have to manually add the balance into next month dues, can this be automated.

If you need I can explain further through email or skype.
This post has been edited by manansafdar: Mar 27 2020, 07:24 AM
Go to the top of the page
 
RJD
post Mar 27 2020, 11:04 AM
Post#6


UtterAccess VIP
Posts: 10,496
Joined: 25-October 10
From: Gulf South USA


Hi again: You should not store the total current due that includes the previous balance, only store the regular due amount. The total due including the previous balance will be dynamic and can be easily calculated See the revision to your db attached. See [qryDueCollectionAndBalanceWithCarryForward]. When prompted, use the current month ID (Mar = 1, Apr = 2, etc., per your schema). For example, if you want to know the results for April, use 2 in the prompt. Use 2 as your first test and see if these are the results you want.

HTH
Joe
Attached File(s)
Attached File  AsianEducators_V33_Use__09.13_Rev2.zip ( 1.46MB )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
manansafdar
post Mar 28 2020, 05:06 AM
Post#7



Posts: 14
Joined: 28-July 19



Thanks again, this is very helpfull.

It does the trick and brings the required results, although I have tried to solve it on my own for quite long and consulted many but could not find the solution.

However, I am unable to incorporte it into the form that will actually displays this figure.

On the form "frmStudents", under the tab manage payments, i want to display this previous balance. Please see the screen shot for your reference. Can you kindly look into it?

Thanks in advance,
Attached File(s)
Attached File  Capture.PNG ( 14.7K )Number of downloads: 4
 
Go to the top of the page
 
RJD
post Mar 28 2020, 10:13 AM
Post#8


UtterAccess VIP
Posts: 10,496
Joined: 25-October 10
From: Gulf South USA


Hi again: Having now seen where you are putting the results, it is clear that you do not need to use the new query in the form, just a DSum in a form control looking at [qryDueCollection&Balance]. (Please note also that using a special character like & is bad practice and can cause problems in some cases when seen as a concatenation symbol. I suggest you change that.)

The DSum in the form control does what the new query does to get the previous balance, using data from the form rather than a limit or prompt in the query. See the form now, select a student, year and month and see if this gives you the results you want.

HTH
Joe
Attached File(s)
Attached File  AsianEducators_V33_Use__09.13_Rev3.zip ( 1.48MB )Number of downloads: 9
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
manansafdar
post Mar 28 2020, 12:40 PM
Post#9



Posts: 14
Joined: 28-July 19



Hello again,

Thank you for your advice regarding use of special characters. I will be careful in their use in future.

About your feedback and solution, there is a small detail that I would like to bring in your notice.

It is carrying-forward the due amount from previous months but it is missing the fee under the fee type "OneOff and Other Payments". See form "frmStudents" and tab "setup class and fee".

It will carry all amount except dues under this fee type catagory.

Please see attached screenshots for the reference.

Again, Thanks very much for your support.



Attached File(s)
Attached File  Capture.PNG ( 19.81K )Number of downloads: 2
Attached File  Capture1.PNG ( 18.15K )Number of downloads: 2
Attached File  Capture2.PNG ( 19.36K )Number of downloads: 3
Attached File  Capture3.PNG ( 18.19K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Mar 28 2020, 01:26 PM
Post#10


UtterAccess VIP
Posts: 10,496
Joined: 25-October 10
From: Gulf South USA


Hi: What have you done so far to try to correct this? See the formulas in the relevant controls and how you might incorporate this new data.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
manansafdar
post Mar 28 2020, 02:59 PM
Post#11



Posts: 14
Joined: 28-July 19



Hi Sir,

I had checked the contol source on form which is taking input from "qryDueCollection&Balance" which is based on "qryDueAmountWithClassReport" which is taking input from "qryDueAmountReport" that is taking input from "qryAllDue" which is based upon "qryUnionAllFeesForStudents".

I had checked it and "qryUnionAllFeesForStudents" is including all fee types but I was unable to figure out the issue.

Now, I have checked it again and found that studentClassID was missing for the student that I was testing which is why it was skipping that particular feeType.

It is my mistake, I should have checked it more rigorously. Please accept my apologies for unnecessary trouble and my gratitude for your support and patience.
Go to the top of the page
 
RJD
post Mar 28 2020, 03:13 PM
Post#12


UtterAccess VIP
Posts: 10,496
Joined: 25-October 10
From: Gulf South USA


Hi: No apologies necessary. I am glad you found the data issue.

You are very welcome. I am happy we have been able to resolve the issues.

Please let us know if we can be of further assistance.

Say well.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th May 2020 - 06:41 PM