Help with SQL code

C

ctdak

I did a mock up in Access query view before creating the following SQL code. I created two queries and linked them - the results were exactly what I need for a report.

Now I am trying to translate this into an SQL statement but I'm having trouble. I'm very inexperienced at SQL. Here's what I have:

SELECT * FROM [tbl_History] AS DetailQry
INNER JOIN
(SELECT [tbl_History].[HistAccountNo]
FROM [tbl_History] AS SubQry
WHERE SubQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
SubQry.[HistTaxDed] = TRUE AND
SubQry.[HistFundNo] = 4282
GROUP BY SubQry.[HistAccountNo]
HAVING SUM(SubQry.[HistAmt]) >= 1000) AS SummaryQry
ON DetailQry.[HistAccountNo] = SummaryQry.[HistAccountNo]
WHERE DetailQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
DetailQry.[HistTaxDed] = TRUE AND
DetailQry.[HistFundNo] = 4282

I don't know if I'm using the "AS" alias names correctly and so far I am getting an error message: "The specified field 'HistAccountNo' could refer to more than one table listed in the FROM clause of your SQL statement."

Any help to get this code to work would be much appreciated.

Thanks.
ctdak
 
G

Gary Walter

(SELECT subQry.[HistAccountNo]

ctdak said:
I did a mock up in Access query view before creating the following SQL code. I
Click to expand...
created two queries and linked them - the results were exactly what I need for a
report.
Now I am trying to translate this into an SQL statement but I'm having trouble.
Click to expand...
I'm very inexperienced at SQL. Here's what I have:
SELECT * FROM [tbl_History] AS DetailQry
INNER JOIN
(SELECT [tbl_History].[HistAccountNo]
FROM [tbl_History] AS SubQry
WHERE SubQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
SubQry.[HistTaxDed] = TRUE AND
SubQry.[HistFundNo] = 4282
GROUP BY SubQry.[HistAccountNo]
HAVING SUM(SubQry.[HistAmt]) >= 1000) AS SummaryQry
ON DetailQry.[HistAccountNo] = SummaryQry.[HistAccountNo]
WHERE DetailQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
DetailQry.[HistTaxDed] = TRUE AND
DetailQry.[HistFundNo] = 4282

I don't know if I'm using the "AS" alias names correctly and so far I am getting an
Click to expand...
error message: "The specified field 'HistAccountNo' could refer to more than one
table listed in the FROM clause of your SQL statement."
 
J

John Spencer (MVP)

From what I can see you might be able to do this a bit differently and get the
same results.

SELECT *
FROM [tbl_History] AS DetailQry
WHERE DetailQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
DetailQry.[HistTaxDed] = TRUE AND
DetailQry.[HistFundNo] = 4282 AND
DetailQry.[HistAccountNo] IN
(SELECT SubQry.[HistAccountNo]
FROM [tbl_History] AS SubQry
WHERE
SubQry.[HistReceiptDate] BETWEEN #06/01/2003# AND #04/30/2004# AND
SubQry.[HistTaxDed] = TRUE AND
SubQry.[HistFundNo] = 4282
GROUP BY SubQry.[HistAccountNo]
HAVING SUM(SubQry.[HistAmt]) >= 1000)

The problem you had was that you did not use the Alias of SubQry in the Select
statement of the inner join subquery.
Also, depending on which version of Access you are using, you may or may not be
able to use [] within the subquery. Luckily, they are not required since all
your tables, aliases, and fieldnames do not contain spaces or other "special" characters.

SELECT *
FROM tbl_History AS DetailQry
INNER JOIN
(SELECT SubQry.HistAccountNo <<<-----
FROM tbl_History AS SubQry
WHERE SubQry.HistReceiptDate
BETWEEN #06/01/2003# AND #04/30/2004# AND
SubQry.HistTaxDed = TRUE AND
SubQry.HistFundNo = 4282
GROUP BY SubQry.HistAccountNo
HAVING SUM(SubQry.HistAmt) >= 1000) AS SummaryQry
ON DetailQry.HistAccountNo = SummaryQry.HistAccountNo
WHERE DetailQry.HistReceiptDate
BETWEEN #06/01/2003# AND #04/30/2004# AND
DetailQry.HistTaxDed = TRUE AND
DetailQry.HistFundNo = 4282



Gary said:
(SELECT subQry.[HistAccountNo]
Click to expand...

ctdak said:
I did a mock up in Access query view before creating the following SQL code. I
Click to expand...
created two queries and linked them - the results were exactly what I need for a
report.
Now I am trying to translate this into an SQL statement but I'm having trouble.
Click to expand...
I'm very inexperienced at SQL. Here's what I have:
SELECT * FROM [tbl_History] AS DetailQry
INNER JOIN
(SELECT [tbl_History].[HistAccountNo]
FROM [tbl_History] AS SubQry
WHERE SubQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
SubQry.[HistTaxDed] = TRUE AND
SubQry.[HistFundNo] = 4282
GROUP BY SubQry.[HistAccountNo]
HAVING SUM(SubQry.[HistAmt]) >= 1000) AS SummaryQry
ON DetailQry.[HistAccountNo] = SummaryQry.[HistAccountNo]
WHERE DetailQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
DetailQry.[HistTaxDed] = TRUE AND
DetailQry.[HistFundNo] = 4282

I don't know if I'm using the "AS" alias names correctly and so far I am getting an
Click to expand...
error message: "The specified field 'HistAccountNo' could refer to more than one
table listed in the FROM clause of your SQL statement."
Any help to get this code to work would be much appreciated.

Thanks.
ctdak
Click to expand...
Click to expand...
 
D

Dale Fye

ctdak,

Glad that you and Gary got this far. I've been a little preoccupied. I
think you should be able to do it as follows:

Dim strSQL as string
strSQL = "SELECT * " _
& "FROM [tbl_History] AS DetailQry " _
& "INNER JOIN " _
& "(SELECT [HistAccountNo] " _
& " FROM [tbl_History] " _
& "WHERE ([HistReceiptDate] " _
& "BETWEEN #06/01/2003# AND #04/30/2004#) " _
& " AND [HistTaxDed] = -1 " _
& " AND [HistFundNo] = 4282 " _
& "GROUP BY [HistAccountNo] " _
& "HAVING SUM([HistAmt]) >= 1000) AS SummaryQry " _
& " ON DetailQry.[HistAccountNo] = SummaryQry.[HistAccountNo] "
_
& "WHERE (DetailQry.[HistReceiptDate] " _
& "BETWEEN #06/01/2003# AND #04/30/2004#) " _
& " AND DetailQry.[HistTaxDed] = -1 " _
& " AND DetailQry.[HistFundNo] = 4282 " _
& " ORDER BY DetailQry.HistAccountNo, DetailQry.HistReceiptDate"
me.recordsource = strSQL

HTH
Dale

ctdak said:
I did a mock up in Access query view before creating the following SQL
Click to expand...
code. I created two queries and linked them - the results were exactly what
I need for a report.
Now I am trying to translate this into an SQL statement but I'm having
Click to expand...
trouble. I'm very inexperienced at SQL. Here's what I have:
SELECT * FROM [tbl_History] AS DetailQry
INNER JOIN
(SELECT [tbl_History].[HistAccountNo]
FROM [tbl_History] AS SubQry
WHERE SubQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
SubQry.[HistTaxDed] = TRUE AND
SubQry.[HistFundNo] = 4282
GROUP BY SubQry.[HistAccountNo]
HAVING SUM(SubQry.[HistAmt]) >= 1000) AS SummaryQry
ON DetailQry.[HistAccountNo] = SummaryQry.[HistAccountNo]
WHERE DetailQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
DetailQry.[HistTaxDed] = TRUE AND
DetailQry.[HistFundNo] = 4282

I don't know if I'm using the "AS" alias names correctly and so far I am
Click to expand...
getting an error message: "The specified field 'HistAccountNo' could refer
to more than one table listed in the FROM clause of your SQL statement."
 
C

ctdak

John,

Your "a genius". Well, at least your suggestion here of removing the subquery from the INNER JOIN ... ON construct and placing it after the IN predicate instead works and gives me the results I need. This was the first construct that worked for me, so thanks a bunch.

As I said to Gary, it's people like you that make this forum extremely helpful to those less knowledgeable, like me.

By the way, your thoughts on why I was getting an error using the original construct were not correct. I tried removing all the square brackets and using the SubQry alias where you indicated, but those things did not change the erroe message I was getting.

ctdak


John Spencer (MVP) said:
From what I can see you might be able to do this a bit differently and get the
same results.

SELECT *
FROM [tbl_History] AS DetailQry
WHERE DetailQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
DetailQry.[HistTaxDed] = TRUE AND
DetailQry.[HistFundNo] = 4282 AND
DetailQry.[HistAccountNo] IN
(SELECT SubQry.[HistAccountNo]
FROM [tbl_History] AS SubQry
WHERE
SubQry.[HistReceiptDate] BETWEEN #06/01/2003# AND #04/30/2004# AND
SubQry.[HistTaxDed] = TRUE AND
SubQry.[HistFundNo] = 4282
GROUP BY SubQry.[HistAccountNo]
HAVING SUM(SubQry.[HistAmt]) >= 1000)

The problem you had was that you did not use the Alias of SubQry in the Select
statement of the inner join subquery.
Also, depending on which version of Access you are using, you may or may not be
able to use [] within the subquery. Luckily, they are not required since all
your tables, aliases, and fieldnames do not contain spaces or other "special" characters.

SELECT *
FROM tbl_History AS DetailQry
INNER JOIN
(SELECT SubQry.HistAccountNo <<<-----
FROM tbl_History AS SubQry
WHERE SubQry.HistReceiptDate
BETWEEN #06/01/2003# AND #04/30/2004# AND
SubQry.HistTaxDed = TRUE AND
SubQry.HistFundNo = 4282
GROUP BY SubQry.HistAccountNo
HAVING SUM(SubQry.HistAmt) >= 1000) AS SummaryQry
ON DetailQry.HistAccountNo = SummaryQry.HistAccountNo
WHERE DetailQry.HistReceiptDate
BETWEEN #06/01/2003# AND #04/30/2004# AND
DetailQry.HistTaxDed = TRUE AND
DetailQry.HistFundNo = 4282



Gary said:
(SELECT subQry.[HistAccountNo]
Click to expand...

ctdak said:
I did a mock up in Access query view before creating the following SQL code. I
Click to expand...
created two queries and linked them - the results were exactly what I need for a
report.
Now I am trying to translate this into an SQL statement but I'm having trouble.
Click to expand...
I'm very inexperienced at SQL. Here's what I have:
SELECT * FROM [tbl_History] AS DetailQry
INNER JOIN
(SELECT [tbl_History].[HistAccountNo]
FROM [tbl_History] AS SubQry
WHERE SubQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
SubQry.[HistTaxDed] = TRUE AND
SubQry.[HistFundNo] = 4282
GROUP BY SubQry.[HistAccountNo]
HAVING SUM(SubQry.[HistAmt]) >= 1000) AS SummaryQry
ON DetailQry.[HistAccountNo] = SummaryQry.[HistAccountNo]
WHERE DetailQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
DetailQry.[HistTaxDed] = TRUE AND
DetailQry.[HistFundNo] = 4282

I don't know if I'm using the "AS" alias names correctly and so far I am getting an
Click to expand...
error message: "The specified field 'HistAccountNo' could refer to more than one
table listed in the FROM clause of your SQL statement."
Any help to get this code to work would be much appreciated.

Thanks.
ctdak
Click to expand...
Click to expand...
Click to expand...
 
C

ctdak

Dale,

Thanks for coming back into the discussion. I received an answer in this thread from John Spencer earlier today and I tried his suggestion and it worked. He had me use a different construct, omitting the join altogether. Yours may work also, but I am now using his and it is giving me the desired results.

Thanks anyway. I really appreciate you taking the time to help with detailed answers.

ctdak


Dale Fye said:
ctdak,

Glad that you and Gary got this far. I've been a little preoccupied. I
think you should be able to do it as follows:

Dim strSQL as string
strSQL = "SELECT * " _
& "FROM [tbl_History] AS DetailQry " _
& "INNER JOIN " _
& "(SELECT [HistAccountNo] " _
& " FROM [tbl_History] " _
& "WHERE ([HistReceiptDate] " _
& "BETWEEN #06/01/2003# AND #04/30/2004#) " _
& " AND [HistTaxDed] = -1 " _
& " AND [HistFundNo] = 4282 " _
& "GROUP BY [HistAccountNo] " _
& "HAVING SUM([HistAmt]) >= 1000) AS SummaryQry " _
& " ON DetailQry.[HistAccountNo] = SummaryQry.[HistAccountNo] "
_
& "WHERE (DetailQry.[HistReceiptDate] " _
& "BETWEEN #06/01/2003# AND #04/30/2004#) " _
& " AND DetailQry.[HistTaxDed] = -1 " _
& " AND DetailQry.[HistFundNo] = 4282 " _
& " ORDER BY DetailQry.HistAccountNo, DetailQry.HistReceiptDate"
me.recordsource = strSQL

HTH
Dale

ctdak said:
I did a mock up in Access query view before creating the following SQL
Click to expand...
code. I created two queries and linked them - the results were exactly what
I need for a report.
Now I am trying to translate this into an SQL statement but I'm having
Click to expand...
trouble. I'm very inexperienced at SQL. Here's what I have:
SELECT * FROM [tbl_History] AS DetailQry
INNER JOIN
(SELECT [tbl_History].[HistAccountNo]
FROM [tbl_History] AS SubQry
WHERE SubQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
SubQry.[HistTaxDed] = TRUE AND
SubQry.[HistFundNo] = 4282
GROUP BY SubQry.[HistAccountNo]
HAVING SUM(SubQry.[HistAmt]) >= 1000) AS SummaryQry
ON DetailQry.[HistAccountNo] = SummaryQry.[HistAccountNo]
WHERE DetailQry.[HistReceiptDate]
BETWEEN #06/01/2003# AND #04/30/2004# AND
DetailQry.[HistTaxDed] = TRUE AND
DetailQry.[HistFundNo] = 4282

I don't know if I'm using the "AS" alias names correctly and so far I am
Click to expand...
getting an error message: "The specified field 'HistAccountNo' could refer
to more than one table listed in the FROM clause of your SQL statement."
Any help to get this code to work would be much appreciated.

Thanks.
ctdak
Click to expand...
Click to expand...
 

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

SQL Statement 2
Sequential values in access table 0
Creating a "grid" of data 3
rst.findfirst not working with two conditions 6
Help with Crosstab query and report 6
DoCmd.RunSQL HELP!!! I will not work! 1
SQL and design mode are different 5
SQL not importing records from ACCDB 2

Top