Cheers Paul, I removed the Having statement and returned the same 49 but my data, if it was perfect wouldn't produce any at all.
The end result of all our work but be an exception query that only shows entries where possible fraud or collusion between consultants and the administrator were...
Paul,
Im going to try that in the morning, in the meantime here is another possible answer. I got to this by using your previous querys. The sql code below cant be viewed in design view as I changed the line '
AND (AudMainOrders.Fees<>qryAudMainOrders.Fees
from = to <> to show disimilar fees...
Paul,
So near and yet so far!
If there are 2 transactions it works, if 3 then it shows only 1.
If the transactions [committed] are the same then it doesn't show (this is fine)
Here is my sample data.
EnquiryNumber AuditDate CommittedTotal
1001 12/12/2002 £1,234.00
1001 14/12/2003 £3,211.00...
Paul,
Let me try the explanantion again. Lets assume that there are 3 enq's with the same number, 1 has a value of 100
2 has a value of 100
3 has a value of 100
Only 1 of the 3 will show in the sort rather than the desired 3 records.
Option 1 would be to have no enq showing if the [committed]...
Paul,
You have a good point there. If there is only 1 Enquiry Number shown because the Committed hasn't changed then in an ideal world there is no need to show the Enquiry Number.
My expectation was that only the Enquiry Numbers that had changed would be shown with the Committed Total.
Whilst...
Paul,
Something very wierd going on here! On my database it still doesn't give the correct results. I entered the test data into a new dbase (1 table) and it works.
I have checked (many times) the syntax and it is the same on both databases.
Is it likely that (a) EnquiryNumber is about the 10th...
Paul,
The sql is the code you posted with auditdate field removed.
If we use the last 2 entries as an example, they should read:
CNC10610 £2500
CNC10610 £2100
CNC10625 £2800
CNC10625 £2700
CNC10625 £2600
What this data means is that the Enquiry Number X has had it monetary...
Paul,
Under I have listed part of the output to the query
EnquiryNumber CommitedTotal
2097008 £3,850.00
2100788 £350.00
2103969 £350.00
2104328 £350.00
2105112 £350.00
2106061 £2,800.00
2108879 £350.00
23783 £110.00
5244...
Sorry Paul, didn't work. The first query produces the EnquiryNumbers >2 with no problem but the second query gives results of only individual Enquiry Numbers (as expected)with a committed fee that pertains to the enquiry Number and not only those that have changed in the fee column.
Here is a...
Paul,
Many thanks for responding so quickly. It is highly probable that more than 2 Enquiry numbers will exist with the same dates and in addition that there may be more than 2 entries.
I was on similar lines using the count function but the latter part has defeated me.
For background info...
Hi guys,
I am hoping someone can point me in the right direction with this problem.
Query:
SELECT AudMainOrders.Date, AudMainOrders.EnquiryNumber, [OrderNumber] & " " & [OrderNumLetter] AS OrderNo, AudMainOrders.Consultant...
I have to design a databse that will allow multiple search criteria as below.
Name
Region
Skill_1
Skill_2
Etc
Now for the hard part, of the 300 or so names, 20 regions and dozen skill types, I want to return as an answer from a query the top X percent closest to the client request.
To...
I have a Table A in the main database that I require to be updated with
information that is imported from the web and then placed in Table B in the
same database.
So far so good, I need to update Table A from Table B only when the primary
keys match. In addition I would like to monitor/record...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.