jex57

Board Regular
Joined
Oct 29, 2015
Messages
62
Hi all,

In desperate need of assistance.

I am currently building a statistical view of data - % data missing, % complete etc.

I am the writing these values into a stats table so that one is able to see progress.

The only way I can do this is via a number of queries, and to keep things neat I am doing this via VBA

I cant get to update the stats table, can append to it.

The steps that I am doing are as follows:

1 - Append data to temp table sorted by domain - data looks like this:

Domain ID Number of fields Number of empty fields
Domain 1 182
Domain 2 184
Domain 3 90
Blank 100


This works

Next I want to update the number of empty fields (I cant append as it creates a new record, which I dont want.

This is where I get stuck.

My code currently looks like this:

Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim newSQL1 As String
Dim Sql As String
Dim NumberOfFields As Integer
Dim DomainCat As Integer
Dim NumberofEmptyFields As Integer
Dim NumberofEmptyBT As Integer
Dim rst As DAO.Recordset
On Error Resume Next

On Error GoTo 0
' Count number of Fields in total
newSQL1 = "INSERT INTO TblTempStats ( FieldCount, DomainCatID )" _
& "SELECT Count(TblField.FieldID) AS CountOfFieldID, TblField.DomainCatID" _
& " FROM TblField GROUP BY TblField.DomainCatID"



DoCmd.RunSQL (newSQL1)



'count number of empty fields


Set dbs = CurrentDb


newSQL1 = "SELECT TblField.DomainCatID, Sum(IIf([fieldname] Is Null,1,0)) AS EmptyField" _
& " FROM TblField LEFT JOIN TblTempStats ON TblField.DomainCatID = TblTempStats.DomainCatID" _
& " GROUP BY TblField.DomainCatID"




Set rst = dbs.OpenRecordset(newSQL1, dbOpenDynaset)


With rst
Do Until rst.EOF
Sql = "UPDATE rst LEFT JOIN TblTempStats ON rst.DomainCatID = TblTempStats.DomainCatID SET TblTempStats.EmptyFields = 'EmptyField'"
dbs.Execute (Sql)
End If
.MoveNext
Loop
End With
rst.Close
dbs.Close



Any help would be appreciated ( the bold lines are where I am struggling, I dont know how to use the results of the first query to update my table)
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,980
1. If you are in Access, there's no need to use vb for queries. Just make queries. You are making more work.
2. If you are in Access, there's no need to use DAO, (or ADO) , Access is already connected to the data. You are slowing things down.
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
Update with left join is not always feasible. Doesn't always make sense either (if there is no join field that matches then at best you are updating to null, which isn't an extremely clear result).

It is in fact easier to not script queries although you can do so.

If you just want to make it easier you can still write the queries without vba, and run them with vba:

Sub RunQueries()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query2"
DoCmd.OpenQuery "Query3"
DoCmd.SetWarnings True
End Sub

If nothing else this is easier to test and write, so you probably at least want to start out with regular queries at first, until they all work.

It is very hard to debug your particular queries as your table data and table relationships are not very clear to outsiders - need sample data or more information about what is in the tables. For instance, it is completely unclear why you would think to put a query in a loop and run it repeatedly, or why you want to update the number of empty fields when your original data shows the number of empty fields already in the table.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,292
Messages
5,641,379
Members
417,207
Latest member
Vxhaet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top