Delete Record Query

[grego9]

I am using an append query to write records to a master table that I have created. I then want to create a query to clear out all the records from this master table. I have created the usual delete query - but when I run it it is saying that it going to delete considerably more records than are in the table. the number of records it seems to be wanting to delete relate to another table in my database (whosre records I want to keep!) I've double checked that my delete query is referencing the correct table- any ideas?

I am not aware that the newly created master table is linked in any way to any other table (I went to relationships and added in the master table and the table with the number of records that the delete query wanted to delete and there was no relationship line between them) Am I missing something? I would be grateful for any ideas on this!!!


thanks

 

[DCrake]

I am using an append query to write records to a master table that I have created. I then want to create a query to clear out all the records from this master table.

Have you described your question correctly? From what you say you are appending records to Table A then Deleting All Records from TableA. Surely TableA will contain more records. The existing ones plus the new ones.

David

 

[grego9]

Yes - I am appending data into the table - and later on when I've reviewed the results in the table I want to be able to clear the table so that new records can be appended later on.

Hope this makes sense!

 

[grego9]

To summarise I am running a number of different append queries on a monthly basis that post data into a table. I then run a report on this table which summarises the data.

I then want to be able to clear this table so that the following month the table is blank before I run the append queries again

thanks

 

[DCrake]

You seem to want to run the delete on completion of the reports. This should be done Prior to running the appends. What happens if someone wants another copy of the reports you would have to run the appends again.

You first task prior to running the append queries is to Delete * From Table Then run the append queries.


You are using an optomistic attitude instead of a pessamistic. You should always plan for the worst, not best.

David

 

[grego9]

Yes - I was planning on running the delete query on the master table before running the append queries the following month. Only I use the data - so I can either delete after doing the analysis or before I run the append queries.

The problem is not the timing more that the delete query looks like it is trying to delete more records than exist in the base table. I take on board your previous point that with an append query the base table will contain previous results as well as results from the last run of append queries - but I already taken this into account. I actually mauually went into table A went to select all and then deleted records - of which there were 38.

I then re-ran the append queries which generated 38 records in my table (called BG). I then went to the delete query which is structured as follows:

field Reference GTEE Number

Table BG BG

Delete Where Where

Ran that and it is trying to delete 5809 records - considerably more than the 38 records that are in table BG. I don't think table BG is linked to any other tables - any ideas!

thanks

 

[DCrake]

Run the delete query and when it say it's going to delete 5000+ records say cancel. Then go to the actual table and open it up in datasheet mode and view the number of records. If it does not match then this is either an indexing issue or you need to run a compact and repair prior to deletion.

David

 

[grego9]

Thanks for your help - I compacted and repaired the database and now it is working fine - it recognises the correct number of records that it should be deleting. Perfect! I am however a little confused as to how the problem arose in the first place and how to prevent it again!

At least I will know what to do now if it does occur again

thanks again

 

[DCrake]

When records are deleted from a table The actual records are removed but the space they used is not. This continues to grow, this is more commonly known as "bloating". By running a C & R this unused space is removed, thus reducing the actual size and Access rebuilds the indexes used in the tables. It is the index values that Access is reporting upon not the number of records it has.

Good housekeeping suggests that C & R is performed on a regular basis,depending on the amount of activity experienced.

Glad to see you have resolved the issue.

David

 

[grego9]

It's obviously been a long time since any housekeeping was done!

Thanks David

 

[gemma-the-husky]

it is possible that your delete query is wrongly joined, and it may be showing you a cross product, so it LOOKS like you have too many records

just try it

copy the table (or database) to make sure you dont lose any data, then run the query and see what happens

 

[grego9]

Thanks Gemma - We solved the problem - I just needed to compact and repair the database. The query worked fine!