Max Values of a field

[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.

 

[Ken Snell]

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.

 

[Guest]

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

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"

Thanks

 

[Ken Snell]

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>

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>

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.