calculating an opening balance

[Tara]

Hello,

I'm trying to run a query on the opening and closing balances for several
years. The factors I need are:
opening balance
closing balance
new reserves
used reserves (same every year - 35,000 - UNLESS the opening balance is less
than 35000)

closing balance in 2006 & opening balance in 2007 is 360,000

to calculate opening balance:
2007 is given
2008 is = 2007 + new reserves - used reserves
2009 is = 2008 + new reserves - used reserves
and so forth
(at about 2015 the used reserves would be less than 35,000 & therefore
should indicate amount in opening balance resulting in a balance of 0)

to calculate closing balance:
each year = next years opening balance

since my used reserves may change depending on the amount left in the
opening balance, I tried to insert it as the following:
Used Reserves: IIF([opening balance]>35000;35000;[opening balance])
By using this formula, I keep ending up with a circular formula.

My years are inserted into a table as a number as I could not figure out how
to insert a year (w/o the day & month) as a date/hour function.

Tara.

 

[Jeff Boyce]

Tara

It all starts with the data. It's hard suggesting "how" until we have a
better idea of "what".

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Tara]

It is a question of Inventory.
I have a known Inventory at the closing of 2006. I add (depending on the
case) new Inventory and I use a predetermined quantity each year, but
obviously I cannot use more than the existing Inventory. I must thus refer
to the Closing Inventory of the previous year before allowing my
predetermined usage to take place in the current year.

The only constant data I have entered is the Fiscal Year. Maybe this is
where I should ask my 1st question: how do you enter only the year as a
"date/hour" function into a table?

Tara

Tara

It all starts with the data. It's hard suggesting "how" until we have a
better idea of "what".

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello,

I'm trying to run a query on the opening and closing balances for several
years. The factors I need are:
opening balance
closing balance
new reserves
used reserves (same every year - 35,000 - UNLESS the opening balance is less
than 35000)

closing balance in 2006 & opening balance in 2007 is 360,000

to calculate opening balance:
2007 is given
2008 is = 2007 + new reserves - used reserves
2009 is = 2008 + new reserves - used reserves
and so forth
(at about 2015 the used reserves would be less than 35,000 & therefore
should indicate amount in opening balance resulting in a balance of 0)

to calculate closing balance:
each year = next years opening balance

since my used reserves may change depending on the amount left in the
opening balance, I tried to insert it as the following:
Used Reserves: IIF([opening balance]>35000;35000;[opening balance])
By using this formula, I keep ending up with a circular formula.

My years are inserted into a table as a number as I could not figure out how
to insert a year (w/o the day & month) as a date/hour function.

Tara.

 

[Jeff Boyce]

Tara

Unless you use a text-type data field, you don't. But you can enter a true
date (day and month and year) in an Access date/time type field, then decide
to DISPLAY only the year portion.

How are you planning to use the "year"? (again, what you have drives what
you can do with it)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

It is a question of Inventory.
I have a known Inventory at the closing of 2006. I add (depending on the
case) new Inventory and I use a predetermined quantity each year, but
obviously I cannot use more than the existing Inventory. I must thus refer
to the Closing Inventory of the previous year before allowing my
predetermined usage to take place in the current year.

The only constant data I have entered is the Fiscal Year. Maybe this is
where I should ask my 1st question: how do you enter only the year as a
"date/hour" function into a table?

Tara

Tara

It all starts with the data. It's hard suggesting "how" until we have a
better idea of "what".

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello,

I'm trying to run a query on the opening and closing balances for several
years. The factors I need are:
opening balance
closing balance
new reserves
used reserves (same every year - 35,000 - UNLESS the opening balance

is
less

than 35000)

closing balance in 2006 & opening balance in 2007 is 360,000

to calculate opening balance:
2007 is given
2008 is = 2007 + new reserves - used reserves
2009 is = 2008 + new reserves - used reserves
and so forth
(at about 2015 the used reserves would be less than 35,000 & therefore
should indicate amount in opening balance resulting in a balance of 0)

to calculate closing balance:
each year = next years opening balance

since my used reserves may change depending on the amount left in the
opening balance, I tried to insert it as the following:
Used Reserves: IIF([opening balance]>35000;35000;[opening balance])
By using this formula, I keep ending up with a circular formula.

My years are inserted into a table as a number as I could not figure

out
how

to insert a year (w/o the day & month) as a date/hour function.

Tara.

 

[Tara]

Jeff,

