Urgent Help needed ...

[Me]

Thanks in advance ...
I have a query joining 3 tables say a,b & c
every record in a maps to one or more in b and one or more in c

I have written a query to fetch me all the records in three tables and
the result looks like (I am showing just the key values here)

Values in A Values in B Values in C

1 1 + A 1 + Ca
1 1 + B 1 + Ca
1 1 + A 1 + Cb
1 1 + B 1 + Cb
2 2 + X 2 + Cp
2 2 + x 2 + Cq
2 2 + x 2 + Cr

....

I would like to design a form that shows each record
in A and corresponding records in B and corresponding records in C

How can I achieve this? Should I use this query or there is another way to
do so. Please note that users will also be doing search on various fields
using
the form.

I already tried it as form/subforms, but in my case it doesn't work 'cause
when I use a filter on say subform b, then it does filter out records, but
it displays all the records, it should display only those that match the
criteria with form a and subform c.

Will appreciate if someone could bear with me and help me in resolving this
issue.


Thanks for your help in advance,
-Me

 

[Marshall Barton]

Thanks in advance ...
I have a query joining 3 tables say a,b & c
every record in a maps to one or more in b and one or more in c

I have written a query to fetch me all the records in three tables and
the result looks like (I am showing just the key values here)

Values in A Values in B Values in C

1 1 + A 1 + Ca
1 1 + B 1 + Ca
1 1 + A 1 + Cb
1 1 + B 1 + Cb
2 2 + X 2 + Cp
2 2 + x 2 + Cq
2 2 + x 2 + Cr

....

I would like to design a form that shows each record
in A and corresponding records in B and corresponding records in C

How can I achieve this? Should I use this query or there is another way to
do so. Please note that users will also be doing search on various fields
using the form.

I already tried it as form/subforms, but in my case it doesn't work 'cause
when I use a filter on say subform b, then it does filter out records, but
it displays all the records, it should display only those that match the
criteria with form a and subform c.

You really want to present an unnoralized view of the data
to users?? Very odd! It's more usual to do this in a
report with grouping to organize the records. Or are you
locked into a spreadsheet way of thinking?

To do that in a form, I think you would need to use a
continuous form based on a query that Joins all the tables
on their linking fields.

SELECT A.*, B.*, C.*
FROM (A INNER JOIN B ON A.pk = B.fk)
INNER JOIN C ON A.pk = C.fk

Be sure the form's AllowAdditions, AllowEdits and
AllowDeletions are all set to No so users don't attempt to
modify stuff that may mess over the combined records.