Ranking Query (1 Viewer)

[Martin_]

Hi Guys,

Wondering if someone will be able to point me in the right directions with a problem with a query I am having.

Currently have a access database which will have details of Trade of different products between countries. The Data comes from several different sources which are held in the same table and I am trying to allow users of the database to create their own ranking system to allow different views of the data from Different Sources.

E.G. There are 4 DataSources (A, B, C, and D)

One user trusts Source D over the rest of the sources but there could be missing data that is covered by sources A, B and C. User ranks Data source as the follow

1.D
2.A
3.B
4.C

So I created a VBA procdure to First of all Copy all Data from Source D to a Temp Blank Table. Easy enough

What happens next is I have set up a For Loop that goes through the saved rankings (See above) and inserts any data that is not already in the temp data (i.e. Any Data that Source D did not have.)

Here is the Code that creates the SQL Statement

For Y = 2 To 10
    strSQL2 = ""
        If Rankings(Y) <> "0" Then
            strSQL2 = "INSERT INTO tbl_Temp_All_Data ( From_Country_ID, To_Country_ID, [Month], [Year], "
            strSQL2 = strSQL2 & "Primary_Product_ID, Secondary_Product_ID, Info_Source_ID, [Value] ) "
            strSQL2 = strSQL2 & "SELECT tbl_All_Trade_DATA.From_Country_ID, tbl_All_Trade_DATA.To_Country_ID, "
            strSQL2 = strSQL2 & "tbl_All_Trade_DATA.Month, tbl_All_Trade_DATA.Year, tbl_All_Trade_DATA.Primary_Product_ID, "
            strSQL2 = strSQL2 & "tbl_All_Trade_DATA.Secondary_Product_ID, tbl_All_Trade_DATA.Info_Source_ID, tbl_All_Trade_DATA.Value "
            strSQL2 = strSQL2 & "FROM tbl_All_Trade_Data "
            strSQL2 = strSQL2 & "WHERE NOT EXISTS (SELECT tbl_Temp_All_Data.From_COuntry_ID, tbl_Temp_All_Data.To_Country_ID, "
            strSQL2 = strSQL2 & "tbl_Temp_All_Data.Month, tbl_Temp_All_Data.Year, tbl_Temp_All_Data.Primary_Product_ID, "
            strSQL2 = strSQL2 & "tbl_Temp_All_Data.Secondary_Product_ID "
            strSQL2 = strSQL2 & "FROM tbl_Temp_All_Data "
            strSQL2 = strSQL2 & "WHERE tbl_Temp_All_Data.From_Country_ID = tbl_All_Trade_DATA.From_Country_ID AND "
            strSQL2 = strSQL2 & "tbl_Temp_All_Data.To_Country_ID = tbl_All_Trade_DATA.To_Country_ID AND "
            strSQL2 = strSQL2 & "tbl_Temp_All_Data.Month = tbl_All_Trade_DATA.Month AND "
            strSQL2 = strSQL2 & "tbl_Temp_All_Data.Year = tbl_All_Trade_DATA.Year AND "
            strSQL2 = strSQL2 & "tbl_Temp_All_Data.Primary_Product_ID = tbl_All_Trade_DATA.Primary_Product_ID AND "
            strSQL2 = strSQL2 & "tbl_Temp_All_Data.Secondary_Product_ID = tbl_All_Trade_DATA.Secondary_Product_ID) AND "
            strSQL2 = strSQL2 & "tbl_All_Trade_Data.Info_Source_ID = " & Rankings(Y) & ";"
            Debug.Print Y & " Start - " & Now() & Chr(13)
            DoCmd.RunSQL (strSQL2)
            Debug.Print Y & " End - " & Now() & Chr(13)
        Else
            Exit For
        End If
    Next Y

This worked fine when using 2 Data Sources now with 4 it has over 4,000,000 records and is taking forever to run the query. Problem is more data sources will be added making even more data.

Looking Forward to your reply, Please note I have only been dealing with Access and SQL for the 3 - 4 months so there could be an easier way to rank these Data sources.

Cheers

Martin_

 

[vbaInet]

This worked fine when using 2 Data Sources now with 4 it has over 4,000,000 records and is taking forever to run the query. Problem is more data sources will be added making even more data.

I wonder if it's time to upscale to a more powerful database. What's the source?

E.G. There are 4 DataSources (A, B, C, and D)

User ranks Data source as the follow

1.D
2.A
3.B
4.C

It's not really a ranking, it's more a sort.

 

[Martin_]

I'm thinking that future versions should be developed on something else but running out time to delivery this version.

First two were IEA exports and imports. Which only cover a select set of countries but now trying to add GTIS import and export data which covers more countries.

Yeah it more of a sort but trying to get rid of data that is covered by preferred sources that's why I called ranking.

 

[BigHappyDaddy]

Instead of getting rid of the data, can you include another field in the TEMP data that identifies the Data Source? Then you could use the order in your "ranking" to determine which of the data sources to use for like records.

 

[vbaInet]

... and I think this data is going to keep growing and you'll soon start struggling to manage it. BigHappyDaddy is right on the money with the extra field, make it a Number field, 1 to 4. Plus an extra table to describe what 1 to 4 represent.

 

[Martin_]

Hi guys sorry for the delay in replying.

Have already got a field with the source as an integer with the name held in another table.

Could you point me to some examples on how I could use this field to create a query to show the data required eg data source d, then any missing data from this dataset that contained in A. Etc.

Hope I am making sense.

Thanks for the help so far

Cheers

Martin

 

[vbaInet]

Is there an ID that you're looking against for comparison?

 

[Martin_]

I am using 6 fields to compare the data. These are as follows: From_country_id, To_country_Id, primary_product_id, secondary_product_id, month and year.

Should I add an extra field to join these 6 fields into one field.

Have been thinking about restricting the query to the selection the user requires to reduce down the data the query will handle.

Fingers crossed that this help.

Thanks again for your help

Cheers

Martin

 

[vbaInet]

Not necessary. You should look at creating indexes for some of those fields too.

Just join the 6 fields from one table to another and change each join to an outter join, that will give you the records that don't exist in one but exist in the other. To filter out those, use Is Null as the criteria.

But then again, I must remind you that you can get more effecient db's for free. SQL Server Express for example.

 

[Martin_]

Thanks vbalnet.

Will give the outer joins a try. Always though joins were to join horizontal (hope this makes sense) with tables together that only have 1 or 2 common fields. But I am new to this game so don't be surprised I didn't know this was also to join them vertically.

Thanks again and will let you know how I get on.

Cheers

Martin

 

[vbaInet]

You can join on as many fields as you wish. The joins will be interpreted as a AND condition.