Query asking for parameters (1 Viewer)

[Reese]

[Solved] Query asking for parameters

I have a query that pulls information from two tables. Some of the fields that are being queried share the same name in the tables, [Reimbursed_Amount] and [Cancel_Fee] specifically. In Design View I have specified that I only want the query to pull these fields from the Event Information table. An error occurs when I try to run it, saying that I need to define which table the field is from in the SQL code.

But then after I added clarification in the SQL, when I run the query it now prompts for a parameter for each of these fields. Why is this happening? I leave it blank, so a parameter has no impact on the query. How can I stop this?

Here's the SQL, after I added the table clarification:

SELECT (Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])+nz([Cancel_Fee].[Event Information])-nz([Reimbursed_Amount].[Event Information]))) AS Total_Cost, [Shared Billing Information].Paid, [Shared Billing Information].Shared_Billing_ID, [Event Information].Shared_Billing_ID
FROM [Shared Billing Information] RIGHT JOIN [Event Information] ON [Shared Billing Information].Shared_Billing_ID = [Event Information].Shared_Billing_ID
GROUP BY [Shared Billing Information].Paid, [Shared Billing Information].Shared_Billing_ID, [Event Information].Shared_Billing_ID, [Event Information].Event_ID, [Event Information].Program_Code, [Event Information].Date_of_Event, [Event Information].Cancel_Fee, [Event Information].Reimbursed_Amount, [Event Information].Sample_Lock, [Event Information].Paid, [Event Information].Incomplete_Booking, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee, [Event Information].Program_Cost, [Event Information].Multiple_On_Billing
HAVING ((([Shared Billing Information].Paid)="-1") AND (([Event Information].Program_Code)="ZM") AND (([Event Information].Date_of_Event) Between [Forms]![Program Summary Reports]![StartDateTxt] And [Forms]![Program Summary Reports]![EndDateTxt]) AND (([Event Information].Sample_Lock) Is Null Or ([Event Information].Sample_Lock)="0") AND (([Event Information].Incomplete_Booking) Is Null Or ([Event Information].Incomplete_Booking)="0") AND (([Event Information].Multiple_On_Billing)="-1"));

 

[Galaxiom]

Could be corruption. Copy the SQL View and paste it into a new query.

 

[Reese]

I just played with it and I think I solved the issue, but I want to make sure. I had included the Shared_Billing_ID (which is the primary key field that links the two tables) from both the Event Information and Shared Billing Information tables.

I just deleted the Event Information table's Shared_Billing_ID field from the query and the prompt for the parameters no longer appears.

Was that the cause of my problem?

 

[Reese]

Okay, never mind. I was wrong, the problem is still there. I'll try copying the SQL into a new query.

 

[Reese]

Nope, sadly copying the SQL code into a new query doesn't work.

 

[spikepl]

You have been sloppy. Access is honest and tells you what it does not understand. You have concocted something very complex in a wrong way, and if the Access messages do not lead you to the solution then de-concoct your thing. Build it by adding one item at a time, and make sure that that works, and then add another item.

The keyword HAVING is used to apply to the values of the aggregated results, which is not what your SQL does. Instead use WHERE. When you have aggregate functions, in the query designer you need to drag the field into the grid again, remove the tick in the Show row and select WHERE in the Total row. Then put your criterion in the criteria row. If you put the criterion in the original column, you'll get the HAVING clause out of it.

 

[CJ_London]

You might also want to look at your code

[Cancel_Fee].[Event Information] should be [Event Information].[Cancel_Fee]

Try building your query using query builder then look at the SQL view to see how it is constructed

 

[Reese]

[Cancel_Fee].[Event Information] should be [Event Information].[Cancel_Fee]

That worked great, CJ_London. Thanks!

Build it by adding one item at a time, and make sure that that works, and then add another item.

Spikepl, I did build it one step at a time, in fact I started this query by creating a copy of another query that works perfectly using only the Event Information table, then adding the conditions from the Shared Billing Information table. That's where things got funky.

The keyword HAVING is used to apply to the values of the aggregated results, which is not what your SQL does. Instead use WHERE.

The HAVING must have been inserted through the Design View because I never wrote it in the SQL (I looked and it's also in the initial query that I copied from). I tried changing it to WHERE in the SQL (after using CJ_London's fix), but then an error came up saying that there was a missing operator in the syntax. Thanks for the suggestion, but Access seems to want me to keep HAVING.