Expression Calc in query using two tables

[dragct]

Ok, I have been trying to add a calculation in a query from two tables. I have read this forum, gone through the help menu and am hitting a roadblock, so now I am requesting help.

I have tables: BUDGET and TODAY'S DATE.

Within the Budget table, I have a field called Expense.

Within the Date table, I have two fields:

Weeks in Year (set as default value of 52) and
Weeks Past (=52-(DateDiff("ww",Now(),#8/31/2016#)))
NOTE: We run on an academic year (9/1 thru 8/31)
​

In my query, I am trying to calculate

([Expense]*[Today's Date]![Weeks in Year])/[Today's Date]![Weeks past]
​

When I run the query, the field with the expression is blank; however, when I run it with the actual data for the Date table fields, it runs fine. ie...([Expense]*"52")/"3"...the calculated amounts are in the field in the query.

What am I missing? Any help is appreciate.

Kim

 

[James Deckert]

what is the relationship between the two tables?
Can you copy the entire SQL?

 

[dragct]

The join is on the academic year...

Here is the SQL

SELECT TBL_Departments.[Department Name], DWNLD_OV_Budget.Fund, DWNLD_OV_Budget.Location, DWNLD_OV_Budget.[Cst Element], DWNLD_OV_Budget.Dept, DWNLD_OV_Budget.Account, TBL_Accounts.[Account Name], DWNLD_OV_Budget.Budget, DWNLD_OV_Budget.Expense, DWNLD_OV_Budget.Encumbrance, DWNLD_OV_Budget.[Pre-Encumbrance], ([Expense]+[Encumbrance]+[Pre-Encumbrance]) AS Total_Expense, DWNLD_OV_Budget.[Available Budget*], ([Expense]*[*Today's Date]![Weeks in Year])/[*Today's Date]![Weeks past] AS Yr_End_Proj_Exp, ([Expense]*"52")/"3" AS Expr1
FROM ((((DWNLD_OV_Budget INNER JOIN TBL_Departments ON DWNLD_OV_Budget.Dept = TBL_Departments.Department) INNER JOIN TBL_Location ON DWNLD_OV_Budget.Location = TBL_Location.Location) INNER JOIN [TBL_Cost Element] ON DWNLD_OV_Budget.[Cst Element] = [TBL_Cost Element].[Cost Element]) LEFT JOIN TBL_Accounts ON DWNLD_OV_Budget.Account = TBL_Accounts.Account) LEFT JOIN [*Today's Date] ON DWNLD_OV_Budget.[Budget Period] = [*Today's Date].[Fiscal Year]
WHERE (((DWNLD_OV_Budget.Account) Like "##00"))
ORDER BY TBL_Departments.[Department Name], DWNLD_OV_Budget.Account;

 

[MarkK]

Having a "Today's Date" table doesn't make any sense to me. How many records does it have?

Rather than a join to a table with that data in it, I would expect to see that type of date expression in a where clause.

A date is a quantity of time like dollars is a quantity of money or inches is a quantity of length. One day as opposed to the next is like $3 as opposed to $4, and in money you would never have a table that just contains quantities of money on which to link your financial data. Like, you don't link your $4 transactions to your $4 dollar record. $4 can't be a link, or, if it is, then you need to link to $4.01, and a link to $4.02, and then you've misunderstood links. The amount is a field in a record, not a link.

Links are made to objects. Time is not an object. Time is a range of values, which is what the WHERE clause is for.

Hope this helps,

 

[dragct]

MarkK, thanks. Your information helped! I rethought how I was handling this and put the equation directly into the expression and that fixed it.

Kim

 

[MarkK]

You bet Kim. Glad you got it figured out.