Linked Field Names/Columns

[zyxwvu44]

Hello,

I am trying to make a new form with the same info as another but in a different view for easily updatable forms/reports. The current table i have looks like this:

Company Product Market Available?
1................1................2..........yes
1................5................2...........yes
2................1................1...........yes
2................2................6............yes

etc... with the numbers linked to tables with the actual name.

what i want to do is make a table with field names that correspond the different products so that it looks like this:

Company Market Product 1 Product 2 Product 3
1.............2............yes..........yes.............no
2.............1.............yes.........no................yes
1.............4..............no.........no................yes

Ive played around with crosstab queries but I'm not getting the results i want. Is there any way to have this new table linked to my first table so that if theres a new product # entered it will automatically make a new column on the new table and fill it in? Let me know if this is too confusing, Thanks for your help.

 

[Rabbie]

It looks like you want to denormalise your database with that table. Is it really a new table you want or just a different view of your data.

Denormalising is going to make life very very difficult for you because you will have to change everything every time you have a new product.

 

[zyxwvu44]

I guess its just a different view of the data. The end view i want is to select a product, and have a page with markets across the top, companies down the left and check boxes for each value. The problem is i have different groups of people, so some want markets 1,2,3. others wants markets 1,5,10,12 etc. I'm trying to not have to make 10 different queries and forms/reports because then if they add a new market or product they will have to make a new query and form/report

 

[Rabbie]

If its a different Dataview then I will have a go at tweaking a crosstab table for you. I should be able to get it done for you tonight. It would be helpful if you could let me know the layout of your tables so I can play around to get it right.

 

[zyxwvu44]

here is my relationships http://img338.imageshack.us/img338/5282/tableif8.jpg

Thanks for your help

 

[zyxwvu44]

Okay, i figured out how to get the data into the format i wanted, but i still have a more complicated problem. Is there any way to have variable fields in a form or report? eg. right now theres products a,b,c,d but if i add e it will automatically add this column to the form/report?