Drop down list with matching values only (1 Viewer)

champion83

New member
Local time
Today, 06:05
Joined
Jan 20, 2020
Messages
13
Hi all,

I need support from access experts as I am trying to build Access Database and at the beginning I have a problem with building the table correctly.

I have to tables: tblDepartment and tblDataEntry with columns as below:

tblDepartment

ID Site Dept
1 London Loading
2 London Tipping
3 Manchester Goods In
4 Manchester Goods Out
5 Liverpool Recycling
6 Liverpool PI

tblDataEntry
ID Site Project ref Dept
1 Manchester MA-01
2 Liverpool LI-02
3 London LO-03
4 London LO-04
5 Liverpool LI-05
6 Manchester MA-06

The problem is in the tblDataEntry table in the Dept column I would like it to insert a drop down list which should be connected with Dept column from the tblDepartment table.

On drop down list I would like to see only the departments associated with a given site for example:

If someone completing the table chooses Manchester as the site, if they click on the Dept column in the same record, they will only show a list with two departments: Goods In and Goods Out


ID Site Project ref Dept
1 Manchester MA-01 Goods In or Goods Out

I tried a lot of solutions but I think I am doing something wrong :(.

I am asking you for help.
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:05
Joined
Sep 21, 2011
Messages
14,048
Search this site or Google for 'Cascading Combo'

That is what you are wanting to create.
 

champion83

New member
Local time
Today, 06:05
Joined
Jan 20, 2020
Messages
13
Search this site or Google for 'Cascading Combo'

That is what you are wanting to create.

Thank you for the very quick reply. I will try to solve this problem again tomorrow morning or tonight. At the moment my head needs some rest because all these problems are arising from nowhere.

As soon as I will test it, I'll let you know if it worked for me.

Thank you again and best regards.

Lucas
 

champion83

New member
Local time
Today, 06:05
Joined
Jan 20, 2020
Messages
13
I managed to create Cascading Combo boxes in a new database but I can't run it in my current database :(


All this works but only partially. I get a list of departments for a particular site, but the same values appear many times (about 16 times :eek:).

Does anyone know what I'm doing wrong? :banghead:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:05
Joined
May 7, 2009
Messages
19,169
you can use Total query or a simple "select distinct" query will eliminate any dups from the query.
 

champion83

New member
Local time
Today, 06:05
Joined
Jan 20, 2020
Messages
13
A few days ago I was looking for the SQL query with distinct to remove duplicates. I don't know why I didn't manage to do it correctly.
Today I have created the query using "Query Builder" I just replaced "select" with "select distinct" and it works. :)

I still have to learn a lot.

Thank you all very much for help.
 

Users who are viewing this thread

Top Bottom