Problem with Querty

[Guest]

I have one tables with customers names and addresses (1 to 1). Second table
is Invoices (1 to Many). There is a field containing Open/Closed Invoices.
Each table uses account number as key field.

What I need is a querty that joins them together so that it shows the
Customers Name and only the first open invoice date closest to today.

When I join them now, I get thousands of records. So I build a querty on
the Invoice table using the DISTINCTROW function which works but is a "Read
only". And joining it to the Customer table I can not make changes to this
record.

Can you advise me on the proper way so that I get the same results and are
able to edit the record.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT C.account_nbr, C.customer_name, I.invoice_nbr
FROM Customers As C INNER JOIN Invoices As I
ON C.account_nbr = I.account_nbr
WHERE I.invoice_date = (SELECT MAX(invoice_date)
FROM Invoices
WHERE account_nbr = C.account_nbr
AND invoice_nbr = I.invoice_nbr
AND open_close = I.open_close)
AND I.open_close = 'open'

If you want this query to be updateable you'll have to add all the
required and Primary Key columns from both tables in the SELECT clause.
Do not use DISTINCTROW or GROUP BY, these will cause the query to be
read only.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAN97YechKqOuFEgEQJwQwCgjkJknrBXLdNdO3KmiU/prg6sBXgAoJcS
CFGDpKaZdsGMp5hBlfbPTGZO
=o4q5
-----END PGP SIGNATURE-----

 

[Guest]

It work's, but not as expected. It does not create a record for customers
with no invoice. Is there a way that to have one record per customer (table
1 to 1) even though there may not be an invoice generated yet.

I tried removing the last two AND statements but that didn't help.

Steve

 

[Guest]

I got it working. Added IS NULL statement etc... Works perfect... Thanks

Steve