Need help on a new database

[poofblah]

Hello everyone,



I'm relatively new to access and have taken on a project to create a new database. There are two problems I am running into with it. They relate to two tables: projects and clients. The projects table includes information such as the project number, type of loss, insured etc. The clients table includes people who refer the projects and their basic contact info.

1. In the projects table I need to enter the existing project numbers. The project number consists of the last two digits of the year and a number. Some examples would be 08-1009 or 98-2194. Unfortunately, this system presents a problem. When I sort the project numbers it will start in the year 2000 and go to 2010, then it will jump to 1991 and continue to 1999. Is there any way to sort the project numbers chronologically instead of numerically?

2. I must include the client and their contact info in the projects table. Is there a way to chose the client's name from a list box and have access copy the rest of the client's information from the client's table to the projects table? At first this may seem redundant, but the projects table needs to store the client's contact info as it was at the time of the project in case the client changes company.

Thanks in advance to any who help!

[airbreathingbiped]

Hi Poofblah,
I would store the date and the 4 digit number in separate fields in the project table. I would use a form to display the information out of the table. That way you can create a field on the form that will display the entire project number. That way, you can sort chronologically.

Yes it is possible to choose the client name from a list box. Again, use a form.

It is also possible to have Access copy the client contact info using DLookup