CODEWITHSUNDEEP
sqlms-accessms-access-2007
Bryan
Bryan

Reputation: 1893

Why is my query not returning what I expect?

The code below is what I am trying to use in order to get the count of one column and the averages of two other columns from multiple tables and then put the results into another table.
I thought this would work, but the count that is being put into the new table is incorrect as are the averages. A lot of times the averages are outside the range of the numbers that are being averaged. The numbers that are being averaged are all negative and most of them contain decimals. The data type for the columns is set to Number and the field size for the numbers being averaged (the source and the destination) is set to Double.

Code:

    For i = 1000 To 1783   
    strQuery1 = "Insert Into MIUsInGridAvgs (NumberofMIUs, ProjRSSI, RealRSSI)  " & _
                "Select  Count(MIUID), Avg(ProjRSSI), Avg(RealRSSI) " & _
                "From MIUsInGrid" & i & " "
                
    
    DoCmd.SetWarnings False  
    DoCmd.RunSQL strQuery1  
    DoCmd.SetWarnings True

    Next

The table names I am querying from all end with numbers between 1000 and 1783 inclusive.

Example Data:

MIUsInGrid1000

    MIUID       Latitude    Longitude    ProjRSSI   RealRSSI
    110108098   32.593021   -85.367073  -97.4625    -108

MIUsInGrid1001

    MIUID        Latitude    Longitude   ProjRSSI   RealRSSI
    110112556   32.592461   -85.337067  -101           -95
    110106208   32.592766   -85.337059  -101           -100
    110115010   32.59288    -85.337189  -101           -98

MIUsInGrid1002

    MIUID       Latitude    Longitude   ProjRSSI            RealRSSI
    110172260   32.593349   -85.366318  -104.408333333333   -99
    110106870   32.593464   -85.365822  -104.408333333333   -106

Results:

    NumberofMIUs    ProjRSSI           RealRSSI
    1             -97.4625             -108      'MIUsInGrid1000
    1             -100.883333333333    -109      'MIUsInGrid1001
    1             -109.521428571429    -99      'MIUsInGrid1002

What am I doing wrong?

Upvotes: 0

Views: 279

Answers (3)

Bryan
Bryan

Reputation: 1893

I found the answer but I don't really understand it. The column NumberofMIUs had the "Indexed" property set to "Yes (Duplicates OK)". When I changed the setting to "No" the query worked fine.

Upvotes: 0

n8wrl
n8wrl

Reputation: 19765

What are the data types involved? What are the types of the columns in the MIUsInGrid### as well as MIUsInGridAvgs?

Are you sure the tables are populated as you list them with no averages or other rows?

What happens if you run the queries one at a time by hand?

Upvotes: 1

Wouter van Nifterick
Wouter van Nifterick

Reputation: 24086

Don't you have to group by something in order to be able to use the AVG() group function?

What value does your i variable contain?

Upvotes: 1

Related Questions