Update Query - Please help!

[AccessHelp]

Good morning,

I posted the below problem last week, and I have not heard anything from
anyone. Can someone help me with the problem? I don't understand why the
update query would not populate correctly, and I really need your help.

Thanks.

Hello,

I have two fields (Student Name and Homework Type) in Table A. Table A may
contain more than one homework type per student. There are 4 different
homework types.

I also have Table B. Table B contains 5 fields (Student Name and 4 Homework
Types).

What I want to do is I want to know what types of homework each student do
in Table B by having X under each homework type column. I use Update query,
and it doesn't come out correctly.

For example, if I filter Homework Type 1 in Table A, I get 200 records. On
the other hand, I get only 150 records with X's in Table B.

In my Update query, I do Inner Join between Table A and Table B on Student.
Then I write this formula 'iif(Table A!Homework Type="1","X","")' next to the
Update To field for each type of homework.

Can someone help me why Table B does not come out correctly?

Thanks.

 

[Kevin Jameson]

you cant do this with microsoft access 2003

you must upgrade to access 20004 by clicking here:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html

HTH

Kevin

 

[Jeff Boyce]

Disregard the "cant do this with Access" message. It is SPAM (or worse).
Follow the link at your own peril...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Hard to say as I really am unsure of your table structure and data types
I am guessing that table A can have multiple records per student. Something
like
Name (a text field) and HomeworkType (a number field)

Spencer 1
Spencer 2
Alice 1
Alice 3
Alice 4

Table B has fields Like
Name
MathHomework
EnglishHomework
ArtHomework
SocialStudiesHomework

IF so, you really have a bad design and that is the source of your problems.

You would be better off if table B was
HomeworkCode (1, 2, 3, 4)
HomeworkDesc(MathHomework, EnglishHomework, etc.)

Then it would be fairly simple to join the two tables on HomeworkCode and
use a crosstab query to return the results you seem to want.

To populate table B with the data from table A you would need to clear all
the homework type fields with an update query that set them all to null and
then a second update query to populate them

Field: MathHomework
UpdateTo: IIF(TableA.HomeworkType = 1, "X")

Field: EnglishHomework
UpDateTo: IIF(TableA.HomeworkType = 2, "X")

etc.