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.
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.
Your material
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.a sample file representing the DB table with column names and one record of sample data as reference.
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.
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.
Last edited by captdkl02; 03-22-2013 at 09:45 AM. Reason: To make the response more clear.
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.
In my sample Customers table this is the output from the vba code listed aboveCode:'--------------------------------------------------------------------------------------- ' 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
There is a free data model related to Course Catalogs at http://www.databaseanswers.org/data_...logs/index.htmCode:I need to put these fields in a select query CustomerID, CompanyName, ContactName, ContactTitle, City, Country, CompSoundex, ContactSoundex
Last edited by orange; 03-22-2013 at 12:15 PM. Reason: added link to data model re course catalogs
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.
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?
Last edited by orange; 03-22-2013 at 01:24 PM. Reason: spelling