Creating a Table - Null Values

Question

I just went to upload a spreadsheet and create a new table in Access.  The spreadsheet has almost 3000 records.  I created the table, but when I went to assign two columns to be primary key(s), I was told that my primary keys couldn't contain null values.  I looked at the bottom of the table and don't see any extra lines.  I scroll through the records and don't see any empty fields, but that's a lot of records and I could be missing something.

Is there some easier way to search through records to find null values?

Answer 1

You can't set a primary key on a query.

You could create a new table containing the results of the query:

Open the query in design view.

Click Make Table in the Query Type group of the Design tab of the ribbon.

Specify a name for the new table, then click OK.

Click the Run button in the Results group.

Confirm that you want to add records to the table.

Answer 2

The primary key of your table at present table is a composite one of StudentID, TopicsMastered and AssessmentDate.  You can return only those rows per student/topic with the earliest assessment date, assuming you have a date column, with a query along these lines:

SELECT *

FROM [YourTable] AS T1

WHERE [AssessmentDate] =

    (SELECT MIN([AssessmentDate])

     FROM [YourTable] AS T2

     WHERE T2.[StudentID] = T1.[StudentID]

     AND T2.[TopicsMastered] = T1.[TopicsMastered]);

So you could use this as the RecordSource for a report.

To permanently eliminate the redundant rows, if you make a copy of the structure only of you table under a new name you should be able to insert the rows returned by the above query into it by changing it to an 'append' query.  In this new table it should now be possible to define StudentID and TopicsMastered as the composite primary key.

Alternatively you could delete the redundant rows from the original table with:

DELETE *

FROM [YourTable] AS T1

WHERE [AssessmentDate] >

    (SELECT MIN([AssessmentDate])

     FROM [YourTable] AS T2

     WHERE T2.[StudentID] = T1.[StudentID]

     AND T2.[TopicsMastered] = T1.[TopicsMastered]);

But if you do the latter, be sure to back up the table first.

Answer 3

If it's for reporting purposes, you could create a query based on the table.

Add the student id and topic mastered fields, and the date field.

In the Show/Hide group of the Design tab of the ribbon, click to highlight the Totals button.

This will add a Total row to the query grid.

Initially, the Total option will be set to Group By for each column.

Leave it that way for the student id and topic mastered fields, but set it to Min for the date field, to make the query select the earliest date for each student/topic.

This query will return unique student/topic combinations.

As mentioned, it is for reporting purposes only - you cannot add, edit or delete records in a Totals query.

Answer 4

Create a new query in design view (click Query Design in the Create tab of the ribbon),

Add the table, then close the Show Table dialog.

Add the two fields to the query grid.

In the Criteria line of the first field (column), enter Is Null

In the line below that of the second field, also enter Is Null

Click the View button on the left hand side of the ribbon to switch to datasheet view.

You should see all records in which either of the fields (or both) is null.

Answer 5

Create a simple query with    Is Null    as criteria.