I am building a timesheet which requires the employee to select three codes for costing. The choices are to come from a flat table of three costcode fields - it has to be a flat table to allow for import from another program.

The first choice relates to the client (or contract), the second a project, and the third a category. Not each client has the same set of projects, and not each project has the same set of categories.

I need to give the employees a simle drop-down box to choose the client, and then another drop-down box that only gives the options available based on the choice made in the client (contract) field.

I have the query on the first field in the timesheet looking up the costcode table using SELECT DISTINCT, which narrows their choice down to two (only two clients at present in the table). It's the next part that I am stuck on....

I know I need a SELECT FROM WHERE statement but am unsure of the correct syntax. eg:
SELECT CostCodes.Project
FROM CostCodes
WHERE CostCodes.Contract=TimeSheet.Contract;

Can anyone help me with the syntax?

>>>it has to be a flat table to allow for import from another program<<<

Have you considered converting it once you have imported it?

It's an option you may wish to consider, there is an explanation of how to do it here, along with some software (http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619%3ABlogPost%3A7026) (in the form of a form) to help you with the process.

No, the table needs to be flat. There will be a nightly update from the CRM software directly into the table - basically "synchronsing" the two. The client wants the table as is.

We just need to limit the values in the drop-down box, based on the value chosen in the previous one.

I don't much understand your tables, but if you have two tables in a query, you can either name both tables in the FROM clause (comma-separated), or convert the query to an INNER JOIN. I prefer to use an INNER JOIN. So maybe try something like this:


Select CostCodes.Project
From CostCodes as C
INNER JOIN TimeSheetTable as T on T.Contract = C.Contract

Maybe you're first query just needed to name the second table in the FROM clause like this:


SELECT CostCodes.Project
FROM CostCodes, TimeSheet
WHERE CostCodes.Contract=TimeSheet.Contract