Connect two tables based on exact match (1 Viewer)

[jmsm]

Hello,

I have the following problem:

2 tables that the input are from forms:

Table A with the following Fields : Transport Number And Pallet_ID (Generated Automatically). The user only have to place the transport number and form will generate a pallet ID (Form 1);

Table B: User have to confirm that those Pallet_ID were for the same transport number. So, the user have other form (Form 2) that will have to create a new registry with a transport number and the Pallet IDs that have in his area (different area from form 1);

I made a subform inside Form 2 for user knows the pallet IDs that were predicted for that transport number (Information from Form 1) and display the pallets IDs that he confirms that have in his area;

I have a query with connection between transport numbers from 2 tables and the atual behaviour is:

Example (Atual behaviour):

Transport number || Pallet ID predicted || Pallet ID confirmed

1234 001 001
1234 002 001
1234 003 001
1234 001 002
1234 002 002
1234 003 002
1234 001 003
1234 002 003
1234 003 003

But I want this behaviour:


Transport number || Pallet ID predicted || Pallet ID confirmed

1234 001 001
1234 002 002
1234 003 003


The objective is to know if there is any pallet missing. Any ideas? It is a problem of Union tables most probably but i cant figure out the solution.

Thank you!

 

[mike60smart]

Hello,

I have the following problem:

2 tables that the input are from forms:

Table A with the following Fields : Transport Number And Pallet_ID (Generated Automatically). The user only have to place the transport number and form will generate a pallet ID (Form 1);

Table B: User have to confirm that those Pallet_ID were for the same transport number. So, the user have other form (Form 2) that will have to create a new registry with a transport number and the Pallet IDs that have in his area (different area from form 1);

I made a subform inside Form 2 for user knows the pallet IDs that were predicted for that transport number (Information from Form 1) and display the pallets IDs that he confirms that have in his area;

I have a query with connection between transport numbers from 2 tables and the atual behaviour is:

Example (Atual behaviour):

Transport number || Pallet ID predicted || Pallet ID confirmed

1234 001 001
1234 002 001
1234 003 001
1234 001 002
1234 002 002
1234 003 002
1234 001 003
1234 002 003
1234 003 003

But I want this behaviour:


Transport number || Pallet ID predicted || Pallet ID confirmed

1234 001 001
1234 002 002
1234 003 003


The objective is to know if there is any pallet missing. Any ideas? It is a problem of Union tables most probably but i cant figure out the solution.

Thank you!

Hi
Can you upload a zipped copy of the database?

 

[theDBguy]

Try using the Find Unmatched Query Wizard.

 

[plog]

You lost me. The best way to communicate query expectations is with 2 sets of related data--starting data and expected results. You gave us the expected results, but you didn't provide what's in the database that you are using to achieve those results. So give us that.

Get rid of your actual behavior data and replace it with the data from your tables that will generate your expected results. That will make things clearer. Be sure to include table and field names.