Need a non-replicating (duplicating) field

  • Thread starter Thread starter eatc7402
  • Start date Start date
E

eatc7402

I am using Access 2002. I am writing a query can creates a 'flat' result from
three other joined tables. One field is a memo field that contains a large
amount of data, especially when it is recreated over and over as a result
of the 'flattening' of the data.

Is there a way to add a criteria that will ONLY fill the memo field when
IT CHANGES DATA. In other words I am look for a way to control a fields
duplication to only fill the memo field under my control at query design time.

I have been searching for a method to do this but seem to be 'lost in the
trees' over what should be a rather simple thing to do (I hope).

eatc7402
 
Perhaps you can do this. It depends on whether or not there is a way to
identify which record of a group displays the memo field.

Do you have a way to identify the group and a way to order records
within the group uniquely?

That would allow you to specify that one of the records should show the
memo field or null, but it would not necessarily be the first record in
the group.

Now if you are doing this query for a report, then just use a control's
property on a report to suppress the duplicates.
 
John said:
Perhaps you can do this. It depends on whether or not there is a way to
identify which record of a group displays the memo field.

Do you have a way to identify the group and a way to order records
within the group uniquely?

That would allow you to specify that one of the records should show the
memo field or null, but it would not necessarily be the first record in
the group.

Now if you are doing this query for a report, then just use a control's
property on a report to suppress the duplicates.

---
John Spencer
Access MVP 2001-2005, 2007
I am using Access 2002. I am writing a query can creates a 'flat' result from
three other joined tables. One field is a memo field that contains a large
Click to expand...
[quoted text clipped - 9 lines]
eatc7402
Click to expand...
Click to expand...


Yes the record that contains the desired memo field is indicated in another
field of the
same record as the first field of the group. In these groups of records there
will be groups of related
records ranging from 2 to 10 records, only the FIRST record of each group
needs to contain the memo
field. I tried unsucessfully to figure out how to use the 'indictor' field
true/false value
to limit the populating of the memo field. Can VBA be used somehow to write
a small
procedure to limit the populating of the memo field to only those indicated
by the
flag field which identifies the first record of a group??

eatc7402
 
Ok, you need a query to identify which record should have the memo field
displayed. Perhaps something like the following

Assume that
TableB.FieldB determines which record should show the memo.
TableB.FK is the foreign key relating B to A.PK (primary key in A)

SELECT ...
, IIF(TableB.FieldB =
(SELECT Min(B.FieldB) as MinB
FROM TableB as B
WHERE B.FK= TableA.PK),TableC.Memo,Null) as ShowMemo
FROM (TableA INNER JOIN TableB
ON TableA.PK = TableB.PK)
INNER JOIN TableC
ON TableB.PK = TableC.FK

This could be quite slow if you have a large number of records.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

eatc7402 via AccessMonster.com said:
John said:
Perhaps you can do this. It depends on whether or not there is a way to
identify which record of a group displays the memo field.

Do you have a way to identify the group and a way to order records
within the group uniquely?

That would allow you to specify that one of the records should show the
memo field or null, but it would not necessarily be the first record in
the group.

Now if you are doing this query for a report, then just use a control's
property on a report to suppress the duplicates.

---
John Spencer
Access MVP 2001-2005, 2007
I am using Access 2002. I am writing a query can creates a 'flat' result
from
three other joined tables. One field is a memo field that contains a
large
Click to expand...
[quoted text clipped - 9 lines]
eatc7402
Click to expand...
Click to expand...


Yes the record that contains the desired memo field is indicated in
another
field of the
same record as the first field of the group. In these groups of records
there
will be groups of related
records ranging from 2 to 10 records, only the FIRST record of each group
needs to contain the memo
field. I tried unsucessfully to figure out how to use the 'indictor' field
true/false value
to limit the populating of the memo field. Can VBA be used somehow to
write
a small
procedure to limit the populating of the memo field to only those
indicated
by the
flag field which identifies the first record of a group??

eatc7402
Click to expand...
 
John said:
Ok, you need a query to identify which record should have the memo field
displayed. Perhaps something like the following

Assume that
TableB.FieldB determines which record should show the memo.
TableB.FK is the foreign key relating B to A.PK (primary key in A)

SELECT ...
, IIF(TableB.FieldB =
(SELECT Min(B.FieldB) as MinB
FROM TableB as B
WHERE B.FK= TableA.PK),TableC.Memo,Null) as ShowMemo
FROM (TableA INNER JOIN TableB
ON TableA.PK = TableB.PK)
INNER JOIN TableC
ON TableB.PK = TableC.FK

This could be quite slow if you have a large number of records.[quoted text clipped - 40 lines]
eatc7402
Click to expand...
Click to expand...


MY SOLUTION
----------------------

Well, it may not very eligant but it works. Here's what I finally did to
produce the results I
needed, which was have the memo field filled in ONLY for the first record of
a subgroup of
records.

All three of the table to be joined have keys to each other. One of the
subtables has a yes/no
field indicating those records that are the 'first' for a subgroup. The
solution involved some
temp tables, some update queries, and a macro to make the whole process
somewhat
a 'one-click' opation.

1. MAKE a temp table of the WHOLE join including all the key fields.
2. MAKE a temp table of ONLY the memo fields that are indicated by the 'first
record' flag.
3. UPDATE the first whole temp table memo fields to NULL.
4. UPDATE the now empty memo fields in the main temp table with the memo
fields
from the ONLY firstr memo temp table, using the key field as a join.
Resulting in
only the 'first records' subfields being updated.
5. Finally MAKE the final desired table to be exported, with only the fields
desired.

Thanks to all who commented. I wish the solution was more streamlined... but
this
works.

eatc7402
 
Back
Top