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

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

You should not be using COLUMNS in the legislation table. You would use ROWS of data, not columns. I believe your table structure needs to be normalized. (See here for more about normalization)

boblarson said:

You should not be using COLUMNS in the legislation table. You would use ROWS of data, not columns. I believe your table structure needs to be normalized.

Click to expand...

Thank you for your reply. I have researched this since I have posted and I absolutely concur. I am still in the process of learning about how to tie this whole database thing together and this is one of the most important lessons I have learnt. Again, thanks for taking the time to get back to me, and rest assured, I will restructure the crap outta my tables.

Dave

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