prevent a function to calculate duplicates in a field

[quandore]

Hi everybody


Basically i have a form that contains a couple of combo boxes that filter a subform.
in the header of the form i have a couple of text box that have expressions inside. one of this expression is:

=sum([field])

the query inside the subform has a field called "key" and another one called "population". the latter field is univoquely linked to the "key" field (i.e. it is the population of the key code)

now i want that, regardless of which combo box i'm using to filter the subform, access calculates the sum of the "population" field without counting duplicates in the "key" field. this way when the expression calculates the sum of the population, it will be a clean calculation, without duplicates.

so basically, i need the form to display the subform query in table view with duplicates BUT the calculation has to be made without duplicates of the "key" field.

any idea?

[orange]

You're telling us technically how you have set up things in terms of subforms, filters and combos, but we don't know your application. How about stepping back and describing to us what you are trying to accomplish --- just plain english in hopes that someone will understand, and offer some suggestions.

[quandore]

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:

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

[orange]

This

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.

suggest a structure issue -- not normalized structures.

There is a simple search form video tutorial at
http://www.datapigtechnologies.com/f...earchform.html