I need the year, because I need the opening & closing inventory of every year.

I'm not sure I understand what else I need to include. I am working between
the years 2006 and 2015 and need to be able to see a query on what my closing
inventory should be for every year, based on the fact that I use up X amount
of inventory each year.

I know what my closing inventory was in 2006 and need to calculate the
amount from then on.

My new inventory has also been calculated for every year (in another query).

I could include the amount of used reserves in a table, but that amount may
change if I do not have enough left in my closing inventory from my
preceeding year... I cannot use up more than I have!

Right now, I do not even know how to insert the first part of my query:
opening inventory: closing inventory of the previous year + new inventory -
used inventory

I thought it might be easier to build a formula on the opening inventory
since the used and new reserves would be from the same year.
I would then need a second query/formula saying that my closing inventory is
equal to the opeing balance of the next year.

My assumptions are all in place, and I am able to do this in excel, but
cannot seem to be able to transfer it into access. (in excel, i simply add
the colomns from the preceeding year)

I hope this clarifies things for you because I really need help with this!

Thanks, Tara.

Tara

Unless you use a text-type data field, you don't. But you can enter a true
date (day and month and year) in an Access date/time type field, then decide
to DISPLAY only the year portion.

How are you planning to use the "year"? (again, what you have drives what
you can do with it)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

It is a question of Inventory.
I have a known Inventory at the closing of 2006. I add (depending on the
case) new Inventory and I use a predetermined quantity each year, but
obviously I cannot use more than the existing Inventory. I must thus refer
to the Closing Inventory of the previous year before allowing my
predetermined usage to take place in the current year.

The only constant data I have entered is the Fiscal Year. Maybe this is
where I should ask my 1st question: how do you enter only the year as a
"date/hour" function into a table?

Tara

Tara

It all starts with the data. It's hard suggesting "how" until we have a
better idea of "what".

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello,

I'm trying to run a query on the opening and closing balances for several
years. The factors I need are:
opening balance
closing balance
new reserves
used reserves (same every year - 35,000 - UNLESS the opening balance is
less
than 35000)

closing balance in 2006 & opening balance in 2007 is 360,000

to calculate opening balance:
2007 is given
2008 is = 2007 + new reserves - used reserves
2009 is = 2008 + new reserves - used reserves
and so forth
(at about 2015 the used reserves would be less than 35,000 & therefore
should indicate amount in opening balance resulting in a balance of 0)

to calculate closing balance:
each year = next years opening balance

since my used reserves may change depending on the amount left in the
opening balance, I tried to insert it as the following:
Used Reserves: IIF([opening balance]>35000;35000;[opening balance])
By using this formula, I keep ending up with a circular formula.

My years are inserted into a table as a number as I could not figure out
how
to insert a year (w/o the day & month) as a date/hour function.

Tara.

 

[Tara]

Maybe it would help if I showed you what my query looks like so far...
I have 5 fields...

year - existing inventory - new inventory - used inventory - closing inventory
2006 - 300,000 - 0 - 0 - (= existing inventory)
2007 - 300,000 - 0 - 35,000 - (= closing inv of 2006 + new inv - used inv)
2008 - 300,000 - 10,000 - 35,000 - (= closing inv of 2007 + new inv - used
inv)
2009 - 300,000 - 3,000 - 35,000 - (= closing inv of 2008 + new inv - used inv)
and so forth

Any suggestions on how I can create a formula for the closing balance?

Tara

Tara

Unless you use a text-type data field, you don't. But you can enter a true
date (day and month and year) in an Access date/time type field, then decide
to DISPLAY only the year portion.

How are you planning to use the "year"? (again, what you have drives what
you can do with it)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

It is a question of Inventory.
I have a known Inventory at the closing of 2006. I add (depending on the
case) new Inventory and I use a predetermined quantity each year, but
obviously I cannot use more than the existing Inventory. I must thus refer
to the Closing Inventory of the previous year before allowing my
predetermined usage to take place in the current year.

The only constant data I have entered is the Fiscal Year. Maybe this is
where I should ask my 1st question: how do you enter only the year as a
"date/hour" function into a table?

Tara

Tara

It all starts with the data. It's hard suggesting "how" until we have a
better idea of "what".

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello,

I'm trying to run a query on the opening and closing balances for several
years. The factors I need are:
opening balance
closing balance
new reserves
used reserves (same every year - 35,000 - UNLESS the opening balance is
less
than 35000)

closing balance in 2006 & opening balance in 2007 is 360,000

