Inefficient Query - Please HELP

  • Thread starter Thread starter Ian Eldred
  • Start date Start date
I

Ian Eldred

I have created a query retrieving data from 3 tables:
Invoices
Purchase Orders
Property Details

Here is my query:

SELECT
Invoices.InvoiceID,
Invoices.RelatePO,
IIf([RelatePO]=-1,Format([PONumber],"0000"),"N/A") AS [PO Number],
IIf([RelatePO]=-1,DLookup("[Property Name]","[Property
Details]","[PropertyRef]=" & DLookUp("[PropertyRef]","[Purchase
Orders]", "[PONumber]=" & [PONumber])),
IIf(IsNull(InvoiceProperty]),"Miscellaneous
Invoices",DLookup("[Property Name]","[Property
Details]","[PropertyRef]=" & [InvoiceProperty])) AS [Property Name],
IIf([RelatePO]=-1,DLookUp("[Description]","[Purchase
Orders]","[PONumber]=" & [PONumber]),[InvoiceDescription]) AS
Description,
Invoices.InvoiceDate,
etc...
FROM Invoices;

I know that all those IIF's and DLookup's are very inefficient, but
just don't know how to avoid using them. Any help would be greatly
appreciated.

TIA

Ian
 
What is in PO Number if RelatePO is not True (-1)? And how are the three
tables related to each other? One-One, One-Many? If [PO Number] is Null
when RelatePO is not True, then you should be able to do the lookups with an
outer join. If not, then you might be able to construct a UNION query that
returns "N/A" and "Miscellaneous Invoices" when RelatePO = 0 in one query
UNION a second query that fetches the related values via Join when RelatePO
= -1.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thanks for your reply John.

The tables are related as follows:

Invoices - Property Details, one-many.
Purchase Orders - Property Details, one-many.
Invoices - Purchase Orders, one-one.

An invoice can either take its data from a purchase order be selecting
the PONumber on the invoice (the property id will then come from the
purchase order) or the data can be input into the invoice from scratch
(the property id will be selected from all the properties in the
property details table).

If RelatePO is not true, the PONumber will be empty (I'm assuming this
is Null).

I have never created an OUTER JOIN before and would not know where to
begin so some help with this would definately be appreciated.

Many Tahnks,

Ian
 
It'll look something like:

SELECT Invoices.InvoiceID, Invoices.RelatePO, "N/A" As [PO Number],
IIF(IsNull([InvoiceProperty], "Miscellaneous Invoices", [Property
Details].[Property Name]) As [Property Name], ...
FROM Invoices LEFT JOIN [Property Details]
ON Invoices.InvoiceProperty = [Property Details].[PropertyRef]
WHERE Invoices.RelatePO = 0
UNION
SELECT .. a query to join the tables you need if RelatePO is True.


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Ian Eldred said:
Thanks for your reply John.

The tables are related as follows:

Invoices - Property Details, one-many.
Purchase Orders - Property Details, one-many.
Invoices - Purchase Orders, one-one.

An invoice can either take its data from a purchase order be selecting
the PONumber on the invoice (the property id will then come from the
purchase order) or the data can be input into the invoice from scratch
(the property id will be selected from all the properties in the
property details table).

If RelatePO is not true, the PONumber will be empty (I'm assuming this
is Null).

I have never created an OUTER JOIN before and would not know where to
begin so some help with this would definately be appreciated.

Many Tahnks,

Ian

"John Viescas" <[email protected]> wrote in message
What is in PO Number if RelatePO is not True (-1)? And how are the three
tables related to each other? One-One, One-Many? If [PO Number] is Null
when RelatePO is not True, then you should be able to do the lookups with an
outer join. If not, then you might be able to construct a UNION query that
returns "N/A" and "Miscellaneous Invoices" when RelatePO = 0 in one query
UNION a second query that fetches the related values via Join when RelatePO
= -1.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Click to expand...
Click to expand...
 
Back
Top