ComboBox..Query..???

[pas123]

Hi
I’m new to this so be patient with me.
I have a db with 40 fields the unique field is the project number and each field after is a task number.
I have a form that uses a combobox [ProjectNumber] to select the project # and then based on that selection I need the next combobox [TaskNumber] to display (for selection) each populated field from the record.
I’m sure this is a Query function but I can’t figure out how to make it work. I would like to have it happen with [ProjectNumber] on change.
Thanks for any help.
Paul

 

[KevinM]

40 fields sounds like an awful lot.

Probably more than you actually need.
The 'give away' is when you said "each field after that is a task number"

Have a look at re-structuring your db into more thane one related tables.

Your 'Task numbers' should be in a seperate table and as records (rows) and not fields (columns).

These will be the 'many' side linked to another table for 'Project Numbers' whcih would be the 'one side'. Linked via the Project Number PK field.

 

[pas123]

If I set up a table for each Project number I'll have a bunch of tables with the same data except the project number. Is this really the way to do it?

paul

 

[KevinM]

I never said you needed a table for each Project number.

Without knowing much about your db, I can only speculate.

If each Project Number can have several Task numbers, then at the very least you need two tables. One to store your unique Proj Numbers and another to store their associated tasks.

Fields in each table would typically be...

Table: Projects

[ProjectNumber]: Unique PK field
[ProjectDescription] etc

Table: Tasks
[ProjectNumber]: Linked field from above
[TaskNumber] : Poss PK Field, if unique
[TaskDescription] etc

Yoyu then create a link between the two in the Relastionship Window.

Without getting into too much detail here, have a look at normalisation and relational databases on the net. There are several sites out there that will give a much better and more detailed answer that I can offer in a single post.

 

[pas123]

Now I see, Thanks so much
paul