Drill down query problem 1

[cbase]

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, AudMainOrders!Hours*AudMainOrders!Rate+AudMainOrders!Days*AudMainOrders!Rate+AudMainOrders!PVB*AudMainOrders!Rate+AudMainOrders!EWS+AudMainOrders!EwsExp AS CommitedTotal
FROM AudMainOrders
GROUP BY AudMainOrders.Date, AudMainOrders.EnquiryNumber, [OrderNumber] & " " & [OrderNumLetter], AudMainOrders.Consultant, AudMainOrders!Hours*AudMainOrders!Rate+AudMainOrders!Days*AudMainOrders!Rate+AudMainOrders!PVB*AudMainOrders!Rate+AudMainOrders!EWS+AudMainOrders!EwsExp
HAVING (((AudMainOrders.Date) Between [Start Date] And [End Date]))
ORDER BY AudMainOrders.EnquiryNumber;

I have listed the sql as far as I can get, now I need to limit the results to those where a change is detected between the committed total field.
That is, most of the results will have only one entry, these are to be disregarded the unique identifier (semi) will be the EnquiryNumber.

If an Enquiry Number appears more than once then if the difference is in the Committed Total Then list.

Appreciate all of your help

Diverdon

 

[PaulBricker]

Well, I'm going to take a stab at this. First create a new query. Add your EnquiryNumber twice. Turn on the Totals and GroupBy the first one and Count the second one. Then add this query to your posted query above. Join the EnquiryNumbers, and add the CountOfEnquiryNumber to the query. On the Total line select Where. On the Criteria line put > 1. This should return records where you have duplicate EnquiryNumbers. Now the question becomes whether we send it out to a function to determine if there is a difference between the Committed Total fields or if we can do this with a derived table. First, is it likely that the same EnquiryNumbers will have the same dates in the date field or will they likely be different dates? Also, how likely is it that you will have more than 2 records with the same EnquiryNumber?

Paul

 

[cbase]

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, the records exist in an audit trail table in which a new record is created each time there is a modification in the main tables. The object of this query is to identify if material changes ie money amounts have been made and who by. The who by, is contained in the record being queried by this sql.
Again my thanks for helping me with this problem and i look forward with interest to the function resolution as I have never progressed that far before.

Don

 

[PaulBricker]

