Help with subform query build

[JohnB]

Hi. I wonder if anyone can help with this. Im afraid it
will take a bit of explaining.

I have tblPlacements with, among others, a LandladyID
number field. The idea is that a combo on a form is used
to select the Landlady, from tblLandladies, allocated to
each placement and put their Key Index LandladyID in that
field. This worked fine but then the users decided that
they wanted occasionably to be able to allocate a second
Landlady to each placement, so I created a
SecondLandladyID field in tblPlacements and created a
second combo to allow them to do this, again drawing on
tblLandladies. This also works fine but now I want to be
able to show, in a Landladies form, a subform showing the
placement history, i.e. all the related placement records
for each Landlady. The trouble is, at the moment the only
way I can do this is to create two history subforms, one
showing the placement records where the LandladyID was
entered and the other where SecondLandladyID was entered.
I cant manage to create a query which produces a
recordset, to feed to just one subform, which simply lists
all placement records where a Landlady was allocated. What
I want this query to do is to find all the placement
records and show the Landlady identifier as LandladyID,
irrespective of whether the placement record shows it as
LandladyID or SecondLandladyID or both, so that I can then
link subform to main form by LandladyID. Is there a way of
doing this? I hope Ive been clear enough. Thanks JohnB

 

[pmkdatabase_at_yahoo_period_ca]

And what happens when they want to add a 3rd landlady? This is the
problem with your approach. A better way than adding a second landlady
field (which is an nice way of saying the right way <G>) is to make a
linking table that holds the tblPlacement primary key along with any
Landlady keys like:

fldPlaceIDS fldLLIDS
1 1
2 3
2 1

I think you will find creating the subforms with this structure will
be quite straightforward.

Peter

 

[JohnB]

Hi. Yes, I understand. Ive been fooled into just modifying
an earlier way of working, rather than rethinking and
doing it properly. Looks like I need to redesign this part
of the db. Thanks for the feedback. Cheers, JohnB