Calender control and listing last 5 payments made

[sajitk]

Good morning,

I am making a database to keep track of payments made by villagers on account of purchasing solar lanters. the rule is the payment can be made in small amounts each month. now wat i want is how do i show the last 5 payments made by a particular villager.

for eg.

cost of the solar lantern is Rs. 3000
initial payment made - 500
balance - Rs 2500

next month - 500
nex - 500
next 100
next 200

so if the user wants to generate a report to see the last 5 payments made by a villager, wat do i do...do i define an array and how do i design a field in the table...

secondly, how do i add a calender control in a form.

thanks in advance
Sajit

[Rabbit]

You just need to use a subquery that returns a SELECT TOP 5.

[sajitk]

You just need to use a subquery that returns a SELECT TOP 5.

where do i write this subquery....and how do i design my table. do i store this in one field or do i store the value in 5 different fields.

and in the form, do i use an array to store these value.....

just be more specific on this count please.

How do i incorporate a calender control....

Thanks in advance

[NeoPa]

Sajit,

You need to put your questions in separate threads.
We'll deal with the last 5 payments in this one as I know nothing about Calendar controls.
To tell you more about how to select what you need from your table(s) we need you to tell us about how your tables are laid out.
Please provide table meta in the following form (copy and paste from the reply screen for help with that).

Table name = [[U]YourTable[/U]]
RecordID; Numeric; PK
Name; String
DateAdded; Date/Time
Parent; Numeric; FK

[sajitk]

Dear NeoPa,

The structure is as follows :

Table name: [[U]Beneficiary[/U]]

solar lantern no; Numeric 
cost of lantern; Numeric
payment; Numeric
balance; Numeric

in the payment field, i am storing the value of last payment. the balance amount gets updated automatically. this was simple

this was the initial requirement, so i made this application like this. Now they want to report the last 5 payments made by the beneficiary.

so how do i change my recordset for this new requirement

sajit

[NeoPa]

Nowhere in the record structure do you identify the villager connected with this payment. This cannot work like this. Further progress is not possible without this info and without a workable design.

[sajitk]

Nowhere in the record structure do you identify the villager connected with this payment. This cannot work like this. Further progress is not possible without this info and without a workable design.

Sorry,

I have given you an incomplete structure

Table Name: Beneficiary

Name of the villager - Text
Solar Lantern No - Numeric (Primary Key)
Address - Text
etc ...etc


Table Name : Payment

Solar Lantern No. - Foreign key
Amt of Solar Lantern - Numeric
Payment - Numeric
Balance payment - Numeric


Hope this solves your confusion...tha nks in advance

Sajit

[NeoPa]

This is not incomplete information - This is simply wrong information. Even the field names have changed between versions.

Please confirm that my updated structure (I even had to do it again for you as your layout is still not following the layout requested) is correct before I even think of spending more time looking at this.

Table name: [[U]Beneficiary[/U]]
[I]Field; Type; IndexInfo[/I]
Name of the villager; Text
Solar Lantern No; Numeric; PK
etc

Table name: [[U]Payment[/U]]
[I]Field; Type; IndexInfo[/I]
Solar Lantern No; Numeric; FK
Cost Of Lantern; Numeric
Payment; Numeric
Balance; Numeric

[sajitk]

ok this is correct.....

thanks

Sajit

This is not incomplete information - This is simply wrong information. Even the field names have changed between versions.

Please confirm that my updated structure (I even had to do it again for you as your layout is still not following the layout requested) is correct before I even think of spending more time looking at this.

Table name: [[U]Beneficiary[/U]]
[I]Field; Type; IndexInfo[/I]
Name of the villager; Text
Solar Lantern No; Numeric; PK
etc

Table name: [[U]Payment[/U]]
[I]Field; Type; IndexInfo[/I]
Solar Lantern No; Numeric; FK
Cost Of Lantern; Numeric
Payment; Numeric
Balance; Numeric

[NeoPa]

I've picked this up to look at it again, and I now find that there is no date information in either of the tables. As you are asking for the "last 5 payments" there surely MUST be a date field. If you don't currently have one in your table then this cannot be done unless and until you add one in. If you do have one but haven't told me about it then you have been wasting my time. I did ask for the information more than once and eventually even proposed the layout for you to accept, which you did.

Please explain what is going on here as my patience is wearing thin.

[sajitk]

Dear NeoPa,

I know you are running out of patience...but please bear with me as i am new to access. Had i been an expert, then i wouldnt have come to this forum for help.

Okay there is a payment date in the payment table

would be great if you can be a bit patient and help me on this. now, if there is something else which is missing please assume it is there and lets get this problem solved.

Thanks in advance for the patient hearning

Sajit

