Summary & detail

[Guest]

How do you create a query that will give both summary results (grouped by one field) and the records that created the summary? In other words, I need to see both the detail that creates the summary and the results of the summary at the same time.

 

[Duane Hookom]

You could create a totals query and then a new union query based on your
detail records unioned with the totals/summary records.

--
Duane Hookom
MS Access MVP

How do you create a query that will give both summary results (grouped by

one field) and the records that created the summary? In other words, I need
to see both the detail that creates the summary and the results of the
summary at the same time.

 

[Guest]

Thanks for the answer Duane. The Union query would work for this. However, when I read your answer I realized I had misstated my question. I actually want the detail records only, but they have to be based on a summary total. So, how do I run the summary query then get the detail records only behind that query?

ctdak

 

[Duane Hookom]

If I understand correctly I don't think this is possible in a query.
Subdatasheets (which I never use) might provide some functionality. I would
create a form based on your totals query. You can then create event code
(say on a double-click) to open a form displaying detailed records.

--
Duane Hookom
MS Access MVP

Thanks for the answer Duane. The Union query would work for this.

However, when I read your answer I realized I had misstated my question. I
actually want the detail records only, but they have to be based on a
summary total. So, how do I run the summary query then get the detail
records only behind that query?

 

[Dale Fye]

ctdak,

I'm assume by your statement, "I actually want the detail records only, but
they have to be based on a summary total.", that what you want is to run the
summary query, with a Having clause to restrict the results to some subset
of the data where the summary totals meet some criteria. To do this, create
your summary query, with no criteria. Then, create a second query that
links to the first query based on whatever field you want, and add the
criteria you want to the query. I'll do it in a single query that contains
a subquery, as an example.

Select S.StoreID, S.ProductID, S.Quantity, S.SalesDate, S.Cost
FROM Sales S
INNER JOIN
(SELECT S1.StoreID, SUM(S1.Cost) as JanSales
FROM Sales S1
WHERE S1.SalesDate Between #1/1/04# and #1/31/04#
GROUP BY S1.StoreID) as S2
ON S.StoreID = S2.StoreID
WHERE S2.JanSales < 30,000
ORDER BY S.StoreID, S.ProductID, S.SalesDate

This query would give you the StoreID, ProductID, Quantity sold, sales date
and cost of each item sold at each store that failed to meet the monthly
goal of $30,000 worth of sales.

Hope this is along the lines of what you were looking for. Your question
was pretty vague and contained no table information.

Dale

Thanks for the answer Duane. The Union query would work for this.

However, when I read your answer I realized I had misstated my question. I
actually want the detail records only, but they have to be based on a
summary total. So, how do I run the summary query then get the detail
records only behind that query?

 

[Guest]

Dale - Thanks for taking the time to give a detailed response. I don't think this is quite where I'm headed though, which is my fault as I didn't give a detailed enough request. I hope I didn't waste too much of your time. (I tend to think that if a request is too verbose, then it will be ignored.) Let me be a bit more specific.

I am trying to use a VBA-coded Select query to create a RecordSource for a report. The records of the table in question contain accounting entries. Each entry/record has an account number, a posting date, a dollar amount, and other fields. My report needs to list the contents of all records for each account whose entries/records when added up are >= a fixed dollar amount over a 12 month period. A total query using Sum and Group By can easily show me which accounts meet this requirement, but I need to pull out all the records that meet the requirement. The summary by itself is not adequate. I need to list all records that make up the summary results. In turn, the report can easily Sum the dollar amounts.

This is what I need to accomplish. There has to be a way to do something like this.

ctdak

 

[Gary Walter]

Hi ctdak,

PMFBI

You have a totals query that gives you
account numbers that meet requirement.

I would think you would use SQL similar
to Dale's where you join all fields from
original table to totals subquery on account
number, applying same date criteria as used
in totals query.

SELECT
t1.AcctNum,
t1.PostDate,
t1.Amt,
t1.OtherField
FROM originaltable As t1
INNER JOIN
(SELECT <-- start of totals subquery
t2.AcctNum
FROM
originaltable As t2
WHERE
t2.PostDate
BETWEEN #1/1/2003# AND #12/31/2003#
GROUP BY t2.AcctNum
HAVING
Sum(t2.Amt) >= 30000) As q1 <-- end
ON
t1.AcctNum = q1.AcctNum
WHERE
t1.PostDate
BETWEEN #1/1/2003# AND #12/31/2003#;

Apologies again for butting in, especially
if I am wrong.

Good luck,

Gary Walter

Dale - Thanks for taking the time to give a detailed response. I don't think this

