Two combobox fields take information from same table

[capsula4]

Although I understood pretty well this, I'm encountering a problem with reports.

I have the following tables:

Table - TRANSACTIONS
[T_ID]
[ID_PERSON1] (combobox from PERSONS)
[ID_PERSON2] (combobox from PERSONS)

Table - PERSONS
[ID_PERSON]
[PERSON_NAME]
[ID_COUNTRY] (combobox from COUNTRY)

Table - COUNTRY
[ID_COUNTRY]
[COUNTRY_NAME]

And I want a report like this:

+---------------------------------------------------+
| TRANSACTION NUMBER_________________________|
| PERSON 1's NAME _________PERSON 2's NAME_____|
| COUNTRY PERSON 1 _______COUNTRY PERSON 2___|
+--------------------------------------------------+
The problem I'm encountering is that both PERSON 1 and PERSON 2 are comboboxes that take information from the same table and I'm not sure how to make the query or report notice that I may want to show the country of PERSON 1 and PERSON 2.... Here i attach you a sample DB of this. The fact is that I haven't created any relationship, it's just a "combo-box relationship"

I tried also making relationships but couldn't really make it up, not sure if it has to be 1-many, 1-1...

 

[RuralGuy]

Here's a link you may want to read: The Evils of Lookup Fields in Tables

 

[capsula4]

Here's a link you may want to read: The Evils of Lookup Fields in Tables

Thanks Rural Guy but then which is the solution to this problem?? I thought of creating different queries for PERSON 1 and PERSON 2 and make the combobox lookup info from the queries instead of the tables, this seems to work good but I haven't finally tried it.

If any have a better idea, just tell me!

 

[RuralGuy]

If they were not lookup fields but simply textbox fields, what would you do?

 

[capsula4]

If they were not lookup fields but simply textbox fields, what would you do?

Thanks for trying to make me think but I can't really tell what I may do something using parameters or filtering somehow... not sure how

Consider also that I'm pretty newbie and I have just started learning access for 1 week

 

[RuralGuy]

It almost looks like you are doing labels but your MAIN table is not normalized and you will probably continue to have difficulty getting the results you want. I made some changes but your db is still not satisfactory for the results you have asked for.

 

[capsula4]

It almost looks like you are doing labels but your MAIN table is not normalized and you will probably continue to have difficulty getting the results you want. I made some changes but your db is still not satisfactory for the results you have asked for.

I thought of making something similar to that but still doesn't make what I was looking for (showing country and name of P1 and P2) I'm guessing I will go with queries for P1 and P2 and make the lookup combo from queries, seems like the only way to make it work.

Thanks for trying it!

 

[RuralGuy]

Good luck with the project.

 

[capsula4]

Good luck with the project.

Thank you!! I'm not sure if I did it in the best way but I can tell you I could make it work!

I had to make dif queries and make the combo lookup from queries instead of the table, and then when I want to make a report I had to select P1 and P2 table ID and query ID and then add a criteria where (query ID=table ID) for each one (P1 and P2)

 

[RuralGuy]

There is a lot to be said for "it works"! Lots of success with the rest of your project.