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

Jase AlexanderCompliance ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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-GilesSenior Application DeveloperCommented:
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.  :)
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.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Jase AlexanderCompliance ManagerAuthor Commented:
HI Paul

That's exactly what Im looking for.

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

Paul Cook-GilesSenior Application DeveloperCommented:
1.  On the Create tab, click Query Design.  The query design window will open.
2.  If the Show Table window is not open, click Show Table in the Query Setup section of the Design tab.
3.  In the Show Table window, double-click DPD Charge and Full Data.  The tables and their fields will appear in the upper portion of the query design window.
4.  Close the Show Table window.
5.  If a line (or lines) exists between the two tables,  click the line to select it;  the line will become thicker.  On the keyboard, press the Delete key  and the line will be deleted.  Repeat if necessary.
6.  Hover the cursor over the Country field in Full Data, and the field will be highlighted.  Drag the field into DPD Charge, and release it on Country.  A line will appear between the Country field in Full Data and the Country field in DPD Charge.  You have just created a relationship between those two tables in your new query.
7.  Double click the TableName of Full Data.  All the fields in that table will be selected.  Drag them into the query grid at the bottom of the query window.  
8.  Double click the DPD Cost field in DPD Charge.  It will be added to the query grid to the right of the Full Data fields.
9.  In the Design tab, click the View button to display the query results.  You should see all of the Full Data fields, and DPD Cost on the far right.  
10.  In the query window, click the X to close the query.  You'll be prompted to save the design;  enter a meaningful name for the new query ("FullDataAndCostQry", for instance) and click OK.  You've saved your query, and can now run it whenever you want.  :)
@spicecave, I actually posted the query you need in my original answer.  You just need to copy it to the SQL window and I explained to you why it won't work for you as the recordsource for a form.  For a report, it doesn't matter if the recordset is updateable but for a form, it does.

To open the query window in SQL View, start a new query, cancel out of the table choosing dialog and switch to SQL view.  Then just past in the query I posted.  You can switch back to QBE view if you prefer to see the query that way.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jase AlexanderCompliance ManagerAuthor Commented:
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

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-GilesSenior Application DeveloperCommented:
Thanks for the assisted solution tag.  :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.