is quite where I'm headed though, which is my fault as I didn't give a detailed
enough request. I hope I didn't waste too much of your time. (I tend to think that
if a request is too verbose, then it will be ignored.) Let me be a bit more
specific.

I am trying to use a VBA-coded Select query to create a RecordSource for a report.

The records of the table in question contain accounting entries. Each entry/record
has an account number, a posting date, a dollar amount, and other fields. My report
needs to list the contents of all records for each account whose entries/records when
added up are >= a fixed dollar amount over a 12 month period. A total query using
Sum and Group By can easily show me which accounts meet this requirement, but I need
to pull out all the records that meet the requirement. The summary by itself is not
adequate. I need to list all records that make up the summary results. In turn, the
report can easily Sum the dollar amounts.

 

[Guest]

Gary,

Fantastic! You were right on, so thanks for "butting in". Your modification of Dale's sample SQL code enabled me to do exactly what I needed. I did a mock up in the Access query view, creating the two queries and linking them - the results are exactly the detailed records I need for my report.

However, I am having some trouble translating this into an SQL statement following your sample code. I'm very inexperienced at SQL. Here's what I have:

Me.RecordSource = "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."

As you can see I have a couple more criteria to be satisfied than I mentioned in my earlier messages, but I don't think they are the problem.

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

Thanks.
ctdak

 

[Gary Walter]

:

Here's what I have:

Me.RecordSource = "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."Hi ctdak,

Thank you for the update.

You probably found it, but I believe you
just need to change "line 3" above to:

"(SELECT SubQry.[HistAccountNo]" & _

I believe that was it.

Good luck,

Gary Walter

 

[Guest]

:

Here's what I have:

Me.RecordSource = "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."Hi ctdak,

Thank you for the update.

You probably found it, but I believe you
just need to change "line 3" above to:

"(SELECT SubQry.[HistAccountNo]" & _

I believe that was it.

Good luck,

Gary Walter

Gary,

Unfortunately, that didn't fix it. I get the same error still.

I don't understand how you can put SubQry in line 3 when it isn't defined until line 4.

Any other ideas on the error?

ctdak

 

[Gary Walter]

Hi ctdak,

"Order of an Access query"
1) Join
2) Where
3) Select

Actually, I would have thought it should have
worked if you just leave off the "tablename"
before [HistAccountNo] in the subquery
(and just *not* work if you used
"tblHistory.HistAccountNo")

"(SELECT [HistAccountNo]" & _

If you are still getting error, then you may
have to bite the bullet and type out your "*"
explicitly.

This is what I would do in your situation
(you certainly don't have to follow this advise)

I would create the subquery in query designer
saving as "SummaryQry," including giving tbl_History
an alias of "SubQry."

Then I would bring tbl_History and "SummaryQry"
into another query design. I would give tblHistory
an alias of DetailQry and join the two on HistAccountNo.

Then I would double-click on top of DetailQry table
so all fields are selected, then drag-and-drop selection
down to Field row, then set Criteria row under
..[HistReceiptDate], [HistTaxDed], [HistFundNo].

The point being to make sure this works, plus leading
to the full SQL will end up using in code.

So...if this works, then I would go back to "SummaryQry"
design and copy SQL. Then go to SQL view in the second
query and paste in the "SummaryQry-SQL" in place of
"SummaryQry" in the Select clause (wrapping in parentheses
and giving subquery alias of "SummaryQry").

Then if this works, I would edit this SQL so get only
so many chars per line, then copy and paste in code
and tidy up with "&'s and _".

That's just what I'd do, you are welcome to disregard.

If you start a new thread, I will keep out of it, so you
can get advice from someone who may know better
than me (I regret now that I responded to your other
thread).

Good luck,

Gary Walter

:

Here's what I have:

Me.RecordSource = "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."Hi ctdak,

Thank you for the update.

You probably found it, but I believe you
just need to change "line 3" above to:

"(SELECT SubQry.[HistAccountNo]" & _

I believe that was it.

Good luck,

Gary Walter

Gary,

Unfortunately, that didn't fix it. I get the same error still.

I don't understand how you can put SubQry in line 3 when it isn't defined until line 4.

Any other ideas on the error?

ctdak

 

[Guest]

Gary,

I'm just responding to your first two suggestions right now.

- I removed the table name in the second SELECT and I still
get the same error message.
- I typed out all the field names in the first Select where I had "*".
This results in a popup box "Enter Parameter Value" for each field!!
(I have no idea why this is happening either.)

I just can't get anything to work at present. I will probably follow your advise and work through your suggested procedure in query designer. Haven't had time yet.

I really appreciate your help. The only reason I started another thread on this is because I was unsure if you would continue to reply, although other input might also be helpful. I may start another thread again at some point since you answered the new one.

Thanks again,
ctdak

Hi ctdak,

"Order of an Access query"
1) Join
2) Where
3) Select

