Queries-Form

[Joe]

Hello,
Thanx in advance for any help!
I have big table with monthly sales of many products. I
have the number of times each product was sold every
month. I want to build a Form to select a product and then
see the number of products sold every half a year.

Reg,
Joe


PS: I already posted a request similar to this, but could
not get the help in my direction.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd set up an unbound form w/ a ComboBox and a subform. The subform's
RecordSource would be blank. There'd be 2 TextBox's on the subform:
one for the month partition; the other for the count of products sold
(see the SQL statement below).

On the main form I'd set up the ComboBox w/ the products in the drop
down list (Presumably there'd be a product ID and the product name. The
product ID would not be visible.). The BoundColumn of the ComboBox
would be the ProductID. In the ComboBox's AfterUpdate event I'd change
the subform's RecordSource based on the selected item in the ComboBox.
E.g.:

Private Sub cboProducts_AfterUpdate()

Const SQL = "SELECT Partition(Month(SalesDate),1,12,6) " & _
"As Months, Count(*) As Sold " & _
"FROM Products WHERE ID = "

If Not IsNull(Me!cboProducts) Then
Me!subformControlName.Form.RecordSource = SQL & Me!cboProducts
End If

End Sub

Change table/column/control names as necessary.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkMOmIechKqOuFEgEQIQGQCgpxcB/zz70hCmbk2wxgwnTPsxP5QAn0P8
c5GXAhBOx1UxwSMosZu2ojpE
=bQEx
-----END PGP SIGNATURE-----