Click here to Skip to main content
14,735,347 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have two tables, named partpayment and pledger. I need to collect data from partpayment only, and the condition in
Where
clause should be applies to both tables.

I need the query something like I mentioned below. (I know the query is wrong. But I need the result as it is.)

Here in the
Where
clause DATE should be filter from the table partpayment and
redeemed = 0
should be from the table pledger

Again I am saying the below query is wrong. I need the result something like that. thats y.

What I have tried:

DataFilterQuery = "Select partpayment.pledgedate As PDATE, " & _
      "partpayment.pledgeno As REFNO, partpayment.pawnername As PAWNER, " & _
      "partpayment.loanamount as AMOUNT, partpayment.totalweight As WEIGHT " & _
      "From partpayment Where partpayment.ppdate >= @FromDate And partpayment.ppdate <= @ToDate " & _
      "And pledger.redeemed = 0 Order By partpayment.pledgedate, partpayment.pledgeno"
Posted
Updated 27-Jul-20 3:00am

Probably, you need to use a JOIN: SQL Joins[^]
If you have two tables Authors:
ID Name
1  Terry Pratchett
2  David Eddings

And Books:
ID AuthorId Name
1  1        Night Watch
2  1        Thief of Time
3  2        Demon Lord of Karanda

And you want to retrieve author and books:
SELECT a.ID, 
       a.[Name] AS Author, 
       b.[Name] AS Title 
FROM Authors a 
JOIN Books b 
ON a.ID=b.AuthorID

Then you will get:
ID      Author             Title
1       Terry Pratchett    Night Watch
1       Terry Pratchett    Thief of Time
2       David Eddings      Demon Lord of Karanda
Your description isn't very clear on exactly what you are trying to achieve, but that should enable you to work it out.
You can use a WHERE clause that involves both tables.
... WHERE a.Name LIKE 'Terry%' AND b.Name LIKE '%Time'
   
Comments
   
Both 2 answers working great.. Thanks...
OriginalGriff 27-Jul-20 15:49pm
   
You're welcome!
First of all, please read this: Visual Representation of SQL Joins[^]

Then, try this:
SELECT pp.pledgedate As PDATE, pp.pledgeno As REFNO, pp.pawnername As PAWNER, pp.loanamount as AMOUNT, pp.totalweight As WEIGHT
FROM partpayment pp
    INNER JOIN pledger p ON pp.[Key] = p.[Key] And pledger.redeemed = 0 --read note below the query
WHERE pp.ppdate >= @FromDate And pp.ppdate <= @ToDate
Order By pp.pledgedate, pp.pledgeno;

Note:
Replace [Key] with proper field name, which defines relationship between tables!

Note #1: MS Access database engine likes brackets ()!
Quote:
When you must join more than one table, you can nest the INNER JOIN clauses. The following example builds on a previous SELECT statement to create the result set, but also includes the city and state of each customer by adding the INNER JOIN for the tblShipping table.
SQL

SELECT [Last Name], InvoiceDate, Amount, City, State 
FROM (tblCustomers AS cus
    INNER JOIN tblInvoices AS inv ON cus.CustomerID=inv.CustomerID) 
      INNER JOIN tblShipping AS shi ON cus.CustomerID=shi.CustomerID 
   ORDER BY InvoiceDate

See: Perform joins using Access SQL | Microsoft Docs[^]


Note #2: Use international date formats!
See: Use international date formats in SQL statements | Microsoft Docs[^]
   
Comments
   
"And pledger.redeemed = 0"
is showing "Syntax error in JOIN operation." when I remove that, there is no error.
Full query is below, I have tried.

DataFilterQuery = "Select pp.pledgedate As PDATE, pp.pledgeno As REFNO, " & _
"pp.pawnername As PAWNER, pp.loanamount as AMOUNT, pp.totalweight As WEIGHT " & _
"From partpayment pp Inner Join pledger p On " & _
"pp.pledgeno = p.pledgeno And pledger.redeemed=0 " & _
"Where pp.ppdate >= @Me.dtpAccountFrom.Value.Date And " & _
"pp.ppdate <= @Me.dtpAccountTo.Value.Date Order By pp.pledgedate, pp.pledgeno"
   
I put this "And pledger.redeemed = 0" in the Where clause...
Thank you... (Y)
Maciej Los 27-Jul-20 15:49pm
   
Replace 'pledger' with 'p'. I'm using aliases for table names.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900