Access Learner - Need Help with Query

[F.I.G.]

Hello,

I am learning Access and need help with a query for a list box using table below. I am trying to create a query that generate only RequestDate and TransactionType that do not repeat. The targeted result would look like:

2/5/2012 new
3/3/2012 modification
4/1/2012 renewal

Is that possible?

Thank you in advance
Van
RequestID LoanInfoID RequestDate TransactionType DocumentDate LoanDocListID Description
12 1 2/5/2012 new 2/6/2012 1 ...
13 1 2/5/2012 new 2/6/2012 2
14 1 2/5/2012 new 2/6/2012 6
15 1 3/3/2012 modification 2/12/2012 8
17 1 3/3/2012 modification 2/12/2012 7
18 1 3/3/2012 modification 2/12/2012 11
22 1 4/1/2012 renewal 3/3/2012 4
23 1 4/1/2012 renewal 3/3/2012 6
25 1 4/1/2012 renewal 3/3/2012 4

 

[Alansidman]

With the data provided, this works for me

SELECT Table1.RequestDate, Table1.TransactionTypeID
FROM Table1
GROUP BY Table1.RequestDate, Table1.TransactionTypeID;

 

[F.I.G.]

It works, thank you!

Van

 

[F.I.G.]

Hello,

The SQL query shown below is for a list box in a form, I added a WHERE condition (in red) to the statement to filter records in the list box that matched to LoanInfoID of the form. I am getting an error message that state:

"Syntax error (missing operator) in query expression 'T_TransactionType.TransactionType WHERE WHERE LoanInfoID=Forms!F_BorrowerLoanHistory!LoanInfoID'."


What is this mean? What did I do wrong? How should I do? A quick explaination would be nice.


SELECT T_Request.LoanInfoID, T_Request.RequestDate, T_TransactionType.TransactionType
FROM T_Request INNER JOIN T_TransactionType ON T_Request.TransactionTypeID = T_TransactionType.TransactionTypeID
GROUP BY T_Request.LoanInfoID, T_Request.RequestDate, T_TransactionType.TransactionType
WHERE LoanInfoID=Forms!F_BorrowerLoanHistory!LoanInfoID
ORDER BY T_Request.RequestDate;

Please help
Thx in advance!
Van

 

[Alansidman]

I'm thinking you need to change your WHERE clause to a HAVING clause.

Look here

http://www.techonthenet.com/sql/having.php

and here

http://www.w3schools.com/sql/sql_having.asp

 

[F.I.G.]

I'm thinking you need to change your WHERE clause to a HAVING clause.

Look here

http://www.techonthenet.com/sql/having.php

and here

http://www.w3schools.com/sql/sql_having.asp

You nail it, its works. Many many many thanks!!!