Advance Payments

[syedadnan]

Regards,

I need solution on this that i have a school database i have a query named "Voucher" where the fields are;

Issuedate: showing the date of voucher issue date
Paiddate: showing the date on which the payment made
Monthlyfee: showing the actual monthly fee of the student
Paid: showing the amount paid against monthly fee
GR: showing the student identity which is not as duplicate
Advance: showing a field specific for those student who pays in advance and the total sum of fee to be mentioned here for example if GR 101 monthly fee is 2000 and paying 5 months in advance then the value in the advance field will me mentioned with 10,000 now what i want is that after generating next month voucher the query to calculate the advance payment with respect to the previous advance payment and in the field of "paid" the monthly fee amount to be come automaticaly till the completion of 5 months.

 

[plog]

Can you please demonstrate your issue with data? Please provide 2 sets:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results. Show me what you expect the ultimate query to return for data based on A.

Again, 2 sets and don't try and talk me through it. Show me with data.

 

[syedadnan]

Can you please demonstrate your issue with data? Please provide 2 sets:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results. Show me what you expect the ultimate query to return for data based on A.

Again, 2 sets and don't try and talk me through it. Show me with data.

Thanks for the reply but the area is too scattered hmmm difficult to attach examples.

 

[plog]

Thanks for the reply but the area is too scattered hmmm difficult to attach examples.

Huh? You can post spreadsheets, or screen shots, or just post it in as text as such:

TableNameHere
Field1Name, Field2Name, Field3Name...
Dave, 145, 2/1/2015
Sally, 42, 3/3/2015
Nick, 33, 2/15/2015

 

[syedadnan]

Huh? You can post spreadsheets, or screen shots, or just post it in as text as such:

TableNameHere
Field1Name, Field2Name, Field3Name...
Dave, 145, 2/1/2015
Sally, 42, 3/3/2015
Nick, 33, 2/15/2015

Ok ..

QueryName "Just"
Advance, Paid, Balance, Monthlyfee

Now example to make you understand the process..

Student name Dave with GR 145, monthlyfee is 2000 suppose if dave paid 4000 in the month of Jan 2016 in field of "Paid" we put 2000 as current fee and in the field of advance we put 2000 so now what we want is a running total orsomething like that after the completion of one month his advance to be reflected in paid field or any other field if you suggest
Dave, GR 145, 2/1/2015
Sally, 42, 3/3/2015
Nick, 33, 2/15/2015[/QUOTE]

 

[plog]

I don't want a word problem, I want data. 2 sets:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results. Show me what you expect the ultimate query to return for data based on A.

 

[syedadnan]

I don't want a word problem, I want data. 2 sets:

A. Starting sample data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results. Show me what you expect the ultimate query to return for data based on A.

Thank you for the time you are sharing with me...

I have attached a sample now what is the process there and what expected is mentioned below;

I have a table of students where all student information is entered.
I have an append query which i use at the start of new month to create vouchers, append query develops data in Voucher table.
Now; in voucher table there are fields like "Paid", "Advance", "monthlyfee", "issuedate", "GR", "Paiddate"

I am receiving fee through a form by using "Paid" and "Paiddate" fields from table voucher. Here i need that if i receive just monthly fee in "Paid" feild then ok there is no problem to me but if someone pays fee in advance suppose if Dave fee is 1200 and in the month of Jan 2016 he paid 4800 so here i will enter 1200 in "paid" and 3600 in "Advance" now here i want that after this when i run append query in next month then the query to see "advance" field and the advance of 3600 to be filled by 1200 in the field of "paid" for the coming 3 months with respect to the Dave "GR" number which is unique ID

 

[jdraw]

Further to plog's comments,
-make a 5-6 line description of the business involved in plain English - No Jargon - so readers can understand WHAT you are dealing with
-post some sample data (input and expected output)
-every table should have a Primary key
-don't use names with embedded spaces or special characters (only alphabetics and "_")

-Don't try to refine your forms before you get your tables and relationships designed and tested

Good luck.

 

[Grumm]

I took a look at the demo and like jdraw said : make sure the tables are correct. I see some columns have ambiguous meanings.
ex : STUDENT LEFT / PRESENT is a yes/no field... Is the student left or present if it is checked ? I would name it something easier like STUDENT LEFT. That way it makes sense when you read "Has the student left ? answer : yes or no (No=present)

Maybe it isn't relevant in your case, but adding the currency can be a bonus. (Or the school is overpricing... I wouldn't pay 1200 € per month )

 

[syedadnan]

