Help on Key Violatoin Errors and Left Join

[icpsvt]

Alright-
This seemed like such an easy idea - but it's not working for me. I'm kinda messing around with access preparing for an upcoming project.

Here's what I'm trying to do:

I have a table with a bunch of client information [client]. Primary key is an autonumber field [id].
I have another table [signups] with classes taken information. This is linked to the client table through the autonumber field, both columns are titled [id].

This is my code. All I want to do is set test to on for every record:
UPDATE clients
LEFT JOIN signups
ON clients.ID = signups.id
SET signups.test = 1;

So when I do an inner join - it tells me 0 records are going to be updated - which makes sense.
A right join will also show 0 records to be updated.
A left join says all the records will show up - but then it tells me that there are key violations for every record.

Why is it doing this? And how do fix it?

 

[lespaul]

Ok, so you should have something like this:

tblClient
PK = ClientID Autonumber

tblSignUps
ClassID (should have another table with a list of classes?)
ClientID (Long - FK to tblClient)

From the update query you are showing it looks like you have the many side of the relationship in Clients?

Why don't you explain a little more about your table structure and what you are trying to do.

Leslie

 

[icpsvt]

That's fair.
Here's a second attempt:

tblClient
PK = ID Autonumber
firstname text
lastname text

tblSignups
ID longint - this is linked to ID from tblClient
overview - yes/no
test - yes/no

and my SQL:
UPDATE clients
LEFT JOIN signups
ON clients.ID=signups.id
SET signups.test = 1;

And what I'm trying to do:
I want to set test=yes for all the clients.
I may not have the tables set-up right for all I know. I know I haven't explicitly declared a foreign key. Is that something I need to do?

Thanks,
Jason

 

[lespaul]

So can a client have more than ONE signup?

If all you want to do is set test to true FOR EVERY RECORD in tblSignups then this will work, no need to join:

UPDATE tblSignups SET Test = True

Leslie

 

[icpsvt]

no a client can only have one signup.

the problem is that in the database - the signups table is empty, unless i check one of the options.
then it appears.

 

[icpsvt]

So there are 2000+ entries in the client table, and 3 entries I manually created in the signups table.

If that makes sense.

-Jason

 

[lespaul]

so what you really want to do is insert a record in tblSignups for each client in tblClinets?


Leslie

 

[icpsvt]

right.

The idea is we'll have multiple tables, a sign-up, class taken, and future classes.
So what I want to be able to say is:

Client #1 has taken class 1 2 3 4
Client #2 has taken class 2 3 4 6
Client #4 has signed up for class #1 3 4 5
Client #5 has signed up for class #1 3 4 2

That probably makes a little more sense.

Thanks.

 

[lespaul]

Ok then the proper table structure would be:

tblClients
ClientID
ClientName

if you only offer each class once you could have:

tblClass
ClassID
ClassName
ClassDate
InstructorID (FK to tblInstructor)

If each class can be offered many times then you would need to remove the date and instructor fields from tblClass and create a new table:

tblClassSchedule
ScheduleID
ClassID
ClassDate
InstructorID

tblClassEnrollment
EnrollmentID
ClassID (or ScheduleID) FK to one of the Class tables
PersonID
EnrollmentDate
CompletionDate

Since what you have is a many to many relationship between clients and classes, you need a junction table that represents both sides of the relationship (tblClassEnrollment).

Now, if you can't change your table structure at this point, I would still recommend that you read 'The Fundamentals of Relational Database Design'. Just reviewing the structure you posted, I would have to say that your database probably isn't normalized and if you can, you should think about changing the structure. There are several threads that go in depth regarding design issues for classes/courses. Secondly, if you can't make changes, the SQL you need to insert a record in tblSignups for each record in tblClients is:

INSERT INTO tblSignups (SELECT ClientID, false, true FROM tblClients)

Leslie