Adding records to a table

I can't believe in my good fortune in getting so much help!

Here is the MakeTable query statement I used.
SELECT qryConsumers.CLN, qryConsumers.CFN, qryConsumers.IsAge,
qryConsumers.County, qryAuthRecords.ConsumerNo2, qryAuthRecords.AuthStart,
qryAuthRecords.AuthEnd, qryAuthRecords.ServiceCode,
qryAuthRecords.AuthsStatus, qryAuthRecords.CostCtr, qryAuthRecords.Source
INTO tblToAppend
FROM qryConsumers LEFT JOIN qryAuthRecords ON qryConsumers.ConsumerNo1 =
qryAuthRecords.ConsumerNo2
WHERE (((qryConsumers.County)="Buncombe") AND
((qryAuthRecords.Source)="IPRS"));

This includes the fields that are to be updated in the next step. The
underlying consumers query has already selected active consumers and computes
the patients' ages.

I would really like to learn how to eliminate the duplicate records because
it's very likely this exercise will have to be repeated with another service
code. If this isn't enough information, please let me know.

Thank you very very much.




--
Nona


"John W. Vinson" wrote:

> On Thu, 1 Jan 2009 17:19:15 -0800, Nona
> wrote:
>
> >Thanks for this additional information. I'll definitely work on this. I've
> >never done a "delete" query, but I'll figure it out! John, I know you said
> >the Maketable Query was an unnecessary step, but I really felt safer updating
> >the data in the new table before appending.
> >
> >This is precisely why I love working with Access - I never get tired of the
> >challenges, and there's always something new to learn. I especially
> >appreciate the help I've gotten from this discussion group site. No where
> >else could I have gotten such good training on so many Access issues. Many
> >Thanks.
>
> If you'ld like help for future reference, please post the SQL view of the
> MakeTable query; I'd be glad to show you how to tweak it to be unique.
>
> Thanks, Tina, for taking care of Nona and so many others here - and apologies
> for jumping in on your thread!
>
> --
>
> John W. Vinson [MVP]
>

On Thu, 1 Jan 2009 20:19:00 -0800, Nona
wrote:

>I can't believe in my good fortune in getting so much help!
>
>Here is the MakeTable query statement I used.
>SELECT qryConsumers.CLN, qryConsumers.CFN, qryConsumers.IsAge,
>qryConsumers.County, qryAuthRecords.ConsumerNo2, qryAuthRecords.AuthStart,
>qryAuthRecords.AuthEnd, qryAuthRecords.ServiceCode,
>qryAuthRecords.AuthsStatus, qryAuthRecords.CostCtr, qryAuthRecords.Source
>INTO tblToAppend
>FROM qryConsumers LEFT JOIN qryAuthRecords ON qryConsumers.ConsumerNo1 =
>qryAuthRecords.ConsumerNo2
>WHERE (((qryConsumers.County)="Buncombe") AND
>((qryAuthRecords.Source)="IPRS"));
>
>This includes the fields that are to be updated in the next step. The
>underlying consumers query has already selected active consumers and computes
>the patients' ages.
>
>I would really like to learn how to eliminate the duplicate records because
>it's very likely this exercise will have to be repeated with another service
>code. If this isn't enough information, please let me know.
>
>Thank you very very much.

What's the ultimate goal, in terms of which fields update which table?

For that matter... if you already have consumers in the Consumers table, why
do you need to create a new consumers table?

Let's take a step back: where is the information coming from, and where is it
going? And why do new records need to be added rather than just accumulating
records in two (or more?) related tables?

--

John W. Vinson [MVP]