query writing dilemma, pt 2

[Renee]

The responses to my question on 6/6 didn't help me. So let me pose my
dilemma again, only provide my tables and field names:

tblSTPDATA- tblSTPSpecies
stID tqSPECID
stRESNO tqEXID
tqSPECNM

stID has the one relationship, tqEXID has the many.

stRESNO is the field that holds the unique resource numbers (areas), and
tqSPECNM is the tree species fields.

The goal is to produce a query that shows all the resource areas that have
douglas firs (their species number is 70) but also show all the other species
in those resources including the douglas firs, and not show all the resource
areas that do not have douglas firs.

 

[Bob Barrows [MVP]]

The responses to my question on 6/6 didn't help me. So let me pose my
dilemma again, only provide my tables and field names:

tblSTPDATA- tblSTPSpecies
stID tqSPECID
stRESNO tqEXID
tqSPECNM

stID has the one relationship, tqEXID has the many.

stRESNO is the field that holds the unique resource numbers (areas),
and tqSPECNM is the tree species fields.

The goal is to produce a query that shows all the resource areas that
have douglas firs (their species number is 70) but also show all the
other species in those resources including the douglas firs, and not
show all the resource areas that do not have douglas firs.

Select stRESNO From tblSTPDATA t
WHERE EXISTS (
Select * FROM tblSTPSpecies s
WHERE s.tqEXID = t.stID
AND tqSPECID=70)

 

[KARL DEWEY]

Using two queries ---
Renee --
SELECT tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
FROM tblSTPSpecies
GROUP BY tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
HAVING (((tblSTPSpecies.tqSPECID)=[Enter species]));

SELECT tblSTPDATA.stRESNO, tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID,
tblSTPSpecies.tqSPECNM
FROM Renee LEFT JOIN (tblSTPDATA LEFT JOIN tblSTPSpecies ON tblSTPDATA.stID
= tblSTPSpecies.tqEXID) ON Renee.tqEXID = tblSTPDATA.stID;

 

[Bob Barrows [MVP]]

The responses to my question on 6/6 didn't help me. So let me pose my
dilemma again, only provide my tables and field names:

tblSTPDATA- tblSTPSpecies
stID tqSPECID
stRESNO tqEXID
tqSPECNM

stID has the one relationship, tqEXID has the many.

stRESNO is the field that holds the unique resource numbers (areas),
and tqSPECNM is the tree species fields.

The goal is to produce a query that shows all the resource areas that
have douglas firs (their species number is 70) but also show all the
other species in those resources including the douglas firs, and not
show all the resource areas that do not have douglas firs.

I just realized I didn't read the spec carefully enough. Karl's solution
should do this.

 

[Renee]

I'm sorry if you have to baby me along here with this, but I'm assuming that
these are subqueries, and I don't know much about SQL statements, so I'm
hoping you'll help me a little further here. I did a copy and paste of the
first half and put it in the field line of my query and did a run, and got a
syntax error message, then I tried doing a cut copy and paste of the whole
thing, and still got a syntax error message. What am I doing wrong? I've
never done a subquery before.

Thanks.

Using two queries ---
Renee --
SELECT tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
FROM tblSTPSpecies
GROUP BY tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
HAVING (((tblSTPSpecies.tqSPECID)=[Enter species]));

SELECT tblSTPDATA.stRESNO, tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID,
tblSTPSpecies.tqSPECNM
FROM Renee LEFT JOIN (tblSTPDATA LEFT JOIN tblSTPSpecies ON tblSTPDATA.stID
= tblSTPSpecies.tqEXID) ON Renee.tqEXID = tblSTPDATA.stID;

--
KARL DEWEY
Build a little - Test a little

The responses to my question on 6/6 didn't help me. So let me pose my
dilemma again, only provide my tables and field names:

tblSTPDATA- tblSTPSpecies
stID tqSPECID
stRESNO tqEXID
tqSPECNM

stID has the one relationship, tqEXID has the many.

stRESNO is the field that holds the unique resource numbers (areas), and
tqSPECNM is the tree species fields.

The goal is to produce a query that shows all the resource areas that have
douglas firs (their species number is 70) but also show all the other species
in those resources including the douglas firs, and not show all the resource
areas that do not have douglas firs.

 

[KARL DEWEY]

What am I doing wrong? I've never done a subquery before.
This has no subquery, it is two queries. I named the first query Renee. If
you decide to rename it you will also need to edit all references to it in
the second query SQL.
Wrong action. Open a new query in design view, click on menu VIEW - SQL
View. Paste the SQL statement into the new window. Edit out any hard
returns that copying and pasting may have added. The only returns should be
before the words in all caps. Save.
Do the same for the second query.

