52 queries - 1 report

[mliggett]

I inherited a database and don't want to scrap it. There are 52 queries which work great to run weekly data. They want these 52 queries in a report format that can be formatted with individual names added to the top. Is there an easy way to create 1 report and let the user choose the query that creates that report?

Thank in advance,

mliggett

 

[dhookom]

Are all 52 queries really different or do they just return a different set of records from the same table or tables?

You can set a report's record source property in the On Open event of the report. However, I think this is the wrong solution.

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]

 

[mliggett]

The queries are from the same table. Each query includes a specific field (for example 52 weeks, week 1 = yes) they also include a parameter for [Enter Area].

 

[dhookom]

I would have one query rather than 52. Do you have fields with names like "Week1"? I expect all queries have the same parameter?

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]

 

[GingerR]

So you have 52 fields, named like "Week1", "Week2", etc.?

First, I highly suggest you normalize your database. See the links in my signature for information. You will have problems forever with this kind of setup. You should not have to have 52 queries and 52 reports for 52 fields. Highly inefficient.

That being said, here's a solution, tho again not my preference.

Instead of a query "prompting" you for an [Area], how about this instead: From a form, the user will pick an area, enter in a week number, hit a button and produce a report. All without using any of your queries. How's that?

Without details from you on table and field names, I've made some assumptions. I have a table called "Weeks", with fields "Area", "Week1", "Week2", "Week3", etc. If you have more information you want on your report, you'll have to tweak as necessary.

AREA combo box: First let's make a form that pulls that information for you. So make a new form; on it put a combo box which has a rowsource of a unique list of your Areas. So it will list for example:

cboArea
Loading Dock
Shop
Engineering

The ideal situation is that you have an existing table which lists each 'area' one time. If you don't, it would be a good idea to have one (normalization/relational database rules again). If there are only a few areas and they never or seldom change, you can make the combo box row source a VALUE list and just type them in yourself. In any case, use the wizard to make a combo box. Call it cboArea.

WEEK NUMBER text box: Then make a text box, and call it txtWeekNumber. In order to make sure the user enters something valid in it, you could
a) set the DEFAULT property: 1
b) set the VALIDATION RULE property: Between 1 And 52
c) set the VALIDATION TEXT property: Must be a value between 1 and 52!


BUTTON: Now put a button on your form. Cancel when the wizard starts up because we're going to write our own code. View PROPERTIES and in the button's NAME property, name it btnViewReport. In it's OnClick event, pick [Event Procedure] and hit the build button (the little button with three dots on it to the right). In that event, paste this code. What it does is take the Week Number the user puts on the form (i.e. 1 or 2 or 18), adds it to the word "Week" to end up with the field name you want (i.e. Week1 or Week2 or Week18), then calls that field a generic name "ReportWeek" which is the field you'll end up putting on the report.

    'First check to make sure the user selected an area
    If Me.cboArea = "" Or IsNull(Me.cboArea) Then
        MsgBox "Please choose an Area!", vbOKOnly, "Missing Info"
        Me.cboArea.SetFocus
        Exit Sub
    End If
    
    'Make sure txtWeekNumber is an integer
    If CInt(Me.txtWeekNumber) <> Me.txtWeekNumber Then
        MsgBox "Please enter an integer!", vbOKOnly, "Invalid Info"
        Me.txtWeekNumber.SetFocus
        Exit Sub
    End If
        
    'Create the recordsource for the Report
    DoCmd.SetWarnings False
    Dim strSQL As String
    strSQL = "Select [Area],[Week" & Me.txtWeekNumber & "] as ReportWeek from [Weeks] where [Area]='" & [cboArea] & "'"
    DoCmd.OpenReport "Report1", acViewDesign
    Reports!Report1.RecordSource = strSQL
    DoCmd.Close acReport, "Report1", acSaveYes
    DoCmd.OpenReport "Report1", acViewPreview
    
    DoCmd.SetWarnings True

REPORT: Now make a new report. In Design mode, in it's recordsource, put

SELECT Area, ReportWeek FROM Weeks;

This will give you the two fields you want. Again, if you want more, put them here. Bring all of the fields you want into the report. Save it as Report1. You can change the name later, and at that time you'll have to tweak the code above to replace "Report1" with whatever you name the report.

Close the report.

View the form in Form View. Pick an area and enter in a Week number. Click the button. The report will open in design view, set the recordsource to pull the correct field's data, close, then reopen in PREVIEW mode with the info you want.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mliggett]

I have more information.
I have a table and it is set up in this way....Item,Description,Frequency,MHRS,Shift, Area, PM_W1,PM_W2,PM_W3 ALL THE WAY TO PM_W52. I need a weekly report so if I choose PM_W1 (criteria=yes) and the Area (criteria=1) then I get the report that looks like this:

ITEM DESCRIPTION FREQ MHRS SHIFT AREA(for the area i selected) and PM_W1 (=yes)

So basically the user would select area hopefully from combo or list box and then either select FieldName (PM_W1-PM_W52 (with the criteria always being yes) and get a report that pulls all the above information from the PMINSPEC Table.

Thanks

 

[GingerR]

Have you tried out what I suggested? Why don't you get that working first. It will do what you want.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244