Query from 2 tables

[Mavis]

Hi,

I am using access 2007.
I have 2 tables Customer and Supplier. In Both table there are 3 comment
fields (First name, Last name and Phone Number). The three information will
first fill in Customer table Form first. I will like the phone number to auto
appear in the Supplier table form when Supplier.firstname =
Customer.firstname and Supplier.lastname = Customer.lastname.

I tried the below SQL code but the result is wrong.
SELECT [TBLRecoveryP&I].[P&I]
FROM TBLCargoClaims INNER JOIN [TBLRecoveryP&I] ON
(TBLCargoClaims.Voyage=[TBLRecoveryP&I].Voyage) AND
(TBLCargoClaims.Vessel=[TBLRecoveryP&I].Vessel);

Please kindly advice.
Thanks!

 

[KARL DEWEY]

Why is that the names of your 2 table (Customer and Supplier) do not match
those in the query?

 

[Mavis]

Hi,

I need to fill in all information in the table TBLRecoveryP&I, then only
enter the voyage and vessel information in TBLCargoClaims and the P&I Ref
will auto show. But when i do that coding, all the P&I Ref number which match
all my records in TBLCargoClaims will be listed instead of the P&I Ref for
each indivdual record.

Why is that the names of your 2 table (Customer and Supplier) do not match
those in the query?

Hi,

I am using access 2007.
I have 2 tables Customer and Supplier. In Both table there are 3 comment
fields (First name, Last name and Phone Number). The three information will
first fill in Customer table Form first. I will like the phone number to auto
appear in the Supplier table form when Supplier.firstname =
Customer.firstname and Supplier.lastname = Customer.lastname.

I tried the below SQL code but the result is wrong.
SELECT [TBLRecoveryP&I].[P&I]
FROM TBLCargoClaims INNER JOIN [TBLRecoveryP&I] ON
(TBLCargoClaims.Voyage=[TBLRecoveryP&I].Voyage) AND
(TBLCargoClaims.Vessel=[TBLRecoveryP&I].Vessel);

Please kindly advice.
Thanks!

 

[KARL DEWEY]

I need to fill in all information in the table TBLRecoveryP&I, then only
enter the voyage and vessel information in TBLCargoClaims

What has this got to do with tables Customer and Supplier?

Post the table structure of all tables involved in your efforts showing
names of fields and datatypes. Post sample data from them.

Hi,

I need to fill in all information in the table TBLRecoveryP&I, then only
enter the voyage and vessel information in TBLCargoClaims and the P&I Ref
will auto show. But when i do that coding, all the P&I Ref number which match
all my records in TBLCargoClaims will be listed instead of the P&I Ref for
each indivdual record.

Why is that the names of your 2 table (Customer and Supplier) do not match
those in the query?

Hi,

I am using access 2007.
I have 2 tables Customer and Supplier. In Both table there are 3 comment
fields (First name, Last name and Phone Number). The three information will
first fill in Customer table Form first. I will like the phone number to auto
appear in the Supplier table form when Supplier.firstname =
Customer.firstname and Supplier.lastname = Customer.lastname.

I tried the below SQL code but the result is wrong.
SELECT [TBLRecoveryP&I].[P&I]
FROM TBLCargoClaims INNER JOIN [TBLRecoveryP&I] ON
(TBLCargoClaims.Voyage=[TBLRecoveryP&I].Voyage) AND
(TBLCargoClaims.Vessel=[TBLRecoveryP&I].Vessel);

Please kindly advice.
Thanks!

 

[Mavis]

Hi,

Customer and supplier got nothing to do with my question now.
My tables as shown below:

TBLCargoClaims:
ClaimNumber Vessel Voyage P&I Ref
C123 Eve 12
C234 Sun 11
C345 Star 13

TBLRecoveryP&I:
ID Voyage Vessel P&I Ref
2 11 Eve PI123
3 12 Sun PI234
4 13 Star PI345

I had created form for each table. User will first enter all the information
in the FormRecoveryP&I then they will go in to the FormCargoClaims.
Voyage+Vessel will have more than one P&I Ref. I want a drop down list for
the P&I Ref in TBLRecoveryP&I to show when match in the TBLRecoveryP&I to
show when "TBLCargoClaims.Voyage=TBLRecoveryP&I.Voyage AND
TBLCargoClaims.Vessel= TBLRecoveryP&I.Vessel"

My code is shown below:

SELECT [TBLRecoveryP&I].[P&I]
FROM TBLCargoClaims INNER JOIN [TBLRecoveryP&I] ON
(TBLCargoClaims.Voyage=[TBLRecoveryP&I].Voyage) AND
(TBLCargoClaims.Vessel=[TBLRecoveryP&I].Vessel);

I have all the P&I Ref in my FormCargoClaims's P&I Ref field with the above
code.

Thanks

enter the voyage and vessel information in TBLCargoClaims

What has this got to do with tables Customer and Supplier?

Post the table structure of all tables involved in your efforts showing
names of fields and datatypes. Post sample data from them.

Hi,

I need to fill in all information in the table TBLRecoveryP&I, then only
enter the voyage and vessel information in TBLCargoClaims and the P&I Ref
will auto show. But when i do that coding, all the P&I Ref number which match
all my records in TBLCargoClaims will be listed instead of the P&I Ref for
each indivdual record.

Why is that the names of your 2 table (Customer and Supplier) do not match
those in the query?

:

Hi,

I am using access 2007.
I have 2 tables Customer and Supplier. In Both table there are 3 comment
fields (First name, Last name and Phone Number). The three information will
first fill in Customer table Form first. I will like the phone number to auto
appear in the Supplier table form when Supplier.firstname =
Customer.firstname and Supplier.lastname = Customer.lastname.

I tried the below SQL code but the result is wrong.
SELECT [TBLRecoveryP&I].[P&I]
FROM TBLCargoClaims INNER JOIN [TBLRecoveryP&I] ON
(TBLCargoClaims.Voyage=[TBLRecoveryP&I].Voyage) AND
(TBLCargoClaims.Vessel=[TBLRecoveryP&I].Vessel);

Please kindly advice.
Thanks!