Access (or excel) check for fields with no data

Sandtree

I receive a CSV file with circa 1m rows in it and circa 50 columns (were you to convert it into a flat table) and at present this goes into an Access db with all fields set to text. Column 1 defines what record type the row is (there are 4 types A-D) and therefore what the data in each column represents so column 5 is surname for type A rows, due date for type B rows etc. There are probably no columns that are a consistent data type across all types other than for the later columns that only apply to type A rows.

I want to see for each row type which columns are "empty", this could be a true null a space or a non-breaking space, in every record of that type... ie is every record of type B missing a due date (ie column 5 is universally empty). I am guessing I could use a long query to create an aggregated function for each of the 49 columns (other than column 1) and just filter on column 1 but this feels slightly clunky so other suggests would be appreciated.

Jenni_D

If it's in Excel then you can filter columns to only show the data you wish to see. But Access is probably the better tool to use for data searching ... have you tried using the Report feature to build a report (hence query) to return the data required?

Sandtree

I've never used reporting in Access... generally I've only used it as a way to run queries against SQL databases over an ODBC connection.

I think with hindsight its probably better to do an aggregate query on each field and just repeat it circa 200 times so that I can not only identify the fields with no data but those with a token amount of junk data and/or fixed values.

I'm sure someone more knowledgeable than me could do it in a more sophisticated way but at least it wont take too long but just be tedious 

Heedtheadvice

I like Excel and it is very powerful but for this sort of problem really comes into it's own Access.

It is very easy to set up a query or queries to return records that meet a specific or several specific criteria in single or multiple tables.

For example any records that have unpopulated (completely empty) fields can be located with the isnull() function.

Similarly there are several functions to find empty fields, specific date data, filed text content or numeric data.

Nesting is easy and visually a lot easier to deal with than Excel.

See the full range of built in functions at https://support.microsoft.com/en-us/office/access-functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83

Many of those will not be of interest but you can have a look under the category headings such as date/time or Inspection etc.

If you need more help just ask.

Irrespective of whatever method you use, key to getting results is being able to define what you want to find/trap! Some things are easy/obvious such as empty fields, future dates, no name entries etc. but the unexpected often fall through checks such as entrues incorrectly formatted (such as post codes required in a specific format -space or no space, capitalisation) or wrong type of data in a field, insufficient record numbers or misding records! You just have to consider what might be wrong and check for them even maybe if very unlikely.

If you have 5 data types then it might be easiest to have 5 queries each of which concentrates on 1 specofic data type and perhaps a sixth that conentrates on undefined types?

Having a csv file with mixed data types in fields (columns) and then importing all as text string does complicate it a bit when you want to check for specific entry types and veracity such as dates so a bit of manipulation might be required giving a short series of queries to cope with record types such as turning text to date.

With access you can see much easier the individual steps and therefore it is better to assure the quality of the tests you are trying to perform and is easy to replicate and modify plus record numbering can be automated with indexes without having to add that as a column in Excel with is not hard fixed to a particular row!

Sandtree

If I wasnt lazy I would do some transformation on load so that the different row types go into independent tables and therefore could have meaningful headers and data types but haven't got that far and what I'm doing is more quick and dirty.

I effectively know how to look along a row (and doing joins etc) but initially had wanted to basically run a query to say for every row where Field1 = D return the field names where all records have a Null, space or NBSP  and it would have come back with a result of Field3, Field5, Field6 and then Field8-Field55

However decided I needed to be a touch more thorough and identify not only the fully empty ones but ones with spurious looking data in them so stepped through each one doing a aggregate function on the field and count of records with each value

WaywardDriver

To get counts of each unique combination of ColA and ColD values
SELECT ColA, ColD, COUNT(*) FROM <tablename> GROUP BY ColA, ColD
 

WaywardDriver

A trick I've used before is to construct a long query in Excel. For this to work the database field names should be something like C1, C2 etc.
In cell B2 enter ="SELECT " & ROW(A2) & " AS ColNum, C1, C" & ROW(A2) & ", COUNT(*) AS Nvals FROM <tablename> GROUP BY C1, C" & ROW(A2)
copy this to B3 and edit to start with " UNION SELECT" etc
copy this down to row 55 or whatever, concatenate/join the values in B2.B55 into an empty cell, then copy and paste contents of this cell into the database query designer.
The advantage of this is you need only change the query once and copy. e.g. to look for all empty fields.