How to use group by in SQL Ms Access? (1 Viewer)

luzz · Aug 29, 2017

Hello everyone, I would like to have the GroupBy in my SQL code below. I would like to group by color as there one fabrications have duplication of colors. Can you assist me in solving this issue? Thanks alot!

Option Compare Database
Dim con As Connection
Dim rsMaxim As Recordset

Private Sub Form_Load()
Set con = CurrentProject.Connection
Set rsMaxim = New Recordset
rsMaxim.Open "Select Fabrication, FabricCuttableWidth, Color, OurQty, SupplierQty from Maxim", con, adOpenDynamic, adLockOptimistic
Call Display
End Sub

Public Sub Dsiplay()
txtFabrication.SetFocus
txtFabrication.Text = rsMaxim!strFabrication
txtWidth.SetFocus
txtWidth.Text = rsMaxim!sngFabricCuttableWidth
txtColor.SetFocus
txtColor.Text = rsMaxim!strColor
txtOurQty.SetFocus
txtOurQty.Text = rsMaxim!sngOurQty
txtSupplierQty.SetFocus
txtSupplierQty.Text = rsMaxim!sngSupplierQty
End Sub

Minty · Aug 29, 2017

Copy your query to the query designer. Click the totals symbol. Play around with the group options and see what it does to your query results.

Once you have it correct either store the query, or copy and paste it to your code to build it as a string.

luzz · Aug 29, 2017

I tried using query to run the SQL , however whenever i use the groupby on color or fabrication, it will prompt me this error "Your query does not include the specified expression 'Fabrication' as part of an aggregate function"

Below is my code:
Private Sub Form_Load()
Set con = CurrentProject.Connection
Set rsMaxim = New ADODB.Recordset
rsMaxim.Open "Select Fabrication,FabricCuttableWidth,Color,OurQty,SupplierQty from Maxim group by Color", con, adOpenDynamic, adLockOptimistic
Call Display

End Sub

Minty · Aug 29, 2017

Why are you using a ADODB connection to try this out?

Link to the table and use the query designer. Grouped queries are not very easy to write if you haven't done it before, and for testing purposes this is not a quick method to get the format correct.

Simply grouping on the colour won't have any effect because with a grouped query all the output fields have to be aggregated by some method. In your case I suspect you maybe want to count the fabrications by colour or something similar.

This is a good beginners guide http://www.fontstuff.com/access/acctut04.htm

Frothingslosh · Aug 29, 2017

Minty is right. Listen to the herb, man!

Seriously, do what he's suggesting. Use the query builder, it's WAY faster than doing it yourself even when you DO know how to build one of these by hand.

Unless, of course, this is for a homework assignment and you're not allowed to, but this seems a bit early in the semester for an assignment like this.

arnelgp · Aug 29, 2017

simply add those field in a group.
for fields with Qty of Value Sum them:

Code:

rsMaxim.Open _
		"SELECT Frabication, FabricCuttableWidth, Color, Sum(OurQty) As SumOfOurQty, " & _
		"Sum(SupplierQty) As SumOfSupplierQty From Maxim : & _
		"Group By Fabrication, FabricCuttableWidth, Color;", con, _
		AdOpenDynamic, adLockOptimistic

luzz · Aug 30, 2017

I am using ADODB connection to act as a bridge between my form and table so that i am able to retrieve the data in my table into my form.

luzz · Aug 30, 2017

@arnelgp

I am facing syntax error at the group by statement.

rsMaxim.Open "Select Fabrication,FabricCuttableWidth,Color,Sum(OurQty) As SumofOurQty," & _
"Sum(SupplierQty) As SumofSupplierQty from Maxim & _
"Group By Fabrication,FabricationCuttableWidth,Color;" ,con,adOpenDynamic,adLockOptimistic

luzz · Aug 30, 2017

@Minty

I tried using query to perform the group by, and copy the SQL code to my VBA code but the group by statement still could not work

arnelgp · Aug 30, 2017

Check your syntax on Group By, you. Don't have FabricationCuttabllleWidth on your Select statement..

luzz · Aug 30, 2017

@arnelgp

Now i am having issue with Expected: End of Statement error.

rsMaxim.Open "Select Fabrication,FabricCuttableWidth,Color,Sum(OurQty) as SumOfOurQty," & _
"Sum(SupplierQty) as SumofSupplierQty from Maxim: & _
"Group By Fabrication,FabricCuttableWidth,Color;", con, adOpenDynamic, adLockOptimistic

Galaxiom · Aug 30, 2017

luzz said:

Code:

rsMaxim.Open "Select Fabrication,FabricCuttableWidth,Color,Sum(OurQty) As SumofOurQty," & _
"Sum(SupplierQty) As SumofSupplierQty from Maxim & _
"Group By Fabrication,FabricationCuttableWidth,Color;" ,con,adOpenDynamic,adLockOptimistic

You are missing a space and close double quote after maxim

luzz · Aug 30, 2017

Thank you all for the help, After solving the group by error
now the error is "item cannot be found in the collection corresponding"

i am thinking if it is because i put sum[ourqty] in the sql statement then the system cannot read OurQty?
This code below is to allow the data in table to be shown in textbox on the form
Public Sub Display()
txtFabrication.SetFocus
txtFabrication.Text = rsMaxim!Fabrication
txtWidth.SetFocus
txtWidth.Text = rsMaxim!FabricCuttableWidth
txtColour.SetFocus
txtColour.Text = rsMaxim!Color
txtLbs.SetFocus
txtLbs.Text = rsMaxim!OurQty [error on this line]
txtYds.SetFocus
txtYds.Text = rsMaxim!SupplierQty

End Sub

Minty · Aug 30, 2017

luzz said:
I am using ADODB connection to act as a bridge between my form and table so that i am able to retrieve the data in my table into my form.

This makes no sense - make the table or in this case the query your forms record source.

The saved query you had earlier will be simple to adjust to get the results you need.

How to use group by in SQL Ms Access? (1 Viewer)

luzz

Registered User.

Minty

AWF VIP

luzz

Registered User.

Minty

AWF VIP

Frothingslosh

Premier Pale Stale Ale

arnelgp

..forever waiting... waiting for jellybean!

luzz

Registered User.

luzz

Registered User.

luzz

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

luzz

Registered User.

Galaxiom

Super Moderator

luzz

Registered User.

Minty

AWF VIP

Similar threads

Users who are viewing this thread