Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-13-2019, 10:58 AM   #1
melodyh40
Newly Registered User
 
Join Date: May 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
melodyh40 is on a distinguished road
Passing the name of a FIELD to a query from a combobox

Hi!

I wish to allow to the user to select the name of a FIELD in a query from a combo box and have the query run and show only that field.

That is, a user could choose MATH101 from a combo box, which is the name of a yes/no field in a query. I added a button that assigns the name MATH101 to a temporary variable, then opens the queyr which references the MATH101 in the Field: line of the query. Of course, that just displays MATH101 in the query, instead of looking up the yes/no value for the field called MATH101.

Is there any way to dynamically make access understand that I only want to see that field in my query? It is a huge table and I want my admin to only see that one field of her choice.

Any tips would be super! Or, alternatively, let me know what to search for in this forum.

Thanks!

Melody

melodyh40 is offline   Reply With Quote
Old 05-13-2019, 11:01 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,974
Thanks: 114
Thanked 3,002 Times in 2,729 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Passing the name of a FIELD to a query from a combobox

I'm not clear what you are asking but combo box record sources have three options: Table/query, value list and field list. Perhaps the last of these will do what you want.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns Ė the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 05-13-2019, 11:01 AM   #3
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Passing the name of a FIELD to a query from a combobox

No, inclusion of a field cannot be dynamic via an expression in query. Field is either in the query or it isn't. This is not the same as constructing a field with an expression. Example:

Data: IIf(Forms!formname!comboboxname = "Math101", [Math101], Null)

The field name Data is fixed in the query, not dynamic. The name does not change even though the data displayed is dynamic via the conditional expression.

How many fields will user choose from?

Users should not work directly with tables and queries, just forms and reports. VBA code behind form can manage display of controls.

Actually sounds like need to normalize data structure then apply filter criteria to retrieve the "Math101" records.

Otherwise, requires VBA code to modify the query object.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 05-13-2019 at 12:47 PM.
June7 is offline   Reply With Quote
Old 05-13-2019, 11:04 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,783
Thanks: 57
Thanked 1,267 Times in 1,248 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Passing the name of a FIELD to a query from a combobox

Hi Melody. Sounds like you may have "repeating groups" in your table if you have multiple Yes/No fields, which is probably against "normalization" rules.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-13-2019, 12:33 PM   #5
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,310
Thanks: 537
Thanked 939 Times in 890 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Passing the name of a FIELD to a query from a combobox

What about something like this?

http://www.niftyaccess.com/show-hide-export-to-excel
__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 05-13-2019, 12:42 PM   #6
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Passing the name of a FIELD to a query from a combobox

As I said, code behind form. That example probably uses code like:

Me.Controls("NameOfControl").ColumnHidden = Me.Controls("NameOfControl").Name <> Me.comboboxname
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 05-13-2019, 12:43 PM   #7
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,035
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Passing the name of a FIELD to a query from a combobox

Hi Melody,

If you have a check box for "Math101", I'm guessing you have a check box for "Math102"also? So what you are doing is storing DATA as FIELD NAMES. What you should be doing is storing this in a child table which would be easy to query.

Your child table would probably have
StudentID - Primary key from Student Table
ClassID - Primary key from either a "Class" table or a linking record that allows you to set up an ED plan for the student based off of a Class table.
Semester - When the student took the class
Grade

This will allow you to track, per student, when they took a class (allowing for repeats) as well as their grade.

This is a very simple version, but probably far more useful than having "MATH101" as a check box in a record.

Mark_ is offline   Reply With Quote
Old 05-13-2019, 01:46 PM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,554
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Passing the name of a FIELD to a query from a combobox

Skip the implied code, skip the names of control types, and tell us in descriptive terms what you were trying to accomplish. As has been noted, there is some question as to whether your setup is appropriate for the action you appear to be implementing.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-14-2019, 12:04 PM   #9
melodyh40
Newly Registered User
 
Join Date: May 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
melodyh40 is on a distinguished road
Re: Passing the name of a FIELD to a query from a combobox

Thanks for the responses, everyone! Yes, I didn't structure this well. However, I don't have time to start over. What I decided to do was to just show the query with all the courses and freeze the left columns with the student info. Then, the Admin can just scroll over to get to whatever course she wants to enter and click her way down (exactly like she would do in MS Excel). Not super elegant, but she shouldn't have to use it often enough to worry about it. Thanks again, everyone!
melodyh40 is offline   Reply With Quote
Old 05-14-2019, 12:06 PM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,783
Thanks: 57
Thanked 1,267 Times in 1,248 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Passing the name of a FIELD to a query from a combobox

Quote:
Originally Posted by melodyh40 View Post
Thanks for the responses, everyone! Yes, I didn't structure this well. However, I don't have time to start over. What I decided to do was to just show the query with all the courses and freeze the left columns with the student info. Then, the Admin can just scroll over to get to whatever course she wants to enter and click her way down (exactly like she would do in MS Excel). Not super elegant, but she shouldn't have to use it often enough to worry about it. Thanks again, everyone!
Hi. Glad to hear you found a working solution. Good luck with your project.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-14-2019, 12:26 PM   #11
melodyh40
Newly Registered User
 
Join Date: May 2019
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
melodyh40 is on a distinguished road
Talking Re: Passing the name of a FIELD to a query from a combobox

Quote:
Originally Posted by Mark_ View Post
Hi Melody,

If you have a check box for "Math101", I'm guessing you have a check box for "Math102"also? So what you are doing is storing DATA as FIELD NAMES. What you should be doing is storing this in a child table which would be easy to query.

