Swap data in multiple fields between 2 records

[Agnelo Fernandes]

I am designing a database for candidates attending a course in a given year.
The list of candidates coming on given day is prefixed. Say for a partiular
course 'Foundation Course' a person X scheduled to attend on 21st March is
swapping with a person Y who was schedules on 30th march.
Information of person X (such as his college, contact details, etc) is on
record A and information of person Y is on record B.

I need to swap X and Y with all their details (i.e fields) between the 2
records.
Can anyone help me?

 

[Rob Parker]

If you're designing your database correctly, there should be no problem with
this, because the details for each person will be in a separate table from
the details of people attending courses. That table will only contain a
single field (eg. PersonID) identifying the person attending the course, and
when you change that to the PersonID from the table containing all people,
everything else automatically follows. If that's not what's happening, then
I strongly suggest that you go and do some reading on relational databases
and table normalisation, because you've got it wrong, and you need to
redesign your database structure. This should help:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

In particular, follow the links to articles about database normalization.

HTH,

Rob

 

[Agnelo Fernandes]

Hi Rob,

Thanks for the help. I understand now the basic concept to swap details
between 2 records as long as their tables are related.

However, as u suggested - after I insert the new 'PersonID' inorder to
replace the the existing candidate - should I then press 'refresh data' and
will the details of the replaced candidate be swapped with the shifted
candidate without entering the personID at the other end .

I hope it makes sense.

 

[Rob Parker]

Comments in-line.

Rob

Hi Rob,

Thanks for the help. I understand now the basic concept to swap details
between 2 records as long as their tables are related.

However, as u suggested - after I insert the new 'PersonID' inorder to
replace the the existing candidate - should I then press 'refresh data'

What is the 'refresh data' object that you are referring to? Is this a
button on your form? A menu item?

If you are entering/editing data in your table(s) via bound forms, there is
no need to do anything - it just happens; the table records are updated as
soon as you move off the current record, by any means, including navigating
to another record, or closing the form. If you're not doing it that way,
you'll need to explain what you're talking about here - I simply don't
understand your question. And further, if you're not doing it that way -
ie. using bound forms for your data entry/editing - then you should be doing
so; you should not be entering data directly into tables (or updateable
queries), and I suspect that using unbound forms (which involves
considerable VBA coding for any operation) is way beyond your level of
expertise.

and
will the details of the replaced candidate be swapped with the shifted
candidate without entering the personID at the other end .

I've got no idea what you mean by "the other end". If you mean in a query
that is combining your related tables, or in a form/report based on such a
query, then the answer is yes (you should already have a record for the
person in the persons table; the entry from the primary key of that table
(what I suggested as the PersonID field) is what you should be
storing/updating in the people/courses table; when you change an entry in
the people/courses table, a query will automatically return the person data
for the changed PersonID). But if you mean something else, I don't know
what you mean. And if what I've just written doesn't make sense to you,
then go and read some more about related tables and data normalisation.

I hope it makes sense.

Not really. It seems to me that you don't yet understand exactly how
related tables work.

If you need more help with this, please post details of your tables and the
fields in each table.