--
KARL DEWEY
Build a little - Test a little

I'm sorry if you have to baby me along here with this, but I'm assuming that
these are subqueries, and I don't know much about SQL statements, so I'm
hoping you'll help me a little further here. I did a copy and paste of the
first half and put it in the field line of my query and did a run, and got a
syntax error message, then I tried doing a cut copy and paste of the whole
thing, and still got a syntax error message. What am I doing wrong? I've
never done a subquery before.

Thanks.

Using two queries ---
Renee --
SELECT tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
FROM tblSTPSpecies
GROUP BY tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
HAVING (((tblSTPSpecies.tqSPECID)=[Enter species]));

SELECT tblSTPDATA.stRESNO, tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID,
tblSTPSpecies.tqSPECNM
FROM Renee LEFT JOIN (tblSTPDATA LEFT JOIN tblSTPSpecies ON tblSTPDATA.stID
= tblSTPSpecies.tqEXID) ON Renee.tqEXID = tblSTPDATA.stID;

--
KARL DEWEY
Build a little - Test a little

The responses to my question on 6/6 didn't help me. So let me pose my
dilemma again, only provide my tables and field names:

tblSTPDATA- tblSTPSpecies
stID tqSPECID
stRESNO tqEXID
tqSPECNM

stID has the one relationship, tqEXID has the many.

stRESNO is the field that holds the unique resource numbers (areas), and
tqSPECNM is the tree species fields.

The goal is to produce a query that shows all the resource areas that have
douglas firs (their species number is 70) but also show all the other species
in those resources including the douglas firs, and not show all the resource
areas that do not have douglas firs.

 

[Renee]

Thank you, Karl for being so patient with me. I have done a cut and paste of
the first query into a new query with the appropriate tables above and the
SQL statement in the "field" line using the expression builder, then ran it.
I got a message that said "Syntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses." I have copied from the S for Select all the way to end where
it says [Enter species])));.

I am signing up for a class in SQL next month so I can understand this
better, but in the meantime, I need to get over this one little hiccup query
issue. I am so close to having this project finished. Thanks.

This has no subquery, it is two queries. I named the first query Renee. If
you decide to rename it you will also need to edit all references to it in
the second query SQL.
Wrong action. Open a new query in design view, click on menu VIEW - SQL
View. Paste the SQL statement into the new window. Edit out any hard
returns that copying and pasting may have added. The only returns should be
before the words in all caps. Save.
Do the same for the second query.

--
KARL DEWEY
Build a little - Test a little

I'm sorry if you have to baby me along here with this, but I'm assuming that
these are subqueries, and I don't know much about SQL statements, so I'm
hoping you'll help me a little further here. I did a copy and paste of the
first half and put it in the field line of my query and did a run, and got a
syntax error message, then I tried doing a cut copy and paste of the whole
thing, and still got a syntax error message. What am I doing wrong? I've
never done a subquery before.

Thanks.

Using two queries ---
Renee --
SELECT tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
FROM tblSTPSpecies
GROUP BY tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
HAVING (((tblSTPSpecies.tqSPECID)=[Enter species]));

SELECT tblSTPDATA.stRESNO, tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID,
tblSTPSpecies.tqSPECNM
FROM Renee LEFT JOIN (tblSTPDATA LEFT JOIN tblSTPSpecies ON tblSTPDATA.stID
= tblSTPSpecies.tqEXID) ON Renee.tqEXID = tblSTPDATA.stID;

--
KARL DEWEY
Build a little - Test a little


:

The responses to my question on 6/6 didn't help me. So let me pose my
dilemma again, only provide my tables and field names:

tblSTPDATA- tblSTPSpecies
stID tqSPECID
stRESNO tqEXID
tqSPECNM

stID has the one relationship, tqEXID has the many.

stRESNO is the field that holds the unique resource numbers (areas), and
tqSPECNM is the tree species fields.

The goal is to produce a query that shows all the resource areas that have
douglas firs (their species number is 70) but also show all the other species
in those resources including the douglas firs, and not show all the resource
areas that do not have douglas firs.

 

[KARL DEWEY]

There is no "field" line and do not use the expression builder. The SQL
statement is a complete query.

In the database view, click on Queries in the navagation pane. Then click
on the NEW icon. Select Design View. When it displays the list of tables to
select from click Close.
Click on menu VIEW - SQL View. It will open another window the is the SQL
view. In the window will be 'SELECT;' and nothing else. Paste the SQL
statement over the existing text in the window . Edit out any hard returns
that copying and pasting may have added. The only returns should be before
the words in all caps. Save.

--
KARL DEWEY
Build a little - Test a little

