Query from two tables that only returns some of data

[Santee]

I have a database that has one table with Vendor Information and the other with Account Number information. When I create a query it only returns half the information (either the Vendor information or the account number information but not both). All the headers from both table will show but one of the tables headers will have no data in the fields.

This is the data that is included in each table:

Vendor Info Table consists of:
Vendor Name
Vendor Phone Number
Vendor Account Number
Invoice Date
Input Date
Amount

Account Number Table consists of:
Vendor Name
Vendor Phone Number
Project
Task
Provider
Resource
Activity
GL #

The primary key for both is the Vendor Name and Vendor Phone Number (the Vendor Info has a 3rd primary key...invoice date). I have set the relationship up as Many records in Vendor Info to one record in Account number.

Please help. How do I get all the information on one query instead of blank fields?

Thanks!

 

[GingerR]

what field(s) are the two tables joined on?

 

[Santee]

Vendor Name and Phone Number. I have checked to make sure that both have the same exact setting on each table and they do. (ie: format, data type...etc)

 

[GingerR]

in your join properties do you have the join properties "Include All...."?? instead, make both join properties be "only include...where both are equal". i think what may be happening is that if you have two joins that are "Include All....." join property that it is including all the records regardless of if they are in the other table or not.

um....also, maybe not related to this problem...looks like your tables might not be set up correctly. Why is the VendorPhoneNumber listed in both tables? In VendorInfo table, what is the VendorAccountNumber for? is it referring to some number in the AccountNumber table? If you have duplicate info in each table as i suspect, I suggest you normalize your data, i.e. in AccountNumber table you add an AutoNum field or some number field that uniquely identifies the Account (maybe they have account numbers assigned by your company). Call it VendorAccountNumber. then THIS is what the relationship between the two tables would be. get rid of the VendorName and VendorPhone number in the VendorInfo table (actually this looks like you mean it is a transaction table, like they've sold you something? i think of VendorInfo being information about the vendor like address, phone, etc). anyhow, that is dup info. when you join the two tables by the VendorAccountNumber, the name and phone number comes with. otherwise you have to type that info in over and over.

so, maybe i'm just blowing hot air, but just trying to help.

good luck--g

 

[WHM]

Santee,
Please paste your SQL statement, and I think we can all help you better.

Hunter

 

[Santee]

No the account number in the Vendor Info is the account number on the bill. These are for telecommunication bills that are received monthly. The account number in the Account number is for company charge back (the account number each area gives me to report to A/P). The reason for the joins (all from Vendor Info to only the one in Account Number) is because there are 6 entries (so far this year) per phone number (900 phone numbers in the database per month - ie: Bellsouth 843-111-1111, Horry 843-111-1111)which give me a total so far this year of 5718 records in Vendor Info. In the Account number there is only 900 records. The reason I have Vendor Name and Phone number is both is because this is the unique only when both are combined. Otherwise there would be alot of Bellsouths, Horry, Verizon, etc. I was told I had to use them for the primary key since that is what everything revovles around.

Hope I haven't lost you! Thanks!!!!!!

 

[Santee]

Here is the SQL statement:

SELECT DISTINCTROW [2001Telco].VendorName, [2001Telco].PN, [2001Telco].InvDate, [2001Telco].InputDate, [2001Telco].Description, [2001Telco].VendorAccountNumber, [Account Numbers].Project, [Account Numbers].Task, [Account Numbers].Resource, [Account Numbers].Provider, [Account Numbers].Activity, [Account Numbers].GL, [2001Telco].Amount
FROM [Account Numbers] RIGHT JOIN 2001Telco ON ([Account Numbers].PN = [2001Telco].PN) AND ([Account Numbers].VendorName = [2001Telco].VendorName);

Thanks!

 

[Santee]

Can anyone help me with this?

 

[Santee]

Found the problem. Table was corrupted.