Don, in thinking about this, I would like to try using another query to see if that might do it. To rehash where I think we are at, you have a query (I'll call qry1) that returns records with a CountOfInquiryNumber >1. If that's the case, then I would create another query using qry1. Add the EnquiryNumber and the CommittedTotal fields. Turn on the Totals, and GroupBy both fields. In the third column on the field line put Count(*) and on the Totals line select Expression and on the Criteria line put < 2. This should give you all the EnquiryNumbers where the CommittedTotals are different. Try it out (I don't have data that matches yours so some of it is assumption) and let me know how the results compare to what you actually need.

Paul

 

[cbase]

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 sample of the data (very simplified) to illustrate the problem:

EnqNumber Fee AuditDate User
1001 25.50 01/08/03 DM
1002 120.00 02/08/03 JK
1001 35.50 01/08/03 JS


The hoped for result would be
EnqNumber Fee
1001 25.50 01/08/03 DM
1001 35.50 01/08/03 JS


The conclusion would be that JS altered the Fee by £10 and we would then question why.

Hopefully this explains better than my original post.

Thanks for helping out.

Don

 

[PaulBricker]

I think we need to narrow our approach and then add addtional info after. Can you do this

SELECT AudMainOrders.EnquiryNumber, AudMainOrders!Hours*AudMainOrders!Rate+AudMainOrders!Days*AudMainOrders!Rate+AudMainOrders!PVB*AudMainOrders!Rate+AudMainOrders!EWS+AudMainOrders!EwsExp AS CommitedTotal
FROM qryEnquiryGroupBY INNER JOIN AudMainOrders ON qryEnquiryGroupBY.EnquiryNumber = AudMainOrders.EnquiryNumber
WHERE (((AudMainOrders.AuditDate) Between [Enter Start Date] And [Enter End Date]))AND ((qryEnquiryGroupBY.CountOfEnquiryNumber)>1))
GROUP BY AudMainOrders.EnquiryNumber, AudMainOrders!Hours*AudMainOrders!Rate+AudMainOrders!Days*AudMainOrders!Rate+AudMainOrders!PVB*AudMainOrders!Rate+AudMainOrders!EWS+AudMainOrders!EwsExp;

qryEnquiryGroupBy is my query that counts the EnquiryNumbers and filters by values >1

See if this query doesn't return the EnquiryNumbers where there is a difference between the CommittedTotals. If it does, then we just need to join it to another Table/Query to get the rest (user, consultant, AuditDate) of the fields into the record. Let me know.

Paul

 

[cbase]

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 £700.00
5244 GS £1,050.00
5244.GS £700.00
5244.GS £875.00
5244.GS £1,050.00
5457 £700.00
CNC £0.00
CNC10382 £0.00
CNC10519 £350.00
CNC10595 £350.00
CNC10610 £2,450.00
CNC10625 £2,800.00

My understanding is that it would need to list at least 2 identical EnquiryNumbers consecutively in order to determine if a commited total had been changed and from what amount to what amount. As you can see some have only 1 number
I have cut out the AuditDate field as it complained it could have come from 2 sources. I don't think this is material to the problem though.

So I'm afraid its still a struggle, I appreciate your valuable time on this especially at this time of the year.

Don

 

[PaulBricker]

Don, can you post what the results should have looked like. Are there records that should be there that don't seem to appear. Can you post the SQL from this latest query. I'm sure it can be done. I was able to do it in my sample data but can't seem to get it translated to your situation. I don't mind the time at all, but I'm running a little short Friday and Saturday. I will be away for Friday night and all day Saturday. Post whatever you can and I'll do whatever possible to get this squared away.

Paul

 

[cbase]

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 value changed (which shouldn't happen) but can on occasions be legitimate on a particular date, and by whom.
The fields not shown give the additional info.


Don

 

[PaulBricker]

OK, I'm not sure why none of the values for CommittedTotal from this group
CNC10610 £2,450.00
CNC10625 £2,800.00
showed up in this group
CNC10610 £2500
CNC10610 £2100
CNC10625 £2800
CNC10625 £2700
CNC10625 £2600
but I'm going to post the SQL from both my queries and see if that helps.

qryEnquiryGroupBy is this

[blue]SELECT AudMainOrders.EnquiryNumber, Count(AudMainOrders.EnquiryNumber) AS CountOfEnquiryNumber
FROM AudMainOrders
GROUP BY AudMainOrders.EnquiryNumber
HAVING (((Count(AudMainOrders.EnquiryNumber))>1));[/blue]

The SQL from the second query, qryAudMainOrders is

[blue]SELECT AudMainOrders.EnquiryNumber, AudMainOrders.CommittedTotal
FROM qryAudMainGroup INNER JOIN AudMainOrders ON qryEnquiryGroupBy.EnquiryNumber = AudMainOrders.EnquiryNumber
GROUP BY AudMainOrders.EnquiryNumber, AudMainOrders.CommittedTotal;[/blue]

My test data looks like this

[red]

EnquiryNumber	   AuditDate	   CommittedTotal
1001	              12/12/03	  $1,234.00
1002	              12/12/03	  $3,214.00
1003	              12/13/03	 $65,245.00
1001	              12/14/03	  $3,211.00
1004	              12/15/03	  $3,654.00
1005	              12/12/03	  $3,652.00
1003	              12/14/03	  $5,224.00

[/red]

My result set from the second query is

[green]

EnquiryNumber	CommittedTotal
1001	          $1,234.00
1001	          $3,211.00
1003	          $5,224.00
1003	          $65,245.00

[/green]

To add the AuditDate back in, I used this SQL

[blue]SELECT qryAudMainOrders.EnquiryNumber, AudMainOrders.AuditDate, qryAudMainOrders.CommittedTotal
FROM AudMainOrders INNER JOIN qryAudMainOrders ON (qryAudMainOrders.CommittedTotal = AudMainOrders.CommittedTotal) AND (AudMainOrders.EnquiryNumber = qryAudMainOrders.EnquiryNumber);[/blue]

Now I realize I don't calculate the CommittedTotal the way you do but changing your calculation for my field value shouldn't make a difference. It might be worth doing the calculations in one query and then build the rest of the queries off that one but again, it shouldn't make a difference. Try it out. I will be leaving in an hour or so and won't be back till tomorrow. I'll check before I go.

Good luck.

Paul

 

[PaulBricker]

Sorry, the From statement in the second query that reads
FROM qryAudMainGroup

should read

FROM qryEnquiryGroupBy

I'm jumping between two PC's and in my second attempt, I change the name of the first query. Sorry for the confusion.

Paul

 

[cbase]

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 field in the dataset could affect it or (b) joins in other tables.

I am going to create a new query with only 3 fields from the AudMainTable and start from there, I will get back to you soon.

By the way, its a very cold wet and windy day in Oxford England ideal for working on this problem.

Part results from qry1 below prove this part works as expected.

EnquiryNumber CountOfEnquiryNumber
2097008 2
2100788 6
2103969 2
2104328 2
2105112 2
2106061 2
2108879 4
23783 3
5244 2

Part Results from qry2. Not at all what is expected and yet syntax the same as test dbase. I have sorted the Enq column to ensure that the numbers would appear adjacent to each other. Other than that I changed Committed to Fees as this value is available without further calculation.

EnquiryNumber Fees
2097008 £3,850.00
2100788 £350.00
2103969 £350.00
2104328 £350.00
2105112 £350.00
2106061 £2,800.00
2108879 £350.00
23783

**********************

Just as I was writing this reply, I had an idea! First one today!
If the Fees value had changed then it shows 2 or more records.
If the Fees value is the same then it shows only 1 of the multiple records.
So, if my thinking is correct then a further sifting of the result is required. Any ideas?
Same results in the test table.
Regards

Don

 

[PaulBricker]

Don, I'm not quite sure what you mean by &quot;a further sifting of the result is required&quot;. My assumption has always been that we only want to return records where the CommittedTotal has changed. If you have two records that are identical

123456 $5,321
123456 $5,321

then these are going to show up as a single record when we do our Group By and we eliminate them because there has been no change in the CommittedTotal. Is that correct thinking? Let me know how you make out with the changes.

Paul

PS I do my best work with the weather makes it impossible to do anything else. Come Spring, I'll spend most of my time on the golf course (worse addition than computers).

 

[cbase]

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 the current sort works, it needs a further sort to get rid of the Enquiry Numbers that are on there own.

Its not a problem with a small dataset to eyball, but the dataset this will be used on is over 16000 records and growing about 3000 a month.

The Audit table the data is sourced from has a line for the original data and a line for the changed data. If the changed data [Committed] is a different value then both lines ought to be shown in order that an assesment can be made as to which is correct.

At the moment it lists those that have changed but as it runs into hundreds each one has to be looked up individually to find the mating half of the pair. I hope this makes sense.

I am in the process of organising a dinner/dance and calling it training for political reasons, and am negotiating to have it at the Belfry golf course Nr. Birmingham where the Ryder cup golf course is. Although I don't play golf, its a great place to walk around.FYI they charge around 450 US dollars a round, now thats what I call an expensive walk!

Cheers

Don.

 

[PaulBricker]

I'm still not really clear on what for the data takes in it's final form and how that might impact your results. For example
[blue]My expectation was that only the Enquiry Numbers that had changed would be shown with the Committed Total.
Whilst the current sort works, it needs a further sort to get rid of the Enquiry Numbers that are on there own.[/blue]

I'm not sure what you mean by [red]EnquiryNumbers that had changed[/red]...We should be returning SETS of EnquiryNumbers where the CommittedTotal changes..Yes/No?

Also, [red]&quot;it needs a further sort to get rid of the EnquiryNumbers that are on their own&quot;[/red]... setting the criteria for the count field to >1 should accomplish this. It will only return records where we see more that one EnquiryNumber.

[blue]At the moment it lists those that have changed but as it runs into hundreds[/blue][red] each one has to be looked up individually to find the mating half of the pair[/red][blue]. I hope this makes sense.[/blue]

The part in [red]Red[/red] confuses me a little. If we sort the result set by EnquiryNumber, then all the matching values should be together so you shouldn't need to hunt down any other records. They should be there all together.
You certainly understand the problems better than I do so if my assumptions are not correct, let me know.
On the lighter side, should I be expecting my invitation in the mail shortly? I don't think I could convince myself that ANY golf course is worth that much to play. I have people around home that I play with that would think it was the bargain of the year to be able to play where the pro's play. I much rather play 15 rounds at $30 a round than 1 at $450. Must be some of the Scottish blood in me.
Keep me posted on your progress.

Paul

 

[cbase]

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] hasn't changed.
option 2 would be to have all 3 showing so that a visual confirmation can be made.

But, what happens is that only 1 shows, this limits the usefulness as if a comparison is to be made with the rest of the set there has to be a manual lookup on the form to find the remainder of the set which could be 1 or more additional records with the same Enq.

Extract.....
If the Fees value had changed then it shows 2 or more records.
If the Fees value is the same then it shows only 1 of the multiple records.

The above is still valid.

Re the golf: Your right about the ridiculous cost of playing golf there, add to that the cost of a night in the Hotel at $250 and you have to be in the big money league or nuts to play there!
I'm only there because I can put it on expenses, if it came out of my pocket........ No way.

Have a great Christmas Paul and I hope the New year is good to you and your family and thanks again.

Don

 

[PaulBricker]

OK, I get it. If we have these values
1001 $2500
1002 $3200
1003 $1200
1002 $3800
1003 $1200
1004 $2100
1003 $1200

our result set currently would be
1002 $3200
1002 $3800
1003 $1200

and that is why we have to either return all the records for 1003 or get rid of it entirely.
So, we could use this SQL in the second query, qryAudMainOrders to get rid of EnquiryNumbers where the CommittedTotal hadn't changed but there were multiple records with the same values.

[blue]SELECT AudMainOrders.EnquiryNumber, AudMainOrders.CommittedTotal, Count(*) AS Expr1
FROM qryEnquiryGroupBy INNER JOIN AudMainOrders ON qryEnquiryGroupBy.EnquiryNumber = AudMainOrders.EnquiryNumber
GROUP BY AudMainOrders.EnquiryNumber, AudMainOrders.CommittedTotal
HAVING (((Count(*))=1));[/blue]

Let me know if I'm closer. Sorry it took so long for the light bulb to go on (if it has).

Paul

 

[cbase]

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
1001 12/12/2003 £1,234.00
1002 12/12/2003 £3,214.00
1003 14/12/2003 £5,224.00
1003 13/12/2003 £65,245.00
1004 15/12/2003 £3,654.00
1004 15/12/2003 £3,654.00
1005 12/12/2003 £3,652.00

Here are the results from the latest version of the querys.

EnquiryNumber CommittedTotal Expr1
1001 £3,211.00 1 ******* Only 1 of 3
1003 £5,224.00 1
1003 £65,245.00 1

Expected results.

1001 12/12/2002 £1,234.00
1001 14/12/2003 £3,211.00
1001 12/12/2003 £1,234.00
This shows a complete audit trail of changes

1003 14/12/2003 £5,224.00
1003 13/12/2003 £65,245.00
This works in the query

1004 15/12/2003 £3,654.00
1004 15/12/2003 £3,654.00
This is ok not to show as the numbers are the same.

I'm wondering if this even possible in query's.

Have a great holiday.

Don

 

[PaulBricker]

OK, try this. This may be slower with all the records we are looking at. Try it with a small (200 +/- records) first to see how it goes. Running against 20K + might take a minute to run. But if it works, we may be able to speed it up. Here's the sql for the first query, [red]QryAudMainEnqGroups[/red]

[blue]SELECT AudMainOrders.EnquiryNumber, AudMainOrders.CommittedTotal, AudMainOrders_1.CommittedTotal
FROM AudMainOrders, AudMainOrders AS AudMainOrders_1
WHERE (((AudMainOrders_1.CommittedTotal)<>[AudMainOrders].[CommittedTotal]) AND ((AudMainOrders_1.EnquiryNumber)=[AudMainOrders].[EnquiryNumber]))
GROUP BY AudMainOrders.EnquiryNumber, AudMainOrders.CommittedTotal, AudMainOrders_1.CommittedTotal;[/blue]

AudMainOrders and AudMainOrders_1 are the same table. When you add the same table twice to a query, Access adds the Suffix &quot;_1&quot; to the end of the table name.

The sql for the second query is

[blue]SELECT AudMainOrders.EnquiryNumber, AudMainOrders.AuditDate, AudMainOrders.CommittedTotal
FROM QryAudMainEnqGroups INNER JOIN AudMainOrders ON (QryAudMainEnqGroups.AudMainOrders.CommittedTotal = AudMainOrders.CommittedTotal) AND (QryAudMainEnqGroups.EnquiryNumber = AudMainOrders.EnquiryNumber)
ORDER BY AudMainOrders.EnquiryNumber, AudMainOrders.CommittedTotal;[/blue]



Paul

 

[cbase]

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

SELECT AudMainOrders.EnquiryNumber, AudMainOrders.OrderNumber, AudMainOrders.Consultant, AudMainOrders.Fees, qryAudMainOrders.Fees
FROM AudMainOrders INNER JOIN qryAudMainOrders ON (AudMainOrders.EnquiryNumber=qryAudMainOrders.EnquiryNumber) AND (AudMainOrders.Fees<>qryAudMainOrders.Fees)
GROUP BY AudMainOrders.EnquiryNumber, AudMainOrders.OrderNumber, AudMainOrders.Consultant, AudMainOrders.Fees, qryAudMainOrders.Fees
HAVING (((Count(qryAudMainOrders.EnquiryNumber))>1));

This query produces 2 of each with eg £0 £100 then £100 £0
to overcome this see below.

I then saved this query and run the report wizard, grouping on Enquiry number. My first checks indicate this is working.

Could you check the logic of this query for me please?
On around 13000 entries it produces 49 changes which is about the expected number.

Thanks again Paul, we have to meet up so I can buy you a drink

Don