RE: Copying in a query



Hi Jerry,

Thanks for getting back to me.

Yeah, the [Client Info] table has a primary key (Client ID).

The error comes up when I try to paste the copied records. It is a
datasheet, so I paste by clicking on the (*) preceding the new record row to
highlight the entire row and hit ctrl v.

"Jerry Whittle" wrote:

Hi Mike,

Do any of the tables have a primary key?

I guess the most obvious question, as in I should have asked it in my first
responce, is when do you get the error? During the copy or paste? Also if
during the paste, what are you pasting the records into?
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Mike" wrote:

Hi Jerry,

Thanks for taking the time.

The one that works:
SELECT [CP Orders].[Client ID], [CP Orders].[ReOrder Date], [CP
Orders].[Issue Date], [Client Info].[Last Name], [Client Info].[First Name],
[CP Orders].[CP Rx #], [CP Orders].[Date Written], [CP Orders].Medication,
[CP Orders].Dosage, [CP Orders].Directions, [CP Orders].Quantity, [CP
Orders].Physician, [CP Orders].Refills, [CP Orders].[Order Date], [CP
Orders].Cost, [CP Orders].[Discontinue Date], [CP Orders].[D/C Reason], [CP
Orders].[Issue Initials]
FROM [Client Info] INNER JOIN [CP Orders] ON [Client Info].[Client ID] = [CP
Orders].[Client ID]
WHERE ((([CP Orders].[ReOrder Date]) Is Null) AND (([CP Orders].[Issue
Date])<Date()-14) AND (([CP Orders].[Discontinue Date]) Is Null) AND (([CP
Orders].[Return Date]) Is Null)) OR ((([CP Orders].[Order Date])=Date())) OR
((([CP Orders].[ReOrder Date])>Date()-2))
ORDER BY [CP Orders].[ReOrder Date] DESC , [CP Orders].[Issue Date], [Client
Info].[Last Name];


The one that doesn't work:
SELECT [Client Info].[Last Name], [Client Info].[First Name], [Client
Info].[Middle Initial], [Client Info].[Client ID], [PAP Applications].PAP,
[PAP Applications].Medication, [PAP Applications].Dosage, [PAP
Applications].Directions, [PAP Applications].Quantity, [PAP
Applications].[Pre-Register], [PAP Applications].Completed, [PAP
Applications].Signed, [PAP Applications].[Prescription Written], [PAP
Applications].Documentation, [PAP Applications].Application, [PAP
Applications].Via, [PAP Applications].[Follow-Up], [PAP
Applications].Response, [PAP Applications].Decision, [PAP
Applications].Received, [PAP Applications].Receiver, [PAP Applications].[Lot
#], [PAP Applications].Balance, [PAP Applications].Issued, [PAP
Applications].Issuer, [PAP Applications].Reordered, [PAP Applications].Notes,
[PAP Applications].Refills, [PAP Applications].Discontinued, [PAP
Applications].Disposed, [PAP Applications].Disposer, [PAP Applications].Method
FROM [Client Info] INNER JOIN [PAP Applications] ON [Client Info].[Client
ID] = [PAP Applications].[Client ID]
WHERE ((([PAP Applications].Application) Is Not Null) AND (([PAP
Applications].Received) Is Null) AND (([PAP Applications].Discontinued) Is
Null));


Thanks again,
Mike

"Jerry Whittle" wrote:

Hi Mike,

Please provide the SQL statement for both queries.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Mike" wrote:

I have two different queries. Both of them share the same main table -
Client Info- which has [Client ID] as the primary key connecting to a
secondary table - in this case PAP Applications, and CP Orders. Both of the
tables in question have a [Client ID] field, with identical properties, by
which they are related to the Client Info table. Both of the queries contain
a [Client ID] field from the secondary table (PAP Applications and CP Orders
respectively). However, in one query, CP Refill Check one can copy and paste
records. In the other, PAPs Receipt, however, this error message appears.

"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or relationship...".

I've checked the "Indexed" property and both of the [Client ID] fields and
they are both set to "Yes (Duplicates OK)". The 3 other related tables in
play do not have indexing in any of the related fields. In fact, I think the
only field in the whole table that does have indexing is the [Client ID]
field. Anyway, I'm stumped. Any help?

Thanks,
Mike
.