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
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_
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
Code:
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_