Recordset using GROUP BY

[cstickman]

Hey everyone,
I have a table that has vendors and managers associated to that vendor. I am trying to create a recordset with an SQL statement that picks the vendors based on manager and then group by. I need this because the table can house the same vendor multiple times. I have it working now, but they display the vendor numerous times based on how many orders are in the table. How do I use the group by method? The below code works without the group by. As soon as I add group by it throws an error (Run time error 3121 - Cannot group on fields selected with '*'.) Here is the code that works with the group by commented out

 Set db = CurrentDb
     strSQL = "SELECT * FROM tablename Where vendormanager = '" & managerName & "';" 'GROUP BY vendorname;"
    
    Set rs = db.OpenRecordset(strSQL)

I can explain the code - vendormanager equals managerName which is a variable DLOOKUP value based on who opened the form. The column that I want to Group By is the vendorname. I have it populating into a list box. Below is the code for the listbox

 Me.lstvendorcounts.RowSource = ""
    Do Until rs.EOF
        Me.lstvendorcounts.AddItem rs![vendorname]
        rs.MoveNext
    Loop

Any suggestions on how I can get the vendor name to populate once instead of all the times they placed orders? Thanks

 

[jdraw]

What are the tables in your database?
I see Vendor, Manager and Order and suspect that your underlying tables and relationships are not normalized.

 

[cstickman]

it is actually one table and the table name is tblorders. When an order comes in it logs the vendor name and manager automatically through a query I believe. So it is all stored in one table. I was thinking about making a separate table with just the vendor and manager and grabbing the information that way. Well that was going to be my workaround.

 

[jdraw]

OK. What exactly is the processing that involves this information? Is this part of some bigger "system" such as Inventory or Finance???
Can you tell us in a few lines of simple plain English what this database is about/its purpose? No jargon, just simple English like you would use to tell a 6 yr old or an 80 yr old granny.

 

[cstickman]

Ok, so the managers have requested a page where they can see the stats. So I am creating a dashboard for them. I want to list the vendors that they are assigned too. So a form with a snap shot of the vendors, how many orders and etc. That is why I have it populating to a list box. So whoever logged in would populate the correct information.

 

[jdraw]

So a Manager is assigned to 1 or more Vendors and Vendors make Orders (Sales)?

Can you show us some sample (even made up data) representing the facts involved? You could also tell us more about the "report/dashboard/form" you have in mind.

This is still not clear ...."how many orders and etc"

 

[Cronk]

Assuming the table has fields, VendorManager and VendorName, how about

select VendorManager, vendorName from tablename group by VendorManager, VendorName

 

[cstickman]

Hi everyone I was able to solve it by using a parameter query and passing the variables through the recordset. Below is part of the code that I did to make it work:

 Set db = CurrentDb()
 Set qdf = db.QueryDefs("qryvendorlist")
 qdf("EnterManager") = varUser
 Set rs = qdf.OpenRecordset()

Thanks everyone!!