Further to plog's comments,
-make a 5-6 line description of the business involved in plain English - No Jargon - so readers can understand WHAT you are dealing with
-post some sample data (input and expected output)
-every table should have a Primary key
-don't use names with embedded spaces or special characters (only alphabetics and "_")

-Don't try to refine your forms before you get your tables and relationships designed and tested

Good luck.

Yes table has a primary key with named "GR"

 

[syedadnan]

I took a look at the demo and like jdraw said : make sure the tables are correct. I see some columns have ambiguous meanings.
ex : STUDENT LEFT / PRESENT is a yes/no field... Is the student left or present if it is checked ? I would name it something easier like STUDENT LEFT. That way it makes sense when you read "Has the student left ? answer : yes or no (No=present)

Maybe it isn't relevant in your case, but adding the currency can be a bonus. (Or the school is overpricing... I wouldn't pay 1200 € per month )

Pllzzzzz someone may change the fields and its name no problem i will do it with the original one but plzzz help me out from this head breaking case..

 

[plog]

Your initial post was straight forward:

I have these tables with some data (A).
Something happens.
This data should now be in my tables (B).

I've tried 3 times to get A & B out of you. The last post contained 1 record for A and none for B.

Normally I would say I give up. But its you who should. And by that I mean you should give up on this issue because your tables are not set up properly. You need to read up on normalization(https://en.wikipedia.org/wiki/Database_normalization), structure your tables properly, then move on. What you want to achieve will propbably still be an issue, but the structure of your tables will most likely be better equiped to handle it.

Again, leave this issue and focus on structuring your tables. You need primary/foreign keys, you need probably another table for payments/debits, and a few other issues with your tables.

 

[syedadnan]

Further to plog's comments,
-make a 5-6 line description of the business involved in plain English - No Jargon - so readers can understand WHAT you are dealing with
-post some sample data (input and expected output)
-every table should have a Primary key
-don't use names with embedded spaces or special characters (only alphabetics and "_")

-Don't try to refine your forms before you get your tables and relationships designed and tested

Good luck.

I need a solution only for advance means if we take fee as advance for few upcoming months then i need the column "Paid" in table "Voucher" to be filled automatically with the fee amount from the field "monthlyfee" from table "voucher" till the completion of the advance amount. :banghead:

 

[jdraw]

You need to get your tables and relationships designed to meet your business rules. And you can test the model with some test data. That testing should include "advance payments".

 

[syedadnan]

Your initial post was straight forward:

I have these tables with some data (A).
Something happens.
This data should now be in my tables (B).

I've tried 3 times to get A & B out of you. The last post contained 1 record for A and none for B.

Normally I would say I give up. But its you who should. And by that I mean you should give up on this issue because your tables are not set up properly. You need to read up on normalization(https://en.wikipedia.org/wiki/Database_normalization), structure your tables properly, then move on. What you want to achieve will propbably still be an issue, but the structure of your tables will most likely be better equiped to handle it.

Again, leave this issue and focus on structuring your tables. You need primary/foreign keys, you need probably another table for payments/debits, and a few other issues with your tables.

Surely will do it or post you different example with correct table meanwhile please see this may this can help

 

[Grumm]

Since we have a hard time to get more tables, why not make multiple records ?
Like if the guy pays 3600 in advance, just make 1 record for the next 3 month. Wouldn't that solve your problem ?

 

[syedadnan]

Since we have a hard time to get more tables, why not make multiple records ?
Like if the guy pays 3600 in advance, just make 1 record for the next 3 month. Wouldn't that solve your problem ?

yes exactly good suggestion but could you just describe how ?

 

[arnelgp]

i created a public function InsertVoucher, that takes care of Advances. you can run it manually on the immediate window or use the Macro InsertVoucher. only tested it with one student.

 

[syedadnan]

i created a public function InsertVoucher, that takes care of Advances. you can run it manually on the immediate window or use the Macro InsertVoucher. only tested it with one student.

Thank you very much for your effort as my new to access could you explain how to call this macro or how to use it ?

 

[syedadnan]

i created a public function InsertVoucher, that takes care of Advances. you can run it manually on the immediate window or use the Macro InsertVoucher. only tested it with one student.

Oh ! Great it's working absoultely fine as per my choice just one thing is there that i am calling the macro from query "Append" and after running query it is producing exactly accurate calculation of advance and posting "paid" field with accuracy but it is producing one extra record ?? i hav attached an image showing extra record. what to do to stop producing extra record and it is happening when i am entering advance figure in "Advance" field of voucher else it is producing single record if not disturbing or entering amount in advance field..