Populating fields/table

[jax]

I have search the forum but dont seem to be able to find the solution i am looking for. I am hoping its because it has a simple solution .......

I have a database which records grant applications for the current year. What i also have is a table which stores all the grant applications from last year and i have a search form which the user can search to see if the applicant applied last year. What i want the user to be able to do is if the applicant did apply last year - to be able to double click on the particular applicant on the search form and populate the fields in the new applicant table, show on the new applicant form, and delete the applicant from last years table. This would save the user time because they would not have to retype name, address etc. Can someone please point me in the right direction? I seem to have a mental blank with this one.

 

[ColinEssex]

There's an excellent sample database called "Quicksearch" that will do this for you with only slight amendment. I use it in one of my databases to do what you need.

Its based on a ListBox search and you click the search result to populate fields.

Col

 

[Rich]

I have a database which records grant applications for the current year. What i also have is a table which stores all the grant applications from last year and i have a search form which the user can search to see if the applicant applied last year. What i want the user to be able to do is if the applicant did apply last year - to be able to double click on the particular applicant on the search form and populate the fields in the new applicant table, show on the new applicant form, and delete the applicant from last years table. This would save the user time because they would not have to retype name, address etc. Can someone please point me in the right direction? I seem to have a mental blank with this one.

I have to say at first glance your structure is seriously flawed, you shouldn't have to create a new table every year and copy existing records into new tables

 

[jax]

Still struggling with this one and we really need to start using it, we dont want to have to retype all the applicants names etc when they applied last year. Why should the information not be stored in two seperate tables? How should it be done? Any suggestions would be welcome

 

[Mile-O]

You need one table for applicants and one table for applications - these are two separate entities and need to be modelled so. You have a many-to-many relationship between these two entities so you need a third table to simulate this relationship (a junction table).

The structure of these tables would be like this:

tblApplicants
ApplicantID (Autonumber, Primark Key)
Forename
Surname
DateOfBirth
Address1
Address2
Telephone
etc..


tblApplications
ApplicationID (Autonumber, Primary Key)
DateOfApplication
DateReviewed
etc...

tblApplicantsToApplications
ApplicantID (Number, Primary Key, Foreign Key)
ApplicationID (Number, Primary Key, Foreign Key)

 

[Mile-O]

In reply to your question about why should data not be stored in two separate tables? The answer: one will suffice. It goes against the rules of normalisation and cascades a need for too many objects. With one table you just query on a specific year and you can get the results you want. With two tables you increase the need for more queries, etc.