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 said:
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"))
:
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.
