Expression question

[Guest]

I have a query with the following fields:
[Period] - 1/1/05, 2/1/05, 3/1/05 etc...
[Client] - Client code
[IEAmount] - dollar amount of IE's during this period
[AdjIEAmount] - The expression field I can't seem to get right.

So for each of my 10 clients, the query generates a record creating
consecutive periods. the IE amount is pulled from a sunquery and links based
on [client] and [period]. However, sometimes there is no corresponding
[IEAmount] for a particular month. What I'm trying to do with my expression
field is say If [IEAmount]>0,[IEAmount], The use the [IEAmount] from the
prior period.

It's obviosly the FALSE clause I'm having trouble with.

 

[[MVP] S.Clark]

This is a classic query problem had by many. How to report on something
that doesn't exist.

The short answer is an Outer Join. For example to find the count of orders
per customer in Northwind, you could use the following query.

SELECT Customers.CustomerID, Customers.CompanyName, Count(Orders.OrderID) AS
OrderCount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName

If you need to do it for a specific date range you could create on query
that finds all of the orders in the date range, then perform the outer join
to the Customer table using the query, instead of the Orders table directly.

 

[Guest]

Hey Steve,

I appreciate the answer. Unfortunetly, it looks like it may be a little
above my head. For instance I don't know what is meant by "outer join" nor
am I fluent in SQL by any stretch. Is there an "Outer Join for Dummies"
tutorial out there that you are aware of?

This is a classic query problem had by many. How to report on something
that doesn't exist.

The short answer is an Outer Join. For example to find the count of orders
per customer in Northwind, you could use the following query.

SELECT Customers.CustomerID, Customers.CompanyName, Count(Orders.OrderID) AS
OrderCount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName

If you need to do it for a specific date range you could create on query
that finds all of the orders in the date range, then perform the outer join
to the Customer table using the query, instead of the Orders table directly.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have a query with the following fields:
[Period] - 1/1/05, 2/1/05, 3/1/05 etc...
[Client] - Client code
[IEAmount] - dollar amount of IE's during this period
[AdjIEAmount] - The expression field I can't seem to get right.

So for each of my 10 clients, the query generates a record creating
consecutive periods. the IE amount is pulled from a sunquery and links
based
on [client] and [period]. However, sometimes there is no corresponding
[IEAmount] for a particular month. What I'm trying to do with my
expression
field is say If [IEAmount]>0,[IEAmount], The use the [IEAmount] from the
prior period.

It's obviosly the FALSE clause I'm having trouble with.

 

[[MVP] S.Clark]

Invest about 10 minutes of time with this query, and you'll be a pro.

Open Northwind
Create a new query
Choose View / SQL from the main menu
Copy the text into this window
Choose View / Design View

Notice what's different.
Run the query
Switch to design view

Right Click on the join line and choose Join Properties.

Read everything in that dialog box and get a feel. Play with the query.
Try writing your own. Add a record to the customer table to see how this
query reacts differently than an Inner Join.

15 minutes, tops.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Hey Steve,

I appreciate the answer. Unfortunetly, it looks like it may be a little
above my head. For instance I don't know what is meant by "outer join"
nor
am I fluent in SQL by any stretch. Is there an "Outer Join for Dummies"
tutorial out there that you are aware of?

This is a classic query problem had by many. How to report on something
that doesn't exist.

The short answer is an Outer Join. For example to find the count of
orders
per customer in Northwind, you could use the following query.

SELECT Customers.CustomerID, Customers.CompanyName, Count(Orders.OrderID)
AS
OrderCount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName

If you need to do it for a specific date range you could create on query
that finds all of the orders in the date range, then perform the outer
join
to the Customer table using the query, instead of the Orders table
directly.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I have a query with the following fields:
[Period] - 1/1/05, 2/1/05, 3/1/05 etc...
[Client] - Client code
[IEAmount] - dollar amount of IE's during this period
[AdjIEAmount] - The expression field I can't seem to get right.

So for each of my 10 clients, the query generates a record creating
consecutive periods. the IE amount is pulled from a sunquery and links
based
on [client] and [period]. However, sometimes there is no corresponding
[IEAmount] for a particular month. What I'm trying to do with my
expression
field is say If [IEAmount]>0,[IEAmount], The use the [IEAmount] from
the
prior period.

It's obviosly the FALSE clause I'm having trouble with.