Form queries from multiple tables

  • Thread starter Thread starter HKKS
  • Start date Start date
H

HKKS

Hello All,

I am trying to get query results from multiple tables, such as
StudentDatabase and TestScores. The relationship is set through a common
StudentID field. I have set up a form to allow a simple user interface. The
query is as follows:

Field: Score
Table: Test Scores
Criteria: Like Forms![frmDetailed Student Report (bands)]![txtTestScore] & "*"

Field: LastName
Table: Student Database
Criteria: Like Forms![frmDetailed Student Report (bands)]![cmbLastName] & "*"

If I include just the Test Score OR Student Database in the query then it
comes up with the results, but when I include 2 or more tables an input box
always comes up before the report is generated prompting the user with
LastName.Student Database or Score.Test Scores. Nothing is displayed even if
criteria is entered.

Any ideas what I am missing?

Thanks so much for your help.
 
Post the SQL statement of the query that does NOT work.
( Select View: SQL from the menu and copy the text)

If you do that perhaps someone can tell you why you are being prompted and why
you are getting no records.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks for your feedback John. I did stumble upon a solution to retrieve data
from 2+ tables in one query, by putting a query within a query (SQL below as
requested). Please let me know if there's a more efficient way. BTW, reading
posts it seems that knowing SQL is critical to creating Access Dbases - could
you recommend a good source to learn SQL? Thanks very much.

SELECT [qryDetailed Student Report (bands)].Gender, [qryDetailed Student
Report (bands)].FirstName, [qryDetailed Student Report (bands)].LastName,
[qryDetailed Student Report (bands)].Company, [qryDetailed Student Report
(bands)].[Band], Textbooks.Textbooks
FROM Textbooks INNER JOIN [qryDetailed Student Report (bands)] ON
Textbooks.StudentID = [qryDetailed Student Report (bands)].StudentID
WHERE (((Textbooks.Textbooks) Like [Forms]![frmDetailed Student Report
(bands)]![cmbTextbooks1] Or (Textbooks.Textbooks) Is Null Or
((Textbooks.Textbooks) Like [Forms]![frmDetailed Student Report
(bands)]![cmbTextbooks3] Or (Textbooks.Textbooks) Is Null) Or
((Textbooks.Textbooks) Like [Forms]![frmDetailed Student Report
(bands)]![cmbTextbooks2] Or (Textbooks.Textbooks) Is Null)));
--
HKKS
Tokyo, Japan


John Spencer MVP said:
Post the SQL statement of the query that does NOT work.
( Select View: SQL from the menu and copy the text)

If you do that perhaps someone can tell you why you are being prompted and why
you are getting no records.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hello All,

I am trying to get query results from multiple tables, such as
StudentDatabase and TestScores. The relationship is set through a common
StudentID field. I have set up a form to allow a simple user interface. The
query is as follows:

Field: Score
Table: Test Scores
Criteria: Like Forms![frmDetailed Student Report (bands)]![txtTestScore] & "*"

Field: LastName
Table: Student Database
Criteria: Like Forms![frmDetailed Student Report (bands)]![cmbLastName] & "*"

If I include just the Test Score OR Student Database in the query then it
comes up with the results, but when I include 2 or more tables an input box
always comes up before the report is generated prompting the user with
LastName.Student Database or Score.Test Scores. Nothing is displayed even if
criteria is entered.

Any ideas what I am missing?

Thanks so much for your help.
Click to expand...
Click to expand...
 
The book I've used is
SQL Queries for Mere Mortals by Hernandez and Viescas
As for your current query, nothing jumps out at me to make it more efficient.
If its performance is acceptable then I would not bother to change it.

I do wonder why you are using LIKE instead of = in the where clause. And the
where clause looks suspicious to me, but since I am not sure what you are
attempting to do I can't say the query is wrong.

