Can I use element in a field as field name in another table?

[mdpepper1]

I am an IT placement student working for a wildlife charity for one year.



I have a table of 20 species. Each row contains some information on a single species. I want to add functionality to add a new species. That is easy.

I have another table which is for the legislations that apply to each species. Each species is used as a field name in the legislation table and there either exists a 'Y' in its cell relevant to each legislation, or a null.

Can I write code/create a macro or expression to add a new species to the species table, which will automatically generate a new column in the legislation table, and also to generate a new field in the 'add legislation' form?

If this is possible, can someone give me a few pointers on how to achieve it?

Thanks,
David

[ssanfu]

I am an IT placement student working for a wildlife charity for one year.

I have a table of 20 species. Each row contains some information on a single species. I want to add functionality to add a new species. That is easy.

I have another table which is for the legislations that apply to each species. Each species is used as a field name in the legislation table and there either exists a 'Y' in its cell relevant to each legislation, or a null.

Can I write code/create a macro or expression to add a new species to the species table, which will automatically generate a new column in the legislation table, and also to generate a new field in the 'add legislation' form?

If this is possible, can someone give me a few pointers on how to achieve it?

Thanks,
David

Yes, you can. There are lots of examples if you search. Having said that, this is an *extremely* bad design. Your structure is not/will not be normalized.

After you add the new fields, what then? You will have to redesign your queries, forms & reports for each new species and/or legislation.

Good luck.....

[mdpepper1]

Yes, you can. There are lots of examples if you search. Having said that, this is an *extremely* bad design. Your structure is not/will not be normalized.

After you add the new fields, what then? You will have to redesign your queries, forms & reports for each new species and/or legislation.

Good luck.....

Thank you. I am in agreement. I have decided to normalise my tables further on the back end and tackle it that way.

[mdpepper1]

Ok, I have fixed my table structure. I am now having trouble with populating my form.

I want to add a species, then have a list of check boxes of the existing pieces of legislation in the legislation table.

Is this the process I should undertake:

1. create form

2. add facility to add species

4. have a button that when there's an onclick event run a query to obtain all pieces of legislation in the legislation table

5. use result from query to create a list of check boxes on the form

6. add button to add the boxes that have been ticked.

David

[ssanfu]

Ok, I have fixed my table structure. I am now having trouble with populating my form.

What is the structure now? Would you attach a PDF of the relationship window?

5. use result from query to create a list of check boxes on the form

6. add button to add the boxes that have been ticked.

David

This still sounds like you want to modify the forms/tables/reports "on-the-fly". If the structure is correct, you should never have to modify the tables/forms/reports just because you add new data; of course, if you add or delete functionality, that is a different matter.