[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Trouble creating a query

Posted on 2004-11-05
15
Medium Priority
?
158 Views
Last Modified: 2008-02-01
I am trying to create a query and having problems.

I have the following tables/fields:

Table ApInvoice:
Supplier
InvoiceNo
InvoiceDate
InvoiceAmount

Table ApInvoicePay:
Supplier
InvoiceNo
PaymentDate

They are linked by the Supplier and InvoiceNo.

I need a query that would show all fields (Supplier, Invoice, InvoiceDate, InvoiceAmount, PaymentDate) for all records with an InvoiceDate prior to 10/1/2004 that have a PaymentDate on or after 10/1/2004 or that haven't been paid yet (ie. no ApInvoicePay record).

I'd like to do this with a single SELECT statement, if possible.
0
Comment
Question by:chlade
  • 6
  • 5
  • 2
  • +1
15 Comments
 
LVL 3

Expert Comment

by:leeskelton83
ID: 12509607
What trouble are you having? You should be able to define your criteria for InvoiceDate <10/1/2004 and PaymentDate >= 10/1/2004. That should do the trick. Do you need the SQL?
0
 
LVL 34

Expert Comment

by:flavo
ID: 12509626
create a new query

Add the 2 tables when prompted

Add all the fiedls that you want to see  (Supplier, Invoice, InvoiceDate, InvoiceAmount, PaymentDate)

Then in the Criteria box under the field InvoiceDate type <#10/1/2004#  and under PaymentDate  type >= #10/1/2004#

>> that haven't been paid yet (ie. no ApInvoicePay record).


Is this all without paying, or only after InvoiceDate before 10/1/2004??
0
 

Author Comment

by:chlade
ID: 12509825
Sorry, I should have explained a bit better.  First, yes, the SQL code would be helpful.

I tried entering the criteria as you both suggested.  The problem is that it doesn't grab those ApInvoice records that have no associated record in the ApInvoicePay table.  That's the problem.

In case it helps, here's my current SQL code that does as you guys suggested:
SELECT ApInvoice.Supplier, ApInvoice.InvoiceNo, ApInvoice.InvoiceDate, ApInvoice.InvoiceAmount, ApInvoicePay.PaymentDate
FROM ApInvoice LEFT JOIN ApInvoicePay ON (ApInvoice.Supplier = ApInvoicePay.Supplier) AND (ApInvoice.InvoiceNo = ApInvoicePay.InvoiceNo)
WHERE ApInvoice.InvoiceDate<#10/1/2004# AND ApInvoicePay.PaymentDate>=#10/1/2004#;
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 34

Accepted Solution

by:
flavo earned 2000 total points
ID: 12509978
WHERE ((ApInvoice.InvoiceDate<#10/1/2004# AND ApInvoicePay.PaymentDate>=#10/1/2004#) or
(ApInvoice.InvoiceDate<#10/1/2004# AND ApInvoicePay.Payment is Null));

Dave
0
 

Author Comment

by:chlade
ID: 12510021
I don't feel so dumb anymore :)   I tried that too and it doesn't work.  Using a WHERE clause like that returns ALL (or at least most I guess) records.  Instead of roughly 500 records, I get over 26,000
0
 
LVL 34

Expert Comment

by:flavo
ID: 12510076
maybe a porblem with my ()

I just did a test, try this out

 (((ApInvoicePay.InvoiceDate)><#10/1/2004#AND ((ApInvoicePay.PaymentDate)>=#10/1/2004#)) OR (((ApInvoicePay.InvoiceDate)><#10/1/2004#) AND ((ApInvoicePay.PaymentDate) Is Null));
0
 

Author Comment

by:chlade
ID: 12510108
Still no luck.  The parenthesis actually don't all match in your last example (1 more open paren than closing paren)
0
 
LVL 3

Expert Comment

by:leeskelton83
ID: 12510130
Try a right join and flavo's suggestion:

In case it helps, here's my current SQL code that does as you guys suggested:
SELECT ApInvoice.Supplier, ApInvoice.InvoiceNo, ApInvoice.InvoiceDate, ApInvoice.InvoiceAmount, ApInvoicePay.PaymentDate
FROM ApInvoice RIGHT JOIN ApInvoicePay ON (ApInvoice.Supplier = ApInvoicePay.Supplier) AND (ApInvoice.InvoiceNo = ApInvoicePay.InvoiceNo)
WHERE (((ApInvoicePay.InvoiceDate)><#10/1/2004#AND ((ApInvoicePay.PaymentDate)>=#10/1/2004#)) OR (((ApInvoicePay.InvoiceDate)><#10/1/2004#) AND ((ApInvoicePay.PaymentDate) Is Null)));

0
 
LVL 34

Expert Comment

by:flavo
ID: 12510156
woops.. my bad changing the dates

(((ApInvoicePay.InvoiceDate)><#10/1/2004#) AND ((ApInvoicePay.PaymentDate)>=#10/1/2004#)) OR (((ApInvoicePay.InvoiceDate)><#10/1/2004#) AND ((ApInvoicePay.PaymentDate) Is Null));
0
 

Author Comment

by:chlade
ID: 12510393
Well, I can't believe what I found out.  The SQL command I had was working.  I just figured out that when I am dealing with a linked table (through ODBC), it does not work.  If I import the table and run exactly the same query, it works fine.  Same table, same query, same everything.  Only difference is linked data versus imported data.

Any ideas on why this is?

If not (or even if so), I'll try to split up the points as you guys did help me answer my question which was to create the query.  I'd still like to get it to work with linked data if possible.  (I don't know the protocol for this ... should I open a new question?)
0
 
LVL 34

Expert Comment

by:flavo
ID: 12510553
ODBC is not in my league sorry...
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12517557
I think it is how you need to format the values for your dates ... what type of database are you connecting to?

Steve
0
 

Author Comment

by:chlade
ID: 12527768
I am connecting to a C-ISAM database using a third party ODBC driver (Transoft's U/SQL).

The date thing is an idea, but if I do other queries with dates, they work fine.  For instance, the following statement returns just 20 records.
SELECT Supplier, Invoice, PaymentDate FROM ApInvoicePay WHERE PaymentDate=#10/1/2004#;
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12539444
Sounds like you need to find the manual on the C-ISAM / driver SQL syntax :-(
0
 

Author Comment

by:chlade
ID: 12540120
Well, I figured out a workaround but never really completely figured out my problem.  I'll chalk it up to the ODBC Driver.  Flavo did answer my original question though so I'll close it out.  Thanks everyone for the help.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question