By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,324 Members | 1,766 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,324 IT Pros & Developers. It's quick & easy.

ACC97: Linking tables using two fields in each table

P: n/a
It seems like at one time I knew how to do this, but right now I'm
drawing a blank. I know there has to be an easier way to link tables
using more than one field.

Heres a simplfication of the problem:

I have two tables that are not normally linked to each other:
------------
tblClientStatusDetails with these fields:
...ClientStatusID = Autonumber (Primary Key)
...GrantNum = Long Integer (Indexed, Dups OK)
...ClientNum = Text (Indexed, Dups OK)
...StatData1 = Text
...StatData2 = Text
...(etc)
------------
tblProjects with these fields:
...ProjectID = Autonumber (Primary Key)
...GrantNum = Long Integer (Indexed, Dups OK)
...ClientNum = Text (Indexed, Dups OK)
...ProjectNum = Integer (Indexed, No Dups)
...ProjData1 = Text
...ProjData2 = Text
...(etc)
------------

THE PROBLEM:
I need to show the data fields from both tables together on the same
row on a form.

They need to be appear like this:
* The tables should be linked by the combination of GrantNum &
ClientNum
* ALL of the records from tblClientStatusDetails should appear
* Corresponding fields from tblProjects would appear only when there's
a match on GrantNum & ClientNum (the corresponding fields would be
blank if there's no match)
* MOST IMPORTANT: I need to be able to add records and edit data fields
from tblClientStatusDetails in the form/underlying query

===== WHAT I TRIED ===
First I made two queries to combine the GrantNum & ClientNum along with
the data fields from each table...

qryKeyJoinClientStatusDetails:
SELECT [GrantNum] & " " & [ClientNum] AS GrantAndClientNumKey,
tblClientStatusDetails.GrantNum, tblClientStatusDetails.ClientNum,
tblClientStatusDetails.StatData1, tblClientStatusDetails.StatData2
FROM tblClientStatusDetails
ORDER BY [GrantNum] & " " & [ClientNum];

qryKeyJoinProjects:
SELECT [GrantNum] & " " & [ClientNum] AS GrantAndClientNumKey,
tblProjects.ProjectNum, tblProjects.GrantNum, tblProjects.ClientNum,
tblProjects.ProjData1, tblProjects.ProjData2
FROM tblProjects
ORDER BY [GrantNum] & " " & [ClientNum];

Then I made a query to link the two above queries...

SELECT DISTINCTROW [qryKeyJoinClientStatusDetails].ClientNum,
[qryKeyJoinClientStatusDetails].GrantNum,
[qryKeyJoinClientStatusDetails].StatData2,
[qryKeyJoinProjects].ProjectNum, [qryKeyJoinProjects].ProjData1,
FROM [qryKeyJoinClientStatusDetails] LEFT JOIN [qryKeyJoinProjects] ON
[qryKeyJoinClientStatusDetails].GrantClientNumKey =
[qryKeyJoinProjects].GrantClientNumKey
ORDER BY [qryKeyJoinClientStatusDetails].ClientNum;

Everything appeared as I wanted, but the data fields were not editable.
I'm sure the solution would take 1/100th of the time I took trying to
explain it ;-)

Thanks,
--Don

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
It is probably because of the many to many join since you allow dups of
GrantNum and ClientNum. You will probably need to create temporary
tables with the concatenated field and run the linked query against
those two temporary tables.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.