Linking One child field or another in subform to main form

[ningi]

Is it possible to have a subform where the Master field
on the main form is linked to the subform via 1 child field OR another? So
the table for the main form, can be matched to 2 different fields (one or the
other).

I want an item in the subform to show up under the master field of the main
form when child field A or child field B is equal to the master field on the
main form.

Please help...

 

[KARL DEWEY]

I think you can use a calculated field in a query but why not use a
normalized database structure?
One way is to concatenate the two fields --
Query4 --
SELECT [x] & " " & [y] AS Expr1
FROM [Change Requests]
ORDER BY [x] & " " & [y];

Then edit the join in SQLView (you will not be able to display in design
view).
SELECT [Change Requests].*
FROM Query4 INNER JOIN [Change Requests] ON Query4.Expr1 like "*"&[Change
Requests].z&"*";
The problem with this is that you will get an almost Cartesian effect.

If your fields maybe null or some other criteria in calculation then you can
use a standard join.

 

[ningi]

Hi and thanks for your answer.

However, I am quite a beginner in Access so I will need some more help and
explanation.

Field 1 is called "Location" and Field 2 in the subform "Reserved for". Both
of them are set to Look Up field, and they get their data from a table with
all locations.

The main form is then sorted per Location and Location is also the master
field. "Location" and "Reserved for" should be the two child fields in the
subform.

Could you please explain me some more what I should put in the quer`y?

I think you can use a calculated field in a query but why not use a
normalized database structure?
One way is to concatenate the two fields --
Query4 --
SELECT [x] & " " & [y] AS Expr1
FROM [Change Requests]
ORDER BY [x] & " " & [y];

Then edit the join in SQLView (you will not be able to display in design
view).
SELECT [Change Requests].*
FROM Query4 INNER JOIN [Change Requests] ON Query4.Expr1 like "*"&[Change
Requests].z&"*";
The problem with this is that you will get an almost Cartesian effect.

If your fields maybe null or some other criteria in calculation then you can
use a standard join.

Is it possible to have a subform where the Master field
on the main form is linked to the subform via 1 child field OR another? So
the table for the main form, can be matched to 2 different fields (one or the
other).

I want an item in the subform to show up under the master field of the main
form when child field A or child field B is equal to the master field on the
main form.

Please help...

 

[Pieter Wijnen]

Wouldn't it be better to use a check box to distinguish between location &
reserved for in this case?
ie location: A, (physically) Allocated: yes/no. I think this will be easier
to understand for the users & will not require copying of data from reserved
to location & will make things easier for you to boon!

Pieter

Hi and thanks for your answer.

However, I am quite a beginner in Access so I will need some more help and
explanation.

Field 1 is called "Location" and Field 2 in the subform "Reserved for".
Both
of them are set to Look Up field, and they get their data from a table
with
all locations.

The main form is then sorted per Location and Location is also the master
field. "Location" and "Reserved for" should be the two child fields in the
subform.

Could you please explain me some more what I should put in the quer`y?

I think you can use a calculated field in a query but why not use a
normalized database structure?
One way is to concatenate the two fields --
Query4 --
SELECT [x] & " " & [y] AS Expr1
FROM [Change Requests]
ORDER BY [x] & " " & [y];

Then edit the join in SQLView (you will not be able to display in design
view).
SELECT [Change Requests].*
FROM Query4 INNER JOIN [Change Requests] ON Query4.Expr1 like "*"&[Change
Requests].z&"*";
The problem with this is that you will get an almost Cartesian effect.

If your fields maybe null or some other criteria in calculation then you
can
use a standard join.

Is it possible to have a subform where the Master field
on the main form is linked to the subform via 1 child field OR another?
So
the table for the main form, can be matched to 2 different fields (one
or the
other).

I want an item in the subform to show up under the master field of the
main
form when child field A or child field B is equal to the master field
on the
main form.

Please help...

 

[ningi]

It would be easier yes. But sometimes it is at a location and should be
reserved for another location afterwards. That is why I need a seperate field
saying what location it is reserved for next.

Wouldn't it be better to use a check box to distinguish between location &
reserved for in this case?
ie location: A, (physically) Allocated: yes/no. I think this will be easier
to understand for the users & will not require copying of data from reserved
to location & will make things easier for you to boon!

Pieter

Hi and thanks for your answer.

However, I am quite a beginner in Access so I will need some more help and
explanation.

Field 1 is called "Location" and Field 2 in the subform "Reserved for".
Both
of them are set to Look Up field, and they get their data from a table
with
all locations.

The main form is then sorted per Location and Location is also the master
field. "Location" and "Reserved for" should be the two child fields in the
subform.

Could you please explain me some more what I should put in the quer`y?

I think you can use a calculated field in a query but why not use a
normalized database structure?
One way is to concatenate the two fields --
Query4 --
SELECT [x] & " " & [y] AS Expr1
FROM [Change Requests]
ORDER BY [x] & " " & [y];

Then edit the join in SQLView (you will not be able to display in design
view).
SELECT [Change Requests].*
FROM Query4 INNER JOIN [Change Requests] ON Query4.Expr1 like "*"&[Change
Requests].z&"*";
The problem with this is that you will get an almost Cartesian effect.

If your fields maybe null or some other criteria in calculation then you
can
use a standard join.

:

Is it possible to have a subform where the Master field
on the main form is linked to the subform via 1 child field OR another?
So
the table for the main form, can be matched to 2 different fields (one
or the
other).

I want an item in the subform to show up under the master field of the
main
form when child field A or child field B is equal to the master field
on the
main form.

Please help...