Thank you, Karl for being so patient with me. I have done a cut and paste of
the first query into a new query with the appropriate tables above and the
SQL statement in the "field" line using the expression builder, then ran it.
I got a message that said "Syntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses." I have copied from the S for Select all the way to end where
it says [Enter species])));.

I am signing up for a class in SQL next month so I can understand this
better, but in the meantime, I need to get over this one little hiccup query
issue. I am so close to having this project finished. Thanks.

What am I doing wrong? I've never done a subquery before.

This has no subquery, it is two queries. I named the first query Renee. If
you decide to rename it you will also need to edit all references to it in
the second query SQL.

I did a copy and paste of the first half and put it in the field line of my query

Wrong action. Open a new query in design view, click on menu VIEW - SQL
View. Paste the SQL statement into the new window. Edit out any hard
returns that copying and pasting may have added. The only returns should be
before the words in all caps. Save.
Do the same for the second query.

--
KARL DEWEY
Build a little - Test a little

I'm sorry if you have to baby me along here with this, but I'm assuming that
these are subqueries, and I don't know much about SQL statements, so I'm
hoping you'll help me a little further here. I did a copy and paste of the
first half and put it in the field line of my query and did a run, and got a
syntax error message, then I tried doing a cut copy and paste of the whole
thing, and still got a syntax error message. What am I doing wrong? I've
never done a subquery before.

Thanks.

:

Using two queries ---
Renee --
SELECT tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
FROM tblSTPSpecies
GROUP BY tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
HAVING (((tblSTPSpecies.tqSPECID)=[Enter species]));

SELECT tblSTPDATA.stRESNO, tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID,
tblSTPSpecies.tqSPECNM
FROM Renee LEFT JOIN (tblSTPDATA LEFT JOIN tblSTPSpecies ON tblSTPDATA.stID
= tblSTPSpecies.tqEXID) ON Renee.tqEXID = tblSTPDATA.stID;

--
KARL DEWEY
Build a little - Test a little


:

The responses to my question on 6/6 didn't help me. So let me pose my
dilemma again, only provide my tables and field names:

tblSTPDATA- tblSTPSpecies
stID tqSPECID
stRESNO tqEXID
tqSPECNM

stID has the one relationship, tqEXID has the many.

stRESNO is the field that holds the unique resource numbers (areas), and
tqSPECNM is the tree species fields.

The goal is to produce a query that shows all the resource areas that have
douglas firs (their species number is 70) but also show all the other species
in those resources including the douglas firs, and not show all the resource
areas that do not have douglas firs.

 

[Renee]

Okay, Mr. Dewey - You are the genius! But I want a little piece of that
action. It wasn't quite what I wanted. It kept returning only one resource
number, but I figured how to tweek it to get it list what I wanted. You are
the man! Thank you, thank you, thank you!!!!!!!!!

There is no "field" line and do not use the expression builder. The SQL
statement is a complete query.

In the database view, click on Queries in the navagation pane. Then click
on the NEW icon. Select Design View. When it displays the list of tables to
select from click Close.
Click on menu VIEW - SQL View. It will open another window the is the SQL
view. In the window will be 'SELECT;' and nothing else. Paste the SQL
statement over the existing text in the window . Edit out any hard returns
that copying and pasting may have added. The only returns should be before
the words in all caps. Save.

--
KARL DEWEY
Build a little - Test a little

Thank you, Karl for being so patient with me. I have done a cut and paste of
the first query into a new query with the appropriate tables above and the
SQL statement in the "field" line using the expression builder, then ran it.
I got a message that said "Syntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses." I have copied from the S for Select all the way to end where
it says [Enter species])));.

I am signing up for a class in SQL next month so I can understand this
better, but in the meantime, I need to get over this one little hiccup query
issue. I am so close to having this project finished. Thanks.

What am I doing wrong? I've never done a subquery before.
This has no subquery, it is two queries. I named the first query Renee. If
you decide to rename it you will also need to edit all references to it in
the second query SQL.

I did a copy and paste of the first half and put it in the field line of my query
Wrong action. Open a new query in design view, click on menu VIEW - SQL
View. Paste the SQL statement into the new window. Edit out any hard
returns that copying and pasting may have added. The only returns should be
before the words in all caps. Save.
Do the same for the second query.

--
KARL DEWEY
Build a little - Test a little


:

I'm sorry if you have to baby me along here with this, but I'm assuming that
these are subqueries, and I don't know much about SQL statements, so I'm
hoping you'll help me a little further here. I did a copy and paste of the
first half and put it in the field line of my query and did a run, and got a
syntax error message, then I tried doing a cut copy and paste of the whole
thing, and still got a syntax error message. What am I doing wrong? I've
never done a subquery before.

Thanks.

:

