Query Based on Form Dates

[weswilson88]

Hello, I am getting back into Access and having trouble figuring out how to get the below to work. I've tried TempVar, Subforms, et cetera but cannot seem to get the right combination.



Essentially, I have two tables: 1) Opening balances in accounts 2) transactions with dates, categories, amounts, account, description

I have a query and form that gives me the current balance by grouping the two tables by accounts and summing the opening balances and transaction amounts.

What I am trying to accomplish, is to have a form or subform return the balances as of a certain date. Meaning that the once a date is input into a 'Short Date' field, the query filters out transactions after that date and returns the summation of the accounts.

Anyone have any advise as far as direction I should be headed?

[orange]

What have you tried? When you say as of a certain Date, do you mean within the current (fiscal/financial) year? That is, what start date are you working with?
Can you show us any query (SQL) you have tried?

So I guess your time period is from the DateofOpeningBalance to the Date requested.

[weswilson88]

What have you tried? When you say as of a certain Date, do you mean within the current (fiscal/financial) year? That is, what start date are you working with?
Can you show us any query (SQL) you have tried?

So I guess your time period is from the DateofOpeningBalance to the Date requested.

The as of date (text13 on form in below code) is the stop date, the opening balances are the start date. i.e as of today you have 5 dollars in the bank. on 3/3/16 you deposited 2 dollars. So if I type 3/2/16 into Text13 of the form I want the query on the form to show 3 dollars for that particular account.

I have the different accounts on the form as continuous records.

Below is an something I have tried:
SELECT Sum([Cash/Credit Transactions].AdjAmount) AS SumOfAdjAmount, Avg(Accounts.[Opening Balance]) AS [AvgOfOpening Balance], [SumOfAdjAmount]+[AvgOfOpening Balance] AS Total
FROM [Cash/Credit Transactions] INNER JOIN Accounts ON [Cash/Credit Transactions].[Account Name] = Accounts.AdjName
WHERE TranDate <= # & [Forms]![AsOfCashBalances]!Text13 & #
GROUP BY Accounts.[Account Name], Accounts.Type
HAVING (((Accounts.Type)="Cash"))
ORDER BY Accounts.Type;

[orange]

What result or error?

Do you have a test database that you can attach? zip format

[weswilson88]

zipped and attached.

[orange]

Hmmm...?? Not sure I'm following your design.

When you have tables that are in a relationship, you should use keys (primary key and Foreign key) for those relationships. Also, if a Transaction relates to an account, and Hopefully that is your design intention.
When I added a field for AcctID_FK in the [Cash/Credit Transactions] table and tried to update the field with the appropriate FK value

UPDATE [Cash/Credit Transactions] INNER JOIN Accounts 
ON [Cash/Credit Transactions].[Account Name] = Accounts.[Account Name] 
SET [Cash/Credit Transactions].AccTID_FK = [Accounts]![ID];

Only a few records were updated.


I then tried this query to see which Accounts were in [Cash/Credit Transactions] that were NOT in Accounts.

SELECT [Cash/Credit Transactions].[Account Name]
, [Cash/Credit Transactions].Amount
, [Cash/Credit Transactions].Trandate
FROM [Cash/Credit Transactions] LEFT JOIN Accounts
 ON [Cash/Credit Transactions].[Account Name] = Accounts.[Account Name];

Then all Accounts should exist in the Accounts table. I see several transactions(1372) that are related to Accounts that do NOT exist in the Accounts table??? How were they going to be applied?

It seems to me your tables are related
1 Account may have 0,1 or Many Transactions

I think your design needs work.
You need to start with a detailed description of the business rules involved.

When I try to open the form Transactions, I get an immediate error that table Transactions does not exist???

Perhaps I am not understanding your set up. Please refine as necessary.