Updating JOIN and UNION ALL query

[Lyn]

This one is difficult to explain, so I will cut it down to the basics.

I have a major table 'tblA' which has an autonum field 'ID-A' as primary key
(of no significance to users). 'tblA' contains many fields including
picture and memo fields. The main user-selectable field is 'NameA'.

There is also a crossreference table (let's call it 'tblB') which provides a
secondary method of accessing records in 'tblA'. The main fields in 'tblB'
are an autonum primary key 'ID-B', a field 'ID-A" corresponding to the
related record in 'tblA', and the alternative user-selectable field 'NameB'.

The main form is 'frmA' in which the user can review and/or update
individual records in table 'tblA'. There is also a search form 'frmS' in
which the user can specify a name or partial name ('txtName') which will
match a record either directly in 'tblA' or via a crossreference in 'tblB'.

If you are still with me, the idea is that the user will enter a (partial)
name in form frmS and a search will be made of both tables 'tblA' and
'tblB', with the end result being a set of records from 'tblA' that match
either directly or via a crossreference. The SQL will be passed to form
frmA as its RecordSource. The user can navigate through the list to review
or update records using the navigation buttons at the bottom of the form.

This is the SQL I am currently using:

"SELECT tblA.*
FROM tblA LEFT JOIN tblB ON tblA.ID-A = tblB.ID-A
WHERE tblB.NameB LIKE '" & txtName & "*'
UNION ALL SELECT tblA.*
FROM tblA
WHERE tblA.NameA LIKE '" & txtName & "*'
ORDER BY ID-A;"

The first part of the query looks for a matching crossreference in tblB and
outputs the corresponding tblA record. The second part looks for a direct
match in tblA.

I have two problems with this:

1) The use of UNION ALL seems to make the query read-only, even though only
tblA columns are output. This prevents the tblA records in frmA from being
updated.

2) It is possible for two or more hits to occur on the same tblA record --
one direct hit and one crossreference hit. Also two different (but
partially matching) crossreference hits. I would like the final output
passed to frmA to have include only one reference to each tblA record (so
that the user does not get the same record more than once when navigating
through the list).

I hope that this makes sense. It is a cut-down version of the actual setup.
As a workaround, there is a third, intermediate form which displays the list
of matching records from the search -- the user can select one and carry
that single record to frmA. But to navigate to the other records in the
list requires the user to close frmA, go back to the intermediate form,
select the next record, and then open frmA again for that record. Very
tedious, and something I would like to avoid.

Any suggestions? Please??? Problem 1) is the main one -- I need to be able
to update the set of records pulled from tblA either directly or via
crossreference. Problem 2) is to avoid user confusion where they navigate
the same record more than once (and to make the solution neater).

TIA.

--
Cheers,
Lyn.

[Rick Brandt]

Lyn wrote:

This one is difficult to explain, so I will cut it down to the basics.

I have a major table 'tblA' which has an autonum field 'ID-A' as
primary key (of no significance to users). 'tblA' contains many
fields including picture and memo fields. The main user-selectable
field is 'NameA'.
There is also a crossreference table (let's call it 'tblB') which
provides a secondary method of accessing records in 'tblA'. The main
fields in 'tblB' are an autonum primary key 'ID-B', a field 'ID-A"
corresponding to the related record in 'tblA', and the alternative
user-selectable field 'NameB'.
The main form is 'frmA' in which the user can review and/or update
individual records in table 'tblA'. There is also a search form
'frmS' in which the user can specify a name or partial name
('txtName') which will match a record either directly in 'tblA' or
via a crossreference in 'tblB'.
If you are still with me, the idea is that the user will enter a
(partial) name in form frmS and a search will be made of both tables
'tblA' and 'tblB', with the end result being a set of records from
'tblA' that match either directly or via a crossreference. The SQL
will be passed to form frmA as its RecordSource. The user can
navigate through the list to review or update records using the
navigation buttons at the bottom of the form.
This is the SQL I am currently using:

"SELECT tblA.*
FROM tblA LEFT JOIN tblB ON tblA.ID-A = tblB.ID-A
WHERE tblB.NameB LIKE '" & txtName & "*'
UNION ALL SELECT tblA.*
FROM tblA
WHERE tblA.NameA LIKE '" & txtName & "*'
ORDER BY ID-A;"

The first part of the query looks for a matching crossreference in
tblB and outputs the corresponding tblA record. The second part
looks for a direct match in tblA.

I have two problems with this:

1) The use of UNION ALL seems to make the query read-only, even
though only tblA columns are output. This prevents the tblA records
in frmA from being updated.

