evaluating duplicate records

[Guest]

I am creating a DB which tracks and atuorises Final accounts for a project i
am workiong on, and I need to be able to show the savings I am making after
challenging the initial request for monies. To this end I have used the find
duplicates wizard to crearte a query (SQL below) that shows the records that
have multiple Final accounts submitted. but am now struggling with how too
work out hte differences.

could someone plesae help.

thank in advance.


SELECT FAsub_info_tbl.[OLO REF], FAsub_info_tbl.AutoNo_FASub_tbl,
FAsub_info_tbl.[Final Account Rec'd], FAsub_info_tbl.[A&D Submission],
FAsub_info_tbl.[Build Submission]
FROM FAsub_info_tbl
WHERE (((FAsub_info_tbl.[OLO REF]) In (SELECT [OLO REF] FROM
[FAsub_info_tbl] As Tmp GROUP BY [OLO REF] HAVING Count(*)>1 )))
ORDER BY FAsub_info_tbl.[OLO REF];

 

[Michel Walsh]

What do you mean by "working out the difference"? Finding the minimum
submission?


SELECT [olo ref], MIN(A&D Submission], LAST(budget)
FROM somewhere
GROUP BY [olo ref]
HAVING MIN(A&D Submission] > LAST(budget)


as example, will display the [olo ref] having a submission exceeding the
budgeted value (I don't know that the fields represent, so I made up my own
scenario).


Vanderghast, Access MVP

 

[Michel Walsh]

typos, should have been:


SELECT [olo ref], MIN([A&D Submission]), LAST(budget)
FROM somewhere
GROUP BY [olo ref]
HAVING MIN([A&D Submission]) > LAST(budget)



Vanderghast, Access MVP

What do you mean by "working out the difference"? Finding the minimum
submission?


SELECT [olo ref], MIN(A&D Submission], LAST(budget)
FROM somewhere
GROUP BY [olo ref]
HAVING MIN(A&D Submission] > LAST(budget)


as example, will display the [olo ref] having a submission exceeding the
budgeted value (I don't know that the fields represent, so I made up my
own scenario).


Vanderghast, Access MVP

I am creating a DB which tracks and atuorises Final accounts for a project
i
am workiong on, and I need to be able to show the savings I am making
after
challenging the initial request for monies. To this end I have used the
find
duplicates wizard to crearte a query (SQL below) that shows the records
that
have multiple Final accounts submitted. but am now struggling with how
too
work out hte differences.

could someone plesae help.

thank in advance.


SELECT FAsub_info_tbl.[OLO REF], FAsub_info_tbl.AutoNo_FASub_tbl,
FAsub_info_tbl.[Final Account Rec'd], FAsub_info_tbl.[A&D Submission],
FAsub_info_tbl.[Build Submission]
FROM FAsub_info_tbl
WHERE (((FAsub_info_tbl.[OLO REF]) In (SELECT [OLO REF] FROM
[FAsub_info_tbl] As Tmp GROUP BY [OLO REF] HAVING Count(*)>1 )))
ORDER BY FAsub_info_tbl.[OLO REF];