RE: Blank Columns Everytime

Tech-Archive recommends: Fix windows errors by optimizing your registry

Try running the following:
SELECT [Brokerage Bookeeping 2006].Account,
[Modified Dietz Step 1 (Jan 06)].Numerator,
[Modified Dietz Step 3 (Jan 06)].[Cash Flow],
FROM [Modified Dietz Step 3 (Jan 06)]
INNER JOIN ([Modified Dietz Step 2 (Jan 06)]
INNER JOIN ([Modified Dietz Step 1 (Jan 06)]
INNER JOIN [Brokerage Bookeeping 2006]
ON [Modified Dietz Step 1 (Jan 06)].Account = [Brokerage Bookeeping
2006].Account)
ON [Modified Dietz Step 2 (Jan 06)].Account = [Brokerage Bookeeping
2006].Account)
ON [Modified Dietz Step 3 (Jan 06)].Account = [Brokerage Bookeeping
2006].Account
ORDERE BY [Modified Dietz Step 3 (Jan 06)]![Cash Flow] DESC ;

Do you see any records returned? If not, that's the problem.

Is [Modified Dietz Step 3 (Jan 06)]![Cash Flow] Null or 0? If so you might
get a divide by zero error.

If you see records and [Modified Dietz Step 3 (Jan 06)]![Cash Flow] isn't 0,
try this:

SELECT DISTINCT [Brokerage Bookeeping 2006].Account,
[Modified Dietz Step 1 (Jan 06)].Numerator,
[Modified Dietz Step 3 (Jan 06)].[Cash Flow],
[Modified Dietz Step 1 (Jan 06)]![Numerator] /
[Modified Dietz Step 3 (Jan 06)]![Cash Flow] AS [Rate of Return]
FROM [Modified Dietz Step 3 (Jan 06)]
INNER JOIN ([Modified Dietz Step 2 (Jan 06)]
INNER JOIN ([Modified Dietz Step 1 (Jan 06)]
INNER JOIN [Brokerage Bookeeping 2006]
ON [Modified Dietz Step 1 (Jan 06)].Account = [Brokerage Bookeeping
2006].Account)
ON [Modified Dietz Step 2 (Jan 06)].Account = [Brokerage Bookeeping
2006].Account)
ON [Modified Dietz Step 3 (Jan 06)].Account = [Brokerage Bookeeping
2006].Account


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"AJ Norris" wrote:

Thanks Jerry!

Here is the SQL code from the last Query. There is a set of 4 queries that
Im trying to use to generate a rate of return on our portfolios.

SELECT [Brokerage Bookeeping 2006].Account, [Modified Dietz Step 1 (Jan
06)].Numerator, [Modified Dietz Step 3 (Jan 06)].[Cash Flow], [Modified Dietz
Step 1 (Jan 06)]![Numerator]/[Modified Dietz Step 3 (Jan 06)]![Cash Flow] AS
[Rate of Return]
FROM [Modified Dietz Step 3 (Jan 06)] INNER JOIN ([Modified Dietz Step 2
(Jan 06)] INNER JOIN ([Modified Dietz Step 1 (Jan 06)] INNER JOIN [Brokerage
Bookeeping 2006] ON [Modified Dietz Step 1 (Jan 06)].Account = [Brokerage
Bookeeping 2006].Account) ON [Modified Dietz Step 2 (Jan 06)].Account =
[Brokerage Bookeeping 2006].Account) ON [Modified Dietz Step 3 (Jan
06)].Account = [Brokerage Bookeeping 2006].Account
GROUP BY [Brokerage Bookeeping 2006].Account, [Modified Dietz Step 1 (Jan
06)].Numerator, [Modified Dietz Step 3 (Jan 06)].[Cash Flow], [Modified Dietz
Step 1 (Jan 06)]![Numerator]/[Modified Dietz Step 3 (Jan 06)]![Cash Flow];


As for the relationships, everything is one to many from the Master Account
List. All of the other tables contain the same account number (primary key).
Sounds like it would be easiest that way. However I'm STUCK in the mud.

Thanks for your help, its been a long day

AJ


"Jerry Whittle" wrote:

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"AJ Norris" wrote:

Does anyone know why each time I get to the last expression in the very last
query for this report, the information wont populate. I'm sure that I'm
doing something wrong. So does anyone know what could cause this? The only
task left is for me to divide one query column by another. Is this not
allowed?

Thanks in advance.

AJ
.

Relevant Pages

  • Re: Refining query
    ... "SMT" wrote: ... Jerry Whittle, Microsoft Access MVP ... I have a query which selects the most recent record that works great ... FROM project_manager AS PM INNER JOIN (enterprise_executive AS E INNER ...
    (microsoft.public.access.queries)
  • RE: Max Query
    ... When I try to use this query I get the message that DocumentTitle is not ... FROM (tblDocument INNER JOIN tblTraining ... INNER JOIN tblPerson ON tblTraining.PersonID = tblPerson.PersonID ... Jerry Whittle, Microsoft Access MVP ...
    (microsoft.public.access.queries)
  • RE: Refining query
    ... I still dont think I am explaining this ... The original query allows the user to pull the project ... Jerry Whittle, Microsoft Access MVP ... FROM project_manager AS PM INNER JOIN (enterprise_executive AS E INNER JOIN ...
    (microsoft.public.access.queries)
  • Re: Refining query
    ... actual_complete_date and now my supervisor wants the completed records to ... Jerry Whittle, Microsoft Access MVP ... I have a query which selects the most recent record that works great ... FROM project_manager AS PM INNER JOIN (enterprise_executive AS E INNER ...
    (microsoft.public.access.queries)