ok sorry, i'll try to explain myself a bit better. i have two problems: i'm new with access (don't have the knowledge, not even to explain my situation); plus English is not my mother tongue either. i'll give it a try anyways, sorry for the lack of synthesis.
I created a form with two subforms that contain two main tables with 380k and 180k records. Now I'd like to link these two subforms to a set of combo boxes to filter data and display the result in the form, in table view. I'd like to give you some background so that maybe it will be easier for you to help me out with this:
we are an automotive parts company and I work in the marketing department. What I would like to achieve for my market analysis is a database that can tell me what is the potential market of my Products.
I created two different subforms for 2 different countries. These big tables have no primary keys, but they do have indexes in those fields I use to filter data. The fields are:
Part Number (indexed); Discount Group (indexed); Product Group 1 (indexed); Product Group 2 (indexed); FAS-key (indexed); FAS-Population (not indexed); Vehicle type (not indexed).
Note that the FAS-Key is the code for the Vehicle type: each one of these codes is associated to one or more PN's.
So i want a form with 4 search fields to filter data in the subform:
- txtDG: to filter for Discount Group
- cboPG1 -> cboPG2: cascading combos to filter for PG1/PG2
- txtPN: to filter for Part Number
Now that I have the search box and combo boxes all set, i can finally filter my subform and display in the header of the form the following stats:
Total Population (it is the total population of the market;i have these data already, don't need to calculate them)
CS or PG1 or PG2 or PN Population: this is the sum of the field [FAS-Population], which is linked to the field [FAS-Key].
Covered Population: which is -> (CS population or PG1 population or PG2 population or PN population : Total Population) * 100
then i'll have an excel button to export everything to excel.
If access calculates the duplicated rows of the FAS-Key field, then I will get an overestimated covered population (in many cases above 100%), because for each Product Group or Discount Group I have several Part Numbers that are associated to the same FAS-Key, i.e. to the same Vehicle. This will lead to a computation of duplicated fas-population.
So, one question at a time: what is the most efficient way to build the search/combo boxes?
btw here's my code:
Code:
Option Compare Database
Option Explicit
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.txtcs) Then
strWhere = strWhere & "([CS] Like ""*" & Me.txtcs & "*"") AND "
End If
If Not IsNull(Me.cboPG2) Then
strWhere = strWhere & "([PG2description] = """ & Me.cboPG2 & """) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
Me.Filter = "(False)"
Me.FilterOn = True
End Sub
Private Sub cboPG1_AfterUpdate()
Me.cboPG2.Requery
Me.cboPG2 = Me.cboPG2.ItemData(0)
End Sub