Variable INNER JOIN

[lucy_maria]

Hi. I am not massively technically minded, I try and do as much as I can in queries, but having done some coding and scripting work in the past I can read SQL.

I have three tables:
1. Data is a list of generic country names, each country might appear multiple times.
2. Countries is a list of generic country names and the names used by different suppliers.
3. Prices is a list of the names used by each supplier and the cost to use their services for each country.

The data table changes regularly and I need it to stay generic so that each country is only counted once e.g. 10 Ireland NOT 9 Ireland and 1 Republic of Ireland.

Likewise the suppliers keep changing their names for countries and it is easier to keep everything updated if I can just import the new prices without having to go through searching and replacing for generic country names.

The query setup is that the generic country field in the Data table is counted and is linked to the generic country field in the Countries table. In the countries table one supplier's country field is linked to the country field in the prices table. This query does a few additional calculations and the results are limited by information I've entered onto a form e.g. standard or first class.

What I am trying to get is a list of prices from each supplier in a separate report, only for the countries in the Data table, but I want to do it with the one query and just change the information in the form to create each report.

Everything seems to work except that I need a variable inner join, that links to information in the form. I don't know how to do this in a query. The SQL line looks like this:

FROM (Data_plus_Counts INNER JOIN Countries ON Data_plus_Counts.Country = [Countries].Country) INNER JOIN [Prices] ON [Countries].Crystal = [Prices].Country

I've had a search online but I'm obviously not using the right terms as I'm struggling to come up with anything. I am trying to broaden my knowledge but I'm not having much luck researching this. Does anyone have any ideas?

 

[sxschech]

Are you saying that Crystal is something you choose on a form?

FROM (Data_plus_Counts INNER JOIN Countries ON Data_plus_Counts.Country = [Countries].Country) INNER JOIN [Prices] ON [Countries].Crystal = [Prices].Country

Could you give a couple other examples of what would be changed in a variable situation? Such as on the form user can pick Crystal, Crayon, Creek and sql would be:

FROM (Data_plus_Counts INNER JOIN Countries ON Data_plus_Counts.Country = [Countries].Country) INNER JOIN [Prices] ON [Countries].Crystal = [Prices].Country

FROM (Data_plus_Counts INNER JOIN Countries ON Data_plus_Counts.Country = [Countries].Country) INNER JOIN [Prices] ON [Countries].Crayon = [Prices].Country

FROM (Data_plus_Counts INNER JOIN Countries ON Data_plus_Counts.Country = [Countries].Country) INNER JOIN [Prices] ON [Countries].Creek = [Prices].Country


Is this what you are after, or is Crystal the actual "variable" name that would contain the text string?

 

[lucy_maria]

That's exactly what I'm after. I've been able to get everything I want to behave using content entered into the form, e.g. by using [Forms]![Travel Agent Form]![TXT_weight])/1000)), except the join.

 

[sxschech]

Is variable portion the field name only or both the table/query name and field name? Is your query in vba code or is it a regular query?

 

[lucy_maria]

The variable field is a field name in the countries table. This field name is entered into multiple records in the Prices table.

This is currently a normal query.

I'm not sure if it helps, but the full query currently looks like this and it's just the one instance of Crystal that I want to make variable according to the form:

SELECT Countries_plus_Counts.Country AS Country, Countries_plus_Counts.Qty AS Qty, ([Countries_plus_Counts].[Qty]*[Forms]![Holiday Form]![TXT_weight])/1000 AS [Total weight (Kg)], ([Item]*[Countries_plus_Counts].[Qty])+([Kg]*(([Countries_plus_Counts].[Qty]*[Forms]![Holiday Form]![TXT_weight])/1000)) AS [Total £], [Prices].Carrier, [Prices].Service, [Prices].Size
FROM (Countries_plus_Counts INNER JOIN _Countries ON Countries_plus_Counts.Country = [_Countries].Country) INNER JOIN [Prices] ON [_Countries].Crystal = [Prices].Country
GROUP BY Countries_plus_Counts.Country, Countries_plus_Counts.Qty, ([Countries_plus_Counts].[Qty]*[Forms]![Holiday Form]![TXT_weight])/1000, ([Item]*[Countries_plus_Counts].[Qty])+([Kg]*(([Countries_plus_Counts].[Qty]*[Forms]![Holiday Form]![TXT_weight])/1000)), [Prices].Carrier, [Prices].Service, [Prices].Size
HAVING ((([Prices].Carrier)=[Forms]![Holiday Form]![DRP_carrier]) AND (([Prices].Service)=[Forms]![Holiday Form]![DRP_service]) AND (([Prices].Size)=[Forms]![Holiday Form]![DRP_size]));

 

[sxschech]

Here is some code that may work, haven't tested it in this context, but have used it in changing where clauses. You will need two queries, the first will be a generic query, that will have the original field name that you choose. The code will copy that (overwrite/replace) to the name of the query that your report is expecting. Since the copy will show a popup asking if you want to do this, I have put in code to turn off this message, however, it is commented out for testing purposes. Once things are working fine, then you can uncomment "Setwarnings False".

Is the field name that you are changing coming from a text box or a combo box on the form? Please choose the appropriate part of the code. Remember to change the names of Me.txtFieldName to the actual text box name on your form, as well as the "Actual", "Generic" to the query names you will be using.

You can call this sub from the event that you are using to run your report.

Sub JoinField()
    'Change the query to use a different field name for
    'the inner join

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String

    Set db = CurrentDb

    Dim stReportTable As String
    Dim stFieldName As String

'For a combobox
'    stFieldName = Me.CboFieldName.Column(Number)
'For a textbox
'   stFieldName = Me.txtFieldName


   'Copy the generic query into the actual query name so that can always
   'locate the join field to replace

'   DoCmd.SetWarnings False  
    DoCmd.CopyObject , "qryActualQueryName", acQuery, "qryGenericQueryName"
   
   'change Join to use the field name from the combo box
        Set qd = db.QueryDefs("qryActualName")
        qd.SQL = Replace(qd.SQL, "ON [_Countries].Crystal", "ON [_Countries]." & stFieldName)

    DoCmd.SetWarnings True
End Sub