I've picked this up to look at it again, and I now find that there is no date information in either of the tables. As you are asking for the "last 5 payments" there surely MUST be a date field. If you don't currently have one in your table then this cannot be done unless and until you add one in. If you do have one but haven't told me about it then you have been wasting my time. I did ask for the information more than once and eventually even proposed the layout for you to accept, which you did.

Please explain what is going on here as my patience is wearing thin.

[sajitk]

Dear NeoPa,

so now the 2 tables contains the following fields :

Table name: [[U]Beneficiary[/U]]
[I]Field; Type; IndexInfo[/I]
Name of the villager; Text
Solar Lantern No; Numeric; PK

Table name: [[U]Payment[/U]]
[I]Field; Type; IndexInfo[/I]
Solar Lantern No; Numeric; FK
Cost Of Lantern; Numeric
Payment; Numeric
Payment date; Date/Time
Balance; Numeric

NeoPa, if you think there is a need to change the structure of the tables (in order to achieve the objective), please feel free to do so.

Looking fwd to hear from you.

Sajit

[NeoPa]

...
I know you are running out of patience...but please bear with me as i am new to access. Had i been an expert, then i wouldnt have come to this forum for help.
...

Sajit,

I will continue to help as long as I can.
You should understand though, that your inexperience at Access is not related to why I am not pleased with your posts. If I ask you to check something, then I expect you to check it before replying. I can guess the answer just as well as you can, but if it's wrong then we all just get confused.
I'm trying to be careful not to send you in the wrong direction. That's why I need clear and reliable information.

I don't think I will need more changes to the structure now as I would guess this is enough to produce what we need. Give me a little time and I will get some SQL together for you.

[NeoPa]

The following is the basic SQL for your query. If you're going to create this in your code then you can replace the [Enter Solar Lantern No] parameter (Line #9) with a previously selected value. If it is going to be the source for another object (Form or Report) then you can open the object with a Filter parameter instead, and leave out line #9 altogether.

SELECT TOP 5 tB.[Name of the Villager],
             tB.[Solar Lantern No],
             tP.[Payment Date],
             tP.[Cost Of Lantern],
             tP.[Payment],
             tP.[Balance]
FROM [Beneficiary] AS tB INNER JOIN [Payment] AS tP
  ON tB.[Solar Lantern No]=tP.[Solar Lantern No]
WHERE tB.[Solar Lantern No]=[Enter Solar Lanter No]
ORDER BY tP.[Payment Date] DESC

[sajitk]

Thanks NeoPa,

this works fine.

A couple of more queries:

Scenario IScenario I

I am entering a new record

1. I enter all the Beneficiary details in the Beneficiary form - linked to the Beneficiary table

2. I enter the Payment details in the payment form. In this form, I have a combo box containing all Solar lantern Nos extracted by a simple query. Now, what I want is that when a Lantern No is selected, the Name of the Beneficiary (FROM the BENEFICIARY table] is displayed.

Scenario IIScenario II

Lets take an example for better understanding:

In the payment form, I am updating a payment for a particular Solar Lantern No.
Lets assume Solar Lantern No = 1

when Solar Lantern No is making First payment
SL No = 1
Cost of S Lantern = 3500
Payment = 500
Payment date = 01/01/08 (dd/mm/yy)
Balance = 3000


Second Payment

SL No = 1
Cost of S Lantern = 3500 (this should come automatically)
Payment = 500
Payment date = 01/02/08
Balance = 2500 (this balance should be automatically updated)

so now the payment table will contain 2 records for Solar Lantern No 1.

Solar Lantern No Cost of Solar Lantern Payment Payment date Balance
1 3500 500 01/01/08 3000
1 3500 500 01/02/08 2500


Creating another table called Balance will do good, when we are generating records.

Balance table can contain:

Solar Lantern No (FK)
Balance Numeric

Would be grateful if you could help me in solving the 2 scenarios....A patient reading would make things easier.

Pls suggest.

Thanks

Sajit

The following is the basic SQL for your query. If you're going to create this in your code then you can replace the [Enter Solar Lantern No] parameter (Line #9) with a previously selected value. If it is going to be the source for another object (Form or Report) then you can open the object with a Filter parameter instead, and leave out line #9 altogether.

SELECT TOP 5 tB.[Name of the Villager],
             tB.[Solar Lantern No],
             tP.[Payment Date],
             tP.[Cost Of Lantern],
             tP.[Payment],
             tP.[Balance]
FROM [Beneficiary] AS tB INNER JOIN [Payment] AS tP
  ON tB.[Solar Lantern No]=tP.[Solar Lantern No]
WHERE tB.[Solar Lantern No]=[Enter Solar Lanter No]
ORDER BY tP.[Payment Date] DESC