How to calculate variant in a field

[zyus]

I hv this table structure

Month Acno Netbal
Jan 123 1000
Feb 123 300

How to make a query or report to show that acno 123 is reduced by 700 in the
netbal.

Thanks

 

[John W. Vinson]

I hv this table structure

Month Acno Netbal
Jan 123 1000
Feb 123 300

How to make a query or report to show that acno 123 is reduced by 700 in the
netbal.

Thanks

So you will never need a second year? Or you scrap the database every
December? or is [Month] a Date/Time field?

I'd strongly suggest using a BalanceDate field with a Date/Time value
(#1/1/2008#, #12/1/2008#, #1/1/2009# for example); you could then do a Query
such as

SELECT A.Acno, A.Netbal, B.Netbal-A.Netbal AS Variance
FROM tablename AS A INNER JOIN tablename AS B
ON A.Acno = B.Acno
WHERE A.BalanceDate = (SELECT MAX C.[BalanceDate] FROM tablename AS C WHERE
C.Acno = A.Acno AND C.[BalanceDate] < B.[BalanceDate])

 

[zyus]

John,

I used access to analyse data that i derived from other system on monthly
basis.

The data that i have to differentiate between each month is

Month (data type text) - consist of 1 - 12
Yr - (data type text) - consist of year ex 2008 and so on

I intend to append 2 months data into one table and to find variance of each
data field based on the month/yr.

I can do a report by grouping the data based on month but i couldnt get the
variance due to the records are in the same field and table.

Hope i've made a clear explaination

I hv this table structure

Month Acno Netbal
Jan 123 1000
Feb 123 300

How to make a query or report to show that acno 123 is reduced by 700 in the
netbal.

Thanks

So you will never need a second year? Or you scrap the database every
December? or is [Month] a Date/Time field?

I'd strongly suggest using a BalanceDate field with a Date/Time value
(#1/1/2008#, #12/1/2008#, #1/1/2009# for example); you could then do a Query
such as

SELECT A.Acno, A.Netbal, B.Netbal-A.Netbal AS Variance
FROM tablename AS A INNER JOIN tablename AS B
ON A.Acno = B.Acno
WHERE A.BalanceDate = (SELECT MAX C.[BalanceDate] FROM tablename AS C WHERE
C.Acno = A.Acno AND C.[BalanceDate] < B.[BalanceDate])

 

[John W. Vinson]

John,

I used access to analyse data that i derived from other system on monthly
basis.

The data that i have to differentiate between each month is

Month (data type text) - consist of 1 - 12
Yr - (data type text) - consist of year ex 2008 and so on

I intend to append 2 months data into one table and to find variance of each
data field based on the month/yr.

I can do a report by grouping the data based on month but i couldnt get the
variance due to the records are in the same field and table.

Hope i've made a clear explaination

Access does not (automatically) know that the Text String "April" comes after
the text string "March". In fact it doesn't; A sorts before M alphabetically.

However, it DOES know that the Date value #1-March-2008# sorts before the Date
value #1-April-2008#.

Your job will be MUCH EASIER if you store Date information in a Date field.
Access can work with date fields, as dates; without code to convert the text
string and integer to date/time values, it cannot.