SEO

Hi,

I have table where i have the following fields in datasheetview:

id | date | image | question1 | question2 | question..N

I would like to have it in this way:

id | date | image | questionnr | answer
1 01-01-2004 test.tif 1 1000 (this is the value of
field question1)

As you can see the first 3 fields remains the same, but the records of those
3 fields should be inserted for each record of the question fields. I can do
this with a union query for each questionfield, but.....

How can i do this automatically, because there are more than 500 columns.

"Ezekiël" <ezekiël@lycos.com> wrote in message
news:415ecf9c$0$76534$b83b6cc0@news.wanadoo.nl...
> Hi,
>
> I have table where i have the following fields in datasheetview:
>
> id | date | image | question1 | question2 | question..N
>
> I would like to have it in this way:
>
> id | date | image | questionnr | answer
> 1 01-01-2004 test.tif 1 1000 (this is the value of
> field question1)
>
> As you can see the first 3 fields remains the same, but the records of
> those
> 3 fields should be inserted for each record of the question fields. I can
> do
> this with a union query for each questionfield, but.....
>
> How can i do this automatically, because there are more than 500 columns.
>
>

I believe "datasheet view" is an Access term - are you using Access or
MSSQL? Or Access with an MSSQL backend? Assuming you have MSSQL, you could
write something to do this, but I would suggest that you take the time to
fix your data model instead, as it will make everything easier for you.
Also, since MSSQL allows only 1024 columns per table, your current data
model means you are quite limited in the number of questions you can store.

Something like this might be a rough starting point:

create table Questions (
QuestionID int not null,
Question varchar(1000) not null,
Answer varchar(1000) not null,
constraint PK_Questions primary key (QuestionID)
)

create table Datasheet (
DatasheetID int not null,
DatasheetDate datetime not null,
ImageFileName varchar(1000) not null,
constraint PK_Datasheet primary key (DatasheetID)
)

create table DatasheetQuestions (
DatasheetID int not null,
QuestionID int not null,
constraint PK_DatasheetQuestions primary ky (DatasheetID, QuestionID)
)

Now your query is a simple join:

select
d.DatasheetID,
d.DatasheetDate,
d.ImageFileName,
dq.QuestionID,
q.Answer
from
Datasheet d
join DatasheetQuestions dq
on d.DatasheetID = dq.DatasheetId

join Questions q
on dq.QuestionID = q.QuestionID
order by
DatasheetID, QuestionID

Simon