Update Query

[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

 

[RickSpr]

The Format property doesn't control how data is stored, only how it is formatted in a datasheet or (by default) in a form or report. Similarly, the Input Mask property only applies to how text is entered into a datasheet, form or report, not how that text is converted and stored in the table. If you define a field as Integer, it will always be stored as a 2-byte signed binary value.

Nevertheless, that doesn't explain why your query "doesn't work", because Max() applied to an integer field should always select the same value as Max() applied to that field converted to a string format with leading zeros. Perhaps I don't understand what you mean by "doesn't work". Could you explain that with examples of what it returned versus what you expected?

By the way, the "Or Is Null" parts you showed above are incomplete. It seems as if there is a larger expression here and you're not showing all of it to us. Perhaps you should; your problem may be in the part we don't know about.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[JonFer]

From what I can see, you were joining Students and SAT tables on SSN with a Left Join and using the criteria you posted to get the Max date taken if they have one. Now you want a similar criteria on SATComb so the most important thing is that you also change the field against which you're applying the criteria to SATComb from DateTaken (sorry if that is obvious to you but I don't know your experience level).

As an alternative, this gets the max SAT for a student and includes students who have not taken the SAT yet:

Select Student.SSN, Max(SATComb) as MaxSATComb
From Students Left Join SAT on Students.SSN=SAT.SSN

But this isn't good if you want other values from the SAT table. As Rick said, posting the complete SQL would help.


John

http://www.interautomateconsulting.com

 

[debbieg]

Thanks for the replies.

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.

Thanks,
Debbie

 

[debbieg]

My subject was wrong ... this is was an append query, not an update query.

Sorry.

Thanks,
Debbie