Problems after combining 2 queries

G

Guest

I'm trying to combine the string of two previously separate queries.

1) called qMembershipSelect

Was as simple as this: (and it is always rebuilt with different parameters)
Select * FROM Membership Where [MemName] like 'Jones*'

2) called qMemberResults - would use qMembershipResults to do something more
complex - it took current members with the latest 'Thru-Dt' OR Thru-Dt being
Null

and looked like this:
SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
[FROM-DT], [THRU-DT]
FROM qMembershipSelect AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC;

This combination worked without a hitch - Perfecto!

My dilema now is that I will use this same string in ADO.NET for an ASP.NET
app and I need the two pieces to be combined... so I tried this:

'The Y piece is what qMembershipSelect could look like

SELECT [MRN],[MemName],[DOB],[SEX],[SSN],[GROUP],[SGR],[FROM-DT],[THRU-DT]
FROM MEMBERSHIP WHERE [MemName] like 'Jones*' AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, [GROUP], SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT]
In (Select Max(X.[Thru-Dt]) From Membership As X Where X.MRN = Y.MRN And
X.MRN Not in(Select Z.MRN From Membership As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC

And now I am getting the following error:
Syntax error (missing operator) in query expression '[MemName] like 'Jones*'
as Y

What is wrong with this?
 
A

Amy Blankenship

What's your connection string? Different connection strings use different
engines to connect, and some of these engines don't use the same syntax.
You could either change your connection string or try like 'Jones%'

HTH;

Amy
 
G

Guest

Actually, I did experience that % problem yesterday but fixed it.
I know Access wants an asterisk, so I actually tried pasting the constructed
string directly in an Access query but I still get the error And now I am
getting the following error:

Syntax error (missing operator) in query expression '[MemName] like
'Jones%'
as Y

does it have something to do with the 'as Y' part?



Amy Blankenship said:
What's your connection string? Different connection strings use different
engines to connect, and some of these engines don't use the same syntax.
You could either change your connection string or try like 'Jones%'

HTH;

Amy

jonefer said:
I'm trying to combine the string of two previously separate queries.

1) called qMembershipSelect

Was as simple as this: (and it is always rebuilt with different
parameters)
Select * FROM Membership Where [MemName] like 'Jones*'

2) called qMemberResults - would use qMembershipResults to do something
more
complex - it took current members with the latest 'Thru-Dt' OR Thru-Dt
being
Null

and looked like this:
SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
[FROM-DT], [THRU-DT]
FROM qMembershipSelect AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC;

This combination worked without a hitch - Perfecto!

My dilema now is that I will use this same string in ADO.NET for an
ASP.NET
app and I need the two pieces to be combined... so I tried this:

'The Y piece is what qMembershipSelect could look like

SELECT [MRN],[MemName],[DOB],[SEX],[SSN],[GROUP],[SGR],[FROM-DT],[THRU-DT]
FROM MEMBERSHIP WHERE [MemName] like 'Jones*' AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, [GROUP], SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT]
In (Select Max(X.[Thru-Dt]) From Membership As X Where X.MRN = Y.MRN And
X.MRN Not in(Select Z.MRN From Membership As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC

And now I am getting the following error:
Syntax error (missing operator) in query expression '[MemName] like
'Jones*'
as Y

What is wrong with this?
Click to expand...
Click to expand...
 
O

OfficeDev18 via AccessMonster.com

Try changing the line

FROM MEMBERSHIP WHERE [MemName] like 'Jones*' AS Y

to

FROM MEMBERSHIP AS Y WHERE [MemName] like 'Jones*'

You're using 'Y' as a table alias, not as a field display alias.

HTH
I'm trying to combine the string of two previously separate queries.

1) called qMembershipSelect

Was as simple as this: (and it is always rebuilt with different parameters)
Select * FROM Membership Where [MemName] like 'Jones*'

2) called qMemberResults - would use qMembershipResults to do something more
complex - it took current members with the latest 'Thru-Dt' OR Thru-Dt being
Null

and looked like this:
SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
[FROM-DT], [THRU-DT]
FROM qMembershipSelect AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC;

This combination worked without a hitch - Perfecto!

My dilema now is that I will use this same string in ADO.NET for an ASP.NET
app and I need the two pieces to be combined... so I tried this:

'The Y piece is what qMembershipSelect could look like

