Look-up Field Query to only show AddressIDs with matching CompanyIDs [SEE SCREENSHOTS]

[Waddell]

Hi,



I am designing a job management system for my company, logistical business, where we will be able to; store all customer & company details; look-up company contact information; input job information.

I have been able to get the company details, addresses and contact to work fine, however I have encountered an issue within the 'Transport Booking' part of the database which I need help with!

There are 5 main tables in the DB as follows: Companies, Company Addresses, Company Contacts, Shipment Tracker and Transport Tracker [See Relationships Screenshot].

Within the 'Companies' table, I have put 3 Yes/No fields, 'Customer', 'Pick-up Point', 'Delivery Point' and have ticked where appropriate to tell the DB whether the company is a customer, pick-up/delivery point, or a mixture of all three.

I have used this to create a query, which I have used in the 'Shipment Tracker' and 'Transport Tracker' to create a Look-up field for CustomerID (Shipment Tracker) and Pick-up and Delivery CompanyID fields (Transport Tracker) which have worked quite well [See Shipment Tracker & Transport Tracker Screenshots]

The issue I am now having is in the Transport tracker, namely the PickupAddressID & DeliveryAddressID [the two fields after the Look-up fields I have mentioned earlier]. I only want them to show the AddressIDs of the company selected in the 'PickupCompanyID' & 'DeliveryCompanyID' which are both foreign keys of the CompanyID on the 'Companies' table. Also, the CompanyID on the 'Company Addresses' is a foreign key of the CompanyID primary key.

This is an issue due to the fact that some of the companies have more than one address, dnata having as many as eight, so for example, when we select dnata as the PickupCompanyID, we only want the eight dnata addresses to be shown in a Look-up field to be selected.

Any help in the right direction on this would be great!

[June7]

This is called cascading (or dependent) combobox (or listbox). A very common topic.

[ssanfu]

Trying to follow what you are trying to do... and I see two problems (IMHO):

1) You have spaces in object names: fields, tables, queries and forms.

Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use Look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.

2) It sounds like you have LOOK UP FIELDS (in tables). Not a good idea. Most experiences Access programmers do not use LOOK UP FIELDS.
Note: Look up tables are different (OK).


You might want to see:
The Ten Commandments of Access
The Evils of Lookup Fields in Tables