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 were similar, they were not exact the same. They needed to modify the headings to be sure they were the same in all tables involved. Then the Excel files can import into the master Access table. If the old tables are in Access, you create an Append action query and select the old table with the fields you wish to append to the new master table. You may want to unselect primary keys in case ID fields are used in individual tables. You can then use a Query Wizard to search for duplicates or unmatched data in a table. In many cases, however, more one-on-one help is required to assist users when moving data from one application to another, especially if it involves several data sources.

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 records, from which you can click the Total button on the Home tab of the ribbon and select Count from the bottom row of the query results screen. We will show this query example in our Queries part 1 class on Thursday, July 21 from 10 a.m. – 12 noon.

Question: I my database I would like to fill multiple cells with the same info. In Excel there is that lovely function where you highlight all the cells you want to fill the same info and do a fill right or fill down select and it’s like magic. I am not finding the easy way to do this in Access but there has to be one.

Answer: That feature is not really available in Access, although you can create autonumber fields that will automatically add incremental values. But if you want to fill in a table with repeated values, you must add the field individually or create an update query. You can however, save typing by pressing Ctrl ” to repeat the contents of a field in the previous record.