Trouble creating a query

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.
chladeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leeskelton83Commented:
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
flavoCommented:
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
chladeAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

flavoCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chladeAuthor Commented:
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
flavoCommented:
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
chladeAuthor Commented:
Still no luck.  The parenthesis actually don't all match in your last example (1 more open paren than closing paren)
0
leeskelton83Commented:
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
flavoCommented:
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
chladeAuthor Commented:
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
flavoCommented:
ODBC is not in my league sorry...
0
stevbeCommented:
I think it is how you need to format the values for your dates ... what type of database are you connecting to?

Steve
0
chladeAuthor Commented:
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
stevbeCommented:
Sounds like you need to find the manual on the C-ISAM / driver SQL syntax :-(
0
chladeAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.