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
.



Relevant Pages

  • RE: Copying in a query
    ... Do any of the tables have a primary key? ... Applications].Received) Is Null) AND (([PAP Applications].Discontinued) Is ... "Jerry Whittle" wrote: ... Client Info- which has as the primary key connecting to a ...
    (microsoft.public.access.queries)
  • Re: Form with 5 subforms, 1 sub-subform
    ... The Meds table would have its own autonumber as its primary key. ... would like to be able to create one big Client Medication Profile form ... is currently linked to the main Client Info table by the [Client ... The PAP Applications form has fields from ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Simply Accounting
    ... So now that directly updating client info is not looking to promising. ... how can you even assign the primary key to the reocrd you plan to add to ... Unfortunately we can't let that intern out of ...
    (microsoft.public.access.modulesdaovba)
  • Re: Access complains of duplicate key
    ... one-to-many referential integery, and set the field indexed, no duplicates? ... There really is only one assessment per client. ... with the same primary key value in each table. ...
    (microsoft.public.access.formscoding)
  • Re: Simple MYSQL Normalization question - input requested.
    ... a way that would sit that client and that client alone. ... int cost_id primary KEY ... a mail order catalog once a month of items for sale, ... Category3 and Category4 fields to the item database? ...
    (comp.lang.php)