Results 1 to 9 of 9
  1. #1
    Katy is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    6

    Searching table column by column and count nulls

    Hi all,



    I have Access database, that store many tables, imported from different localisations (imported from .csv files)
    Tables consist of many columns, and records as well. I must create a tool (macro i think) that will allow me to check the quality of data in particular fields.

    Can you help me get started?

    At first I wanna create macro that checks all the fields in a given table (column by column) and counts "nulls".
    Once completed, the macro will display a short report showing how many cells are in the table, and how many are empty (nulls).
    In the next steps, I will add new conditions for checking the tables.

    Can you please advise me on how this macro design should look like? (this is my first macro in Access).
    Thanks in advance
    K.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Not a macro - use queries and/or VBA code.

    Your question is quite broad and without knowledge of your data structure, hard to provide specific guidance.

    Have you completed and Access training - at least in an introductory tutorial?

    A GROUP BY aggregate query may be place to start.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Katy is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    6
    Yes i did.

    I know quite well SQL and database topics.
    Aggregates and queries using "Group by" are familiar to me. I create reports, queries, and pivot tables i Access.

    I have no problem to write query counting "null", but i have never used queries with VBA code.

    I could write conditions on each table field using SQL, create a query based on each condition, and run them all on a given table.
    Can I do this with VBA? Could you give me an example? I have never used VBA in Access, (only in Excel)

    Is it possible to write a query in VBA code (for example count on nulls), execute it from VBA and save it to a variable?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    VBA code can construct SQL statements.

    Action SQL statements (UPDATE, INSERT, DELETE) can be executed from VBA. Example: CurrentDb.Execute "DELETE FROM tablename"

    SELECT statement can be used to set recordset object and then VBA can manipulate the recordset. A very simple example:

    Code:
    Sub SendEmail()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT EmailAddress, FirstName, LastName FROM Contacts")
    While Not rs.EOF
        DoCmd.SendObject acSendNoObject, , , rs!EmailAddress, , , "Subject Here", "Dear " & rs!FirstName & vbCrLf & " some text here"
    Wend
    End Sub
    So far, I don't understand why you feel VBA is even needed for your requirement.

    Are you familiar with domain aggregate functions?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Katy is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    6
    Quote Originally Posted by June7 View Post

    So far, I don't understand why you feel VBA is even needed for your requirement.
    Are you familiar with domain aggregate functions?
    Thank you for your interest once again.
    Domain aggregate functions? - probably not..

    I will try to describe in details what I would like to achieve and how I saw it, maybe my idea is bad?

    1. receive a table, that consist of columns named e.g "name","telephone"....e.t.c
    2. I run my tool and after a while I see report:

    - table consist of 20 columns
    - column1 - "name" - records: XXXX, nulls: XXXXX, Invalid data format: XXXXX
    - column2 - "telephonr" - records: XXXX, nulls: XXXXX, Invalid data format: XXXXX


    And now the question is how to achieve it?
    In my head I was given the idea to do a macro or function (my tool) that would generate such a report.

    - input: variable table name
    - output: generated report for each columns


    Pprinciple of operation of the tool:
    2.Column by column check data validation in fields:

    A. Count numer of columns in table:
    B. (in loop for each columns)
    column1
    IF column_name = "name"
    Correct data = ....... condition1, condition2....
    Count correct data/all data
    Count nulls..

    IF column_name = "telephone"
    Correct data = ....... condition1, condition2....
    Count correct data/all data
    Count nulls..

    IF .....

    Summary: column1 name is XXXX, has XXXX records, XXXX nulls, XXXX records are incorrect.

    now looks brighter?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    VBA code could do analysis in a function and feed the results back to wherever function is called from - like a query or textbox. Queries calling custom functions can be very slow. Or VBA procedure could write calculated data to another table.

    However, still looks like to me a query using intrinsic functions can provide the calculated data. Then a report bound to query could present the data.

    What would be considered 'invalid' data?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Katy is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    6
    At the beginning, I apologize that I did not response faster.

    I tried in different ways and wrote a procedure based on Dcount and Dsum functions.
    The procedure counts the data depending on the conditions that I create based on the information which type of data may be in a given cell

    Quote Originally Posted by June7 View Post
    What would be considered 'invalid' data?
    As a invalid data i treats data that does not match up, for example: too long field, bad characters, wrong first letter.
    (for example, field 'name' must be between 3 and 10 characters long and does not contain numbers - This is only example)

    I created form, that have different buttons - clicking the 'validation' button performs the procedure and finally after such validation report is generates a as string.
    Everything works great - in the pas, I did not use VBA in ACCESS and I did not know these functions Dcount and Dsum - so I'm glad I learned something )
    I have one more question because I would like to make this procedure more universal.

    Is it possible using VBA in ACCESS to retrieve the names of all tables in the database and all columns in each table?????
    Now, i defined table names and names of all columns in my procedure and i run it exactly for ONE TABLE.

    I would like to do it differently: - after clicking validate button:
    1. retrieve the names of all tables to my procedure
    2. retrieve the names of all columns in each table (and count it) to my procedure
    3. make the procedure able to work in the loop for all columns and all tables.

    Is it possible?

    Thanx a lot for your help
    Best Regards

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Is it possible using VBA in ACCESS to retrieve the names of all tables in the database and all columns in each table?????
    Yes, it is. Not something I have done, nor have any interest in doing.

    As for item 3 in your list, doubtful. Why would the same validation rules be applied to multiple tables? Should not be multiple tables with identical structure.

    Do research, build code, and when you have issues with code, start a thread with your question.

    Recommend data be validated at time of entry, not later.

    Name is a reserved word, should not use reserved words as names of anything.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Katy,

    I suggest you show us your database and some sample data.
    How are we to know what tables are involved? What Columns?
    What are the rules for Valid values for each of the columns?

    You really haven't given readers sufficient detail/context to offer more focused responses.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Number of Data Count in Each column of a table
    By Blessy clara in forum Queries
    Replies: 1
    Last Post: 04-04-2017, 02:30 PM
  2. Count values in a column on a table
    By bradp1979 in forum Queries
    Replies: 53
    Last Post: 08-04-2015, 05:02 PM
  3. Replies: 3
    Last Post: 03-02-2015, 09:50 AM
  4. Replies: 8
    Last Post: 10-03-2013, 08:11 AM
  5. searching 2nd column in multi-column listbox?
    By RedGoneWILD in forum Programming
    Replies: 6
    Last Post: 07-07-2012, 09:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums