Append Queries

R

Ray

I have an Append query that counts members.
I have a table preset with a 0 count.
If my append query finds no records, it deletes the row in the table with
the 0 count and leaves no record in the table, causing all my reports to
return #error,s

How can I get the append query to ignore the append if it finds no records
matching the criteria when it runs?

Thank you

All help appreciated.
 
K

KARL DEWEY

An append query CAN NOT delete records. Maybe you have a macro that runs a
delete query first OR your query is not an append query but is a make table
query and is deleting the existing table.
 
R

Ray

Here is my problem.

I have a database full of members with a field called [Year Paid]
I used queries that use the Year Paid field to count paid up members.
I have preset a table with a Member Type and a CountOfType preset to 0
1. Because the option is there to run this report based on different years,
I first

1. Run a query that sets all the counts back to zero

This leaves me with a table like this
TYPE COUNTOFTYPE
LF 0

2. Run an append query that filters the database by the year entered into a
form.

This leaves me with a table like this
TYPE COUNTOFTYPE
LF 0
LF 10

3. Run an delete query that deletes the records with a 0 count.

This leaves me with a table like this
TYPE COUNTOFTYPE
LF 10

Which one would think would be perfect for my report BUT

If my Append query returns no results the following happens.

1. My table with presets looks like this,
TYPE COUNTOFTYPE
LF 0

2. Run an append query that filters the database by the year entered into a
form.\
BUT DOES NOT FIND ANY APPLICABLE RECORDS so will not append anything

This leaves me with a table like this (My Original)
TYPE COUNTOFTYPE
LF 0

3. Run an delete query that deletes the records with a 0 count.

This leaves me with a table like this
No Records

Which causes all kinds of errors in my reports and leaves me with only a few
hairs left on my head.

I need to somehow be able to keep a 0 count table if no records are found to
append. I have spent hours and hours on this and suspect it is one of those
simple things that you just keep missing.

Any help TRULY appreciated.

Ray
 
K

KARL DEWEY

3. Run an delete query that deletes the records with a 0 count.
Create a query (Query1) that test how many records are in your table.

Use a condition in the macro that runs the delete query.
DCount("[COUNTOFTYPE]","Query1")>1

--
KARL DEWEY
Build a little - Test a little


Ray said:
Here is my problem.

I have a database full of members with a field called [Year Paid]
I used queries that use the Year Paid field to count paid up members.
I have preset a table with a Member Type and a CountOfType preset to 0
1. Because the option is there to run this report based on different years,
I first

1. Run a query that sets all the counts back to zero

This leaves me with a table like this
TYPE COUNTOFTYPE
LF 0

2. Run an append query that filters the database by the year entered into a
form.

This leaves me with a table like this
TYPE COUNTOFTYPE
LF 0
LF 10

3. Run an delete query that deletes the records with a 0 count.

This leaves me with a table like this
TYPE COUNTOFTYPE
LF 10

Which one would think would be perfect for my report BUT

If my Append query returns no results the following happens.

1. My table with presets looks like this,
TYPE COUNTOFTYPE
LF 0

2. Run an append query that filters the database by the year entered into a
form.\
BUT DOES NOT FIND ANY APPLICABLE RECORDS so will not append anything

This leaves me with a table like this (My Original)
TYPE COUNTOFTYPE
LF 0

3. Run an delete query that deletes the records with a 0 count.

This leaves me with a table like this
No Records

Which causes all kinds of errors in my reports and leaves me with only a few
hairs left on my head.

I need to somehow be able to keep a 0 count table if no records are found to
append. I have spent hours and hours on this and suspect it is one of those
simple things that you just keep missing.

Any help TRULY appreciated.

Ray

KARL DEWEY said:
An append query CAN NOT delete records. Maybe you have a macro that runs a
delete query first OR your query is not an append query but is a make table
query and is deleting the existing table.
Click to expand...
Click to expand...
 
R

Ray

Hi Karl,

I have created/modified my delet query as below:

Field Type Type DCount("[COUNTOFTYPE]","Total AV Unpaid Count")
Table Total AV Unpaid Total AV Unpaid
Delete Where Where Where
Criteria 0 >1

My Table (Total AV Unpaid) Looks like below:

Type Count Of Type
AV 0
AV 0

If I run the query as above, it still deletes both records. If I change ot
to >2 it doesn't delete any records.

Now I am confused but I think on the right track.

Ray



KARL DEWEY said:
Create a query (Query1) that test how many records are in your table.

Use a condition in the macro that runs the delete query.
DCount("[COUNTOFTYPE]","Query1")>1

--
KARL DEWEY
Build a little - Test a little


Ray said:
Here is my problem.

I have a database full of members with a field called [Year Paid]
I used queries that use the Year Paid field to count paid up members.
I have preset a table with a Member Type and a CountOfType preset to 0
1. Because the option is there to run this report based on different years,
I first

1. Run a query that sets all the counts back to zero

This leaves me with a table like this
TYPE COUNTOFTYPE
LF 0

2. Run an append query that filters the database by the year entered into a
form.

This leaves me with a table like this
TYPE COUNTOFTYPE
LF 0
LF 10

3. Run an delete query that deletes the records with a 0 count.

This leaves me with a table like this
TYPE COUNTOFTYPE
LF 10

Which one would think would be perfect for my report BUT

If my Append query returns no results the following happens.

1. My table with presets looks like this,
TYPE COUNTOFTYPE
LF 0

2. Run an append query that filters the database by the year entered into a
form.\
BUT DOES NOT FIND ANY APPLICABLE RECORDS so will not append anything

This leaves me with a table like this (My Original)
TYPE COUNTOFTYPE
LF 0

3. Run an delete query that deletes the records with a 0 count.

This leaves me with a table like this
No Records

Which causes all kinds of errors in my reports and leaves me with only a few
hairs left on my head.

I need to somehow be able to keep a 0 count table if no records are found to
append. I have spent hours and hours on this and suspect it is one of those
simple things that you just keep missing.

Any help TRULY appreciated.

Ray

KARL DEWEY said:
An append query CAN NOT delete records. Maybe you have a macro that runs a
delete query first OR your query is not an append query but is a make table
query and is deleting the existing table.

:

I have an Append query that counts members.
I have a table preset with a 0 count.
If my append query finds no records, it deletes the row in the table with
the 0 count and leaves no record in the table, causing all my reports to
return #error,s

How can I get the append query to ignore the append if it finds no records
matching the criteria when it runs?

Thank you

All help appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append Crosstab Query to Existing Table 0
Appending tables 0
query field names changing to expr#: 8
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Append Queries 5
Access 2002 Append Query Problems 2
Append Query is not working 1
Dcount -Date Criteria - Won´t find it - 0

Top