Update Query, 2007 - Not Working?

[Jim Dudley]

I posted this yesterday but under the wrong discussion group, any help would
be appreciated.

SQL Code for update query.

Query is not posting values from Registration
Table([Workshop_Registrations]) to the Summary Table (Workshop_Credits]).

Any suggestions as to Why????

SQL
UPDATE Workshop_Registrations INNER JOIN Workshop_Credits ON
Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num SET
Workshop_Credits.EL_Credits =
IIf(([Worshop_Registrations].[Type]="EL"),(Val(nz("Workshop_Credits].[EL_Credits]",0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.OD_Credits =
IIf(([Worshop_Registrations].[Type]="OD"),(Val(nz([Workshop_Credits].[OD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PD_Credits =
IIf(([Worshop_Registrations].[Type]="PD"),(Val(nz([Workshop_Credits].[PD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"),(Val(nz([Workshop_Credits].[PL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =
IIf(([Worshop_Registrations].[Type]="GC"),(Val(nz([Workshop_Credits].[GC_Credits],0))+(Val(nz([Workshop_Registratrions].[Credits],0))))),
Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)+Nz([Workshop_Credits].[OD_Credits],0)+Nz([Workshop_Credits].[PD_Credits],0)+Nz([Workshop_Credits].[PL_Credits],0)+Nz([Workshop_Credits].[GC_Credits],0),
Workshop_Credits.Posted = Yes, Workshop_Credits.Post_Date = Date(),
Workshop_Credits.Post_Time = Time(), Workshop_Registrations.Posted = Yes,
Workshop_Registrations.Post_Date = Date(), Workshop_Registrations.Post_Time =
Time();

Note Both files are showing Posting dates and times but the values are not
being input into the [Workshop_Credits] Table.

 

[Tom van Stiphout]

On Thu, 19 Mar 2009 05:07:01 -0700, Jim Dudley <Jim
(e-mail address removed)> wrote:

So you're saying this update statement succeeds in the sense that all
records now have the new date and time stamp, but XX_Credits does not
have a new value.
First off it's strange you don't have a WHERE clause; perhaps you left
that off for clarity. Or you DID mean to update all rows.
I would simplify the problem, and as a test write the SQL with:
.... SET Workshop_Credits.EL_Credits = 10, ...
I also seem to see that you have the IIf statement with only two
arguments? There should be three.

-Tom.
Microsoft Access MVP

 

[Jim Dudley]

Thank you for looking at my problem Tom. It is appreciated. As usual, I
needed this to work yesterday!):

Background Summary:

I have on the Summary side [Workshop_Credits], 514 students who may have
earned credits in 5 different categories. These are the records I want to add
credits to and total.

I have 3,857 records on the Data side [Workshop_Registrations]. Each record
contains a single students attendance at a workshop and a credit earned. Each
record contains a student number which is unique to that student and in the
Summary Table, Student number is the Primary Key. The Registration Table has
a Auto Number [ID] field as the Primary Key.

I am trying to tell the query to add each credit (from the
[Workshop_Registrations] Table) to its' appropriate Type in the Summary Table
and to add that same credit number to the [Total_Credits] field for that
particular student.

Each student may have many records in the Registration Table but only One
record in the Summary Table.

Questions:

If the IIF stmt looks at a record and it is not "EL" Type, I want it to then
evaluate the next scenerio and the next until it finds which Type to post the
credit to. What would my 3rd argument be?

What is the purpose of the "10" in the "SET" stmt.?

I do intent to post all 3K+ records to the Summary file. My where clause
would be the student number on the many side matches the Primary Key Student
number on the Summary side. In other words if a student num has 10 records on
the many side, post each record to its appropriate field on the one side.

Any further insite you can provide (ASAP) would be sincerely appreciated.

Thank you.

Jim

So you're saying this update statement succeeds in the sense that all
records now have the new date and time stamp, but XX_Credits does not
have a new value.
First off it's strange you don't have a WHERE clause; perhaps you left
that off for clarity. Or you DID mean to update all rows.
I would simplify the problem, and as a test write the SQL with:
.... SET Workshop_Credits.EL_Credits = 10, ...
I also seem to see that you have the IIf statement with only two
arguments? There should be three.

-Tom.
Microsoft Access MVP

I posted this yesterday but under the wrong discussion group, any help would
be appreciated.

SQL Code for update query.

Query is not posting values from Registration
Table([Workshop_Registrations]) to the Summary Table (Workshop_Credits]).

Any suggestions as to Why????

SQL
UPDATE Workshop_Registrations INNER JOIN Workshop_Credits ON
Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num SET
Workshop_Credits.EL_Credits =
IIf(([Worshop_Registrations].[Type]="EL"),(Val(nz("Workshop_Credits].[EL_Credits]",0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.OD_Credits =
IIf(([Worshop_Registrations].[Type]="OD"),(Val(nz([Workshop_Credits].[OD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PD_Credits =
IIf(([Worshop_Registrations].[Type]="PD"),(Val(nz([Workshop_Credits].[PD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"),(Val(nz([Workshop_Credits].[PL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =
IIf(([Worshop_Registrations].[Type]="GC"),(Val(nz([Workshop_Credits].[GC_Credits],0))+(Val(nz([Workshop_Registratrions].[Credits],0))))),
Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)+Nz([Workshop_Credits].[OD_Credits],0)+Nz([Workshop_Credits].[PD_Credits],0)+Nz([Workshop_Credits].[PL_Credits],0)+Nz([Workshop_Credits].[GC_Credits],0),
Workshop_Credits.Posted = Yes, Workshop_Credits.Post_Date = Date(),
Workshop_Credits.Post_Time = Time(), Workshop_Registrations.Posted = Yes,
Workshop_Registrations.Post_Date = Date(), Workshop_Registrations.Post_Time =
Time();

Note Both files are showing Posting dates and times but the values are not
being input into the [Workshop_Credits] Table.

 

[Tom van Stiphout]

On Thu, 19 Mar 2009 08:05:01 -0700, Jim Dudley

Can you send me a zipped copy of a condensed version (stripped to bare
essentials) of your database? My email address minus the spam trap
should be obvious. No guarantees, of course.

To answer some of your questions:
IIf (test, true_value, false_value)
So you could say:
IIf(Worshop_Registrations].[Type]="EL", "yippee", "sad")

In the place of "sad" you can write another IIf expression, e.g.:
IIf(Worshop_Registrations].[Type]="EL", "yippee1",
IIf([Worshop_Registrations].[Type]="OD", "yippee2", "sad"))
See my point?

10 was just a simplification of a complex statement with Val, Nz, etc.
If I get 10 in that column, I know I have to focus on that expression.
If I don't, I know I have to focus on the overall structure of the
Insert statement.

-Tom.
Microsoft Access MVP

Thank you for looking at my problem Tom. It is appreciated. As usual, I
needed this to work yesterday!):

Background Summary:

I have on the Summary side [Workshop_Credits], 514 students who may have
earned credits in 5 different categories. These are the records I want to add
credits to and total.

I have 3,857 records on the Data side [Workshop_Registrations]. Each record
contains a single students attendance at a workshop and a credit earned. Each
record contains a student number which is unique to that student and in the
Summary Table, Student number is the Primary Key. The Registration Table has
a Auto Number [ID] field as the Primary Key.

I am trying to tell the query to add each credit (from the
[Workshop_Registrations] Table) to its' appropriate Type in the Summary Table
and to add that same credit number to the [Total_Credits] field for that
particular student.

Each student may have many records in the Registration Table but only One
record in the Summary Table.

Questions:

If the IIF stmt looks at a record and it is not "EL" Type, I want it to then
evaluate the next scenerio and the next until it finds which Type to post the
credit to. What would my 3rd argument be?

What is the purpose of the "10" in the "SET" stmt.?

I do intent to post all 3K+ records to the Summary file. My where clause
would be the student number on the many side matches the Primary Key Student
number on the Summary side. In other words if a student num has 10 records on
the many side, post each record to its appropriate field on the one side.

Any further insite you can provide (ASAP) would be sincerely appreciated.

Thank you.

Jim

So you're saying this update statement succeeds in the sense that all
records now have the new date and time stamp, but XX_Credits does not
have a new value.
First off it's strange you don't have a WHERE clause; perhaps you left
that off for clarity. Or you DID mean to update all rows.
I would simplify the problem, and as a test write the SQL with:
.... SET Workshop_Credits.EL_Credits = 10, ...
I also seem to see that you have the IIf statement with only two
arguments? There should be three.

-Tom.
Microsoft Access MVP

I posted this yesterday but under the wrong discussion group, any help would
be appreciated.

SQL Code for update query.

Query is not posting values from Registration
Table([Workshop_Registrations]) to the Summary Table (Workshop_Credits]).

Any suggestions as to Why????

SQL
UPDATE Workshop_Registrations INNER JOIN Workshop_Credits ON
Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num SET
Workshop_Credits.EL_Credits =
IIf(([Worshop_Registrations].[Type]="EL"),(Val(nz("Workshop_Credits].[EL_Credits]",0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.OD_Credits =
IIf(([Worshop_Registrations].[Type]="OD"),(Val(nz([Workshop_Credits].[OD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PD_Credits =
IIf(([Worshop_Registrations].[Type]="PD"),(Val(nz([Workshop_Credits].[PD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"),(Val(nz([Workshop_Credits].[PL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =
IIf(([Worshop_Registrations].[Type]="GC"),(Val(nz([Workshop_Credits].[GC_Credits],0))+(Val(nz([Workshop_Registratrions].[Credits],0))))),
Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)+Nz([Workshop_Credits].[OD_Credits],0)+Nz([Workshop_Credits].[PD_Credits],0)+Nz([Workshop_Credits].[PL_Credits],0)+Nz([Workshop_Credits].[GC_Credits],0),
Workshop_Credits.Posted = Yes, Workshop_Credits.Post_Date = Date(),
Workshop_Credits.Post_Time = Time(), Workshop_Registrations.Posted = Yes,
Workshop_Registrations.Post_Date = Date(), Workshop_Registrations.Post_Time =
Time();

Note Both files are showing Posting dates and times but the values are not
being input into the [Workshop_Credits] Table.

 

[Jim Dudley]

Tom,

I am relatively new to this discussion group and have in the past few days
had trouble with post, "System Temporarily Unavailable". Any way I cannot
decipher your email from the post so if you would be kind enough to send it
to me direct, I will clean up my application and zip it to you.

Your help is greatly appreciated.

Regards,

Jim
(e-mail address removed)

On Thu, 19 Mar 2009 08:05:01 -0700, Jim Dudley

Can you send me a zipped copy of a condensed version (stripped to bare
essentials) of your database? My email address minus the spam trap
should be obvious. No guarantees, of course.

To answer some of your questions:
IIf (test, true_value, false_value)
So you could say:
IIf(Worshop_Registrations].[Type]="EL", "yippee", "sad")

In the place of "sad" you can write another IIf expression, e.g.:
IIf(Worshop_Registrations].[Type]="EL", "yippee1",
IIf([Worshop_Registrations].[Type]="OD", "yippee2", "sad"))
See my point?

10 was just a simplification of a complex statement with Val, Nz, etc.
If I get 10 in that column, I know I have to focus on that expression.
If I don't, I know I have to focus on the overall structure of the
Insert statement.

-Tom.
Microsoft Access MVP

Thank you for looking at my problem Tom. It is appreciated. As usual, I
needed this to work yesterday!):

Background Summary:

I have on the Summary side [Workshop_Credits], 514 students who may have
earned credits in 5 different categories. These are the records I want to add
credits to and total.

I have 3,857 records on the Data side [Workshop_Registrations]. Each record
contains a single students attendance at a workshop and a credit earned. Each
record contains a student number which is unique to that student and in the
Summary Table, Student number is the Primary Key. The Registration Table has
a Auto Number [ID] field as the Primary Key.

I am trying to tell the query to add each credit (from the
[Workshop_Registrations] Table) to its' appropriate Type in the Summary Table
and to add that same credit number to the [Total_Credits] field for that
particular student.

Each student may have many records in the Registration Table but only One
record in the Summary Table.

Questions:

If the IIF stmt looks at a record and it is not "EL" Type, I want it to then
evaluate the next scenerio and the next until it finds which Type to post the
credit to. What would my 3rd argument be?

What is the purpose of the "10" in the "SET" stmt.?

I do intent to post all 3K+ records to the Summary file. My where clause
would be the student number on the many side matches the Primary Key Student
number on the Summary side. In other words if a student num has 10 records on
the many side, post each record to its appropriate field on the one side.

Any further insite you can provide (ASAP) would be sincerely appreciated.

Thank you.

Jim

So you're saying this update statement succeeds in the sense that all
records now have the new date and time stamp, but XX_Credits does not
have a new value.
First off it's strange you don't have a WHERE clause; perhaps you left
that off for clarity. Or you DID mean to update all rows.
I would simplify the problem, and as a test write the SQL with:
.... SET Workshop_Credits.EL_Credits = 10, ...
I also seem to see that you have the IIf statement with only two
arguments? There should be three.

-Tom.
Microsoft Access MVP


I posted this yesterday but under the wrong discussion group, any help would
be appreciated.

SQL Code for update query.

Query is not posting values from Registration
Table([Workshop_Registrations]) to the Summary Table (Workshop_Credits]).

Any suggestions as to Why????

SQL
UPDATE Workshop_Registrations INNER JOIN Workshop_Credits ON
Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num SET
Workshop_Credits.EL_Credits =
IIf(([Worshop_Registrations].[Type]="EL"),(Val(nz("Workshop_Credits].[EL_Credits]",0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.OD_Credits =
IIf(([Worshop_Registrations].[Type]="OD"),(Val(nz([Workshop_Credits].[OD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PD_Credits =
IIf(([Worshop_Registrations].[Type]="PD"),(Val(nz([Workshop_Credits].[PD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"),(Val(nz([Workshop_Credits].[PL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =
IIf(([Worshop_Registrations].[Type]="GC"),(Val(nz([Workshop_Credits].[GC_Credits],0))+(Val(nz([Workshop_Registratrions].[Credits],0))))),
Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)+Nz([Workshop_Credits].[OD_Credits],0)+Nz([Workshop_Credits].[PD_Credits],0)+Nz([Workshop_Credits].[PL_Credits],0)+Nz([Workshop_Credits].[GC_Credits],0),
Workshop_Credits.Posted = Yes, Workshop_Credits.Post_Date = Date(),
Workshop_Credits.Post_Time = Time(), Workshop_Registrations.Posted = Yes,
Workshop_Registrations.Post_Date = Date(), Workshop_Registrations.Post_Time =
Time();

Note Both files are showing Posting dates and times but the values are not
being input into the [Workshop_Credits] Table.

 

[John Spencer]

Let's see in the first Set you have quotes around and the table and
field names and you have misspelled a table name in the IIF statement.

(Val(nz("Workshop_Credits].[EL_Credits]",0)) << no quotes
IIf(([Worshop_Registrations].[Type]="EL") << Workshop_Registrations

You also have an abundance of parentheses. Not all of them are needed,
but they should not cause a problem. I'll get you started and allow you
to clean up the mess.

UPDATE Workshop_Registrations INNER JOIN Workshop_Credits
ON Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num

SET Workshop_Credits.EL_Credits =
IIf([Workshop_Registrations].[Type]="EL"
, Val(nz(Workshop_Credits].[EL_Credits],0)) +
Val(nz([Workshop_Registrations].[Credits],0)))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Tom,

I am relatively new to this discussion group and have in the past few days
had trouble with post, "System Temporarily Unavailable". Any way I cannot
decipher your email from the post so if you would be kind enough to send it
to me direct, I will clean up my application and zip it to you.

Your help is greatly appreciated.

Regards,

Jim
(e-mail address removed)

On Thu, 19 Mar 2009 08:05:01 -0700, Jim Dudley

Can you send me a zipped copy of a condensed version (stripped to bare
essentials) of your database? My email address minus the spam trap
should be obvious. No guarantees, of course.

To answer some of your questions:
IIf (test, true_value, false_value)
So you could say:
IIf(Worshop_Registrations].[Type]="EL", "yippee", "sad")

In the place of "sad" you can write another IIf expression, e.g.:
IIf(Worshop_Registrations].[Type]="EL", "yippee1",
IIf([Worshop_Registrations].[Type]="OD", "yippee2", "sad"))
See my point?

10 was just a simplification of a complex statement with Val, Nz, etc.
If I get 10 in that column, I know I have to focus on that expression.
If I don't, I know I have to focus on the overall structure of the
Insert statement.

-Tom.
Microsoft Access MVP

Thank you for looking at my problem Tom. It is appreciated. As usual, I
needed this to work yesterday!):

Background Summary:

I have on the Summary side [Workshop_Credits], 514 students who may have
earned credits in 5 different categories. These are the records I want to add
credits to and total.

I have 3,857 records on the Data side [Workshop_Registrations]. Each record
contains a single students attendance at a workshop and a credit earned. Each
record contains a student number which is unique to that student and in the
Summary Table, Student number is the Primary Key. The Registration Table has
a Auto Number [ID] field as the Primary Key.

I am trying to tell the query to add each credit (from the
[Workshop_Registrations] Table) to its' appropriate Type in the Summary Table
and to add that same credit number to the [Total_Credits] field for that
particular student.

Each student may have many records in the Registration Table but only One
record in the Summary Table.

Questions:

If the IIF stmt looks at a record and it is not "EL" Type, I want it to then
evaluate the next scenerio and the next until it finds which Type to post the
credit to. What would my 3rd argument be?

What is the purpose of the "10" in the "SET" stmt.?

I do intent to post all 3K+ records to the Summary file. My where clause
would be the student number on the many side matches the Primary Key Student
number on the Summary side. In other words if a student num has 10 records on
the many side, post each record to its appropriate field on the one side.

Any further insite you can provide (ASAP) would be sincerely appreciated.

Thank you.

Jim
:

So you're saying this update statement succeeds in the sense that all
records now have the new date and time stamp, but XX_Credits does not
have a new value.
First off it's strange you don't have a WHERE clause; perhaps you left
that off for clarity. Or you DID mean to update all rows.
I would simplify the problem, and as a test write the SQL with:
.... SET Workshop_Credits.EL_Credits = 10, ...
I also seem to see that you have the IIf statement with only two
arguments? There should be three.

-Tom.
Microsoft Access MVP


I posted this yesterday but under the wrong discussion group, any help would
be appreciated.

SQL Code for update query.

Query is not posting values from Registration
Table([Workshop_Registrations]) to the Summary Table (Workshop_Credits]).

Any suggestions as to Why????

SQL
UPDATE Workshop_Registrations INNER JOIN Workshop_Credits ON
Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num SET
Workshop_Credits.EL_Credits =
IIf(([Worshop_Registrations].[Type]="EL"),(Val(nz("Workshop_Credits].[EL_Credits]",0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.OD_Credits =
IIf(([Worshop_Registrations].[Type]="OD"),(Val(nz([Workshop_Credits].[OD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PD_Credits =
IIf(([Worshop_Registrations].[Type]="PD"),(Val(nz([Workshop_Credits].[PD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"),(Val(nz([Workshop_Credits].[PL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =
IIf(([Worshop_Registrations].[Type]="GC"),(Val(nz([Workshop_Credits].[GC_Credits],0))+(Val(nz([Workshop_Registratrions].[Credits],0))))),
Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)+Nz([Workshop_Credits].[OD_Credits],0)+Nz([Workshop_Credits].[PD_Credits],0)+Nz([Workshop_Credits].[PL_Credits],0)+Nz([Workshop_Credits].[GC_Credits],0),
Workshop_Credits.Posted = Yes, Workshop_Credits.Post_Date = Date(),
Workshop_Credits.Post_Time = Time(), Workshop_Registrations.Posted = Yes,
Workshop_Registrations.Post_Date = Date(), Workshop_Registrations.Post_Time =
Time();

Note Both files are showing Posting dates and times but the values are not
being input into the [Workshop_Credits] Table.

 

[Jim Dudley]

Thank you, I will give your suggestions a try and let you know how I make out.

JD

Tom,

I am relatively new to this discussion group and have in the past few days
had trouble with post, "System Temporarily Unavailable". Any way I cannot
decipher your email from the post so if you would be kind enough to send it
to me direct, I will clean up my application and zip it to you.

Your help is greatly appreciated.

Regards,

Jim
(e-mail address removed)

On Thu, 19 Mar 2009 08:05:01 -0700, Jim Dudley

Can you send me a zipped copy of a condensed version (stripped to bare
essentials) of your database? My email address minus the spam trap
should be obvious. No guarantees, of course.

To answer some of your questions:
IIf (test, true_value, false_value)
So you could say:
IIf(Worshop_Registrations].[Type]="EL", "yippee", "sad")

In the place of "sad" you can write another IIf expression, e.g.:
IIf(Worshop_Registrations].[Type]="EL", "yippee1",
IIf([Worshop_Registrations].[Type]="OD", "yippee2", "sad"))
See my point?

10 was just a simplification of a complex statement with Val, Nz, etc.
If I get 10 in that column, I know I have to focus on that expression.
If I don't, I know I have to focus on the overall structure of the
Insert statement.

-Tom.
Microsoft Access MVP

Thank you for looking at my problem Tom. It is appreciated. As usual, I
needed this to work yesterday!):

Background Summary:

I have on the Summary side [Workshop_Credits], 514 students who may have
earned credits in 5 different categories. These are the records I want to add
credits to and total.

I have 3,857 records on the Data side [Workshop_Registrations]. Each record
contains a single students attendance at a workshop and a credit earned. Each
record contains a student number which is unique to that student and in the
Summary Table, Student number is the Primary Key. The Registration Table has
a Auto Number [ID] field as the Primary Key.

I am trying to tell the query to add each credit (from the
[Workshop_Registrations] Table) to its' appropriate Type in the Summary Table
and to add that same credit number to the [Total_Credits] field for that
particular student.

Each student may have many records in the Registration Table but only One
record in the Summary Table.

Questions:

If the IIF stmt looks at a record and it is not "EL" Type, I want it to then
evaluate the next scenerio and the next until it finds which Type to post the
credit to. What would my 3rd argument be?

What is the purpose of the "10" in the "SET" stmt.?

I do intent to post all 3K+ records to the Summary file. My where clause
would be the student number on the many side matches the Primary Key Student
number on the Summary side. In other words if a student num has 10 records on
the many side, post each record to its appropriate field on the one side.

Any further insite you can provide (ASAP) would be sincerely appreciated.

Thank you.

Jim
:

So you're saying this update statement succeeds in the sense that all
records now have the new date and time stamp, but XX_Credits does not
have a new value.
First off it's strange you don't have a WHERE clause; perhaps you left
that off for clarity. Or you DID mean to update all rows.
I would simplify the problem, and as a test write the SQL with:
.... SET Workshop_Credits.EL_Credits = 10, ...
I also seem to see that you have the IIf statement with only two
arguments? There should be three.

-Tom.
Microsoft Access MVP


I posted this yesterday but under the wrong discussion group, any help would
be appreciated.

SQL Code for update query.

Query is not posting values from Registration
Table([Workshop_Registrations]) to the Summary Table (Workshop_Credits]).

Any suggestions as to Why????

SQL
UPDATE Workshop_Registrations INNER JOIN Workshop_Credits ON
Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num SET
Workshop_Credits.EL_Credits =
IIf(([Worshop_Registrations].[Type]="EL"),(Val(nz("Workshop_Credits].[EL_Credits]",0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.OD_Credits =
IIf(([Worshop_Registrations].[Type]="OD"),(Val(nz([Workshop_Credits].[OD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PD_Credits =
IIf(([Worshop_Registrations].[Type]="PD"),(Val(nz([Workshop_Credits].[PD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"),(Val(nz([Workshop_Credits].[PL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =
IIf(([Worshop_Registrations].[Type]="GC"),(Val(nz([Workshop_Credits].[GC_Credits],0))+(Val(nz([Workshop_Registratrions].[Credits],0))))),
Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)+Nz([Workshop_Credits].[OD_Credits],0)+Nz([Workshop_Credits].[PD_Credits],0)+Nz([Workshop_Credits].[PL_Credits],0)+Nz([Workshop_Credits].[GC_Credits],0),
Workshop_Credits.Posted = Yes, Workshop_Credits.Post_Date = Date(),
Workshop_Credits.Post_Time = Time(), Workshop_Registrations.Posted = Yes,
Workshop_Registrations.Post_Date = Date(), Workshop_Registrations.Post_Time =
Time();

Note Both files are showing Posting dates and times but the values are not
being input into the [Workshop_Credits] Table.