Calculated Field To see if a Minimum is met

[trevor2524]

Hello,

Is there a way to do the following. I have a field in a table that I'm trying to make calculated. What that field will look for is information based on the date of two other columns in the table.

Start Date
End Date

Based on the information that is in that column for a particular field it will look on another table for the information and add up the sum it finds. I'm trying to have the sum printed back on the orginal table in the calculated field so I can see if a minium number has been met based on the date criteria.

So in conclusion this will be the following:
New Column will use the date columns as a filter. The new column will then check to see what the total number is on another table based on the date filter.

Any ideas are appreciated.

Thanks,
Trevor

 

[plog]

I'm trying to have the sum printed back on the orginal table in the calculated field so I can see if a minium number has been met based on the date criteria.

First, don't do that. You don't store calculated values...you calculated them when you need them in a query. Then if needed further (form, report, another query), you reference the query.

To use dates in one table as criteria on another you would link those two tables using an INNER JOIN with the appropriate date fields. Now, the design view isn't going to do this for you, you would have to set up the query, then manually edit the SQL to achieve what you want.

If you could post the relevant table an field names I could write that INNER JOIN for you.

 

[trevor2524]

Ok thanks for the reply,

For the table with dates here is the criteria:
Table Name: SP Account Info
Column Fields:
"Effective Date"
"Expiration Date"
Joining Column to other table: "Name"

For the table with the data here is the criteria:
Table Name: Master Numbers File
Column Fields to calculate the sum:
"Total Records"
Joining Column to other table: "Account Name"

So i would need the query to run for every account name it finds on the SP Account Info table.

 

[plog]

You said that somehow the Date fields factored in to this. You have date fields in SP Account Info, but no date field in Master Numbers File. So, how are the date fields to be used?

 

[trevor2524]

Yes on the master table there is a column called "Date Received". The filter dates are the ones located on the SP Account Info.

 

[plog]

At first, I didn't know if those tables where related by any other fields. Since they are related by Name-Account Name, you can use a WHERE clause to achieve what you want.

Here' is the full SQL of the query to tie your tables together:

SELECT [SP Account Info].Name, [Master Numbers File].[Total Records]
FROM [SP Account Info] INNER JOIN [Master Numbers File] ON [SP Account Info].Name = [Master Numbers File].[Account Name]
WHERE [Date Received]>=[Effective Date] AND [Date Received]<=[Expiration Date];

Things would be simpler if you removed the spaces from your table and field names. Those spaces makes things just a little more difficult.

 

[CJ_London]

Based on the information that is in that column for a particular field it will look on another table for the information and add up the sum it finds. I'm trying to have the sum printed back on the orginal table in the calculated field so I can see if a minium number has been met based on the date criteria.

FYI - calculated fields in tables a) cannot reference other tables and b)many functions will not work either (certainly not domain and user defined ones), So you will need to go plog's route

 

[trevor2524]

Quick Question, Thanks for responded that query worked to the way I want. Just one question. I'm trying to get the sum of all the total records for each account name that query finds. But for some reason it is showing more than one result for a single account name. I just attached totals and had it group by account name. Do you know the reason why this would happen or how to fix it?

 

[plog]

My guess is that you are grouping by more than just account name. If so, don't.

Can you post your full SQL?

 

[trevor2524]

I did it without all the groupings and it is still showing up with more than one name. The reason for more columns is because I'm looking to just summarize everything on one screen and have it all there in one viewing.

SELECT [SP Account Info].Name, [SP Account Info].[Term of contract], [SP Account Info].[Effective Date], [SP Account Info].[Expiration Date], Sum([Master Numbers File].[Total Records]) AS [SumOfTotal Records], [SP Account Info].[Minimum Input Records/Term], [SP Account Info].[Maximum Input Records/Term]
FROM [SP Account Info] INNER JOIN [Master Numbers File] ON [SP Account Info].[Account#] = [Master Numbers File].[Account ID]
GROUP BY [SP Account Info].Name, [Master Numbers File].[Date Received], [SP Account Info].[Term of contract], [SP Account Info].[Effective Date], [SP Account Info].[Expiration Date], [SP Account Info].[Minimum Input Records/Term], [SP Account Info].[Maximum Input Records/Term]
HAVING ((([Master Numbers File].[Date Received])>=[Effective Date] And ([Master Numbers File].[Date Received])<=[Expiration Date]));

 

[plog]

There you go:

GROUP BY [SP Account Info].Name, [Master Numbers File].[Date Received], [SP Account Info].[Term of contract], [SP Account Info].[Effective Date], [SP Account Info].[Expiration Date], [SP Account Info].[Minimum Input Records/Term], [SP Account Info].[Maximum Input Records/Term]

Every field you GROUP BY makes your data less unique for lack of a better term. If you want your data unique per Account Name, then that should be the only non-totaled field (SUM, COUNT, MAX, MIN, etc.) in the SELECT and GROUP BY clauses.

 

[trevor2524]

Yea the problem I'm having is even if I just have it group by just the Account Name itself it still is splitting up the sum results. Do you know the reason for that when everything matches in the file except for the records?

 

[plog]

Can you post your database? Maybe some Account names have extra spaces in them.

 

[trevor2524]

I actually just ran another query against my query to get the results I need so that ended up working for me.