Max values of a field (again)

[Merrill]

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.

 

[Duane Hookom]

Could you set up an append query based on Table1 that Groups by CustNum and
the field expressions like
ValA:Sum(ValueA) <>0
Expression


--
Duane Hookom
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.