Sql Search

[pobuckley]

Hi,
I have a program which stores pension contributions in a two table access database. Each Pension can be calculated any amount of times. I am having a problem searching as I must presume that I do not know the calculation run number. I therefore need to enter the Pension number which will return the list of runs performed on it. Then select which run I wish to view.
Is this possible or would it be too complicated.

SchemeDetails Table ContributionDetails Table

SchemeRunID (PK) Month
PensionID MonthlyCharge
DateCalculated Value of Contrib
SchemeRunID (FK)


The PK and FK are joined in Access Relationships
I have sent the info into the access db using ado and I display the contents of the ContributionDetails table in a flexgrid

[JaredM]

Create an SQL statement using a string in VBA if you're using access. Concatenate the variables in the parts where you need to specify criteria. Once the whole SQL statement is properly concatenated in a string apply it to the recordsource of the form.

MyForm.RecordSource = strMySQLStatement

Then show the appropriate fields of information on the form and navigate through the searched results. (NOTE: Concatenating a SQL statement together can be kinda of tricky due to SYNTAX error)

[sjwesley]

You could use a query like this,


select * from ContributionDetails where SchemeRunID in (select SchemeRunID from SchemeDetails where PensionID = your_actual_pension_IDyour_actual_pension_ID