Actually, I would have thought it should have
worked if you just leave off the "tablename"
before [HistAccountNo] in the subquery
(and just *not* work if you used
"tblHistory.HistAccountNo")

"(SELECT [HistAccountNo]" & _

If you are still getting error, then you may
have to bite the bullet and type out your "*"
explicitly.

This is what I would do in your situation
(you certainly don't have to follow this advise)

I would create the subquery in query designer
saving as "SummaryQry," including giving tbl_History
an alias of "SubQry."

Then I would bring tbl_History and "SummaryQry"
into another query design. I would give tblHistory
an alias of DetailQry and join the two on HistAccountNo.

Then I would double-click on top of DetailQry table
so all fields are selected, then drag-and-drop selection
down to Field row, then set Criteria row under
..[HistReceiptDate], [HistTaxDed], [HistFundNo].

The point being to make sure this works, plus leading
to the full SQL will end up using in code.

So...if this works, then I would go back to "SummaryQry"
design and copy SQL. Then go to SQL view in the second
query and paste in the "SummaryQry-SQL" in place of
"SummaryQry" in the Select clause (wrapping in parentheses
and giving subquery alias of "SummaryQry").

Then if this works, I would edit this SQL so get only
so many chars per line, then copy and paste in code
and tidy up with "&'s and _".

That's just what I'd do, you are welcome to disregard.

If you start a new thread, I will keep out of it, so you
can get advice from someone who may know better
than me (I regret now that I responded to your other
thread).

Good luck,

Gary Walter

:
<snip>
Here's what I have:

Me.RecordSource = "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."

Hi ctdak,

Thank you for the update.

You probably found it, but I believe you
just need to change "line 3" above to:

"(SELECT SubQry.[HistAccountNo]" & _

I believe that was it.

Good luck,

Gary Walter

Gary,

Unfortunately, that didn't fix it. I get the same error still.

I don't understand how you can put SubQry in line 3 when it isn't defined until line 4.

Any other ideas on the error?

ctdak

 

[Guest]

Gary,

Before getting to your query designer experiment, I received another response to my second thread. Check it out. It is from John Spencer and his suggested modification to replace the INNER JOIN ... ON with an IN predicate works!! His may not be the only solution but it's the first one I could get to work and produce the results I need. I think I now have this one licked.

Thanks a bunch for your lengthy and useful responses. This forum is extremely helpful because people like you are willing to spend time and effort helping those of us less knowledgable.

ctdak

Hi ctdak,

"Order of an Access query"
1) Join
2) Where
3) Select

Actually, I would have thought it should have
worked if you just leave off the "tablename"
before [HistAccountNo] in the subquery
(and just *not* work if you used
"tblHistory.HistAccountNo")

"(SELECT [HistAccountNo]" & _

If you are still getting error, then you may
have to bite the bullet and type out your "*"
explicitly.

This is what I would do in your situation
(you certainly don't have to follow this advise)

I would create the subquery in query designer
saving as "SummaryQry," including giving tbl_History
an alias of "SubQry."

Then I would bring tbl_History and "SummaryQry"
into another query design. I would give tblHistory
an alias of DetailQry and join the two on HistAccountNo.

Then I would double-click on top of DetailQry table
so all fields are selected, then drag-and-drop selection
down to Field row, then set Criteria row under
..[HistReceiptDate], [HistTaxDed], [HistFundNo].

The point being to make sure this works, plus leading
to the full SQL will end up using in code.

So...if this works, then I would go back to "SummaryQry"
design and copy SQL. Then go to SQL view in the second
query and paste in the "SummaryQry-SQL" in place of
"SummaryQry" in the Select clause (wrapping in parentheses
and giving subquery alias of "SummaryQry").

Then if this works, I would edit this SQL so get only
so many chars per line, then copy and paste in code
and tidy up with "&'s and _".

That's just what I'd do, you are welcome to disregard.

If you start a new thread, I will keep out of it, so you
can get advice from someone who may know better
than me (I regret now that I responded to your other
thread).

Good luck,

Gary Walter

:
<snip>
Here's what I have:

Me.RecordSource = "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."

Hi ctdak,

Thank you for the update.

You probably found it, but I believe you
just need to change "line 3" above to:

"(SELECT SubQry.[HistAccountNo]" & _

I believe that was it.

Good luck,

Gary Walter

Gary,

Unfortunately, that didn't fix it. I get the same error still.

I don't understand how you can put SubQry in line 3 when it isn't defined until line 4.

Any other ideas on the error?

ctdak