Your child table would probably have
StudentID - Primary key from Student Table
ClassID - Primary key from either a "Class" table or a linking record that allows you to set up an ED plan for the student based off of a Class table.
Semester - When the student took the class
Grade

This will allow you to track, per student, when they took a class (allowing for repeats) as well as their grade.

This is a very simple version, but probably far more useful than having "MATH101" as a check box in a record.
Your assessment is exactly bang on. I was pretty much an idiot when I sent it up originally and, for that database, there's no turning back now.

Just if I can impose upon you for my general education, how could I have done this correctly?

Here's the structure I should have had, I think:

tblEmployeeInfo
EmployeeID - Employee ID number - number field - Primary Key
LastName - Employee last name - text field
FirstName - Employee first name - text field

tblCourseInfo
CourseID - Course identification number - number field - Primary Key
CourseName - name of the course - text field

tblTrainingTaken (this is, I think, the child table you mention)
EmployeeIDTraining (related to EmployeeID in tblEmployee)
CourseIDTraining (related to CourseID in tblCourseInfo)
CourseTaken - has the course been completed? - yes/no field
CourseTakenDate - the date that the course was completed - date field

The interesting thing about this is that EVERY employee has to take EVERY course in tblCourseInfo as a condition of employment. So, I want tblTrainingTaken to contain a record for every employee for every course. Adding a new employee to tblEmployeeInfo would then automagically add a record for every single course in tblCourseInfo so that the Admin could then just access their training record on a single form and check whatever off. I couldn't figure out the relationships.

Does this make any sense? I'm sure I am going to encounter this in the future so now is the time to learn! If no one has the time to answer, that's OK. It's not critical.

Thanks again!

Melody
melodyh40 is offline   Reply With Quote
Old 05-14-2019, 02:03 PM   #12
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,035
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Passing the name of a FIELD to a query from a combobox

I'd say you are half way there with your thinking!

For myself, how I'd do this is the same table's you've laid out, but the way I'd set up the user interface is a little different.

Make a query on tblCourseInfo. Join it to tblTrainingTaken but EXCLUDE every record in TlbCourseInfo where there is a matching entry in tblTrainingTaken for a given employee ID. This lists ONLY those courses one employee hasn't taken.

Use a continuous form to display ONLY those records that need to be entered. Prompt for a date. I'd use a button to then create the linking record with the date supplied.

Oddly I'm working out an example app for how to do this, but I haven't had a chance to finish it up. Once I do, I'll post it.
Mark_ is offline   Reply With Quote
Old 05-14-2019, 03:43 PM   #13
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,554
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Passing the name of a FIELD to a query from a combobox

You asked how we might do this.

Actually, what you want to look up is a "Junction Table" which is how you "fake" a many-to-many relationship. You have many employees who must take many courses, so this is classic many-to-many. The junction table is a child to TWO parents - employees and courses. An entry in the junction table lists the course ID, employee ID, date taken, and grade - and anything else needed that doesn't clearly go into the course table by itself or the employee table by itself.

In the SET theory sense, a junction table IS the junction (overlap) between two tables each containing an independent entity (a person or a course in your case). It shows (or lists, if you prefer) how the persons and courses interacted (overlapped). That means that this record (perhaps an "attendance" record?) is DEPENDENT to both tables. Which means that relational integrity could be used so that the course ID depends on the course table and the person ID depends on the person table. TECHNICALLY this junction table doesn't require any indexes (but if you establish RI, the parent tables DO require PK-type indexes). IF you want to establish a unique index on it, it would have to be a compound index on Person, Course, and perhaps DateTaken (since if it is possible to pass, it is also possible to fail and thus need a re-take.)

From this junction table, you can JOIN to a list of classes and then look up a personnel ID to see the classes the person HAS taken, or JOIN to a list of persons and then look up a course to see who has taken it. BUT... there is this neat little syntax such that you can ask this question:

Code:
SELECT PersID, PersName FROM Persons 
WHERE NOT 
( PersID IN ( SELECT PersID From PersCourse WHERE CourseID = 'Math101' ) ) ;
In English, that is "Tell me every person who is NOT listed as having taken Math101."

Now, the beauty of this approach is that if you add a course, you add that data to the course table and the queries INSTANTLY return everybody as needing to take that new course. If you add a person to the person table, the queries INSTANTLY return every course that the new person will have to take, only depending on how you structure that sub-query.

Which means you don't change table STRUCTURE at all. No new fields for new courses. Just new records darned near as many as you need forever (or until you blow out the size limit).
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 05-15-2019, 04:01 AM   #14
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,791
Thanks: 55
Thanked 1,027 Times in 993 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Passing the name of a FIELD to a query from a combobox

I presume you don't want to just see one field from a table. That would be meaningless. I presume you want a core set of fields, and then some additional user selected fields.

So it's easy to present a list (rather than a combo box) of the fields, and then produce a select query to include just the selected fields.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-15-2019, 04:22 AM   #15
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,408
Thanks: 0
Thanked 559 Times in 555 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Passing the name of a FIELD to a query from a combobox

How would you accomplish producing a select query to include just the selected fields?

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Reply

Tags
combo box , field , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing report field value to query rjrichar40 Reports 0 05-20-2009 09:20 AM
Passing a date field to a Query scampbell Queries 3 04-09-2007 01:58 AM
Passing a Field Name into a SELECT Query? ac163601 Queries 4 08-09-2006 03:29 AM
Passing a Field Name as a Parameter to a Query John Baker Reports 1 12-06-2005 08:28 AM




All times are GMT -8. The time now is 02:08 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World