to calculate opening balance:
2007 is given
2008 is = 2007 + new reserves - used reserves
2009 is = 2008 + new reserves - used reserves
and so forth
(at about 2015 the used reserves would be less than 35,000 & therefore
should indicate amount in opening balance resulting in a balance of 0)

to calculate closing balance:
each year = next years opening balance

since my used reserves may change depending on the amount left in the
opening balance, I tried to insert it as the following:
Used Reserves: IIF([opening balance]>35000;35000;[opening balance])
By using this formula, I keep ending up with a circular formula.

My years are inserted into a table as a number as I could not figure out
how
to insert a year (w/o the day & month) as a date/hour function.

Tara.

 

[Michael Gramelspacher]

Maybe it would help if I showed you what my query looks like so far...
I have 5 fields...

year - existing inventory - new inventory - used inventory - closing inventory
2006 - 300,000 - 0 - 0 - (= existing inventory)
2007 - 300,000 - 0 - 35,000 - (= closing inv of 2006 + new inv - used inv)
2008 - 300,000 - 10,000 - 35,000 - (= closing inv of 2007 + new inv - used
inv)
2009 - 300,000 - 3,000 - 35,000 - (= closing inv of 2008 + new inv - used inv)
and so forth

Any suggestions on how I can create a formula for the closing balance?

In a recent thread, I proposed this code for daily balances.


SELECT Vouchers.processing_date AS [Date],
Vouchers.vouchers_received AS Received,
Vouchers.vouchers_processed AS Processed,
NZ((SELECT SUM(t.vouchers_received) - SUM(t.vouchers_processed)
FROM Vouchers AS t
WHERE t.processing_date < Vouchers.processing_date),0)
+ Vouchers.vouchers_received - Vouchers.Vouchers_processed
AS [On Hand]
FROM Vouchers;

You are asking for the balances on the last day of the year, so maybe:

