CODEWITHSUNDEEP
sqlms-accessfiltering
SRahmani
SRahmani

Reputation: 359

Assistance with msaccess sql query

I have a form called “Search” with the question regarding the section under Transaction Search. It invokes a query based on the selection of full name (Combo40) which pulls from a table called “Individuals” and the committee(s) the user selected in Combo40 has contributed to in the Committee field:

Committee Query:

SELECT Transactions.[Committee Name] FROM Transactions GROUP BY Transactions.[Committee   Name], Transactions.[Employee Name] HAVING (((Transactions.[Employee Name])=[Forms]![Search]![Combo40]));

I’m trying to add an additional parameter that also compares the year in which an individual gave which is a specific filed in the Transactions table called Combo46.

New Query: 

SELECT Transactions.[Committee Name] FROM Transactions GROUP BY Transactions.           [Committee Name], Transactions.[Employee Name] HAVING (((Transactions.[Employee Name])=Forms!Search!Combo40) And (((Transactions.Combo46)=Forms!Search!Combo51)))

Forms!Search!Combo40 = pulls the values from a table called “Individuals”
Transactions.Combo46 = year (i.e. 2011, 2012, 2013 etc.)
Forms!Search!Combo51 = year (i.e. 2011, 2012, 2013 etc.)

When I make my selections on the form I get a window that asks me to enter the value for Combo 46 and the committee list displays all records for that individual and not just transactions with the year I entered. I would expect it to not come up with a pop-up at all but instead take the value from Combo46 in the Transactions table and compare it to the value entered in Combo51.

screenshot1

screenshot2

Upvotes: 1

Views: 64

Answers (2)

Chris Rolliston
Chris Rolliston

Reputation: 4808

What jacouh says is surely the answer (i.e., your SQL is referring to Combo46 as a member of Transactions when it is a member of Forms!Search). As an appendum, I'd simplify things a bit like this:

(1) Name the combo boxes something sensible (e.g. cboName and cboYear) rather than leaving them as Combo40 and Combo51 (yuck!).

(2) The GROUP BY seems unnecessary to me:

SELECT DISTINCT [Committee Name] FROM Transactions
WHERE ([Employee Name] = Forms!Search!cboName) AND ([Year] = Forms!Search!cboYear)

Upvotes: 2

jacouh
jacouh

Reputation: 8741

It's too long and unclear for a comment, so I use this anwser space:

I suppose that the table column Transactions.Combo46 does not exist, so Transactions.Combo46 is popuped by Access for Input, it should be Transactions.Year or something meaning this:

SELECT Transactions.[Committee Name]
FROM Transactions
GROUP BY Transactions.[Committee Name], Transactions.[Employee Name]
HAVING (((Transactions.[Employee Name])=Forms!Search!Combo40)
And (((Transactions.Year)=Forms!Search!Combo51)))

Upvotes: 3

Related Questions