Append Query does not Work but same query as a select does

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an append query that I am trying to run. When I run it the records
are repeated. But if I change the query to a select query the records only
appear one time each (which is correct).

What could I be doing wrong or is there a hot fix for this? I am using
Microsoft Office Access 2003 SP2.
 
GINY,

Post your SQL as it exist in the SELECT and again as it appears as an Append.

Dale
 
Here is the SQL as an Append:
INSERT INTO [Individual Class Results] ( Category, [Show No], [Show Class
Type Code], [Beginning Show Date], [Exhibitor Cust Id], Place, [Show Class
Type Description], [Entries in Class], [Owner Cust Id], [Horse Registration
Number], [Horse Name], [Show Detail Status], [Exhibitor Print Name], [Owner
Print Name], [Club Points] )
SELECT [AQHA Class Codes and Descriptions].Category, [Show Results].[Show
No], [Show Results].[Show Class Type Code], [Show Results].[Beginning Show
Date], [Show Results].[Exhibitor Cust Id], [Show Results].Place, [Show
Results].[Show Class Type Description], [Show Results].[Entries in Class],
[Show Results].[Owner Cust Id], [Show Results].[Horse Registration Number],
[Show Results].[Horse Name], [Show Results].[Show Detail Status], [Show
Results].[Exhibitor Print Name], [Show Results].[Owner Print Name], [Show
Results].[Club Points]
FROM ([Show Results] INNER JOIN [AQHA Class Codes and Descriptions] ON [Show
Results].[Show Class Type Code] = [AQHA Class Codes and Descriptions].[Show
Class Type Code]) INNER JOIN [Exhibitor - Horse Information] ON [Show
Results].[Exhibitor Cust Id] = [Exhibitor - Horse Information].[Exhibitors
AQHA Number]
WHERE ((([Show Results].[Show Class Type Code])=4072 Or ([Show
Results].[Show Class Type Code])=4077 Or ([Show Results].[Show Class Type
Code])=4076) AND (([Show Results].[Beginning Show Date])>=[exhibitor - Horse
information.Date to Start Pointkeeping] And ([Show Results].[Beginning Show
Date])<=[Exhibitor - Horse Information.Date Club Membership Expires]) AND
(([Exhibitor - Horse Information].[Y Sr Geldings])=-1));

Here is the same as a Select:
SELECT [AQHA Class Codes and Descriptions].Category, [Show Results].[Show
No], [Show Results].[Show Class Type Code], [Show Results].[Beginning Show
Date], [Show Results].[Exhibitor Cust Id], [Show Results].Place, [Show
Results].[Show Class Type Description], [Show Results].[Entries in Class],
[Show Results].[Owner Cust Id], [Show Results].[Horse Registration Number],
[Show Results].[Horse Name], [Show Results].[Show Detail Status], [Show
Results].[Exhibitor Print Name], [Show Results].[Owner Print Name], [Show
Results].[Club Points]
FROM ([Show Results] INNER JOIN [AQHA Class Codes and Descriptions] ON [Show
Results].[Show Class Type Code] = [AQHA Class Codes and Descriptions].[Show
Class Type Code]) INNER JOIN [Exhibitor - Horse Information] ON [Show
Results].[Exhibitor Cust Id] = [Exhibitor - Horse Information].[Exhibitors
AQHA Number]
WHERE ((([Show Results].[Show Class Type Code])=4072 Or ([Show
Results].[Show Class Type Code])=4077 Or ([Show Results].[Show Class Type
Code])=4076) AND (([Show Results].[Beginning Show Date])>=[exhibitor - Horse
information.Date to Start Pointkeeping] And ([Show Results].[Beginning Show
Date])<=[Exhibitor - Horse Information.Date Club Membership Expires]) AND
(([Exhibitor - Horse Information].[Y Sr Geldings])=-1));
 
When you do a select query, how many records does the query return?

When you run it as an append query, how many records does it say it is
appending to your [Individual Class Results] table?

Have you tried inserting DISTINCT into the SELECT portion of the query? If
not, change the query to SELECT DISTINCT [AQHA Class Codes and
Descriptions].Category, .....