SELECT . . . .
FROM (above query)
WHERE [Date] = DateAdd("yyyy",DateDiff("yyyy",#1989-12-31#,[Date]),#1989-12-31#)

 

[Jeff Boyce]

Tara

Please re-read this thread.

It all starts with the data. You're starting with the query.

Please post a description of what data you are storing in what tables you
are using.

We need to figure out whether "you can get there from here". You are giving
us the "there", but not the "here".


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Maybe it would help if I showed you what my query looks like so far...
I have 5 fields...

year - existing inventory - new inventory - used inventory - closing inventory
2006 - 300,000 - 0 - 0 - (= existing inventory)
2007 - 300,000 - 0 - 35,000 - (= closing inv of 2006 + new inv - used inv)
2008 - 300,000 - 10,000 - 35,000 - (= closing inv of 2007 + new inv - used
inv)
2009 - 300,000 - 3,000 - 35,000 - (= closing inv of 2008 + new inv - used inv)
and so forth

Any suggestions on how I can create a formula for the closing balance?

Tara

Tara

Unless you use a text-type data field, you don't. But you can enter a true
date (day and month and year) in an Access date/time type field, then decide
to DISPLAY only the year portion.

How are you planning to use the "year"? (again, what you have drives what
you can do with it)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

It is a question of Inventory.
I have a known Inventory at the closing of 2006. I add (depending on the
case) new Inventory and I use a predetermined quantity each year, but
obviously I cannot use more than the existing Inventory. I must thus refer
to the Closing Inventory of the previous year before allowing my
predetermined usage to take place in the current year.

The only constant data I have entered is the Fiscal Year. Maybe this is
where I should ask my 1st question: how do you enter only the year as a
"date/hour" function into a table?

Tara



:

Tara

It all starts with the data. It's hard suggesting "how" until we have a
better idea of "what".

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello,

I'm trying to run a query on the opening and closing balances for several
years. The factors I need are:
opening balance
closing balance
new reserves
used reserves (same every year - 35,000 - UNLESS the opening

balance
is

less
than 35000)

closing balance in 2006 & opening balance in 2007 is 360,000

to calculate opening balance:
2007 is given
2008 is = 2007 + new reserves - used reserves
2009 is = 2008 + new reserves - used reserves
and so forth
(at about 2015 the used reserves would be less than 35,000 & therefore
should indicate amount in opening balance resulting in a balance of 0)

to calculate closing balance:
each year = next years opening balance

since my used reserves may change depending on the amount left in the
opening balance, I tried to insert it as the following:
Used Reserves: IIF([opening balance]>35000;35000;[opening balance])
By using this formula, I keep ending up with a circular formula.

My years are inserted into a table as a number as I could not

figure
out

how
to insert a year (w/o the day & month) as a date/hour function.

Tara.

 

[Tara]

Jeff,

Sorry, but I thought I had already given you all the data that needed to be
used, but here are the tables and all the info in each table. For this
query, I am pulling information from two tables (main variable assumptions
and ms) and a query.

main variable assumptions:
Fiscal Year which is the primary key - entered as a number
Currency Exchange
X Price
Y Price
Inflation
Electricity 1
Electricity 2
Diesel fuel
Transportation

ms:
case ID which is the primary key
Case Number
Existing Inventory
New Inventory %
Production
Exp.
Inv.
Total Delivered
Used Inventory - I have just added this colomn although as mentioned before
this may vary depending on what I have left in my opening inventory

Query 1 - MS New Inventory
New Reserves %
New Reserves 1: IIF([Fiscal Year]=2008;(IIF([Case
Number]=1;0;200000))*0.85;"")
New Inventory: IIF([New Reserves 1]="";0;[New Reserves 1]*[New Reserves %])

How my query looks so far:
Fiscal Year (from main variable assumption)
Case Number (from ms)
New Inventory (from Query 1)
Existing Inventory (from ms)
Used Inventory (from ms, but still have problem if the opening inventory is
less than what I plan to use up)
Opening Inventory (need help with: is equal to last years closing inventory)
Closing Inventory (need help with: year 2006 is equal to the exiting
inventory because that is where we started out. Every year following is equal
to the preceeding year plus new inventory minus used inventory)

I hope this helps to give a better idea of what I am trying to do.
Please let me know if there is anything else I need to give you.
Sorry, but I am still a beginner with Access, so I don't understand
everything that is needed.

Thank you,
Tara

Tara

Please re-read this thread.

It all starts with the data. You're starting with the query.

Please post a description of what data you are storing in what tables you
are using.

We need to figure out whether "you can get there from here". You are giving
us the "there", but not the "here".


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Maybe it would help if I showed you what my query looks like so far...
I have 5 fields...

year - existing inventory - new inventory - used inventory - closing inventory
2006 - 300,000 - 0 - 0 - (= existing inventory)
2007 - 300,000 - 0 - 35,000 - (= closing inv of 2006 + new inv - used inv)
2008 - 300,000 - 10,000 - 35,000 - (= closing inv of 2007 + new inv - used
inv)
2009 - 300,000 - 3,000 - 35,000 - (= closing inv of 2008 + new inv - used inv)
and so forth

Any suggestions on how I can create a formula for the closing balance?

Tara

Tara

Unless you use a text-type data field, you don't. But you can enter a true
date (day and month and year) in an Access date/time type field, then decide
to DISPLAY only the year portion.

How are you planning to use the "year"? (again, what you have drives what
you can do with it)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

It is a question of Inventory.
I have a known Inventory at the closing of 2006. I add (depending on the
case) new Inventory and I use a predetermined quantity each year, but
obviously I cannot use more than the existing Inventory. I must thus
refer
to the Closing Inventory of the previous year before allowing my
predetermined usage to take place in the current year.

The only constant data I have entered is the Fiscal Year. Maybe this is
where I should ask my 1st question: how do you enter only the year as a
"date/hour" function into a table?

Tara



:

Tara

It all starts with the data. It's hard suggesting "how" until we have a
better idea of "what".

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hello,

I'm trying to run a query on the opening and closing balances for
several
years. The factors I need are:
opening balance
closing balance
new reserves
used reserves (same every year - 35,000 - UNLESS the opening balance
is
less
than 35000)

closing balance in 2006 & opening balance in 2007 is 360,000

to calculate opening balance:
2007 is given
2008 is = 2007 + new reserves - used reserves
2009 is = 2008 + new reserves - used reserves
and so forth
(at about 2015 the used reserves would be less than 35,000 & therefore
should indicate amount in opening balance resulting in a balance of 0)

to calculate closing balance:
each year = next years opening balance

since my used reserves may change depending on the amount left in the
opening balance, I tried to insert it as the following:
Used Reserves: IIF([opening balance]>35000;35000;[opening balance])
By using this formula, I keep ending up with a circular formula.

My years are inserted into a table as a number as I could not figure
out
how
to insert a year (w/o the day & month) as a date/hour function.

Tara.