Query to return records with Field names containing "PL-" and contain number values

[captdkl02]

Looking to develop a MS Access 2010 query to return records with field names containing "PL-" and have numeric value. I am attaching a sample file representing the DB table with column names and one record of sample data as reference.Thank you.

[orange]

Your material

a sample file representing the DB table with column names and one record of sample data as reference.

seems totally not normalized. I don't know your experience with Access nor database design, but it seems that a review of Normalization concepts may be useful.

May be if readers knew more about what PL-3 PL5 ... meant, and had a plain English description of WHAT you were trying to accomplish would help the reader.

There is a tutorial on Entity Relationship Diagramming at
http://www.rogersaccesslibrary.com/T...lationship.zip

Good luck.

[captdkl02]

Orange,This DB table is a course catalog information. PL represents proficiency level. I appreciate the ER diagramming info. This still does not answer my question how to do a query that returns each record when the field names containing "PL-" within the name and having a data value in the field.

Thank you.

[orange]

I still don't know your proficiency with Access or vba.
There is no simple query to select fields from a table based on their name and if the corresponding records have values.

You can set up a (SELECT) query to get the data from your Table with this general pattern, where field1, field2 are your PL- fields.

SELECT field1, field2, field9 FROM YourTableName

But to get those records where these fields have values, you need to add a constraint to the query above.

WHERE len(field1 & "") >0 and len(field2 & "") >0..... or some constraint(s) that makes sense to your issue.


You could probably work toward some vba code starting with code to find the fields with names PL-* and then create the query. But if it's your table, you know the field names so using the query wizard you should be able to build a query with all the fields and data you need.

If you want to pursue some vba, which I don't recommend, this may get you started with step 1.

'---------------------------------------------------------------------------------------
' Procedure : captdkl02
' Author    : Jack
' Date      : 22/03/2013
' Purpose   : To get a list of field names with common starting string from a known tabledef
' and output those field names with a comma separator in the immediate window.
'---------------------------------------------------------------------------------------
'
Sub captdkl02()
    Dim MyFields As String
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
    Dim db As DAO.Database
    On Error GoTo captdkl02_Error

    Set db = CurrentDb
    Set tbl = db.TableDefs("Customers")  'Put your table name here in place of my Customers
    For Each fld In tbl.Fields
        If fld.name Like "C*" Then   'Replace the C with your PL-
            MyFields = MyFields & fld.name & ", "
        End If
    Next fld
    MyFields = Mid(MyFields, 1, Len(MyFields) - 2)
    Debug.Print " I need to put these fields in a select query " & MyFields

    On Error GoTo 0
    Exit Sub

captdkl02_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure captdkl02 of Module AWF_Related"
End Sub

In my sample Customers table this is the output from the vba code listed above

 I need to put these fields in a select query CustomerID, CompanyName, ContactName, ContactTitle, City, Country, CompSoundex, ContactSoundex

There is a free data model related to Course Catalogs at http://www.databaseanswers.org/data_...logs/index.htm

[captdkl02]

Orange,I appreciate the reply back. I have a similar VBA routine pulling field names with PL-. From what I can tell with query capability within Access you can not query a name field containing a string subset of the name. I was hoping a one step query; however, I will proceed with VBA routine and write a query with those fieldnames.Thank you for your help.

[orange]

Why can't you just look at the table design (or query design) and pick the fields directly?
With most databases, unless you have some privileges, you would not be allowed into the DDL and field names in tables.

It seems you are going down a long circuitous path to get field names that should be readily available to you.

I have some routines to do " a simple data dictionary" of tables, queries, fields descriptions. But doing a query to get field names from a table is not common. If you have to put some constraint inn your query, you will have to know the field in question.

Perhaps if you described why you are proceeding in the way you are, I can help. But it's like your creating a generalized routine so that you could work with any field in any table and create a query on the fly or something similar.

At some point you have to know which field has which name or datatype.

Is this an academic exercise or is there some reason for this approach? I'm curious and could help, if it isn't just because you don't know how to do it directly -say with the query wizard.

Did you look at the data model I referenced earlier?