What happens to the append query when you do that?

--
Email address is not valid.
Please reply to newsgroup only.


ReginaGINY said:
Here is the SQL as an Append:
INSERT INTO [Individual Class Results] ( Category, [Show No], [Show Class
Type Code], [Beginning Show Date], [Exhibitor Cust Id], Place, [Show Class
Type Description], [Entries in Class], [Owner Cust Id], [Horse Registration
Number], [Horse Name], [Show Detail Status], [Exhibitor Print Name], [Owner
Print Name], [Club Points] )
SELECT [AQHA Class Codes and Descriptions].Category, [Show Results].[Show
No], [Show Results].[Show Class Type Code], [Show Results].[Beginning Show
Date], [Show Results].[Exhibitor Cust Id], [Show Results].Place, [Show
Results].[Show Class Type Description], [Show Results].[Entries in Class],
[Show Results].[Owner Cust Id], [Show Results].[Horse Registration Number],
[Show Results].[Horse Name], [Show Results].[Show Detail Status], [Show
Results].[Exhibitor Print Name], [Show Results].[Owner Print Name], [Show
Results].[Club Points]
FROM ([Show Results] INNER JOIN [AQHA Class Codes and Descriptions] ON [Show
Results].[Show Class Type Code] = [AQHA Class Codes and Descriptions].[Show
Class Type Code]) INNER JOIN [Exhibitor - Horse Information] ON [Show
Results].[Exhibitor Cust Id] = [Exhibitor - Horse Information].[Exhibitors
AQHA Number]
WHERE ((([Show Results].[Show Class Type Code])=4072 Or ([Show
Results].[Show Class Type Code])=4077 Or ([Show Results].[Show Class Type
Code])=4076) AND (([Show Results].[Beginning Show Date])>=[exhibitor - Horse
information.Date to Start Pointkeeping] And ([Show Results].[Beginning Show
Date])<=[Exhibitor - Horse Information.Date Club Membership Expires]) AND
(([Exhibitor - Horse Information].[Y Sr Geldings])=-1));

Here is the same as a Select:
SELECT [AQHA Class Codes and Descriptions].Category, [Show Results].[Show
No], [Show Results].[Show Class Type Code], [Show Results].[Beginning Show
Date], [Show Results].[Exhibitor Cust Id], [Show Results].Place, [Show
Results].[Show Class Type Description], [Show Results].[Entries in Class],
[Show Results].[Owner Cust Id], [Show Results].[Horse Registration Number],
[Show Results].[Horse Name], [Show Results].[Show Detail Status], [Show
Results].[Exhibitor Print Name], [Show Results].[Owner Print Name], [Show
Results].[Club Points]
FROM ([Show Results] INNER JOIN [AQHA Class Codes and Descriptions] ON [Show
Results].[Show Class Type Code] = [AQHA Class Codes and Descriptions].[Show
Class Type Code]) INNER JOIN [Exhibitor - Horse Information] ON [Show
Results].[Exhibitor Cust Id] = [Exhibitor - Horse Information].[Exhibitors
AQHA Number]
WHERE ((([Show Results].[Show Class Type Code])=4072 Or ([Show
Results].[Show Class Type Code])=4077 Or ([Show Results].[Show Class Type
Code])=4076) AND (([Show Results].[Beginning Show Date])>=[exhibitor - Horse
information.Date to Start Pointkeeping] And ([Show Results].[Beginning Show
Date])<=[Exhibitor - Horse Information.Date Club Membership Expires]) AND
(([Exhibitor - Horse Information].[Y Sr Geldings])=-1));

Dale Fye said:
GINY,

Post your SQL as it exist in the SELECT and again as it appears as an Append.

Dale
Click to expand...
Click to expand...
 
I get 55 records when I run the query as a select and 55 records when I run
it as an append. But the 55 records that I get each time are not exactly the
same.

I am not sure how to use the distinct command since I write my queries using
the design view. Also, distinct on the AQHA class code may not be valid
since the person could have gone in that class multiple times over the course
of the show season.

Dale Fye said:
When you do a select query, how many records does the query return?

When you run it as an append query, how many records does it say it is
appending to your [Individual Class Results] table?

