MS-Access Insert User Created Field

[MoonMullen]

Is there a way to create VBA code that will insert within an active query
window a pre-defined field in the first available "open" field column? I am
usually inserting a field called "Records" that is counting occurrences of
any fields with a table within my query. I want my field "Records" to use the
function, count(*), and return results with the format "#,##0" every time. Is
it possible to do this? If so, can someone please show the necessary code to
accomplish it? It just seems like it could be possible, yet it is beyond my
current coding capabilities. Also if this is possible how did you discover
this ability to code this functionality?

 

[Tim Ferguson]

I am
usually inserting a field called "Records" that is counting
occurrences of any fields with a table within my query. I want my
field "Records" to use the function, count(*), and return results with
the format "#,##0" every time. Is it possible to do this?

Can you have another attempt to explain this, perhaps offering a bit more
detail of your tables' design and what you are trying to achieve?

B Wishes


Tim F

 

[MoonMullen]

A sample SQL would be as follows;

SELECT DATA.SUPPLIER_PLANT_NAME, Count(*) AS Records
FROM DATA
GROUP BY DATA.SUPPLIER_PLANT_NAME;

I want a generic funtionality of adding to any select query the SQL of,
Count(*) AS Records. Does that help?

 

[Tim Ferguson]

?
A sample SQL would be as follows;

SELECT DATA.SUPPLIER_PLANT_NAME, Count(*) AS Records
FROM DATA
GROUP BY DATA.SUPPLIER_PLANT_NAME;

I want a generic funtionality of adding to any select query the SQL
of, Count(*) AS Records. Does that help?

I must be having a very thick weekend: I can't think of anything that is
more generic than that. It's about as simple a grouping query as it's
possible to get. Perhaps you want to parameterise it like

public function GroupingQuery( _
TableName as String, _
FieldName as String) _
As String

GroupingQuery = _
"SELECT ALL " & FieldName & " AS Grouper," & vbnewline & _
" COUNT(*) AS GroupCount" & vbNewLine & _
"FROM " & TableName & vbnewline & _
"GROUP BY " & FieldName & vbNewLine & _
"ORDER BY 2 DESC;"

End Function


and then use it in a report like

me.recordset = groupingquery("Data", "SupplierPlantName")


but that's all quite easy to do.

HTH


Tim F

 

[david epsom dot com dot au]

If you have an open query window, you will need to close the
window, edit the sql, and re-open the window.

Although you can edit the sql while the window is open, if you
do so Access is likely to become very confused, and it won't
work correctly until you close and re-open the window.

You edit the SQL in VB like this:

codedb.QueryDefs("query4").SQL = "select idxrecord from tblBS_banks;"


(david)