In a SELECT QUERY collect data from table a and the condition should be based on table a and table B

Question

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

VB

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

VB

Where

clause DATE should be filter from the table partpayment and

VB

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:

VB

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 26-Jul-20 20:40pm

Venu Gopal Mulavana Kayamkulam

Updated 27-Jul-20 2:00am

Add a Solution

2 solutions

Solution 2

First of all, please read this: Visual Representation of SQL Joins[^]

Then, try this:

SQL

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

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[^]

Posted 27-Jul-20 2:00am

Maciej Los

Comments

Venu Gopal Mulavana Kayamkulam 27-Jul-20 13:13pm

"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"

Venu Gopal Mulavana Kayamkulam 27-Jul-20 15:21pm

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.

Add a Solution

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

OriginalGriff · Accepted Answer · 2020-07-26T20:47:00

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:

SQL

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.

SQL

... WHERE a.Name LIKE 'Terry%' AND b.Name LIKE '%Time'

In a SELECT QUERY collect data from table a and the condition should be based on table a and table B

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

In a SELECT QUERY collect data from table a and the condition should be based on table a and table B

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us