insert and update

[elianeasmar]

i have 2 tables: members and membercars. i need to be able to add more than one car for one member. so i thought if i added the second car, and i put a condition--where memberid=txtserial.text--the memberid is for the membercars and the txtserial.text is the serial for a member(autincremented). but i can't put a where condition in insert.
So any suggestions please?

 

[IanRyder]

but i can't put a where condition in insert.

Hi,

No you cannot put a where condition on an Inset statement and nor would you want to. You need to remember the concept of a relational data structure.

You will have a Parent Table, let’s say Member’s, and that table SHOULD be uniquely identified in some way. When you add each “MemberCar” then that “MemberCar” record should have a Field which holds the Unique Member reference which that car belongs to. This is the basic concept of a relational data structure.

Hope that helps.

Cheers,

Ian

BTW, I am tied up a lot at the moment, not posted for weeks, so I hope one of the other members will be able to help you further if you are still struggling.

 

[jmcilhinney]

As IanRyder suggests, it doesn't make sense to include a WHERE clause in an INSERT statement. A WHERE clause is to filter a list of existing records so that you may then act on just those that match the specified criteria. If you're inserting a record then you're not acting on existing records, so a WHERE clause has no place.

The Member table should have a MemberId column that uniquely identifies a Member record, i.e. the primary key. The MemberCar table then also has a MemberId record which contains the primary key of the Member record it is related to, i.e. a foreign key. When you insert a MemberCar record, you must insert the value into its MemberId field that comes from the Member Id field of the Member record it is related to. That's what you're thinking you need to put in the WHERE clause but it's just one of the field values that you set in the INSERT.