SELECT [MRN],[MemName],[DOB],[SEX],[SSN],[GROUP],[SGR],[FROM-DT],[THRU-DT]
FROM MEMBERSHIP WHERE [MemName] like 'Jones*' AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, [GROUP], SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT]
In (Select Max(X.[Thru-Dt]) From Membership As X Where X.MRN = Y.MRN And
X.MRN Not in(Select Z.MRN From Membership As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC

And now I am getting the following error:
Syntax error (missing operator) in query expression '[MemName] like 'Jones*'
as Y

What is wrong with this?
Click to expand...
 
A

Amy Blankenship

What do the two queries look like now in Access, and how are you calling
them from ASP? Is the "outer" query still the same?

jonefer said:
Actually, I did experience that % problem yesterday but fixed it.
I know Access wants an asterisk, so I actually tried pasting the
constructed
string directly in an Access query but I still get the error And now I am
getting the following error:

Syntax error (missing operator) in query expression '[MemName] like
'Jones%'
as Y

does it have something to do with the 'as Y' part?



Amy Blankenship said:
What's your connection string? Different connection strings use
different
engines to connect, and some of these engines don't use the same syntax.
You could either change your connection string or try like 'Jones%'

HTH;

Amy

jonefer said:
I'm trying to combine the string of two previously separate queries.

1) called qMembershipSelect

Was as simple as this: (and it is always rebuilt with different
parameters)
Select * FROM Membership Where [MemName] like 'Jones*'

2) called qMemberResults - would use qMembershipResults to do something
more
complex - it took current members with the latest 'Thru-Dt' OR Thru-Dt
being
Null

and looked like this:
SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
[FROM-DT], [THRU-DT]
FROM qMembershipSelect AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select
Z.MRN
From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC;

This combination worked without a hitch - Perfecto!

My dilema now is that I will use this same string in ADO.NET for an
ASP.NET
app and I need the two pieces to be combined... so I tried this:

'The Y piece is what qMembershipSelect could look like

SELECT
[MRN],[MemName],[DOB],[SEX],[SSN],[GROUP],[SGR],[FROM-DT],[THRU-DT]
FROM MEMBERSHIP WHERE [MemName] like 'Jones*' AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, [GROUP], SGR, [FROM-DT],
[THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT]
In (Select Max(X.[Thru-Dt]) From Membership As X Where X.MRN = Y.MRN
And
X.MRN Not in(Select Z.MRN From Membership As Z Where Z.[THRU-DT] Is
Null))
ORDER BY MemNAME, [FROM-DT] DESC

And now I am getting the following error:
Syntax error (missing operator) in query expression '[MemName] like
'Jones*'
as Y

What is wrong with this?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yes, the outer query is still the same -
I'm presently using the DataAdapter to do what I was doing for
qMembershipSelect
I construct the CommandText and assign it to the adapter...

Then I fill the dataset with it.
(but from your suggestion of a 'view', I probably want to do it another way)

Could you walk me through creating a view and using it inside another SQL
statement?

So that I can accomplish the same result set as below?

Amy Blankenship said:
What do the two queries look like now in Access, and how are you calling
them from ASP? Is the "outer" query still the same?

jonefer said:
Actually, I did experience that % problem yesterday but fixed it.
I know Access wants an asterisk, so I actually tried pasting the
constructed
string directly in an Access query but I still get the error And now I am
getting the following error:

Syntax error (missing operator) in query expression '[MemName] like
'Jones%'
as Y

does it have something to do with the 'as Y' part?



Amy Blankenship said:
What's your connection string? Different connection strings use
different
engines to connect, and some of these engines don't use the same syntax.
You could either change your connection string or try like 'Jones%'

HTH;

Amy

I'm trying to combine the string of two previously separate queries.

1) called qMembershipSelect

Was as simple as this: (and it is always rebuilt with different
parameters)
Select * FROM Membership Where [MemName] like 'Jones*'

2) called qMemberResults - would use qMembershipResults to do something
more
complex - it took current members with the latest 'Thru-Dt' OR Thru-Dt
being
Null

and looked like this:
SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
[FROM-DT], [THRU-DT]
FROM qMembershipSelect AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select
Z.MRN
From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC;

This combination worked without a hitch - Perfecto!

My dilema now is that I will use this same string in ADO.NET for an
ASP.NET
app and I need the two pieces to be combined... so I tried this:

'The Y piece is what qMembershipSelect could look like

SELECT
[MRN],[MemName],[DOB],[SEX],[SSN],[GROUP],[SGR],[FROM-DT],[THRU-DT]
FROM MEMBERSHIP WHERE [MemName] like 'Jones*' AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, [GROUP], SGR, [FROM-DT],
[THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT]
In (Select Max(X.[Thru-Dt]) From Membership As X Where X.MRN = Y.MRN
And
X.MRN Not in(Select Z.MRN From Membership As Z Where Z.[THRU-DT] Is
Null))
ORDER BY MemNAME, [FROM-DT] DESC

And now I am getting the following error:
Syntax error (missing operator) in query expression '[MemName] like
'Jones*'
as Y

