Combo related query

[nsiotto]

I have a form with two combos. Both source from a single table (the same one). When the first on the top is selected it enables the ones below. The issue is that I would like to link the two. In other words the first combo is Projects and the second one is Supplier. Right now when I select the project all the suppliers show in the combo below while I'd like to show only the suppliers related to that project.

Any help?

[PhilOfWalton]

Nslotto. Before going any further, please read any articles you can find on Database normalisation. In particular, you will find that a table should contain information about one subject and that subject should exist in it's own right.

So you should have a table of projects (and obviously they have no direct connection to the Supplier, and a table of Suppliers which is basically a "free standing" list of people.

Assuming the primary key for the Suppliers is SupplierID (AutoNumber), we link the two tables together by having the SupplierID as a foreign key in the Project table (assuming there is only one supplier per project. If more than one supplier can be involved in a single project, it gets a bit more complicated, but I'll deal with that if the situation arises)

See how you get on, and if you care to send an image of the relationship page, we can check that over, and then get back to the Combo Boxes.

Phil

[nsiotto]

Hi Phil, I couldn’t find anything similar to my question. Which of course doesn’t mean it’s not there but couldn’t find.

So let me answer what I believe to be the most important question you have made in response to mine. I have one table which has all the data and it is called “Main”. Combos source the data from one of the records and it is grouped. So both Project and Beneficiary are grouped and will appear as one entry but in reality on the Main table are many. That’s why shouldn’t be impossible to show on the Beneficiary combo only the records matching the Project. Did I manage to make my reply clear?

[PhilOfWalton]

As I said, you need either 2 or 3 tables.

I gather you are saying that one project can have several beneficiaries. In which case you need the 2 tables I have already mentioned
Does the reverse also apply that one Beneficiary can be involved with more than 1 project? In which case you need 3 tables.

Either way you will need TblProjects and TblBeneficiaries. The latter table will hold things like name, address, contact detail etc. The former table will probably have a ProjectRefNo and bank details.

Your first post mentions a supplier. Where does that fit in?

Try to avoid meaningless table names like "Main". Use something descriptive like "TblProjects" then you have an idea of the information it will hold.

Phil

[nsiotto]

Phil, this MS Access database exists since almost ten years. I am just trying to enhance some functions such as report in this case. Report works. The only issue is that I wanted when selecting a Project to show in the combo below only the Beneficiaries (which are the suppliers) that have been engaging on that particular project.

Now let's come to the table.

[nsiotto]

As you can see apart from Switchboards, user table there's only one working table and everything is present inside this table. So what has been done in the combos it is possible not only to select but also to edit and to insert a new information. Which means that this becomes available in that record which is grouped although there are so many records with different projects names and different beneficiary names. So the Projects and the Beneficiary are sourced from the Main table.

Here's the query for Project combo:

SELECT main.project FROM main GROUP BY main.project ORDER BY main.project; 

And here the one for Beneficiary combo:

SELECT main.beneficiary FROM main GROUP BY main.beneficiary ORDER BY main.beneficiary; 

In Main table for each record there is one Project and one Beneficiary. It would be nice that when the Project name is selected in the combo, automatically the query in the Beneficiary combo shows only the ones that are present in the Project.

[PhilOfWalton]

I'm sorry, I can give you no further help. Again, if you look at table normalisation, you will see that if the same piece if Alpha data (ProjectName), the database is not normalised, and you will run into trouble. The way you have set out your table the ProjectName will appear once for each beneficiary, so what happens when the Project name is unintentionally mistyped. "ProjectABC" is not the same as "Project ABC", so your Combo box may show "Fred" as the beneficiary in "ProjectABC" and "John" in "Project ABC". I know what you are trying to do id type in "ProjecxtABC" and see both Fred & John's names. It won't work.

You need a main form for the Project, and a linked continuous Subform for all the Beneficiaries.

I can't find anything very close to what you need, but here is an image of the closest thing I have got.

So wherever you see the word "Interest", use Project, and where you see the "Member", think Beneficiary.

I am sorry not to help further, but if the underlying structure of your database is wrong, it is not my policy to encourage you to proceed further, as we may solve this particular problem, and you will be back in a few weeks with another problem caused by a non-normalised database.

Phil