Query where the query field is based on the record

[wass0035]

Good Morning,



I have attached a sample file to help with my question. In the sample I have two tables. One is a fixed table, "ConversionRates" (records won't be added but the rates will be periodically updated) that will contain a series of conversion rates for different components based on the city that they came from. The components are records in that table and their corresponding conversion rates are listed under fields corresponding to the city.

The second table "Entries" will contain records entered by other people. It contains the Component number that corresponds to a Component in the ConversionRates tables, and it will contain a Location which corresponds to a field in "ConversionRates". It also contains a cost they entered.

I would like to be able to have a query field that looks up the Conversion Rate based on the Location and the Component entered in Entries and output the rate. This is Expr1 field in my query. Then Expr2 field is simply the cost multiplied by the Conversion Rate.

Thank you

[Welshgasman]

You could look at using the IIF() function in your query as a workaround.
Would be better to structure your tables correctly then it would not be an issue.

[wass0035]

You could look at using the IIF() function in your query as a workaround.
Would be better to structure your tables correctly then it would not be an issue.

Thank you for your feedback.

What would be your suggestions as far as restructuring the tables? Performance on the database is a concern and if another structure would work better, please advise. We are basically trying to normalize or get a national average cost from the data which then gets fed into our excel files where the national average costs are reconverted back to a city cost that pertains to the city being worked in. Essentially, we are trying to enlarge our local dataset by normalizing data we have from other cities knowing typical ratios between cities. Initially we set it up with a common conversion percentage per city regardless of component however this method has worked well for some components but not all components and we would like to switch to having a ratio per city, per component. My actual dataset has about 65 cities, 300 components and currently I have around 9,000 cost records. I simplified things for the example file.

[Welshgasman]

The second table would have a field for town/City per record.
Tables tend to narrow and long, not wide and short.
If you add another city as you have it now, everything needs to change to allow for the new column, when in reality it should just be one or more records.

[wass0035]

Thanks,

I gave up on using built in Access functions and ended up writing a macro that runs on record save that will grab the value I need and plunk it into my table. My familiarity with visual basic is better than my familiarity with SQL statements.

[madpiet]

SELECT ConversionRates.ID, ConversionRates.Component, ConversionRates.Chicago AS Rate, "Chicago" as City
FROM ConversionRates
UNION ALL
SELECT ConversionRates.ID, ConversionRates.Component, ConversionRates.Miami AS Rate, "Miami" as City
FROM ConversionRates
UNION ALL
SELECT ConversionRates.ID, ConversionRates.Component, ConversionRates.Minneapolis AS Rate, "Minneapolis" as City
FROM ConversionRates

(I left ID in there because I wasn't sure if it meant something). But you really need to learn normalization in order to work with databases... ANY databases.

[wass0035]

SELECT ConversionRates.ID, ConversionRates.Component, ConversionRates.Chicago AS Rate, "Chicago" as City
FROM ConversionRates
UNION ALL
SELECT ConversionRates.ID, ConversionRates.Component, ConversionRates.Miami AS Rate, "Miami" as City
FROM ConversionRates
UNION ALL
SELECT ConversionRates.ID, ConversionRates.Component, ConversionRates.Minneapolis AS Rate, "Minneapolis" as City
FROM ConversionRates

(I left ID in there because I wasn't sure if it meant something). But you really need to learn normalization in order to work with databases... ANY databases.

Thanks, I see where you are going with this and how I could work that into what I am trying to do. I was misunderstanding you when you said wider than taller since the dataset is 50 cities and 300 components. In my wrong format flipping this would have given me an even wider table.

However, the format you just wrote is similar to how I have it setup in my working database where all my cities are in a single field and then my rates are in a separate field but that is with a common rate for all components. To expand and use varying rates per component, rather than adding additional fields we instead add new records with their corresponding component number, rate and city. I would assume you would save yourself the step of having to expand on the Select Union Statements in a query and just appropriately format your ConversionRates table from the get-go saving yourself ongoing edits when you add cities.

Makes sense and thank you.

[madpiet]

Thanks, I see where you are going with this and how I could work that into what I am trying to do. I was misunderstanding you when you said wider than taller since the dataset is 50 cities and 300 components. In my wrong format flipping this would have given me an even wider table.

However, the format you just wrote is similar to how I have it setup in my working database where all my cities are in a single field and then my rates are in a separate field but that is with a common rate for all components. To expand and use varying rates per component, rather than adding additional fields we instead add new records with their corresponding component number, rate and city. I would assume you would save yourself the step of having to expand on the Select Union Statements in a query and just appropriately format your ConversionRates table from the get-go saving yourself ongoing edits when you add cities.

Makes sense and thank you.

If the data isn't in Access already, I'd do all the UNION / Append stuff in PowerQuery... And then attach to the sheet with the cleaned / appended. (Why you can't call PowerQuery in Access is a bit of a mystery to me, but anyway...)