If statement in Update query

[Gntlhnds]

I have an Update Query that updates two fields [Last Name], [First Name] in
one table (Barracks Rooms) based on one field [Room #] in another table
(Students). If [Room #] in (Students) is Null, I want [Last Name], [First
Name] in (Barracks) to be Null. If [Room #] in (Students) has a value, I
want [Last Name], [First Name] in (Barracks Rooms) to reflect [Last Name],
[First Name] in (Students). What would be my SQL statement for that? I've
already determined that I can't set it up the way I want in the design view.
Thanks for all the help.

 

[Gntlhnds]

Further clarification:

This query is run from a form in datasheet view, so I would actually prefer
if this would only make changes to the selected record. Again, thanks for
the help.

 

[Jeff Boyce]

Am I understanding you correctly? You already have values for [LastName]
and [FirstName] in one table, and you are trying to stuff those values into
a second table?

If so, why?! Access is a relational database, so you don't need to copy
over names and other fields from one spreadsheet to ... oops, I meant from
one table to another.<g>

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[TedMi]

You need to revisit your design. What if you have two students named John
Smith? Which one gets assigned to a particular room number?
You should have a Student table that has StudentID, FirstName, LastName.
That way, each of two (or more) John Smiths gets identified by a different
StudentID. You Rooms table should then carry Room#, StudentID
-TedMi

Further clarification:

This query is run from a form in datasheet view, so I would actually
prefer
if this would only make changes to the selected record. Again, thanks for
the help.

I have an Update Query that updates two fields [Last Name], [First Name]
in
one table (Barracks Rooms) based on one field [Room #] in another table
(Students). If [Room #] in (Students) is Null, I want [Last Name],
[First
Name] in (Barracks) to be Null. If [Room #] in (Students) has a value, I
want [Last Name], [First Name] in (Barracks Rooms) to reflect [Last
Name],
[First Name] in (Students). What would be my SQL statement for that?
I've
already determined that I can't set it up the way I want in the design
view.
Thanks for all the help.

 

[Gntlhnds]

I have a table that has all of my students, and then a table that has all of
the barracks room I manage. What I want to happen is as I gain a student, I
have a combo box with all the available rooms, and I can select one to place
the student in. Then as the student leaves, I have a check-box (Archive)
that affects how the student is displayed on various forms, but it also
removes the room assignment for that student. The problem I'm having is the
combo box. Having the separate table was how I was keeping the combo box
updated so that it would only have the vacant rooms show up. If there is a
better way, I'm open. Thanks for the help.

Am I understanding you correctly? You already have values for [LastName]
and [FirstName] in one table, and you are trying to stuff those values into
a second table?

If so, why?! Access is a relational database, so you don't need to copy
over names and other fields from one spreadsheet to ... oops, I meant from
one table to another.<g>

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have an Update Query that updates two fields [Last Name], [First Name] in
one table (Barracks Rooms) based on one field [Room #] in another table
(Students). If [Room #] in (Students) is Null, I want [Last Name], [First
Name] in (Barracks) to be Null. If [Room #] in (Students) has a value, I
want [Last Name], [First Name] in (Barracks Rooms) to reflect [Last Name],
[First Name] in (Students). What would be my SQL statement for that?
I've
already determined that I can't set it up the way I want in the design
view.
Thanks for all the help.

.

 

[Jeff Boyce]

I'm not sure I get it yet, but it sounds like your tables may be set up to
"match" your physical situation. If so, check into "normalization" and
"relational database design" ... tables in a relational database like Access
need to reflect the "things" about which you keep information, and don't
have to (and shouldn't necessarily) be a match for the physical.

It sounds like you have Students, Rooms, and Student-in-Room ... these would
be the underlying tables if I've correctly understood your situation.

Until your data structure is something more like what Access works best
with, coming up with a "how do I do it" answer may only make your
application more complex than it needs to be.

Please post your underlying data structure. Here's a simplified example of
a Registration database...

tblStudent
StudentID
LastName
FirstName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelRegistration
RegistrationID
StudentID
ClassID
RegistrationDate

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have a table that has all of my students, and then a table that has all
of
the barracks room I manage. What I want to happen is as I gain a student,
I
have a combo box with all the available rooms, and I can select one to
place
the student in. Then as the student leaves, I have a check-box (Archive)
that affects how the student is displayed on various forms, but it also
removes the room assignment for that student. The problem I'm having is
the
combo box. Having the separate table was how I was keeping the combo box
updated so that it would only have the vacant rooms show up. If there is
a
better way, I'm open. Thanks for the help.

Am I understanding you correctly? You already have values for [LastName]
and [FirstName] in one table, and you are trying to stuff those values
into
a second table?

If so, why?! Access is a relational database, so you don't need to copy
over names and other fields from one spreadsheet to ... oops, I meant
from
one table to another.<g>

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have an Update Query that updates two fields [Last Name], [First Name]
in
one table (Barracks Rooms) based on one field [Room #] in another table
(Students). If [Room #] in (Students) is Null, I want [Last Name],
[First
Name] in (Barracks) to be Null. If [Room #] in (Students) has a value,
I
want [Last Name], [First Name] in (Barracks Rooms) to reflect [Last
Name],
[First Name] in (Students). What would be my SQL statement for that?
I've
already determined that I can't set it up the way I want in the design
view.
Thanks for all the help.

.

 

[Gntlhnds]

Here's my tables:

tblStudents
ID
LastName
FirstName
Age
ArrivalDate
GradDate
DepartureDate
Class#
Barracks
Room#
Archive
....

tblBarracks
ID
Bldg#
Room#
LastName
FirstName

tblPFA
ID
Name
Age
Date
Weight
Height
BCA
....

I actually have more fields than those for tblStudents and tblPFA, but those
are the relevant fields. Hope that helps.

I'm not sure I get it yet, but it sounds like your tables may be set up to
"match" your physical situation. If so, check into "normalization" and
"relational database design" ... tables in a relational database like Access
need to reflect the "things" about which you keep information, and don't
have to (and shouldn't necessarily) be a match for the physical.

It sounds like you have Students, Rooms, and Student-in-Room ... these would
be the underlying tables if I've correctly understood your situation.

Until your data structure is something more like what Access works best
with, coming up with a "how do I do it" answer may only make your
application more complex than it needs to be.

Please post your underlying data structure. Here's a simplified example of
a Registration database...

tblStudent
StudentID
LastName
FirstName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelRegistration
RegistrationID
StudentID
ClassID
RegistrationDate

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have a table that has all of my students, and then a table that has all
of
the barracks room I manage. What I want to happen is as I gain a student,
I
have a combo box with all the available rooms, and I can select one to
place
the student in. Then as the student leaves, I have a check-box (Archive)
that affects how the student is displayed on various forms, but it also
removes the room assignment for that student. The problem I'm having is
the
combo box. Having the separate table was how I was keeping the combo box
updated so that it would only have the vacant rooms show up. If there is
a
better way, I'm open. Thanks for the help.

Am I understanding you correctly? You already have values for [LastName]
and [FirstName] in one table, and you are trying to stuff those values
into
a second table?

If so, why?! Access is a relational database, so you don't need to copy
over names and other fields from one spreadsheet to ... oops, I meant
from
one table to another.<g>

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have an Update Query that updates two fields [Last Name], [First Name]
in
one table (Barracks Rooms) based on one field [Room #] in another table
(Students). If [Room #] in (Students) is Null, I want [Last Name],
[First
Name] in (Barracks) to be Null. If [Room #] in (Students) has a value,
I
want [Last Name], [First Name] in (Barracks Rooms) to reflect [Last
Name],
[First Name] in (Students). What would be my SQL statement for that?
I've
already determined that I can't set it up the way I want in the design
view.
Thanks for all the help.


.

.

 

[Jeff Boyce]

See comments in-line below...

Here's my tables:

tblStudents
ID
LastName
FirstName
Age

You'll find it much less work in the long run to store Date of Birth. If
you store Age, you can count on it being out-of-date almost immediately!
You can use a procedure to calculate Age from DOB.

ArrivalDate
GradDate
DepartureDate
Class#
Barracks
Room#

All of the above have nothing to do with a person (i.e., student), but
everything to do with arrival, departure, graduation and where the student
is housed. These belong in different tables (see my previous response).

Archive

If it is your intent to use this field ([Archive]) to identify when the
record of a student-in-room is no longer valid, consider using a date or
date/time value, rather than a simple yes/no.

...

tblBarracks
ID
Bldg#
Room#
LastName
FirstName

Nope, Barracks don't have a "FirstName" or a "LastName" -- these are
characteristics of people/students. And what are you storing in that [ID]
field?

tblPFA
ID
Name
Age
Date
Weight
Height
BCA
...

I don't understand what PFA represents... but it appears you have more
"person" information here, and it appears you are repeating a person's name
for the THIRD time. You only need it once (again, see my previous post).

I actually have more fields than those for tblStudents and tblPFA, but
those
are the relevant fields. Hope that helps.

My suggestion would be to look back over my previous response. The table
structure you've described will cause both you and Access to work overtime
to come up with workarounds. A well-normalized table structure will let you
(and Access) make good use of the features and functions that expect
well-normalized data.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.