query from multiple tables

G

Guest

Hi all,

I got a table "tCorrespondence" where i have lots of combo-fields linked to
sub-tables.
Somebody told me to store the reference number (id from sub-table) but not
actual text in the fields of main table. Which i did, and all was fine with
Forms and Reports where i also used combo-boxes, - untill i came to a need to
have all values to be represented in 1 query as text fields (for output to a
Word template using mail-merge).

I successfully got data from main table and 1 sub-table, but when i try to
add more sub-tables, i get unexpected results.

I was making the query in the "Design view" and when i switched to SQL i saw
that
the function used is "INNER JOIN". May be it is wrong?

Please help!!!!

SELECT tCorrespondence.ID, tCorrespondence.IssueDate,
tCorrespondence.RecDelDate, tCorrespondence.Type, tCorrespondence.RefNo,
tCorrespondence.MeanOfDelivery, tCorrespondence.FromToClient,
tCorrespondence.Project, tCorrespondence.Subject, tCorrespondence.FiledAt,
tCorrespondence.PriceVND, tCorrespondence.DocType, tCorrespondence.FollowUp,
tContacts.CompID, tContacts.CompanyFullName, tContacts.BusinessStreet,
tContacts.BusinessCity, tContacts.BusinessCountry, tCorrespondence.ToPerson,
tCorrespondence.DHLNo, tCorrespondence.Comments,
tCorrespondence.PersonSigned, tCorrespondence.CommentsW
FROM tContacts INNER JOIN tCorrespondence ON tContacts.CompID =
tCorrespondence.FromToClient
WHERE (((tCorrespondence.Type)=2));

The above code works all right, but i need to introduce data from
"tContactPersons" and "tProjects" to replace there "IDs" stored in the main
table. How can i do that?

Thank you.
Lana
 
J

Jeff Boyce

Lana

From your description, it sounds like your tCorrespondence has "lookup" data
fields ("lots of combo-fields linked to sub-tables"). You may be
encountering one of the problems folks run into by using the lookup data
type, instead of using a numeric or text data type, holding the key value
(i.e., the foreign key) from the other tables. I believe you called this
the "reference number" or the "id from sub-table".

Have you opened a query in design mode and tried adding the additional
tables, then modifying the join between them to take all of the "main" table
records and "any" of the sub-table records?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
C

Cinzia

Lana said:
Hi all,

I got a table "tCorrespondence" where i have lots of combo-fields linked to
sub-tables.
Somebody told me to store the reference number (id from sub-table) but not
actual text in the fields of main table. Which i did, and all was fine with
Forms and Reports where i also used combo-boxes, - untill i came to a need to
have all values to be represented in 1 query as text fields (for output to a
Word template using mail-merge).

I successfully got data from main table and 1 sub-table, but when i try to
add more sub-tables, i get unexpected results.

I was making the query in the "Design view" and when i switched to SQL i saw
that
the function used is "INNER JOIN". May be it is wrong?

Please help!!!!

SELECT tCorrespondence.ID, tCorrespondence.IssueDate,
tCorrespondence.RecDelDate, tCorrespondence.Type, tCorrespondence.RefNo,
tCorrespondence.MeanOfDelivery, tCorrespondence.FromToClient,
tCorrespondence.Project, tCorrespondence.Subject, tCorrespondence.FiledAt,
tCorrespondence.PriceVND, tCorrespondence.DocType, tCorrespondence.FollowUp,
tContacts.CompID, tContacts.CompanyFullName, tContacts.BusinessStreet,
tContacts.BusinessCity, tContacts.BusinessCountry, tCorrespondence.ToPerson,
tCorrespondence.DHLNo, tCorrespondence.Comments,
tCorrespondence.PersonSigned, tCorrespondence.CommentsW
FROM tContacts INNER JOIN tCorrespondence ON tContacts.CompID =
tCorrespondence.FromToClient
WHERE (((tCorrespondence.Type)=2));

The above code works all right, but i need to introduce data from
"tContactPersons" and "tProjects" to replace there "IDs" stored in the main
table. How can i do that?

Thank you.
Lana
Click to expand...

Hi Lana,
probably you have to change the INNER JOIN with LEFT JOIN.
To Do this in Design View :
click with left mouse button on each line joining the Table with the
sub-table, Select Property from the menu and Choose the right option,
usually the second one: "Select All rows from Table and only the rows from
subTable ....where...."

Bye
 
G

Guest

Thank you guys,

I have revised all links between my tables and now it works all right.

Somehow Access assigned its own links when I add tables in the query design
- absolutely different from what i have assigned in "Relationships" page.
Now i will double check this when making queries.

Thanks again!
Lana
 
Top