Append Queries

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.

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.

"Ray" wrote:

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.

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" wrote:

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.

"Ray" wrote:

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.

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" wrote:

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" wrote:

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.

"Ray" wrote:

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.

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" wrote:

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" wrote:

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" wrote:

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.

"Ray" wrote:

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.