FIRST record in a match

[mark r]

I have a query now that outputs the records I am interested in. However,
the query will not let me update any of the fields. Specifically, I wish to
"mark TRE" the PRINTFLAG field so that I can run a report and print of only
the records I am interested in.

I assume there is no way to allow me to just point and click. So, perhaps
there is a way i could write another query which will eliminate duplicate
records from my output file. One way might be to select the first record from
any duplicated last names.

Another approach I thought of was to use SET in an currentdb.Execute
where somehow I match the lastnames of the query output to the original
source table and SET printflag to TRUE on Match . However, the wource table
is keyed on IDfield and my query "duplicates " are identified by lastname.

printflag lastname dateofappointment

alton 11/01/2007
alton 03/02/2007
alton 05/01/2006

I want to print only one of these into a label using REPORT.
I cannot seem to click on printflag. I donot know of a filter or query
function
FIRST.

please reference reply on how to do a MATCH using Join.

 

[Stefan Hoffmann]

hi Mark,

I have a query now that outputs the records I am interested in. However,
the query will not let me update any of the fields. Specifically, I wish to
"mark TRE" the PRINTFLAG field so that I can run a report and print of only
the records I am interested in.

According to your full quote at the end, you're using subqueries. These
may lead to non-key-preserving queries. So Access/Jet cannot correctly
identify the rows to updated.

You may try appending all primary key fields from your involed tables to
your queries field list. Otherwise you can either create queries and use
them instead of subqueries or use JOINs instead of subqueries. Using
JOINS is not always possible.

Another approach I thought of was to use SET in an currentdb.Execute
where somehow I match the lastnames of the query output to the original
source table and SET printflag to TRUE on Match . However, the wource table
is keyed on IDfield and my query "duplicates " are identified by lastname.

Append the IDfield to your recordsets source.


mfG
--> stefan <--

 

[mark r]

THIS IS THE GOAL:
ANY IDEAS?
THIS OUTPUT IS CREATED FROM SUBQUERIES. note, the zipcode was entered
incompletely on one occurrence, so when I consolidate alton to one record, I
might wind up with an incomplete zipcode - so be it. I do need the zipcode
field however, in my final output. How do you output FIRST record in a
MATCH ?

name appointment zipcode
alton 11/01/2007 33321
alton 03/02/2007 33321
alton 05/01/2006 33

I want to print only one of these into a label using REPORT.

alton anydate anyzip