Hello Amberly,
Good documentation!
SELECT tblContacts.*, qARCsel2Participants.ParticipantID
FROM tblContacts, qARCsel2Participants;
Whenever you add a table but you don't join it, for every entry in table A you'll get a combination of A plus B and vice versa. If you offered a power tool (powertool_tbl), saw (saw_tbl) and hammer (hammer_tbl) as a prize to your customers for winning a drawing and your query was
Select powertoolname, sawname, hammername
from powertool_tbl, saw_tbl, hammer_tbl
And you get 80,000 results
Then you tell your customers ,"YOU COULD WIN ONE OF 80,000 PRIZES!"
and really the query its just listing every possible combination of the 10 power tools, 16 saws and 12 hammers.
Now,
My knee jerk reaction why your query WITH the join wouldn't be working...
Is this (highlighted red) column in qARC3appContacts really named tblContacts.ParticipantID?
If so, your query should be.
SELECT tblContacts.*, qARCsel2Participants.ParticipantID
FROM qARCsel2Participants INNER JOIN tblContacts ON qARCsel2Participants
.tblContacts.ParticipantID = tblContacts.ParticipantID;
The above query may not be a valid query.
I'd strongly recommend renaming this column to simply ParticipantID if it is indeed named this way.
If you want to keep its foreign key table name, use the underscore _ symbol to break it from the table and column name.
Such as: tblContacts_ParticipantID
A period in true sql is used for tunnelling from a large object to a child object (server, database, table, column).
Thats why it doesn't seem logical to include it in your query (why you excluded it).
Lets say you rename it and your query should then be:
SELECT tblContacts.*, qARCsel2Participants.ParticipantID
FROM qARCsel2Participants INNER JOIN tblContacts ON qARCsel2Participants
.tblContacts_ParticipantID = tblContacts.ParticipantID;
If you STILL get 0 results, make sure the participantID in both tables are the same data type (int preferably).
About the delete:
INSERT INTO tblPrograms IN 'S:\Database\Zoran 4.0_Archive_be.mdb'
SELECT tblPrograms.*
FROM tblPrograms INNER JOIN qARCsel1Program ON tblPrograms.ProgramID = qARCsel1Program.ProgramID;
Your delete command should be:
DELETE FROM tblPrograms
WHERE tblPrograms.ProgramID in (Select ProgramID from tblprograms IN 'S:\Database\Zoran 4.0_Archive_be.mdb')
What you are doing is a sub query.
While we read querys left to right, top to bottom.
Databases actually perform subqueries first and work their way back so essentially its first going out and reading all the programID's in the archive.
Then it says ,"ok we have a list of programID's that are archived and now they shouldn't exist in the production database, lets start deleting"
I hope this helps,
James