open a report based on a combo box

[rutica]

I have a table with 2 fields that looks like this:

ReportID CriteriaCode
1 ...........Cost-R3
1 ...........Cost-Y6
2 ...........Sched-Y7
2 ...........Sched-R3
2 ...........Risk-R5

etc.

I have a report based on a query. The query contains a field called CriteriaCode.

I have a combo box on a Form that shows all the ReportIDs. If the user selects a ReportID and clicks on a command button, can I use the table above to build a Where statement to open the report?

So if a user chooses ReportID 1 on the Form, the report would open and the Where statement would be: Where CriteriaCode=Cost-R3 or Cost-Y6.

How can I do this?

Thanks,

 

[rutica]

Hello,

Just following up to see if anyone can help me.

Thanks,

 

[ansentry]

rutica,

You asked:

open a report based on a combo box

Answer: Yes.

I have looked at the other part of your question and sorry but I am unable to understand what you are trying to do.

What is ReportID is it the name of a field in a table?
What is CriteriaCode is t the name of a field in a table.

Also I don't understand how ReportID can have 2 items linked to it.

I have attached a sample of mine that you may find helpful, it allows you to select a report from a combo box and then select a date range and preview the report.

 

[rutica]

thanks for writing. your database looks great.
but i don't think i can use that because i need to add an 'or' statement for my query.

Please see attached database that shows what i'm trying to do. The Form
frmEscalationReports has a combo box.

I'm trying to build a Where statement so it pulls all the Criteria Codes from the tblEscalationReportsDetail depending on which ReportID is selected from the ComboBox.

Then it uses those Criteria Codes to filter when opening the Rpt- KPIDetail Report.

Example: The Business Process Report in the Combo Box (report ID=1) has Cost-R10 and Org-R4 shown in the tblEscalationReportsDetail. Therefore, if a user chooses the Business Process Report from the Combo Box and
clicks the button, the KPI Report should open filtering by:
Criteria Code = Cost-R10 or Criteria Code=Org-R4.

 

[ansentry]

I'm not a guru but I think that your table structure is wrong.

You have the same data in 3 tables;

Criteria Code in table:blEscalationReportsDetail
and
table: KPI Rating Rationale
and
table: KPI Rating Criteria

In my opinion the data should only be in 1 table.

 

[rutica]

Thanks for writing. You're right, I should have Criteria Code as a number (instead of Text) and then use that number when needed.

But I had help from someone who told me:

"Create a query joining the two tables. For example:

SELECT Table1.Field1, Table1.Field2
FROM Table1
INNER JOIN Table2
ON Table1.CriteriaCode=Table2.CriteriaCode
WHERE Table2.ReportID = Forms!NameOfForm.NameOfCombobox
"

It works. My SQL is:
SELECT Project.[Project ID], [KPI Rating Rationale].[Criteria Code], Project.[Project Name], Project.[AD Domain], [Project Status Survey].ProjectName, [Project Status Survey].ADDomain, [Project Status Survey].CustomerOrganization, [Project Status Survey].ScopeWork, tblEscalationReportsDetail.ReportIDLink
FROM tblEscalationReportsDetail INNER JOIN (([KPI Rating Rationale] INNER JOIN Project ON [KPI Rating Rationale].[Project ID] = Project.[Project ID]) INNER JOIN [Project Status Survey] ON Project.[Project ID] = [Project Status Survey].[Project ID]) ON tblEscalationReportsDetail.[Criteria Code] = [KPI Rating Rationale].[Criteria Code]
WHERE (((tblEscalationReportsDetail.ReportIDLink)=[Forms]![frmEscalationReports]![cboEscalation]));

thanks for your help.

 

[ansentry]

rutica,

I glad you got it "working", but I think that you will run into problems in the future as (in my opinion) you should not have the same data in Three Tables.

Post a question on this site and ask if you should have the same data in 3 separate tables. What happens if you want to add another ReportID - where do you add it?

Anyway its your database and that is my rant for the day.

Good luck with your project.

 

[rutica]

But the data is used different ways in the 3 different tables.

KPI Rating Criteria table has the criteria code and their descriptions. (this is the table where I ideally should have an autonumber field so each criteria code has a number associated with it. That way, in other tables, I would repeat the number instead of repeating the text.). Primary key is criteria code, so that field can't be repeated.

KPI Rating Rationale table associates project ids with criteria codes each month. Primary keys are combination of criteria codes, as-of date and project id.

tblEscalationReportsDetail table associates report ids with criteria codes. Primary keys are combination of reportID and criteria code.

So while you're right that the field Criteria Codes is shown 3 places, that is what relational databases are all about.
If I want to add another ReportID, I would go into tblEscalationReports and add another one.


For example, if you had a Human Resources database, you might have a table with Employee ID, Employee First Name, Employee Last Name, Address, etc.
Then you might have a table called Monthly Sales Revenue where you would have Employee ID, Date, Amount.
Then you might have a table called Employee and Customers where you would have Employee ID and Customer ID.
So you repeat Employee ID in 3 different tables, but each table shows different data for Employee ID.