Make Table and Delete Query - when are they used.

[bearcub]

i'm trying to learn how to use Access efficiently. I do know that it does have several types of queries. I've used the update append, simple and delete query

What is the rule of thumb for creating Make Table queries and delete queries. Are delete queries normally used to clear existing tables so that new data can be added to it using an append or update query.

Thank you in advance,

Michael

 

[Joe4]

Yes, Delete queries can be used that way. They can also be used to clean up data (let's say that you import a file that has a bunch of blank rows at the end, and you want to remove them).

I very rarely use the Make Table query myself. Some people do when they need to import data to a temporary table first, and then use an Append Query to write the data to a final table. But I find it usually works better to import to an existing temporary table, and just delete the data after every run. Why? Because with a Make Table query, there are certain properties and data types that you cannot control, whereas it is easier if you import to a pre-designed temporary table that is set up just the way you like.

 

[bearcub]

thank you for the explanation. So, it seems that the Make table query is available but in actual practice it might not be the best option - good to know.

For the deleted query you mentioned something about blank rows. Let's say I'm importing an Excel file that contains 68000 rows of data. Does Access import only the 68,000 rows of data or the entire spreadsheet? If it is the entire spreadsheet, how would the delete query be used to delete the rows start from 68001 to EOF?

The reason I'm asking is that I have imported an Excel spreadsheet with close to 68K rows by 77 col. The size of my file is 73M just with this import. Is that size caused by all the blank rows after the last record?

Michael

 

[Joe4]

Excel usually will only import the populated rows. However, note that if you have formulas copied down past the end of your data, or have formatted entire columns, it may import blanks rows.
The easiest way to delete them is to identify some field which is never blank, and then have your Delete Query delete all records with blanks in that field.

 

[bearcub]

Good to know, thank you. I've noticed that the last record in my table matches the last row of my Excel data so it is only importing the "dirtied" rows.

Michael

 

[Joe4]

You are welcome.

I have had experiences where I am working with exported reports, and have need to delete repeated page headers, and things like that. So Delete Queries can be helpful with that kind of stuff too.

 

[bearcub]

Thank you again, I'll probably run into this too and I'll use the delete query accordingly.

Michael

 

[Micron]

PMFJI Regarding make table queries. There were only 2 reasons I ever used them.
1) to create tables based on remote tables (ODBC) that I had no write/edit/delete permissions on and didn't need all of the columns in the source tables. My table fields would then automatically have the correct data types (based on the source tables) so a good table could be created in the time it takes to drag the needed fields to a query design and run it.
2) to create a temp table based on another table in the db.
Some use make table queries as normal db function so as to over-write an existing table with new data. I would advise against this as it's an invitation to bloat at best, and corruption at worst.

 

[bearcub]

Thank you for the clarification. this helps alot