I am working on a form and have a need for a Master Permit number field that is not in the table. In the table there are fields of Permit_ID, Master_permit_ID and Permit_Number. I need the case of when there is a master permit id attached to a permit, that the permit number related to that master permit ID is returned. ex:
Permit ID: 164
Permit number: DSD08-000100
Master Pemit ID: 200
Master Permit number: DSD08-00050
The SQL that I am trying to use is:
select permit_no from ESMPRMTR
where permit_id in
(select master_permit_id from ESMPRMTR where permit_ID=master_permit_ID)
I have even writing a sup report, but can not get it to work. I am doing something wrong, but am at a loss.