Max Values of a field

  • Thread starter Thread starter Merrill
  • Start date Start date
M

Merrill

I am trying to use the DMax function in the Update cell of
an update query. I would like to set [Table2].[ValueA] =
the maximum of [Table1].[ValueA].

Table2 contains unique records, Table1 contains multiple
records. ValueA is actually a text box and is set in
Table1 based on given criteria, and I want to set [Table2].
[ValueA] to YES or "checked" if any of the like records in
Table1 are checked.

I appreciate any suggestions. Thus far, my DMax attempts
have yielded various errors, the latest is simply stated
as error type "Unknown".

Thanks.
 
You don't give us much to go on. How are Table1 and Table2 related? Through
which field? A value of -1 is assigned for a "checkbox" field that is set to
True (checked), while a value of 0 is assigned for an unchecked (False)
checkbox. Thus, looking for a maximum value won't work if you're trying to
find checked values.

Post some example records and the tables' structure so that we can see what
you have.
 
Okay, I understand the point about a numeric value of -1 for "checked" boxes.....could I use the DMin function in the same vein

Table1 and Table2 really are not related other than they have identical structures. Table1 is not normalized, I want to create Table2 via a make-table query that will have unique records (key set on CustNum), then populate the checkboxes in Table2 as a summary of all like records found in Table1

Example is as follows

Table

CustNum ValueA ValueB Value
---------- -------- -------- -------
1411 Checke
1411 Checke
1522 Checke
1644 Checke
1888 Checke
1888 Checke


I would like to build Table2 to include a compression of Table1 using unique CustNum, and finding all checks for each CustNum in Table1, resulting in the following

Table

CustNum ValueA ValueB Value
---------- -------- -------- -------
1411 Checked Checke
1522 Checke
1644 Checke
1888 Checked Checke


I hope this example makes sense. Table1 contains individual transactions recorded at the time of a business event, and I want to generate a table with a synopsis of all boxes checked

Thanks again




----- Ken Snell wrote: ----

You don't give us much to go on. How are Table1 and Table2 related? Throug
which field? A value of -1 is assigned for a "checkbox" field that is set t
True (checked), while a value of 0 is assigned for an unchecked (False
checkbox. Thus, looking for a maximum value won't work if you're trying t
find checked values

Post some example records and the tables' structure so that we can see wha
you have

--
Ken Snel
<MS ACCESS MVP

Merrill said:
I am trying to use the DMax function in the Update cell o
an update query. I would like to set [Table2].[ValueA]
the maximum of [Table1].[ValueA]
Table2 contains unique records, Table1 contains multipl
records. ValueA is actually a text box and is set i
Table1 based on given criteria, and I want to set [Table2]
[ValueA] to YES or "checked" if any of the like records i
Table1 are checked
I appreciate any suggestions. Thus far, my DMax attempt
have yielded various errors, the latest is simply state
as error type "Unknown"
 
OK - yes, you could use the DMin function for this situation. The SQL for
such an update query would be something like this:

UPDATE [Table2] SET [ValueA] = DMin("ValueA", "Table1", "[CustNum]=" &
[CustNum]),
[ValueB] = DMin("ValueB", "Table1", "[CustNum]=" & [CustNum]),
[ValueC] = DMin("ValueC", "Table1", "[CustNum]=" & [CustNum]);


--
Ken Snell
<MS ACCESS MVP>

Merrill said:
Okay, I understand the point about a numeric value of -1 for "checked"
boxes.....could I use the DMin function in the same vein?
Table1 and Table2 really are not related other than they have identical
structures. Table1 is not normalized, I want to create Table2 via a
make-table query that will have unique records (key set on CustNum), then
populate the checkboxes in Table2 as a summary of all like records found in
Table1.
Example is as follows:


Table1

CustNum ValueA ValueB ValueC
---------- -------- -------- --------
1411 Checked
1411 Checked
1522 Checked
1644 Checked
1888 Checked
1888 Checked



I would like to build Table2 to include a compression of Table1 using
unique CustNum, and finding all checks for each CustNum in Table1, resulting
in the following:
Table2

CustNum ValueA ValueB ValueC
---------- -------- -------- --------
1411 Checked Checked
1522 Checked
1644 Checked
1888 Checked Checked




I hope this example makes sense. Table1 contains individual transactions
recorded at the time of a business event, and I want to generate a table
with a synopsis of all boxes checked.
Thanks again.





----- Ken Snell wrote: -----

You don't give us much to go on. How are Table1 and Table2 related? Through
which field? A value of -1 is assigned for a "checkbox" field that is set to
True (checked), while a value of 0 is assigned for an unchecked (False)
checkbox. Thus, looking for a maximum value won't work if you're trying to
find checked values.

Post some example records and the tables' structure so that we can see what
you have.

--
Ken Snell
<MS ACCESS MVP>

Merrill said:
I am trying to use the DMax function in the Update cell of
an update query. I would like to set [Table2].[ValueA] =
the maximum of [Table1].[ValueA].
Table2 contains unique records, Table1 contains multiple
records. ValueA is actually a text box and is set in
Table1 based on given criteria, and I want to set [Table2].
[ValueA] to YES or "checked" if any of the like records in
Table1 are checked.
I appreciate any suggestions. Thus far, my DMax attempts
have yielded various errors, the latest is simply stated
as error type "Unknown".
 
Back
Top