Update Query

  • Thread starter Thread starter DebbieG
  • Start date Start date
D

DebbieG

I have an update query that was working great until there needed to be a
change made.

Previously, I was told to gather the most current score by doing:
(SELECT MAX(DateTaken) FROM SAT Where SAT.SSN = Students.SSN) Or Is Null

Now they want the highest score no matter when the test was taken. I tried
to add this code
(SELECT MAX(SATComb) FROM SAT Where SAT.SSN = Students.SSN) Or Is Null
but it won't work because I had to do this to the field:
defSATComb: Format([SATComb],"0000")
because it has to output 4 characters and it's not finding anything with
less than 4 characters or the nulls.

In the SAT table for the SATComb field, I have:

Field size: Integer
Format: Fixed
Input Mask: 0000;1;_

but if I enter a score of 0720 it stores as 720.

Am I missing something very simple? Can anyone help me?

Thanks in advance,
Debbie
 
Debbie

When is a number not a number? (no, not a Zen thing, or even rhetorical...)

If you won't do math on it, it isn't a number. Although it is called a SSN,
it doesn't make much sense to store it as a numeric type.

My hunch is that you are trying to store 0720 (a literal string of
characters that happen to be digits) in a numeric field -- and Access is
rightfully saying "that leading zero is meaningless -- the value is 720".
 
If you want to store leading zeroes on a number then don't use a number field.
You have to use a text field.

As far as your query goes - you are showing us a piece of the query and it is
difficult to tell what you are doing.

A GUESS
SELECT MAX(FORMAT([SatComb],"0000")) FROM ...

This may very well not be the most efficient way to accomplish whatever you are
trying to accomplish.
 
I got what I needed by
appending Format([SATComb],"0000") AS defSATComb to SATComb
and not appending ((SAT.SATComb)=(SELECT MAX(SATComb) FROM SAT Where SAT.SSN
= Students.SSN) Or (SAT.SATComb) Is Null) - just using it for my criteria.


Debbie

When is a number not a number? (no, not a Zen thing, or even rhetorical...)

If you won't do math on it, it isn't a number. Although it is called a SSN,
it doesn't make much sense to store it as a numeric type.

My hunch is that you are trying to store 0720 (a literal string of
characters that happen to be digits) in a numeric field -- and Access is
rightfully saying "that leading zero is meaningless -- the value is 720".
 
I got what I needed by
appending Format([SATComb],"0000") AS defSATComb to SATComb
and not appending ((SAT.SATComb)=(SELECT MAX(SATComb) FROM SAT Where SAT.SSN
= Students.SSN) Or (SAT.SATComb) Is Null) - just using it for my criteria.


If you want to store leading zeroes on a number then don't use a number
field.
You have to use a text field.

As far as your query goes - you are showing us a piece of the query and it
is
difficult to tell what you are doing.

A GUESS
SELECT MAX(FORMAT([SatComb],"0000")) FROM ...

This may very well not be the most efficient way to accomplish whatever you
are
trying to accomplish.
 
I misquoted ... this is an append query, not an update query.


I have an update query that was working great until there needed to be a
change made.

Previously, I was told to gather the most current score by doing:
(SELECT MAX(DateTaken) FROM SAT Where SAT.SSN = Students.SSN) Or Is Null

Now they want the highest score no matter when the test was taken. I tried
to add this code
(SELECT MAX(SATComb) FROM SAT Where SAT.SSN = Students.SSN) Or Is Null
but it won't work because I had to do this to the field:
defSATComb: Format([SATComb],"0000")
because it has to output 4 characters and it's not finding anything with
less than 4 characters or the nulls.

In the SAT table for the SATComb field, I have:

Field size: Integer
Format: Fixed
Input Mask: 0000;1;_

but if I enter a score of 0720 it stores as 720.

Am I missing something very simple? Can anyone help me?

Thanks in advance,
Debbie
 
Back
Top