Report with different record source - possible??? (1 Viewer)

[phinix]

Is it possible to create a report that will have different record source depeneds on a combo field?
If user picks "english" for example and clicks the button, it will open the report that is based on eng_query, if he picks biology, same report will open based on bio_query...

How can I do that?

 

[vbaInet]

Is there an ID field that would determine what record should be displayed?

 

[phinix]

Is there an ID field that would determine what record should be displayed?

Hello again!

Let me explain a bit more, you already saw my database a bit so it should be easy I guess: I have those subjects, I have crosstab query that shows residuals for each subject. Now I need to generate report that shows those residuals in specific way: report shows it only for one subject, columns are: year, level, girls, biys, all. Simple, right?
Now thing is my all subject residual crosstab query has year, level, gender, art_resisuals, biology_residuals, etc...
So I need to do pivot again (crosstab query form that crosstab query to pivot that gender to girls and boys, but now because you can choose only one valuse field in crosstab, I pick one subject, so as value I got girls and boys art residuals. This way I got 25 crosstab queries for each subject.
Now,I could create 25 reports for each, but I want to make it better
I would like to have one report that will pick a query for it.

 

[vbaInet]

Hello there

The problem with your pivot is that the subjects are not dynamic so if you want it to be then you can do so by changing the sql of the querydef by code. Have a look at this thread, my post #2. So what you would need to amend is the PIVOT part of the sql string. Create a copy of your original query and use the copy for this.

Report wise you need just one report.

1. Set the Record Source of the report to the cloned query
2. Open the report in design view and change the Control Sources of the relevant textboxes in code
3. Then open the report as you would normally

I stated the problem with making design changes in that thread so for closing the report you would need a custom close button if you don't want the message to pop-up.

Another way would be to ensure that the dynamic field name has the same name for the textbox's control source. So what you do is create another query based on the cloned query (mentioned above) and use an alias. That alias field will now be the textbox's Control Source and there will be no need to make design changes.

 

[phinix]

I think I know what you mean, but can you please take a look on this:
I came up with this, but of course it doesn't work
I made a button on the form next to combo box with list of subjects. Now, under on click I wrote this:

Private Sub Open_Report_Button_Click()
On Error GoTo Err_Open_Report_Button_Click

    Dim stDocName As String
    Dim ReportSource As String
    
    Select Case Form_select_options_Resisduals.Department = "Art"
    ReportSource = Query_Residuals_Departments_F&M_Crosstab_art
        
    Report_residuals.RecordSource = ReportSource

    stDocName = "Report_residuals"
    DoCmd.OpenReport stDocName, acPreview

Exit_Open_Report_Button_Click:
    Exit Sub

Err_Open_Report_Button_Click:
    MsgBox Err.Description
    Resume Exit_Open_Report_Button_Click
    
End Sub

I'm totally new to VB so this is all wrong, but let me explain what I'm trying to achieve.
I'm trying to make this button open "Report_residuals" report but set its record source as a value form case function, something like, in case combobox "Department" on "Form_select_options_Resisduals" form is "Art", open report with record source as query called "Query_Residuals_Departments_F&M_Crosstab_art".

How can I make it work? Please help!

 

[vbaInet]

Yes, I understood what you were trying to do which was prompted my response. First of all create a blank query, this is what you will use. Re-read what is in this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=195515

... post #2 and create the first two steps as explained then show us what you've done. To get the SQL string of your query, just go into SQL VIEW of the crosstab query.

 

[phinix]

OK, so you say it better to send sql query every time, instead of pulling it via its name, got it.

Where should I add this "QueryDef" code? To report or form?

 

[vbaInet]

You add it to the click event of the button (on the form).

 

[phinix]

You add it to the click event of the button (on the form).

OK.
So, here is what I got so far, button code:

Private Sub Open_Report_Button_Click()
On Error GoTo Err_Open_Report_Button_Click

    Dim stDocName As String

    Dim qdf As QueryDef
    
    Const sqlString = "SELECT * FROM [TableName] "
    
    Set qdf = CurrentDb.QueryDefs("Query_Residuals_Departments_F&M_Crosstab_option")
    
    qdf.SQL = sqlString & "WHERE " & IIf(Forms!Form_select_options_Residuals.Department = "Art", )
    
    
    stDocName = "Report_residuals"
    DoCmd.OpenReport stDocName, acPreview

Exit_Open_Report_Button_Click:
    Exit Sub

Err_Open_Report_Button_Click:
    MsgBox Err.Description
    Resume Exit_Open_Report_Button_Click
    
End Sub

and here is my crosstab query:

TRANSFORM First([Query_Residuals_Departments_F&M].[AvgOfart-res]) AS [FirstOfAvgOfart-res]
SELECT [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year
FROM [Query_Residuals_Departments_F&M]
GROUP BY [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year
ORDER BY [Query_Residuals_Departments_F&M].Year DESC , [Query_Residuals_Departments_F&M].Level
PIVOT [Query_Residuals_Departments_F&M].Gender;

Now, based on my query, should this [TableName] actually be my "Query_Residuals_Departments_F&M"? Cause this is the source of crosstab query.
I haven't finished IIF sattement there - what should I do next? First IIF expression is if the combobox=Art, now true part should be this query with "AvgOfart-res" field, if not then second IIF would be biology and "AvgOfbio-res" field, ..etc.

What should I do next?

 

[vbaInet]

What was given in that link was actually generic and should have been amended to your code. In any case, show me a screen shot of the output of the cross tab query.

 

[phinix]

Can I do sth like this?
Create two constant sql strings, first part transform, second rest of the sql statement and middle part based on iif:

Const sqlString = "TRANSFORM "
    Const sqlString2 = "SELECT [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year"
FROM [Query_Residuals_Departments_F&M]
GROUP BY [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year
ORDER BY [Query_Residuals_Departments_F&M].Year DESC , [Query_Residuals_Departments_F&M].Level
PIVOT [Query_Residuals_Departments_F&M].Gender;"
    
    Set qdf = CurrentDb.QueryDefs("Query_Residuals_Departments_F&M")
    
    qdf.SQL = sqlString & IIf(Forms!Form_select_options_Residuals.Department = "Art",
    First([Query_Residuals_Departments_F&M].[AvgOfart-res]) AS [FirstOfAvgOfart-res] & sqlString2, IIF...
    )

 

[vbaInet]

You don't need the IIF() part. That was related to that post. Let me a screen shot of the datasheet of the query.

 

[phinix]

You don't need the IIF() part. That was related to that post. Let me a screen shot of the datasheet of the query.

No problem, this one is art query:
Code:

TRANSFORM First([Query_Residuals_Departments_F&M].[AvgOfart-res]) AS [FirstOfAvgOfart-res]
SELECT [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year
FROM [Query_Residuals_Departments_F&M]
GROUP BY [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year
ORDER BY [Query_Residuals_Departments_F&M].Year DESC , [Query_Residuals_Departments_F&M].Level
PIVOT [Query_Residuals_Departments_F&M].Gender;

..and screenshot attached.

 

[vbaInet]

Where are you getting the list of subjects that is displayed in the combo box? Are they values you typed in or it's coming from a table?

 

[phinix]

Where are you getting the list of subjects that is displayed in the combo box? Are they values you typed in or it's coming from a table?

They come from simple query:

SELECT [imported data - RAW].Subject
FROM [imported data - RAW]
GROUP BY [imported data - RAW].Subject
ORDER BY [imported data - RAW].Subject;

 

[vbaInet]

Let's see the original query, that is this query - Query_Residuals_Departments_F&M

 

[phinix]

Let's see the original query, that is this query - Query_Residuals_Departments_F&M

No problem:
Code:

SELECT Query_Residuals_ALL.Year, Query_Residuals_ALL.Level, Query_Residuals_ALL.Gender, Query_Residuals_ALL.school_year, Avg(Query_Residuals_ALL.[art-res]) AS [AvgOfart-res], Avg(Query_Residuals_ALL.[bio-res]) AS [AvgOfbio-res], Avg(Query_Residuals_ALL.[bus-res]) AS [AvgOfbus-res], Avg(Query_Residuals_ALL.[che-res]) AS [AvgOfche-res], Avg(Query_Residuals_ALL.[cla-res]) AS [AvgOfcla-res], Avg(Query_Residuals_ALL.[com-res]) AS [AvgOfcom-res], Avg(Query_Residuals_ALL.[dra-res]) AS [AvgOfdra-res], Avg(Query_Residuals_ALL.[eco-res]) AS [AvgOfeco-res], Avg(Query_Residuals_ALL.[eng-res]) AS [AvgOfeng-res], Avg(Query_Residuals_ALL.[esol-res]) AS [AvgOfesol-res], Avg(Query_Residuals_ALL.[fre-res]) AS [AvgOffre-res], Avg(Query_Residuals_ALL.[gae-res]) AS [AvgOfgae-res], Avg(Query_Residuals_ALL.[geo-res]) AS [AvgOfgeo-res], Avg(Query_Residuals_ALL.[his-res]) AS [AvgOfhis-res], Avg(Query_Residuals_ALL.[ita-res]) AS [AvgOfita-res], Avg(Query_Residuals_ALL.[lat-res]) AS [AvgOflat-res], Avg(Query_Residuals_ALL.[mat-res]) AS [AvgOfmat-res], Avg(Query_Residuals_ALL.[med-res]) AS [AvgOfmed-res], Avg(Query_Residuals_ALL.[mod-res]) AS [AvgOfmod-res], Avg(Query_Residuals_ALL.[mus-res]) AS [AvgOfmus-res], Avg(Query_Residuals_ALL.[pe-res]) AS [AvgOfpe-res], Avg(Query_Residuals_ALL.[phy-res]) AS [AvgOfphy-res], Avg(Query_Residuals_ALL.[pro-res]) AS [AvgOfpro-res], Avg(Query_Residuals_ALL.[re-res]) AS [AvgOfre-res], Avg(Query_Residuals_ALL.[spa-res]) AS [AvgOfspa-res]
FROM Query_Residuals_ALL
GROUP BY Query_Residuals_ALL.Year, Query_Residuals_ALL.Level, Query_Residuals_ALL.Gender, Query_Residuals_ALL.school_year
ORDER BY Query_Residuals_ALL.Year DESC;

and screenshot attached - cut off some part, but you can see how it goes - basically all subject results.

 

[vbaInet]

So your subjects do not have an ID?

 

[phinix]

So your subjects do not have an ID?

Nope, they come from table in Subject field. Each student has few rows with level of exam he took, subject it was, results, etc...

 

[vbaInet]

In the last query you posted, I would want to code it so it refers to the 3 letter unique ID of each subject. For example, I could do this:

"Avg(Query_Residuals_ALL.[" & "[COLOR=Red][B]art[/B][/COLOR]" & "-res]"

Where art is that 3 letter that uniquely identifies art. You can't use Left("Subject_Name", 3) because you have subjects like English Language and English Literature, hence, trying to use the first 3 letters would mean those two subjects would end up being "eng" which will cause an error.