2) It is possible for two or more hits to occur on the same tblA
record -- one direct hit and one crossreference hit. Also two
different (but partially matching) crossreference hits. I would like
the final output passed to frmA to have include only one reference to
each tblA record (so that the user does not get the same record more
than once when navigating through the list).

I hope that this makes sense. It is a cut-down version of the actual
setup. As a workaround, there is a third, intermediate form which
displays the list of matching records from the search -- the user can
select one and carry that single record to frmA. But to navigate to
the other records in the list requires the user to close frmA, go
back to the intermediate form, select the next record, and then open
frmA again for that record. Very tedious, and something I would like
to avoid.
Any suggestions? Please??? Problem 1) is the main one -- I need to
be able to update the set of records pulled from tblA either directly
or via crossreference. Problem 2) is to avoid user confusion where
they navigate the same record more than once (and to make the
solution neater).
TIA.

UNION instead of UNION ALL will eliminate duplicates in the output.

A UNION query is never editable. You might be able to change your union query
so that it only returns the primary key values of the records you want and then
use another query...

SELECT * FROM tblA
WHERE PKField In(SELECT PKField FROM YourUnionQuery)

The above query should be updateable.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Lyn]

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:Rc***********@newssvr12.news.prodigy.com...

UNION instead of UNION ALL will eliminate duplicates in the output.

A UNION query is never editable. You might be able to change your union
query so that it only returns the primary key values of the records you
want and then use another query...

SELECT * FROM tblA
WHERE PKField In(SELECT PKField FROM YourUnionQuery)

The above query should be updateable.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Rick,
Thanks for your advice. I can't use UNION instead of UNION ALL because as I
stated, tblA contains picture and memo fields -- and UNION doesn't work with
these. I suppose that it is too intensive for the SQL engine to compare
large binaries and up to 32K of memo to determine if a row duplicates
another. If you have any further advice about eliminating duplicates I
would appreciate it.

I had thought of producing a recordset of just the PK field, but didn't know
how to apply that to a further query. I have never tried to write an SQL
query inside another one -- I guess I am about to try it now! I will let
you know how I go.

Thanks again.

--
Cheers,
Lyn.

[Bob Quintal]

"Lyn" <lh******@ihug.com.au> wrote in
news:d3**********@lust.ihug.co.nz:

This one is difficult to explain, so I will cut it down to the
basics.

I have a major table 'tblA' which has an autonum field 'ID-A'
as primary key (of no significance to users). 'tblA' contains
many fields including picture and memo fields. The main
user-selectable field is 'NameA'.

There is also a crossreference table (let's call it 'tblB')
which provides a secondary method of accessing records in
'tblA'. The main fields in 'tblB' are an autonum primary key
'ID-B', a field 'ID-A" corresponding to the related record in
'tblA', and the alternative user-selectable field 'NameB'.

The main form is 'frmA' in which the user can review and/or
update individual records in table 'tblA'. There is also a
search form 'frmS' in which the user can specify a name or
partial name ('txtName') which will match a record either
directly in 'tblA' or via a crossreference in 'tblB'.

If you are still with me, the idea is that the user will enter
a (partial)name in form frmS and a search will be made of both
tables 'tblA' and 'tblB', with the end result being a set of
records from 'tblA' that match either directly or via a
crossreference. The SQL will be passed to form
frmA as its RecordSource. The user can navigate through the
list to review
Any suggestions? Please??? Problem 1) is the main one -- I
need to be able to update the set of records pulled from tblA
either directly or via crossreference. Problem 2) is to avoid
user confusion where they navigate the same record more than
once (and to make the solution neater).