SELECT [qryDetailed Student Report (bands)].Gender, [qryDetailed Student
Report (bands)].FirstName, [qryDetailed Student Report (bands)].LastName,
[qryDetailed Student Report (bands)].Company, [qryDetailed Student Report
(bands)].[Band], Textbooks.Textbooks
FROM Textbooks INNER JOIN [qryDetailed Student Report (bands)] ON
Textbooks.StudentID = [qryDetailed Student Report (bands)].StudentID

I would expect the WHERE clause to look more like the following

WHERE Textbooks.Textbooks =
[Forms]![frmDetailed Student Report (bands)]![cmbTextbooks1]
Or Textbooks.Textbooks =
[Forms]![frmDetailed Student Report (bands)]![cmbTextbooks3]
Or Textbooks.Textbooks =
[Forms]![frmDetailed Student Report (bands)]![cmbTextbooks2]
Or Textbooks.Textbooks Is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks John. I'll pick up the SQL book that your recommended and have a look
at the alternate coding that you posted. I use "like" more out of habit, I've
read up on using "=" and is another useful tool I have overlooked. I need
that book ASAP.

Now I'm one step closer to finishing our much needed database. It is
pathetic that I have been given no support or resourcses by my company to do
this project so I am VERY grateful for your advice and solutions!

Thanks again.
--
HKKS
Tokyo, Japan


John Spencer MVP said:
The book I've used is
SQL Queries for Mere Mortals by Hernandez and Viescas
As for your current query, nothing jumps out at me to make it more efficient.
If its performance is acceptable then I would not bother to change it.

I do wonder why you are using LIKE instead of = in the where clause. And the
where clause looks suspicious to me, but since I am not sure what you are
attempting to do I can't say the query is wrong.

SELECT [qryDetailed Student Report (bands)].Gender, [qryDetailed Student
Report (bands)].FirstName, [qryDetailed Student Report (bands)].LastName,
[qryDetailed Student Report (bands)].Company, [qryDetailed Student Report
(bands)].[Band], Textbooks.Textbooks
FROM Textbooks INNER JOIN [qryDetailed Student Report (bands)] ON
Textbooks.StudentID = [qryDetailed Student Report (bands)].StudentID

I would expect the WHERE clause to look more like the following

WHERE Textbooks.Textbooks =
[Forms]![frmDetailed Student Report (bands)]![cmbTextbooks1]
Or Textbooks.Textbooks =
[Forms]![frmDetailed Student Report (bands)]![cmbTextbooks3]
Or Textbooks.Textbooks =
[Forms]![frmDetailed Student Report (bands)]![cmbTextbooks2]
Or Textbooks.Textbooks Is Null

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks for your feedback John. I did stumble upon a solution to retrieve data
from 2+ tables in one query, by putting a query within a query (SQL below as
requested). Please let me know if there's a more efficient way. BTW, reading
posts it seems that knowing SQL is critical to creating Access Dbases - could
you recommend a good source to learn SQL? Thanks very much.

SELECT [qryDetailed Student Report (bands)].Gender, [qryDetailed Student
Report (bands)].FirstName, [qryDetailed Student Report (bands)].LastName,
[qryDetailed Student Report (bands)].Company, [qryDetailed Student Report
(bands)].[Band], Textbooks.Textbooks
FROM Textbooks INNER JOIN [qryDetailed Student Report (bands)] ON
Textbooks.StudentID = [qryDetailed Student Report (bands)].StudentID
WHERE (((Textbooks.Textbooks) Like [Forms]![frmDetailed Student Report
(bands)]![cmbTextbooks1] Or (Textbooks.Textbooks) Is Null Or
((Textbooks.Textbooks) Like [Forms]![frmDetailed Student Report
(bands)]![cmbTextbooks3] Or (Textbooks.Textbooks) Is Null) Or
((Textbooks.Textbooks) Like [Forms]![frmDetailed Student Report
(bands)]![cmbTextbooks2] Or (Textbooks.Textbooks) Is Null)));
Click to expand...
Click to expand...
 
Back
Top