Using two queries ---
Renee --
SELECT tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
FROM tblSTPSpecies
GROUP BY tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
HAVING (((tblSTPSpecies.tqSPECID)=[Enter species]));

SELECT tblSTPDATA.stRESNO, tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID,
tblSTPSpecies.tqSPECNM
FROM Renee LEFT JOIN (tblSTPDATA LEFT JOIN tblSTPSpecies ON tblSTPDATA.stID
= tblSTPSpecies.tqEXID) ON Renee.tqEXID = tblSTPDATA.stID;

--
KARL DEWEY
Build a little - Test a little


:

The responses to my question on 6/6 didn't help me. So let me pose my
dilemma again, only provide my tables and field names:

tblSTPDATA- tblSTPSpecies
stID tqSPECID
stRESNO tqEXID
tqSPECNM

stID has the one relationship, tqEXID has the many.

stRESNO is the field that holds the unique resource numbers (areas), and
tqSPECNM is the tree species fields.

The goal is to produce a query that shows all the resource areas that have
douglas firs (their species number is 70) but also show all the other species
in those resources including the douglas firs, and not show all the resource
areas that do not have douglas firs.

 

[KARL DEWEY]

The second query uses the first query joined with the table to list as you
wanted.

--
KARL DEWEY
Build a little - Test a little

Okay, Mr. Dewey - You are the genius! But I want a little piece of that
action. It wasn't quite what I wanted. It kept returning only one resource
number, but I figured how to tweek it to get it list what I wanted. You are
the man! Thank you, thank you, thank you!!!!!!!!!

There is no "field" line and do not use the expression builder. The SQL
statement is a complete query.

In the database view, click on Queries in the navagation pane. Then click
on the NEW icon. Select Design View. When it displays the list of tables to
select from click Close.
Click on menu VIEW - SQL View. It will open another window the is the SQL
view. In the window will be 'SELECT;' and nothing else. Paste the SQL
statement over the existing text in the window . Edit out any hard returns
that copying and pasting may have added. The only returns should be before
the words in all caps. Save.

--
KARL DEWEY
Build a little - Test a little

Thank you, Karl for being so patient with me. I have done a cut and paste of
the first query into a new query with the appropriate tables above and the
SQL statement in the "field" line using the expression builder, then ran it.
I got a message that said "Syntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parentheses." I have copied from the S for Select all the way to end where
it says [Enter species])));.

I am signing up for a class in SQL next month so I can understand this
better, but in the meantime, I need to get over this one little hiccup query
issue. I am so close to having this project finished. Thanks.

:

What am I doing wrong? I've never done a subquery before.
This has no subquery, it is two queries. I named the first query Renee. If
you decide to rename it you will also need to edit all references to it in
the second query SQL.

I did a copy and paste of the first half and put it in the field line of my query
Wrong action. Open a new query in design view, click on menu VIEW - SQL
View. Paste the SQL statement into the new window. Edit out any hard
returns that copying and pasting may have added. The only returns should be
before the words in all caps. Save.
Do the same for the second query.

--
KARL DEWEY
Build a little - Test a little


:

I'm sorry if you have to baby me along here with this, but I'm assuming that
these are subqueries, and I don't know much about SQL statements, so I'm
hoping you'll help me a little further here. I did a copy and paste of the
first half and put it in the field line of my query and did a run, and got a
syntax error message, then I tried doing a cut copy and paste of the whole
thing, and still got a syntax error message. What am I doing wrong? I've
never done a subquery before.

Thanks.

:

Using two queries ---
Renee --
SELECT tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
FROM tblSTPSpecies
GROUP BY tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID
HAVING (((tblSTPSpecies.tqSPECID)=[Enter species]));

SELECT tblSTPDATA.stRESNO, tblSTPSpecies.tqSPECID, tblSTPSpecies.tqEXID,
tblSTPSpecies.tqSPECNM
FROM Renee LEFT JOIN (tblSTPDATA LEFT JOIN tblSTPSpecies ON tblSTPDATA.stID
= tblSTPSpecies.tqEXID) ON Renee.tqEXID = tblSTPDATA.stID;

--
KARL DEWEY
Build a little - Test a little


:

The responses to my question on 6/6 didn't help me. So let me pose my
dilemma again, only provide my tables and field names:

tblSTPDATA- tblSTPSpecies
stID tqSPECID
stRESNO tqEXID
tqSPECNM

stID has the one relationship, tqEXID has the many.

stRESNO is the field that holds the unique resource numbers (areas), and
tqSPECNM is the tree species fields.

The goal is to produce a query that shows all the resource areas that have
douglas firs (their species number is 70) but also show all the other species
in those resources including the douglas firs, and not show all the resource
areas that do not have douglas firs.