Inner join

[Anon]

I have three tables.
Table "profile" (ID & position)
Table "requirements" (Position & requirements)
Table "staffrecord" (ID & requirements)
I would like to populate table "staffrecord" with a join from Table "profile" and Table "requirements" where there may be up to 30 rows of data put into table "staffrecord" as a result of the join. I have this:INSERT INTO staffrecord(ID,reqrmnt,due_in)SELECT profile.ID,requirements.requirement,requirements.duein FROM requirements INNER JOIN requirements ON profile.position=requirements.position WHERE profile.ID='$id'"
It inserts one row. Thats my problem. What is wrong with the join that only inserts one row or is that what a INSERT INTO ....SELECT
query does? Thanks for any help

[Anon]

I can't write SQL but I can make a fair attempt at reading it. The WHERE part at the end "WHERE profile.ID='$id'" could be giving you problems unless the '$id' is like a "wildcard". MSAccess uses the asterisk (*) as a wildcard. You could try to eliminate part of your problem by setting your criteria to match "profile" (position) with "requirements" (Position) i.e. WHERE profile.position=requirements.position. This should pull all the matches from your tables. You could then design another query (using the results from the first query) to select only the ID matches. Sorry if this does not help your problem. Databases are a law onto themselves.

regards

Geoff.