convert text to numeric values
in [General]
|
Prev: TEst schreib zur�ck
Next: a
From: Toppo on 18 Feb 2010 05:49 My database (Access 2007) keeps track of bids from FE colleges. These are made in an academic year (these span two 'calendar years e.g. 2009-10 for the current academic year). Sometimes the bids are carried forward to, and paid in, the following academic year. The 'Bid Year' and 'Paid Year' are text fields. When designing a query that will give me the total sum of bids made, bids approved and bids actually paid, I can do separate ones based on 'Bid Year' and 'Paid Year' but the totals are quite often different if a year has bids that have been carried forward and paid in a different year than that which it was made. e.g. If a bid of £250 was made in 2008-09 but carried forward to 2009-10 and paid in that year, then a query based on the bid year will include the £250 in the bids made and also bids approved in 2008-09. If I do a query based on the paid year, this item will not appear. What I want is for a bid that is carried forward to appear on 2 academic year's 'accounts'. In my example I want the £250 to appear on the 2008-09 totals for bids made and approved and also on the 2009-10 totals as bids made, approved and paid. I am trying to replicate an excel spreadsheet that is being used at the moment and have nearly succeeded except for this business of items carried/brought forward.
From: Mrs. Ugh on 18 Feb 2010 08:58 Toppo- Can't you just use an OR in your query? Something like: SELECT...FROM...WHERE (((tblBids.BidYear)="2008-09") OR (tbl.Bids.PaidYear) = "2008-09")) "Toppo" wrote: > My database (Access 2007) keeps track of bids from FE colleges. These are > made in an academic year (these span two 'calendar years e.g. 2009-10 for the > current academic year). Sometimes the bids are carried forward to, and paid > in, the following academic year. The 'Bid Year' and 'Paid Year' are text > fields. When designing a query that will give me the total sum of bids made, > bids approved and bids actually paid, I can do separate ones based on 'Bid > Year' and 'Paid Year' but the totals are quite often different if a year has > bids that have been carried forward and paid in a different year than that > which it was made. e.g. If a bid of £250 was made in 2008-09 but carried > forward to 2009-10 and paid in that year, then a query based on the bid year > will include the £250 in the bids made and also bids approved in 2008-09. If > I do a query based on the paid year, this item will not appear. What I want > is for a bid that is carried forward to appear on 2 academic year's > 'accounts'. In my example I want the £250 to appear on the 2008-09 totals for > bids made and approved and also on the 2009-10 totals as bids made, approved > and paid. I am trying to replicate an excel spreadsheet that is being used at > the moment and have nearly succeeded except for this business of items > carried/brought forward.
From: Toppo on 23 Feb 2010 04:51 Many thanks for your reply, but I'm not too sure that I understand what you are getting at. Almost certainly it will be my fault for a less than explicit explanation. Can I try again? In my database (Access 2007), amongst many related tables, I have 2 that are pertinent: Table “Fund details” - fields (amongst others) Fund ID (auto number PK) Academic Year - Text Funds available - currency Additional funds - currency Special one-off funds - currency Funds carried forward from previous year - currency Special claims total - currency Special claims approved - currency Special claims paid - currency Table “Bids” Bid ID (Auto number – PK) Partner ID – number (linked to another table (College Details) with name details etc) Fund ID – number – linked to Fund Table on many to one) Bid Year - Text Paid Year – Text Bid carried forward to following year – Yes/No Bid carried forward to - Text Paid Year - text Name of bidder Bid detail Amount of bid - currency Amount approved – currency Final claim amount - currency I have 2 querys that sum up the totals for money available (funds) and bids made/approved/paid First – TOTAL_FUNDS: SELECT TBL_ FUND_DETAILS.id, TBL_FUND_DETAILS.[Academic Year], TBL_ FUND_DETAILS.[Total Funds], [TBL_FUND_DETAILS]![Confirmed Funds]+NZ([TBL_FUND_DETAILS]![Carried forward from previous year])+Nz([TBL_FUND_DETAILS]![Additional Funds])+Nz([TBL_FUND_DETAILS]![Special 'one-off funds']) AS [Total Funds Available inc special one-off] FROM TBL_FUND_DETAILS GROUP BY TBL_FUND_DETAILS.id, TBL_FUND_DETAILS.[Academic Year], [TBL_FUND_DETAILS]![Confirmed Funds]+NZ([TBL_FUND_DETAILS]![Carried forward from previous year])+Nz([TBL_FUND_DETAILS]![Additional Funds])+Nz([TBL_FUND_DETAILS]![Special 'one-off funds']); 2nd - query(BIDS_SUM) which sums up all the bids for each year in which they are made, so giving a SUM of bids made/bids approved/bids finally paid (i.e. grouping on the Bid Year). I can do a similar one for sum of bids for the year in which they are paid (i.e. grouping on the Paid Year). This is the SQL of that query SELECT TBL_BIDS.[Bid Year], Sum(TBL_BIDS.[Amount of Bid]) AS [SumOfAmount of Bid], Sum(TBL_BIDS.[Amount approved]) AS [SumOfAmount approved], Sum(TBL_BIDS.[Final claim amount]) AS [SumOfFinal claim amount] FROM TBL_BIDS INNER JOIN [College Details] ON TBL_BIDS.[Partner ID] = [College Details].[Ref No] GROUP BY TBL_BIDS.[Bid Year]; But what I can't do (& what I want to do) is get a sum of the bids made in a particular academic year including those brought forward (if any) AND carried forward (if any). I want to use this in another query that will give me a summary query/table listing the totals for a number of academic years So, for instance, if in 2006-07: Bids brought forward from 2005-06 £250 Bids received 2006-07 £5,000 Then total bids received 2006-07 should read £5,250 Bid approved from 2005-06 £250 Bids approved from 2006-07 £4,000 Total bids approved 2006-07 should read £4,250 Total bids paid 2006-07 (which includes £250 from 2005-06) £3,750 Bids carried forward to 2007-08 £500 (this to show in respective columns for 2007-08) I want the query to show me (for the row for 2006-07) Total Bids received £5,250 Total Bids approved £4,250 Total bids paid £3,750 Below is the query that I can't get to work & which combines TBL_FUND_DETAILS& QRY_BID_SUM & QRY_TOTAL_FUNDS plus 5 columns with expressions. I think it is these that the solution (if there is one) lies. SELECT TBL_FUND_DETAILS.[Confirmed Funds], TBL_FUND_DETAILS.[Carried forward from previous year], TBL_FUND_DETAILS.[Additional Funds], TBL_FUND_DETAILS.[Special 'one-off funds'], TBL_FUND_DETAILS.[Special 'One-off' claims], QRY_TOTAL_FUNDS.*, (QRY_BIDS_SUM![SumOfAmount of Bid]+Nz(TBL_FUND_DETAILS![Special 'One-off' claims])) AS [Total bids inc special one offs], ([QRY_BIDS_SUM]![SumOfAmount approved]+Nz([TBL_FUND_DETAILS]![Special claims approved])) AS [Total Approved inc Special], ([TBL_FUND_DETAILS]![Confirmed Funds]+Nz([TBL_FUND_DETAILS]![Carried forward from previous year])+NZ([TBL_FUND_DETAILS]![Additional Funds])+Nz([TBL_FUND_DETAILS]![Special 'one-off funds']))-[Total Approved inc Special] AS [Balance Funds less Bids app inc specials], [QRY_BIDS_SUM]![SumOfFinal claim ammount]+Nz([TBL_FUND_DETAILS]![Special claims claimed]) AS [Total Final Claims submitted inc specials], ([TBL_FUND_DETAILS]![Confirmed Funds]+Nz([TBL_FUND_DETAILS]![Carried forward from previous year])+Nz([TBL_FUND_DETAILS]![Additional Funds])+Nz([TBL_FUND_DETAILS]![Special 'one-off funds']))-Nz([Total Final Claims submitted inc specials]) AS [Total funds available less final claims inc Specials] FROM (QRY_BIDS_SUM INNER JOIN QRY_TOTAL_FUNDS ON QRY_BIDS_SUM.[Bid Year] = QRY_TOTAL_FUNDS.[Academic Year]) INNER JOIN TBL_FUND_DETAILS ON QRY_TOTAL_FUNDS.id = TBL_FUND_DETAILS.id; Any help would be gratefully received. "Mrs. Ugh" wrote: > Toppo- > Can't you just use an OR in your query? Something like: > SELECT...FROM...WHERE (((tblBids.BidYear)="2008-09") OR (tbl.Bids.PaidYear) > = "2008-09")) > > "Toppo" wrote: > > > My database (Access 2007) keeps track of bids from FE colleges. These are > > made in an academic year (these span two 'calendar years e.g. 2009-10 for the > > current academic year). Sometimes the bids are carried forward to, and paid > > in, the following academic year. The 'Bid Year' and 'Paid Year' are text > > fields. When designing a query that will give me the total sum of bids made, > > bids approved and bids actually paid, I can do separate ones based on 'Bid > > Year' and 'Paid Year' but the totals are quite often different if a year has > > bids that have been carried forward and paid in a different year than that > > which it was made. e.g. If a bid of £250 was made in 2008-09 but carried > > forward to 2009-10 and paid in that year, then a query based on the bid year > > will include the £250 in the bids made and also bids approved in 2008-09. If > > I do a query based on the paid year, this item will not appear. What I want > > is for a bid that is carried forward to appear on 2 academic year's > > 'accounts'. In my example I want the £250 to appear on the 2008-09 totals for > > bids made and approved and also on the 2009-10 totals as bids made, approved > > and paid. I am trying to replicate an excel spreadsheet that is being used at > > the moment and have nearly succeeded except for this business of items > > carried/brought forward.
From: Arvin Meyer [MVP] on 23 Feb 2010 08:04 Perhaps this will help: http://www.mvps.org/access/modules/mdl0001.htm -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Toppo" <Toppo(a)discussions.microsoft.com> wrote in message news:8F639499-419E-448E-B816-A6D95995C91C(a)microsoft.com... > Many thanks for your reply, but I'm not too sure that I understand what > you > are getting at. Almost certainly it will be my fault for a less than > explicit > explanation. Can I try again? > In my database (Access 2007), amongst many related tables, I have 2 that > are > pertinent: > Table "Fund details" - fields (amongst others) > Fund ID (auto number PK) > Academic Year - Text > Funds available - currency > Additional funds - currency > Special one-off funds - currency > Funds carried forward from previous year - currency > > Special claims total - currency > Special claims approved - currency > Special claims paid - currency > Table "Bids" > Bid ID (Auto number - PK) > Partner ID - number (linked to another table (College Details) with name > details etc) > Fund ID - number - linked to Fund Table on many to one) > Bid Year - Text > Paid Year - Text > Bid carried forward to following year - Yes/No > Bid carried forward to - Text > Paid Year - text > Name of bidder > Bid detail > Amount of bid - currency > Amount approved - currency > Final claim amount - currency > I have 2 querys that sum up the totals for money available (funds) and > bids > made/approved/paid > First - TOTAL_FUNDS: > SELECT TBL_ FUND_DETAILS.id, TBL_FUND_DETAILS.[Academic Year], TBL_ > FUND_DETAILS.[Total Funds], [TBL_FUND_DETAILS]![Confirmed > Funds]+NZ([TBL_FUND_DETAILS]![Carried forward from previous > year])+Nz([TBL_FUND_DETAILS]![Additional > Funds])+Nz([TBL_FUND_DETAILS]![Special 'one-off funds']) AS [Total Funds > Available inc special one-off] > FROM TBL_FUND_DETAILS > GROUP BY TBL_FUND_DETAILS.id, TBL_FUND_DETAILS.[Academic Year], > [TBL_FUND_DETAILS]![Confirmed Funds]+NZ([TBL_FUND_DETAILS]![Carried > forward > from previous year])+Nz([TBL_FUND_DETAILS]![Additional > Funds])+Nz([TBL_FUND_DETAILS]![Special 'one-off funds']); > 2nd - query(BIDS_SUM) which sums up all the bids for each year in which > they > are made, so giving a SUM of bids made/bids approved/bids finally paid > (i.e. > grouping on the Bid Year). I can do a similar one for sum of bids for the > year in which they are paid (i.e. grouping on the Paid Year). This is the > SQL > of that query > SELECT TBL_BIDS.[Bid Year], Sum(TBL_BIDS.[Amount of Bid]) AS [SumOfAmount > of > Bid], Sum(TBL_BIDS.[Amount approved]) AS [SumOfAmount approved], > Sum(TBL_BIDS.[Final claim amount]) AS [SumOfFinal claim amount] > FROM TBL_BIDS INNER JOIN [College Details] ON TBL_BIDS.[Partner ID] = > [College Details].[Ref No] > GROUP BY TBL_BIDS.[Bid Year]; > > But what I can't do (& what I want to do) is get a sum of the bids made in > a > particular academic year including those brought forward (if any) AND > carried > forward (if any). I want to use this in another query that will give me a > summary query/table listing the totals for a number of academic years > So, for instance, if in 2006-07: > Bids brought forward from 2005-06 �250 > Bids received 2006-07 �5,000 > Then total bids received 2006-07 should read �5,250 > Bid approved from 2005-06 �250 > Bids approved from 2006-07 �4,000 > Total bids approved 2006-07 should read �4,250 > Total bids paid 2006-07 > (which includes �250 from 2005-06) �3,750 > Bids carried forward to 2007-08 �500 (this to show in respective columns > for 2007-08) > I want the query to show me (for the row for 2006-07) > Total Bids received �5,250 > Total Bids approved �4,250 > Total bids paid �3,750 > Below is the query that I can't get to work & which combines > TBL_FUND_DETAILS& QRY_BID_SUM & QRY_TOTAL_FUNDS plus 5 columns with > expressions. I think it is these that the solution (if there is one) lies. > SELECT TBL_FUND_DETAILS.[Confirmed Funds], TBL_FUND_DETAILS.[Carried > forward > from previous year], TBL_FUND_DETAILS.[Additional Funds], > TBL_FUND_DETAILS.[Special 'one-off funds'], TBL_FUND_DETAILS.[Special > 'One-off' claims], QRY_TOTAL_FUNDS.*, (QRY_BIDS_SUM![SumOfAmount of > Bid]+Nz(TBL_FUND_DETAILS![Special 'One-off' claims])) AS [Total bids inc > special one offs], ([QRY_BIDS_SUM]![SumOfAmount > approved]+Nz([TBL_FUND_DETAILS]![Special claims approved])) AS [Total > Approved inc Special], ([TBL_FUND_DETAILS]![Confirmed > Funds]+Nz([TBL_FUND_DETAILS]![Carried forward from previous > year])+NZ([TBL_FUND_DETAILS]![Additional > Funds])+Nz([TBL_FUND_DETAILS]![Special 'one-off funds']))-[Total Approved > inc > Special] AS [Balance Funds less Bids app inc specials], > [QRY_BIDS_SUM]![SumOfFinal claim ammount]+Nz([TBL_FUND_DETAILS]![Special > claims claimed]) AS [Total Final Claims submitted inc specials], > ([TBL_FUND_DETAILS]![Confirmed Funds]+Nz([TBL_FUND_DETAILS]![Carried > forward > from previous year])+Nz([TBL_FUND_DETAILS]![Additional > Funds])+Nz([TBL_FUND_DETAILS]![Special 'one-off funds']))-Nz([Total Final > Claims submitted inc specials]) AS [Total funds available less final > claims > inc Specials] > FROM (QRY_BIDS_SUM INNER JOIN QRY_TOTAL_FUNDS ON QRY_BIDS_SUM.[Bid Year] = > QRY_TOTAL_FUNDS.[Academic Year]) INNER JOIN TBL_FUND_DETAILS ON > QRY_TOTAL_FUNDS.id = TBL_FUND_DETAILS.id; > Any help would be gratefully received. > > > "Mrs. Ugh" wrote: > >> Toppo- >> Can't you just use an OR in your query? Something like: >> SELECT...FROM...WHERE (((tblBids.BidYear)="2008-09") OR >> (tbl.Bids.PaidYear) >> = "2008-09")) >> >> "Toppo" wrote: >> >> > My database (Access 2007) keeps track of bids from FE colleges. These >> > are >> > made in an academic year (these span two 'calendar years e.g. 2009-10 >> > for the >> > current academic year). Sometimes the bids are carried forward to, and >> > paid >> > in, the following academic year. The 'Bid Year' and 'Paid Year' are >> > text >> > fields. When designing a query that will give me the total sum of bids >> > made, >> > bids approved and bids actually paid, I can do separate ones based on >> > 'Bid >> > Year' and 'Paid Year' but the totals are quite often different if a >> > year has >> > bids that have been carried forward and paid in a different year than >> > that >> > which it was made. e.g. If a bid of �250 was made in 2008-09 but >> > carried >> > forward to 2009-10 and paid in that year, then a query based on the bid >> > year >> > will include the �250 in the bids made and also bids approved in >> > 2008-09. If >> > I do a query based on the paid year, this item will not appear. What I >> > want >> > is for a bid that is carried forward to appear on 2 academic year's >> > 'accounts'. In my example I want the �250 to appear on the 2008-09 >> > totals for >> > bids made and approved and also on the 2009-10 totals as bids made, >> > approved >> > and paid. I am trying to replicate an excel spreadsheet that is being >> > used at >> > the moment and have nearly succeeded except for this business of items >> > carried/brought forward.
From: Toppo on 23 Feb 2010 10:08
Thanks for the interest Arvin. However the module you gave a link to was to convert numeric (currency) to text, wereas my original problem (& still is) that I can only see being able to convert TEXT (academic years such as 2007-08) into a Numeric Value will assit me in being able to do what I want to do - see below for full explanation. Thanks anyway. "Arvin Meyer [MVP]" wrote: > Perhaps this will help: > > http://www.mvps.org/access/modules/mdl0001.htm > -- > Arvin Meyer, MCP, MVP > http://www.datastrat.com > http://www.mvps.org/access > http://www.accessmvp.com > > > "Toppo" <Toppo(a)discussions.microsoft.com> wrote in message > news:8F639499-419E-448E-B816-A6D95995C91C(a)microsoft.com... > > Many thanks for your reply, but I'm not too sure that I understand what > > you > > are getting at. Almost certainly it will be my fault for a less than > > explicit > > explanation. Can I try again? > > In my database (Access 2007), amongst many related tables, I have 2 that > > are > > pertinent: > > Table "Fund details" - fields (amongst others) > > Fund ID (auto number PK) > > Academic Year - Text > > Funds available - currency > > Additional funds - currency > > Special one-off funds - currency > > Funds carried forward from previous year - currency > > > > Special claims total - currency > > Special claims approved - currency > > Special claims paid - currency > > Table "Bids" > > Bid ID (Auto number - PK) > > Partner ID - number (linked to another table (College Details) with name > > details etc) > > Fund ID - number - linked to Fund Table on many to one) > > Bid Year - Text > > Paid Year - Text > > Bid carried forward to following year - Yes/No > > Bid carried forward to - Text > > Paid Year - text > > Name of bidder > > Bid detail > > Amount of bid - currency > > Amount approved - currency > > Final claim amount - currency > > I have 2 querys that sum up the totals for money available (funds) and > > bids > > made/approved/paid > > First - TOTAL_FUNDS: > > SELECT TBL_ FUND_DETAILS.id, TBL_FUND_DETAILS.[Academic Year], TBL_ > > FUND_DETAILS.[Total Funds], [TBL_FUND_DETAILS]![Confirmed > > Funds]+NZ([TBL_FUND_DETAILS]![Carried forward from previous > > year])+Nz([TBL_FUND_DETAILS]![Additional > > Funds])+Nz([TBL_FUND_DETAILS]![Special 'one-off funds']) AS [Total Funds > > Available inc special one-off] > > FROM TBL_FUND_DETAILS > > GROUP BY TBL_FUND_DETAILS.id, TBL_FUND_DETAILS.[Academic Year], > > [TBL_FUND_DETAILS]![Confirmed Funds]+NZ([TBL_FUND_DETAILS]![Carried > > forward > > from previous year])+Nz([TBL_FUND_DETAILS]![Additional > > Funds])+Nz([TBL_FUND_DETAILS]![Special 'one-off funds']); > > 2nd - query(BIDS_SUM) which sums up all the bids for each year in which > > they > > are made, so giving a SUM of bids made/bids approved/bids finally paid > > (i.e. > > grouping on the Bid Year). I can do a similar one for sum of bids for the > > year in which they are paid (i.e. grouping on the Paid Year). This is the > > SQL > > of that query > > SELECT TBL_BIDS.[Bid Year], Sum(TBL_BIDS.[Amount of Bid]) AS [SumOfAmount > > of > > Bid], Sum(TBL_BIDS.[Amount approved]) AS [SumOfAmount approved], > > Sum(TBL_BIDS.[Final claim amount]) AS [SumOfFinal claim amount] > > FROM TBL_BIDS INNER JOIN [College Details] ON TBL_BIDS.[Partner ID] = > > [College Details].[Ref No] > > GROUP BY TBL_BIDS.[Bid Year]; > > > > But what I can't do (& what I want to do) is get a sum of the bids made in > > a > > particular academic year including those brought forward (if any) AND > > carried > > forward (if any). I want to use this in another query that will give me a > > summary query/table listing the totals for a number of academic years > > So, for instance, if in 2006-07: > > Bids brought forward from 2005-06 £250 > > Bids received 2006-07 £5,000 > > Then total bids received 2006-07 should read £5,250 > > Bid approved from 2005-06 £250 > > Bids approved from 2006-07 £4,000 > > Total bids approved 2006-07 should read £4,250 > > Total bids paid 2006-07 > > (which includes £250 from 2005-06) £3,750 > > Bids carried forward to 2007-08 £500 (this to show in respective columns > > for 2007-08) > > I want the query to show me (for the row for 2006-07) > > Total Bids received £5,250 > > Total Bids approved £4,250 > > Total bids paid £3,750 > > Below is the query that I can't get to work & which combines > > TBL_FUND_DETAILS& QRY_BID_SUM & QRY_TOTAL_FUNDS plus 5 columns with > > expressions. I think it is these that the solution (if there is one) lies. > > SELECT TBL_FUND_DETAILS.[Confirmed Funds], TBL_FUND_DETAILS.[Carried > > forward > > from previous year], TBL_FUND_DETAILS.[Additional Funds], > > TBL_FUND_DETAILS.[Special 'one-off funds'], TBL_FUND_DETAILS.[Special > > 'One-off' claims], QRY_TOTAL_FUNDS.*, (QRY_BIDS_SUM![SumOfAmount of > > Bid]+Nz(TBL_FUND_DETAILS![Special 'One-off' claims])) AS [Total bids inc > > special one offs], ([QRY_BIDS_SUM]![SumOfAmount > > approved]+Nz([TBL_FUND_DETAILS]![Special claims approved])) AS [Total > > Approved inc Special], ([TBL_FUND_DETAILS]![Confirmed > > Funds]+Nz([TBL_FUND_DETAILS]![Carried forward from previous > > year])+NZ([TBL_FUND_DETAILS]![Additional > > Funds])+Nz([TBL_FUND_DETAILS]![Special 'one-off funds']))-[Total Approved > > inc > > Special] AS [Balance Funds less Bids app inc specials], > > [QRY_BIDS_SUM]![SumOfFinal claim ammount]+Nz([TBL_FUND_DETAILS]![Special > > claims claimed]) AS [Total Final Claims submitted inc specials], > > ([TBL_FUND_DETAILS]![Confirmed Funds]+Nz([TBL_FUND_DETAILS]![Carried > > forward > > from previous year])+Nz([TBL_FUND_DETAILS]![Additional > > Funds])+Nz([TBL_FUND_DETAILS]![Special 'one-off funds']))-Nz([Total Final > > Claims submitted inc specials]) AS [Total funds available less final > > claims > > inc Specials] > > FROM (QRY_BIDS_SUM INNER JOIN QRY_TOTAL_FUNDS ON QRY_BIDS_SUM.[Bid Year] = > > QRY_TOTAL_FUNDS.[Academic Year]) INNER JOIN TBL_FUND_DETAILS ON > > QRY_TOTAL_FUNDS.id = TBL_FUND_DETAILS.id; > > Any help would be gratefully received. > > > > > > "Mrs. Ugh" wrote: > > > >> Toppo- > >> Can't you just use an OR in your query? Something like: > >> SELECT...FROM...WHERE (((tblBids.BidYear)="2008-09") OR > >> (tbl.Bids.PaidYear) > >> = "2008-09")) > >> > >> "Toppo" wrote: > >> > >> > My database (Access 2007) keeps track of bids from FE colleges. These > >> > are > >> > made in an academic year (these span two 'calendar years e.g. 2009-10 > >> > for the > >> > current academic year). Sometimes the bids are carried forward to, and > >> > paid > >> > in, the following academic year. The 'Bid Year' and 'Paid Year' are > >> > text > >> > fields. When designing a query that will give me the total sum of bids > >> > made, > >> > bids approved and bids actually paid, I can do separate ones based on > >> > 'Bid > >> > Year' and 'Paid Year' but the totals are quite often different if a > >> > year has > >> > bids that have been carried forward and paid in a different year than > >> > that > >> > which it was made. e.g. If a bid of £250 was made in 2008-09 but > >> > carried > >> > forward to 2009-10 and paid in that year, then a query based on the bid > >> > year > >> > will include the £250 in the bids made and also bids approved in > >> > 2008-09. If > >> > I do a query based on the paid year, this item will not appear. What I > >> > want > >> > is for a bid that is carried forward to appear on 2 academic year's > >> > 'accounts'. In my example I want the £250 to appear on the 2008-09 > >> > totals for > >> > bids made and approved and also on the 2009-10 totals as bids made, > >> > approved > >> > and paid. I am trying to replicate an excel spreadsheet that is being > >> > used at > >> > the moment and have nearly succeeded except for this business of items > >> > carried/brought forward. > > > . > |