Have you tried inserting DISTINCT into the SELECT portion of the query? If
not, change the query to SELECT DISTINCT [AQHA Class Codes and
Descriptions].Category, .....

What happens to the append query when you do that?

--
Email address is not valid.
Please reply to newsgroup only.


ReginaGINY said:
Here is the SQL as an Append:
INSERT INTO [Individual Class Results] ( Category, [Show No], [Show Class
Type Code], [Beginning Show Date], [Exhibitor Cust Id], Place, [Show Class
Type Description], [Entries in Class], [Owner Cust Id], [Horse Registration
Number], [Horse Name], [Show Detail Status], [Exhibitor Print Name], [Owner
Print Name], [Club Points] )
SELECT [AQHA Class Codes and Descriptions].Category, [Show Results].[Show
No], [Show Results].[Show Class Type Code], [Show Results].[Beginning Show
Date], [Show Results].[Exhibitor Cust Id], [Show Results].Place, [Show
Results].[Show Class Type Description], [Show Results].[Entries in Class],
[Show Results].[Owner Cust Id], [Show Results].[Horse Registration Number],
[Show Results].[Horse Name], [Show Results].[Show Detail Status], [Show
Results].[Exhibitor Print Name], [Show Results].[Owner Print Name], [Show
Results].[Club Points]
FROM ([Show Results] INNER JOIN [AQHA Class Codes and Descriptions] ON [Show
Results].[Show Class Type Code] = [AQHA Class Codes and Descriptions].[Show
Class Type Code]) INNER JOIN [Exhibitor - Horse Information] ON [Show
Results].[Exhibitor Cust Id] = [Exhibitor - Horse Information].[Exhibitors
AQHA Number]
WHERE ((([Show Results].[Show Class Type Code])=4072 Or ([Show
Results].[Show Class Type Code])=4077 Or ([Show Results].[Show Class Type
Code])=4076) AND (([Show Results].[Beginning Show Date])>=[exhibitor - Horse
information.Date to Start Pointkeeping] And ([Show Results].[Beginning Show
Date])<=[Exhibitor - Horse Information.Date Club Membership Expires]) AND
(([Exhibitor - Horse Information].[Y Sr Geldings])=-1));

Here is the same as a Select:
SELECT [AQHA Class Codes and Descriptions].Category, [Show Results].[Show
No], [Show Results].[Show Class Type Code], [Show Results].[Beginning Show
Date], [Show Results].[Exhibitor Cust Id], [Show Results].Place, [Show
Results].[Show Class Type Description], [Show Results].[Entries in Class],
[Show Results].[Owner Cust Id], [Show Results].[Horse Registration Number],
[Show Results].[Horse Name], [Show Results].[Show Detail Status], [Show
Results].[Exhibitor Print Name], [Show Results].[Owner Print Name], [Show
Results].[Club Points]
FROM ([Show Results] INNER JOIN [AQHA Class Codes and Descriptions] ON [Show
Results].[Show Class Type Code] = [AQHA Class Codes and Descriptions].[Show
Class Type Code]) INNER JOIN [Exhibitor - Horse Information] ON [Show
Results].[Exhibitor Cust Id] = [Exhibitor - Horse Information].[Exhibitors
AQHA Number]
WHERE ((([Show Results].[Show Class Type Code])=4072 Or ([Show
Results].[Show Class Type Code])=4077 Or ([Show Results].[Show Class Type
Code])=4076) AND (([Show Results].[Beginning Show Date])>=[exhibitor - Horse
information.Date to Start Pointkeeping] And ([Show Results].[Beginning Show
Date])<=[Exhibitor - Horse Information.Date Club Membership Expires]) AND
(([Exhibitor - Horse Information].[Y Sr Geldings])=-1));

Dale Fye said:
GINY,

Post your SQL as it exist in the SELECT and again as it appears as an Append.

Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

I have an append query that I am trying to run. When I run it the records
are repeated. But if I change the query to a select query the records only
appear one time each (which is correct).

What could I be doing wrong or is there a hot fix for this? I am using
Microsoft Office Access 2003 SP2.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top