VBA Access - Multiple Tables count by date

Question

We'd like to count from an Access database that has multiple tables - about 50.

We need to count from 1 column in each table that is 'QCPASS' This is a check box - if a product passed the box was checked if failed then not. We need to count both for EACH table, also allowing the user to specify a date range from a date column that exists in every table.

I've tried this with a query but I am told the query is unable to select, count and do the date range. Any VBA help would be great.

Exporting to Excel would be great, but any results would be fine. Here is the query I created that counts in a column from each table passes and failures. I can't iterate with a query either, so VBA seems the way to go:

SELECT "Table1" , Count('qcpass') AS column 
FROM 5000028 
GROUP BY [5000028].qcpass
union 
SELECT "Table2",count('qcpass')
FROM 5000029 
Group By [5000029].qcpass;

marc_s · Accepted Answer · 2017-01-24 18:21:03Z

2

You can traverse the full TableDefs collection in your database, and create a query using VBA.

A word of warning: The TableDefs collection has the Access database system tables, so you need to skip this. A way I suggest you is to check for a specific table name prefix (it is noted in the code below).

public sub createMyBigUnionQuery()
dim db as DAO.database(), tbl as DAO.tableDef
dim strSQL as string, i as integer
set db = currentdb()
i = 1
for each tbl in db.TableDefs
    if left(tbl.name, 1) = "5" then ' Check for a table name prefix
        if i = 1 then
            ' The final spaces are important
            strSQL = "select '" & tbl.Name & "' as table, count(qcpass) as column " & _
                     "from [" & tbl.Name & "] " & _
                     "group by qcpass "
        else
            ' The final spaces are important
            strSQL = strSQL & " union all " & _
                     "select '" & tbl.Name & "' as table, count(qcpass) as column " & _
                     "from [" & tbl.Name & "] " & _
                     "group by qcpass "
        end if
        i = i + 1
    end if
next tbl
db.createQueryDef "qryYourFinalQuery", strSQL
db.close
exit sub

Notice that you can define any valid query you want. Take this as a hint, and tweak it to fit your specific needs.

Hope this helps you

Adressing @HansUp comment, if you need to filter your data by date, you have two options:

Include the where condition on every select created by the procedure
Include the date field in your query and group by it, and create a second query to filter the data you need from the created query.

I would personally go with option 1, and here is a sample code:

public sub createMyBigUnionQueryWithDates(d0 as date, d1 as date)
dim db as DAO.database(), tbl as DAO.tableDef
dim strSQL as string, i as integer
set db = currentdb()
i = 1
for each tbl in db.TableDefs
    if left(tbl.name, 1) = "5" then ' Check for a table name prefix
        if i = 1 then
            ' The final spaces are important
            strSQL = "select '" & tbl.Name & "' as table, count(qcpass) as column " & _
                     "from [" & tbl.Name & "] " & _
                     "where rowDate between " & cDbl(d0) & " and " &cDbl(d1) & " " & _
                     "group by qcpass "
        else
            ' The final spaces are important
            strSQL = strSQL & " union all " & _
                     "select '" & tbl.Name & "' as table, count(qcpass) as column " & _
                     "from [" & tbl.Name & "] " & _
                     "where rowDate between " & cDbl(d0) & " and " &cDbl(d1) & " " & _
                     "group by qcpass "
        end if
        i = i + 1
    end if
next tbl
db.createQueryDef "qryYourOtherFinalQuery", strSQL
db.close
exit sub

The reason I use cDbl(d0) is because Access dates are sensitive to regional settings, and I've had a lot of headaches dealing with it. Access (and many other Microsoft products) store dates as floating-point numbers (the integer part is the date, and the decimal part is the time).

Another word of warning: If your dates don't include time, then the between condition will work. But if they do include time, then I recommend you change the where condition to this:

"where rowDate >= " & cDbl(d0) & " and rowDate < " & cDbl(d1 + 1)"

edited Jan 24, 2017 at 18:21

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 12, 2013 at 21:54

Barranka

21.1k14 gold badges72 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

21 Comments

HansUp Over a year ago

This answer ignores the "count by date" part of the question.

Barranka Over a year ago

@HansUp It shouldn't be hard to write the appropriate fields, where conditions and group by criteria in the query, don't you think?

HansUp Over a year ago

Not really, but in a previous question OP said he does not want to write a WHERE for each of the 50 SELECTs he is unioning together.

Barranka Over a year ago

@HansUp I've edited the question to adress your comment. And I think the where condition in every select is the easiest way to go (this post says nothing about not wanting wheres on every select). Anyway, this is just one way to do it, and it works.

Barranka Over a year ago

@HansUp I've read the previous question from the OP... I think he didn't want to write a where condition "by hand" on every single select. By using VBA code the effort is minimal.

|

Collectives™ on Stack Overflow

VBA Access - Multiple Tables count by date

1 Answer 1

21 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

21 Comments

Your Answer

Sign up or log in

Post as a guest

Related