I am developing a small package using VB, Access database and Crystal Reports 8.0
There is a table named "tblstatus" having primary key as "branch_code" This table stores the latest status of different branch offices of an organisation.
Every time the status of a branch changes it is updated in this table. There is another table named tbl_arc which stores all the records of updation of the status of the differet branches. Whenever the status of a branch changes it is updated in the table "tblstatus" (The same record is updated)and a new record with status_date is added to the table tbl_arc.
table name:"tblstatus"
main columns:"branch_code"(Primary Key), "status", etc.
(This table stores one record(the latest status) of each branch)
table name : "tbl_arc"(Archive)
main columns : "branch_code", "status_date", "status" etc.
(This table stores upto 10-15 records(status on different dates) for each branch.
Now I want to add a crystal report which shows the status of all the branches on any given date. The user will select the date for which he wants the status from a datepicker given on a form and then the Crystal Report is called using a selection formula so that only one appropriate record for each branch is picked from the table tbl_arc whose status_date is <or= the date given by the user and at the same time maximum(if there are a number of records of a branch code having status_date<or= the date given by the user then the record with maximum date out of these will be shown) In this way the report have to show one record of each branch.
I don't know how to give the formula for doing this. Anyone who can help me is most welcome.
Thanks
There is a table named "tblstatus" having primary key as "branch_code" This table stores the latest status of different branch offices of an organisation.
Every time the status of a branch changes it is updated in this table. There is another table named tbl_arc which stores all the records of updation of the status of the differet branches. Whenever the status of a branch changes it is updated in the table "tblstatus" (The same record is updated)and a new record with status_date is added to the table tbl_arc.
table name:"tblstatus"
main columns:"branch_code"(Primary Key), "status", etc.
(This table stores one record(the latest status) of each branch)
table name : "tbl_arc"(Archive)
main columns : "branch_code", "status_date", "status" etc.
(This table stores upto 10-15 records(status on different dates) for each branch.
Now I want to add a crystal report which shows the status of all the branches on any given date. The user will select the date for which he wants the status from a datepicker given on a form and then the Crystal Report is called using a selection formula so that only one appropriate record for each branch is picked from the table tbl_arc whose status_date is <or= the date given by the user and at the same time maximum(if there are a number of records of a branch code having status_date<or= the date given by the user then the record with maximum date out of these will be shown) In this way the report have to show one record of each branch.
I don't know how to give the formula for doing this. Anyone who can help me is most welcome.
Thanks