What is wrong with this?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That was what I was hoping I'd get... thanks!

OfficeDev18 via AccessMonster.com said:
Try changing the line

FROM MEMBERSHIP WHERE [MemName] like 'Jones*' AS Y

to

FROM MEMBERSHIP AS Y WHERE [MemName] like 'Jones*'

You're using 'Y' as a table alias, not as a field display alias.

HTH
I'm trying to combine the string of two previously separate queries.

1) called qMembershipSelect

Was as simple as this: (and it is always rebuilt with different parameters)
Select * FROM Membership Where [MemName] like 'Jones*'

2) called qMemberResults - would use qMembershipResults to do something more
complex - it took current members with the latest 'Thru-Dt' OR Thru-Dt being
Null

and looked like this:
SELECT DISTINCT MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
[FROM-DT], [THRU-DT]
FROM qMembershipSelect AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, GROUP, SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT] In (Select Max(X.[Thru-Dt]) From
qMembershipSelect As X Where X.MRN = Y.MRN And X.MRN Not in(Select Z.MRN
From qMembershipSelect As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC;

This combination worked without a hitch - Perfecto!

My dilema now is that I will use this same string in ADO.NET for an ASP.NET
app and I need the two pieces to be combined... so I tried this:

'The Y piece is what qMembershipSelect could look like

SELECT [MRN],[MemName],[DOB],[SEX],[SSN],[GROUP],[SGR],[FROM-DT],[THRU-DT]
FROM MEMBERSHIP WHERE [MemName] like 'Jones*' AS Y
GROUP BY MRN, MemNAME, DOB, SEX, SSN, [GROUP], SGR, [FROM-DT], [THRU-DT]
HAVING [THRU-DT] Is Null Or [THRU-DT]
In (Select Max(X.[Thru-Dt]) From Membership As X Where X.MRN = Y.MRN And
X.MRN Not in(Select Z.MRN From Membership As Z Where Z.[THRU-DT] Is Null))
ORDER BY MemNAME, [FROM-DT] DESC

And now I am getting the following error:
Syntax error (missing operator) in query expression '[MemName] like 'Jones*'
as Y

What is wrong with this?
Click to expand...
Click to expand...
 
A

Amy Blankenship

jonefer said:
Yes, the outer query is still the same -
I'm presently using the DataAdapter to do what I was doing for
qMembershipSelect
I construct the CommandText and assign it to the adapter...

Then I fill the dataset with it.
(but from your suggestion of a 'view', I probably want to do it another
way)

Could you walk me through creating a view and using it inside another SQL
statement?

So that I can accomplish the same result set as below?
Click to expand...

OK, open the Query Builder window and when it prompts for table, select
"Membership." Double-click the "*" field to make that what you're
selecting. Now, double-click MemName. Unclick the check box you'll see
beneath it. Find the row labeled "Criteria" and follow it across to beneath
MemName. Beneath it, type: Like [Enter a Name] & '*'

Save this as qMembershipResults.

Open a new query, select qMembershipResults as the source. If you want it
to be aliased, right click on the title bar, and select properties. You'll
see a field in the properties window called "Alias". Change the value in
that field from qMembershipResults to "Y".

Now, click on the gray background of the query itself, which will change the
properties window to refer to the query instead of the table. Set the
"Unique Values" field to Yes (this is your DISTINCT keyword).

Highlight the fields MRN, MemNAME AS Member, DOB, SEX, SSN, GROUP, SGR,
FROM-DT, THRU-DT and drag them down to the query grid. I don't thing you
actually need to be grouping anything, since you're not aggregating. You're
grouping on everything, which is pretty much the same as not grouping. If
you want it to order by MRN, MemName, etc., you can select the ascending
order option for each column.

Below THRU-DT, type "Is Null" without the quotes. You *can* put your IN
(subquery) below it on the or line, but I'm not sure that's the best option.
Could you explain in English what you're looking to do here? I often find
that subqueries go away in favor of a simpler expression when I haven't
looked at a database for a few weeks. :).

So at this point we have everything except your subselect, which probably
needs to be simplified. This won't affect the ability of the query to run
when called from outside, just the results will be narrower than what you
want. Save it as qMemberResults.

Assuming you have a connection string that supports '*' in the Like clause
(something like "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath(dbName) & ";") and you have connection conn that is properly
opened, you can execute the query something like this:

conn.Execute("Exec [qMemberResults, 'Jones']")

HTH;

Amy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

More help with A Not so simple - Max Function 5
2nd layer of filtering? 6
something about stored procedure not returning the result 5
why code for datagrid doesn't work 1
DateTime parameter keeps seeing string even though not passed 5
Access quit after saving query 1
Combining Queries (2) 1
Query Does Not Pick Up Data 5

Top