How do I turn the following query into a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I turn the following query into a table? I've gone through the
documentation, tried several potential leads, to no avail?

What is this about? I want to make a table out of all KeywordSets assigned
to a KSGroup whose ID is 5. Five consists of persons. A keywordset can be
assigned to more than one group, for example Persons, Palestinians and
possibly others.

SELECT KeywordSets.KeywordSet, KeywordSets.ID
FROM KSGroups INNER JOIN (KeywordSets INNER JOIN KSGroupKSAssignments ON
KeywordSets.ID = KSGroupKSAssignments.KeywordSetID) ON KSGroups.ID =
KSGroupKSAssignments.KSGroupID
WHERE (((KSGroups.ID)=5))
ORDER BY KeywordSets.KeywordSet;

Thank you very much.

dennist685
 
do you mean that the query is returning the records you need, and now you
want to put those records into a new table? if yes, open the query in design
view. on the menu bar, click Query, Make-Table Query, and enter the name you
want for the new table, then click OK. then click the Run button on the
toolbar.

hth
 
tina,

I created the table Person easily enough. However, since the number of
keywordsets that are assigned to Person grows daily, I needed an additional
function. Creating a PersonDelete query was easy enough, but refilling the
table with an append function didn't work.

Here's the sql the append function created:

INSERT INTO Person ( KeywordSet, ID, ID )
SELECT KeywordSets.KeywordSet, KeywordSets.ID, KSGroups.ID
FROM KSGroups INNER JOIN (KeywordSets INNER JOIN KSGroupKSAssignments ON
KeywordSets.ID = KSGroupKSAssignments.KeywordSetID) ON KSGroups.ID =
KSGroupKSAssignments.KSGroupID
WHERE (((KSGroups.ID)=5))
ORDER BY KeywordSets.KeywordSet;

This created the error:

Duplicate output destination ID

Is there a solution to this. My problem in recreating the table is that I'm
expanding a project in vs2005 beta 2, and I would have to delete the table
and recreate it in code. Besides being cumbersome, the newsgroups said this
would create havoc with my dataset and dataset schema. (vs2005 dataset, not
vs2003 dataset)Then the queries would have to be recreated for the
tableadapters each time, and there is a question whether vs2005 can handle
all this gracefully.

dennist685
 
you can take the same query that you used as a Make-Table query, open it in
design view, from the menu bar click Query, Append to turn it into an append
query, enter the name of the "already-created" table into the dialog box and
click OK. check your field names in the query, presumably they've all be
automatically assigned.
Duplicate output destination ID
Click to expand...

i don't know offhand what might be causing that error message. if i recall
correctly, a value can be appended to an Autonumber field, even if that
number was previously used in the table, *as long as the number doesn't
currently exist* in the table.

try setting up the Append query as noted above. if you get an error message
when running it after the Delete query, try compacting the database before
between the two query runs.

hth
 
You can only insert into one table at a time.

And you can't insert into the same field twice. What you probably need is the
following modification to make the query work. I'm not sure that will give you
the results you want, so test it and see. Also, I doubt that the ORDER BY
clause has any real use in an insert statement - no proof, but I think the query
optimizer will ignore it.


INSERT INTO Person ( KeywordSet, ID )
SELECT KeywordSets.KeywordSet, KeywordSets.ID
FROM KSGroups INNER JOIN (KeywordSets INNER JOIN KSGroupKSAssignments ON
KeywordSets.ID = KSGroupKSAssignments.KeywordSetID) ON KSGroups.ID =
KSGroupKSAssignments.KSGroupID
WHERE (((KSGroups.ID)=5))
 
tina,
I did what you suggested but got the same error as before. I think the
problem is the first line in the generated query -INSERT INTO Person (
KeywordSet, ID, ID )

the second ID being that of KSGroup. No way to eliminate that.

Now I'll try John's suggestion
 
John,
Your solution worked perfectly

dennist685

John Spencer (MVP) said:
You can only insert into one table at a time.

And you can't insert into the same field twice. What you probably need is the
following modification to make the query work. I'm not sure that will give you
the results you want, so test it and see. Also, I doubt that the ORDER BY
clause has any real use in an insert statement - no proof, but I think the query
optimizer will ignore it.


INSERT INTO Person ( KeywordSet, ID )
SELECT KeywordSets.KeywordSet, KeywordSets.ID
FROM KSGroups INNER JOIN (KeywordSets INNER JOIN KSGroupKSAssignments ON
KeywordSets.ID = KSGroupKSAssignments.KeywordSetID) ON KSGroups.ID =
KSGroupKSAssignments.KSGroupID
WHERE (((KSGroups.ID)=5))
Click to expand...
 
Back
Top