Group By Query from List Box Selection

[A_McKibben]

I have only scratched the surface of using Access so I may be in way over my head here.

I have a List Box on a form that I want to use to select the field for a query. The Row Source is the table the query is being ran on, and the Row Source Type is set to Field List. I guess I am just missing the disconnect of how to get my selection in the List Box to change the field for the query.

I have attached a very basic example of the Database I'm working on, and will do my very best to give more insight, but again I'm still just scratching the surface. Thank you.

 

[theDBguy]

Hi. Welcome to AWF! I'm not clear what you're trying to do here. Normally, you would use a form to set the criteria of a query. But, you seem to be trying to maybe "modify" a query structure based on the fields you selected from the form. Is that right? If so, why are you trying to do this?

PS. Looking at your table structure, I think maybe the problem is there. You seem to have a "repeating group," which is probably what you're trying to select from the Listbox. If so, you might consider redesigning your table to a properly "normalized" structure. Cheers!

 

[plog]

I suggest you demonstrate your issue with data. You posted this in the query section so it seems you want to end up with a data set. Please show us what data it is you expect to end up with. Give a concrete example using the 5 records you posted in the tblStudents table.

 

[A_McKibben]

Thank you both for the prompt response!

theDBguy: My end goal in all of this is to try to automate these queries a little bit more. I have a few more queries like this I use fairly regularly where I only need to change one field in the query to get a different count. Being able to change the field one place and have it update multiple queries to group by that field would be sublime.

Data Structure is a little difficult. The data is created in ArcGIS (joining points to a polygon based on spatial location) and imported as a .dbf in to access to make these queries easier.

plog: Here is what I am trying to accomplish. right now this is the result of the query:

I would like to be able to change it to this:

using the List Box of fields. If I'm trying to do to much please let me know.

 

[theDBguy]

Hi. In essence, you're trying to modify the query design, correct? For example, let's say you have a bunch of queries like so:

QueryDaily:

SELECT Sum(Field1) AS DailyTotal FROM TableName GROUP BY DateField

QueryWeekly:

SELECT Sum(Field1) AS WeeklyTotal FROM TableName GROUP BY DatePart("ww",DateField)

QueryMonthly:

SELECT Sum(Field1) AS MonthlyTotal FROM TableName GROUP BY Month(DateField)

Then, sometimes you want to know the "numbers" (daily/weekly/monthly) for Field2 or Field3 or Field4, correct?

If so, you should look into the QueryDef object. That's what you'll need to manipulate to "automate" your queries.

However, as I was saying, I think the "root" cause of your issue is the table structure. But if you can't change it, then I guess you have to use code.

Good luck!

 

[A_McKibben]

You are correct in your assessment, and having to dive in to code is exactly what I was afraid I'd have to do. I'm not a coder by any stretch of the imagination. Thank you though for your assistance!

Back to the books to learn more about querydef.

 

[theDBguy]

You are correct in your assessment, and having to dive in to code is exactly what I was afraid I'd have to do. I'm not a coder by any stretch of the imagination. Thank you though for your assistance!

Back to the books to learn more about querydef.

Let us know how it goes...

 

[A_McKibben]

Discretion being the better part of valor, I don't think this is possible with my current skill set. I have exactly zero experience in VBA. I would like to thank you again though for your assistance.

 

[theDBguy]

Discretion being the better part of valor, I don't think this is possible with my current skill set. I have exactly zero experience in VBA. I would like to thank you again though for your assistance.

Hi. What you're asking for is not hard but not easy either. We all started out with a bunch of queries and forms in our databases before we had enough skills to trim them down. I'm sure you'll get there someday. Perhaps for now, just go slow and just make sure you have a good working db. You can add the "niceties" later. Cheers!

 

[A_McKibben]

I'd like to thank you guys for the impetus to take a shot and learn some VBA. I got it doing what I want it to do. Of course there's going to be some cleaning and quality of life stuff (like if no field is selected) that need to be cleaned up. but I'm over the moon on this one right now.

Private Sub btnCalcOpt_Click()

Dim daStudentJoin As DAO.Database
Dim daQdf_LiveIn As DAO.QueryDef
Dim OptName As String
Dim str_LiveInSQL As String

CurrentDb.QueryDefs.Delete "qryLI"

Set daStudentJoin = CurrentDb
OptName = Me.lbxOptSelect

strK5_LiveInSQL = "SELECT Count(tblStudents.StudentNum) AS CountOfStudentNum, tblStudents." & OptName & " FROM tblStudents GROUP BY tblStudents." & OptName & ";"

Set daQdf_LiveIn = daStudentJoin.CreateQueryDef("qryLI", strK5_LiveInSQL)

Me.sfLive.SourceObject = "Query.qryLI"
Me.sfLive.Form.RecordSource = "qryLI"
Me.sfLive.Requery

End Sub

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.