TIA.

You need to rethink your strategy on this one.

You are only going to enter data to table A. That is the
recordset for form a.

Your search form S should have the union query, but only of the
fields you want to search on. Form S should then pass a list of
the tblA records meeting the query criteria for form a to filter
on.

strmatchlist is a public variable in form a
Form B builds the variable

strMatchMist = "1,3,4,7,13,27"

Your form A query could then be

SELECT * from tblA where ID IN (strmatchlist)

--
Bob Quintal

PA is y I've altered my email address.

[Lyn]

"Lyn" <lh******@ihug.com.au> wrote in message
news:d3**********@lust.ihug.co.nz...

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:Rc***********@newssvr12.news.prodigy.com...

A UNION query is never editable. You might be able to change your union
query so that it only returns the primary key values of the records you
want and then use another query...

SELECT * FROM tblA
WHERE PKField In(SELECT PKField FROM YourUnionQuery)

I had thought of producing a recordset of just the PK field, but didn't
know how to apply that to a further query. I have never tried to write an
SQL query inside another one -- I guess I am about to try it now! I will
let you know how I go.

Rick,
Sorry for the delay in getting back -- I was not able to work on this
project for the last few days. In short, your suggestion worked fine,
though not without a little drama.

I found what seems to be a timing issue when you set RecordSource. What I
had been doing with my previous SQL was to pass the SQL and the
AbsolutePosition of the selected record to the detail form via OpenArgs,
firstly SQL then AbsPos separated by a semicolon. In the detail form I have
a function to parse out each OpenArgs parameter. Something like this
(pseudo code)...

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = ParseArgs(Me.OpenArgs,1)
Me.Recordset.AbsolutePosition = ParseArgs(Me.OpenArgs,2)
End If

This worked fine with the original SQL, but when I reworked the SQL as per
your suggestion, it went beresk! After a lot of tearing out of hair, I
eventually found that it would work OK if I parsed out the parameters into
variables first, then set RecordSource and AbsPos from the variables.

Stepping through this code with breakpoints showed that as soon as
RecordSource was set, form events occurred (starting with BeforeUpdate)
before the AbsPos could be set. Apparently the run time of the ParseArgs
function in getting the second parameter was too long.

Anyway, setting these form properties directly from variables has resolved
the problem. Something to keep in mind for the future. Using AbsPos this
way may not be the most elegant solution, but it makes the form work the way
I want it to.

Thanks again for your help.

--
Cheers,
Lyn.

[Lyn]

"Bob Quintal" <rq******@sPAmpatico.ca> wrote in message
news:1113745659.1d4060e241607ecab68b183beb4b862a@t eranews...

You need to rethink your strategy on this one.

You are only going to enter data to table A. That is the
recordset for form a.

Your search form S should have the union query, but only of the
fields you want to search on. Form S should then pass a list of
the tblA records meeting the query criteria for form a to filter
on.

strmatchlist is a public variable in form a
Form B builds the variable

strMatchMist = "1,3,4,7,13,27"

Your form A query could then be

SELECT * from tblA where ID IN (strmatchlist)

Bob,
Thanks for your response. Sorry for the delay in getting back to you. Your
suggestion is similar in principle to the one offered by Rick, and as you
will see from my response to Rick, it worked.

What you suggested was exactly what I was trying to do, but was having a
mind block about it. And Rick's solution lets this happen in a single SQL
query.

Thanks again.

--
Cheers,
Lyn.