Multiple Records Generated from Append Query

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

I created an append query that creats new records in a
database when I enter criteria. For examle I enter a
month (which is a field in my query). I also enter a date
which is creating a field in the query database. The
first time I run the query, it creates one new record for
all entries it finds matching that month. The next time I
run the query, it creats multiple enteries to double the
number of fields in the append to database. After running
it 3 or 4 times, I entered a month that had only one entry
in the query, and it generated 56 new fields for the one
piece of data - the appended fields were all the same
(with the exception of the record ID that is automatically
generated for each field!

Why is my query doubling the size of the database each
time I run it?

Thanks.
 
Dear Ken:

Your question seems difficult to read. For example:

"I also enter a date which is creating a field in the query database."

Are you actually adding a new column to a query?

"it generated 56 new fields"

Surely you don't mean this, do you?

If this is being done by an append query, then you are adding rows,
not columns. That's another way of saying you're adding records, not
fields.

Now, if I've got this sorted out so far, then I really need to see the
SQL of what your append query does. Also, can you explain the logic
of what you intend to accomplish.

My guess is that your append is based on a select query and that the
select query may need a filter so it generates only the desired rows
to append. That's just a guess, and I can't give you any specifics as
I don't know them yet.

But if you run the SELECT query on which the append is being done by
itself, that should give you some assistance.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thank you for your response; I appologize for making it
difficult to read.
My "Monthly Billing Query" is a select query that is the
source of data. My "Billing Invoice Update" is the append
query that takes data from "Monthly Billing Query" and
appends my "Billing Invoices" database. I have one
criteria in the "Billing Invoice Update" that asks for
the "Month to Bill" when it is run. When I run
the "Billing Invoice Update" and enter the "Month to
Bill", it duplicates every new entry by the number of
columns/records in the "Billing Invoices" database. For
example, I had 14 records in the database. I ran the
append query and entered a month that should have added 6
records to the database. It actually created 84 new
records (each new record was added 14 times). The SQL is:
INSERT INTO [Billing Invoices]
([CustomerID],NameFull,Billed,Tax,[Total Invoice],[Invoice
Date])
SELECT[Monthly Billing Query].[Customer ID],[Monthly
Billing Query].NameFull,[Monthly Billing Query].[Annual
Monitoring] AS Billed,[Monthly Billing Query].[Monitoring
Tax]AS Tax,[Monthly Billing Query].[Monitoring Total] AS
[Total Invoice],Date() AS[Invoice Date]
FROM[Monthly Billing Query],[Billing Invoices]
Where((([Monthly Billing Query].[Monitoring Renewal])=
[Month to Bill]))
I am running Access 97. Thank you.
 
Tom,

If you get this message, I found the problem after
printing out the SQL code and a little trial and error. I
thought you had to have the database open in the append
query design window. I removed the Billing Invoice
database from the append query design window and I do not
get the multiple entries.

Thanks.

Ken
-----Original Message-----
Thank you for your response; I appologize for making it
difficult to read.
My "Monthly Billing Query" is a select query that is the
source of data. My "Billing Invoice Update" is the append
query that takes data from "Monthly Billing Query" and
appends my "Billing Invoices" database. I have one
criteria in the "Billing Invoice Update" that asks for
the "Month to Bill" when it is run. When I run
the "Billing Invoice Update" and enter the "Month to
Bill", it duplicates every new entry by the number of
columns/records in the "Billing Invoices" database. For
example, I had 14 records in the database. I ran the
append query and entered a month that should have added 6
records to the database. It actually created 84 new
records (each new record was added 14 times). The SQL is:
INSERT INTO [Billing Invoices]
([CustomerID],NameFull,Billed,Tax,[Total Invoice], [Invoice
Date])
SELECT[Monthly Billing Query].[Customer ID],[Monthly
Billing Query].NameFull,[Monthly Billing Query].[Annual
Monitoring] AS Billed,[Monthly Billing Query].[Monitoring
Tax]AS Tax,[Monthly Billing Query].[Monitoring Total] AS
[Total Invoice],Date() AS[Invoice Date]
FROM[Monthly Billing Query],[Billing Invoices]
Where((([Monthly Billing Query].[Monitoring Renewal])=
[Month to Bill]))
I am running Access 97. Thank you.
-----Original Message-----
Dear Ken:

Your question seems difficult to read. For example:

"I also enter a date which is creating a field in the query database."

Are you actually adding a new column to a query?

"it generated 56 new fields"

Surely you don't mean this, do you?

If this is being done by an append query, then you are adding rows,
not columns. That's another way of saying you're adding records, not
fields.

Now, if I've got this sorted out so far, then I really need to see the
SQL of what your append query does. Also, can you explain the logic
of what you intend to accomplish.

My guess is that your append is based on a select query and that the
select query may need a filter so it generates only the desired rows
to append. That's just a guess, and I can't give you
Click to expand...
any
specifics as
I don't know them yet.

But if you run the SELECT query on which the append is being done by
itself, that should give you some assistance.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...
time
I

.
Click to expand...
.
Click to expand...
 
Back
Top