asked on

Access Lookup issue - creating a new column to look up from a separate table

Hey guys

Hope you can help

I have quite a simple Access database that details shipments made to various countries against which are dates of shipments, reference, number of cartons etc.

The complete list of data is held in the Full data table. In addition to this, I have two other tables called DPD charges and Inhouse charge. The DPD charge is what the courier has charged for the delivery whereas the Inhouse charge is what the business has charged the customer for the delivery.

The issue im having is in creating a lookup in the Full Data table in the form of an extra column next to the Weight column on the right, that looks at the country in DPD charge table and return the relevant courier charge for that particular line.

For example, on the first line, in the DPD Charge column, the first row would return 9.87 as this would be the charge for deliveries to Austria. This is evident in the attachment after my unsuccessful lookup trial.

I would be extremely grateful for some help on this, I’ve tried using the lookup wizard by creating a relationship however, I cannot get a result to auto-populate? Is this possible? or would this only be a manual entry only situation?

Your suggestions would be very welcome - ive attached the database to see if you can give me some direction

J
EE.accdb

PatHartman

I modified the query to bring in the two extra charges. However, this is not going to work correctly unless you modify DPD Charge and Inhouse Charge to make Country the primary key because because as it stands, Access will mark the query as not updateable since you are not joining to the lookup tables on their primary keys.

SELECT DISTINCTROW [Full Data].Country, Sum([Full Data].Cartons) AS [Sum Of Cartons], Avg([Full Data].Weight) AS [Avg Of Weight], [DPD Charge].[DPD Cost], [Inhouse Charge].[Customer Charge]
FROM ([Full Data] LEFT JOIN [DPD Charge] ON [Full Data].Country = [DPD Charge].Country) LEFT JOIN [Inhouse Charge] ON [Full Data].Country = [Inhouse Charge].Country
GROUP BY [Full Data].Country, [DPD Charge].[DPD Cost], [Inhouse Charge].[Customer Charge];

Most people advise using autonumbers as the primary key and I concur. To use the autonumbers as the primary key, you need to actually define a table of countries and then since both DPD and Inhouse would have 1-1 relationships with tblCountries, I would simply put the two charge columns in the same table.

Leaving separate tables as you have them, means you need to be very careful with spelling so you don't add the same country twice AND you must remove the two ID fields and make Country the PK in the two lookup tables.

And finally a bit of generic advice. Never use embedded spaces or special characters in your table or column names. They will force you to always enclose the names in square brackets and can lead to some strange situations when working with forms and reports.

Names should contain ONLY letters (upper or lower), numbers, and the underscore. My preference is to use CamelCase which capitalizes the first letter of each word but others prefer the_underscore which separates each word with an underscore.

Paul Cook-Giles

A lookup is going to offer you a list of values from which you can choose. Creating a lookup field in the FullData table is pretty simple; add a new field, click the dropdown in Data Type and choose Lookup wizard, and then follow the prompts to choose the DPD Charge table, the two data columns (Country and Charge), and don't hide the ID column. That'll give you a dropdown that displays Country and Charge, and you can choose the appropriate one. But I suspect that's not what you want; that you want a view of the rows in Full Data with the DPD charge displayed without having to choose it manually. Is that right?

If so, the quickest way to get that is to build a query that joins FullData and DPD Charge on Country, select all the fields from FullData, and the Charge field from DPD Charge. Then open the query instead of the table, and you'll see your results.

If that's not what you want, let me know. :)

PatHartman

Lookups at the table level cause serious problems in VBA and in queries. They are NOT RECOMMENDED. Combos on forms serve the purpose well. Users should NEVER be given a raw table or query to work with in any event so the only one would would ever use the lookup would be the developer and the developer should know better. The MVP site has a whole list of different problems caused by table level lookups.

Jase Alexander

ASKER

HI Paul

That's exactly what Im looking for.

Ive followed your suggestion, however, how to I join the tables on the Country option?

J

SOLUTION

Paul Cook-Giles

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jase Alexander

ASKER

Thank you so much - both solutions were amazing and I appreciate your time in looking at this for me

This taught me a conventional method as well as with SQL that im currently going through some tutoring with.

Cant thank you enough

You guys are the best

J

PatHartman

You're welcome. Don't forget that you have a serious design flaw and that should be corrected. You should not be joining data-field to data-field. Whenever you join two tables, one side or the other should be a primary key or unique index. So, Country should be the primary key in the country table (remove the autonumber. It is dangerous to have one that is not the PK) and the country table should have the two charge fields so you'll end up with one table instead of two. Make the default for the two charge amounts 0 rather than null so you won't have any arithmetic issues.

Paul Cook-Giles

Thanks for the assisted solution tag. :)