How do I create a query that will create a table in sub reports

[AjilonSS]

I have a report with sub reports. I currently have a table with a query that filters a specific field. How do I go about creating another query filter to create a table in the report sub reports?

Instead of me manually entering 25 new tables (sub reports), how can I create a query to filter the sub reports and turn it into a table?

Is this possible?

 

[dhookom]

Reports don't create tables. Reports display values from tables/queries/expressions.

I think you need to be more specific regarding your requirements.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[AjilonSS]

dhookom,

Thanks for the reply. Would this be more clear?

I have been successful in using this report when running data through a simple report and having the code apply a filter...

The challenge I am now having is that I have a much more complex report, with sub-reports and all, where the filter approach does not work.

So, what I am trying to do now is apply the filter to the make table query - from there the report then uses the temp table and the problem of filtering via the report goes away...

What I cannot figure out how to do is apply the filter to the make table query...from there it will be easy to loop through the report create/distribution steps...

 

[dhookom]

You can use criteria within your maketable query.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[AjilonSS]

dhookom,

What criteria can I use in my make-table query. I entered [break stop] in the criteria and when I open the query make table the message "you are about to run a make-table query that will modify data in your table" I select ok then a Enter Parameter Value box pops-up. I enter a field in the table but no information appears. The table gets created but under the field it is empty. Any suggestions?

 

[dhookom]

[blue]"I enter a field in the table"[/blue]
A parameter expects a value to compare to a field, not a field.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[AjilonSS]

dhookom,

Your right my mistake.

What other criteria do you suggest I should use in my make-table query that will apply me to filter the make table query?

 

[dhookom]

How do I know what your criteria should be? I don't know if we are even talking about the same thing.

You can add a criteria to any query. Do you have any questions regarding adding criteria to queries?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[AjilonSS]

dhookom,

MY QUESTION IS I have a much more complex report, with sub-reports and all, where the filter approach DOES NOT WORK.

So, what I am trying to do now is apply the filter to the make table query - from there the report then uses the temp table and the problem of filtering via the report goes away...

What I cannot figure out how to do is apply the filter to the make table query...from there it will be easy to loop through the report create/distribution steps...

 

[AjilonSS]

dhookom,

Does this help you out in figuring out my issue?

Option Explicit

'****************************************************************
'This function will walk through a table and email the
'report, which is filtered using
'MS Outlook through the MS Access SendObject.
'
'This function assumes the report has the default printer
'set to PDF.
'****************************************************************

Public strInvoiceWhere As String

Function AJSendStaffingReport()
Dim headcounts As DAO.Database

'set table containing data source?
Dim Staffing_Report_ALL_NEW_BREAKS As DAO.Recordset

Set headcounts = Currentdb()
Set Staffing_Report_ALL_NEW_BREAKS = headcounts.OpenRecordset("Staffing_Report_ALL_NEW_BREAKS", _
dbOpenDynaset)

'If MsgBox("Do you want to create report" & Chr(13) & _ <put prompt back in later
' "to all receiptents using Microsoft Outlook?", 4) = 6 Then

With Staffing_Report_ALL_NEW_BREAKS
Do Until .EOF

'Create the report Filter <-how to replace in query
'used by the Report_Open event. <-how to replace report_open to define variable

strInvoiceWhere = "[hierarchy_code] = '" & ![hierarchy_code] & "'"

DoCmd.SetWarnings False
DoCmd.OpenQuery "Staffing_Report_ALL_NEW"
DoCmd.SetWarnings True


' create report as pdf with unique filename
'SendKeys "c:\z\Staffing_Report_" & ![hierarchy_code] & "_" & ![rvp_avp_last_name] & "_" & Format(Now(), "YYYYMMDD") & "~^sy", False
'DoCmd.OpenReport "_Staffing Report - COMBINED - Consolidated", acViewNormal, "", "", acHidden



' then attach both files and send
' DoCmd.SendObject acReport, "_Staffing Report - COMBINED - Consolidated", acFormatRTF, _
' ![test], , , "Weekly Open Requisition Report - " & Format(Now(), "mm/dd/yy"), ![hierarchy_name] & " THIS IS A TEST open requisition report attached - ARE YOU HAPPY???", False


.MoveNext
Loop
End With
' End If < remove comment later when prompt is put back in

Staffing_Report_ALL_NEW_BREAKS.Close ' close break list
'DONE with hierarchy pass