Learn IT

Access Query Questions

Question: I added a field to my Access table but when I tried to modify a query and add that new field, it didn’t appear? What is happening? Answer: If your query came from a table, then you should be able to add the field. If the query was actually created from another query, then that could be why the field doesn’t appear on the list. In looking at the query, it was coming from a query by the same name as the table. When… Continue Reading

Looking up and replacing massive Excel data

Question during a “drop in” session: I have an Excel Workbook with several large worksheets. Three worksheets contain over 100,000 rows, one contains over 80,000 and another 23,000 rows. I need to match up the IDs and place the information from two massive worksheets into the one with 23,000 rows and I can’t figure out how to do it without having to search for each record manually. Answer: I was possible to use a VLookup formula, however by the time I got to the third… Continue Reading

Using Excel data in Access

I have data in an Excel spreadsheet that I would like to use in an Access Database. What are the steps? Importing or linking data from Excel to Access works well if the Excel data is in rows and columns without any subtotal rows, formulas or blank rows or blank columns within the worksheet or range. Column headings should not be wrapped or contain special characters and must be unique names. Then the data should import pretty cleanly by: Create a blank database in Access… Continue Reading

Question of the week of 11/28/11 – Importing data from different sources into a single database

Question: I have a number of Excel spreadsheets and Access tables with similar data that I would like to merge into one large database to make queries and reports. What is the best way to do this? Answer: This is a more complex question to give a quick and simple answer without knowing what the data looks like. Basically, if the data has the same headings (fields) and field types, it should import well into an Access table. In looking and this user’s data, even though the headings… Continue Reading

Question of the week of 7/18/11 – Counting unique records in Access

Question: I’m trying to count only unique records in an Access table and can’t seem to figure it out. Answer: You need to create a query containing the fields that are duplicated. (If there is just one record with an item in a field that is different that will consider that record unique). Then in the query options (right click in the light gray area of the query) select Properties and change Unique Values to Yes. Then run the query and it should show the unique… Continue Reading