Solved Loop de Loop

[cheekybuddha]

First try this:

SELECT
  ftq1.odate,
  filtertable1.orderid,
  ftq1.sumofunitprice,
  Dcount("*", "ftq1", "sumofunitprice>=" & [sumofunitprice]) AS DayRank,
  filtertable1.unitprice
FROM filtertable1
INNER JOIN ftq1
        ON filtertable1.orderid = ftq1.orderid
WHERE ftq1.odate = #2024-05-27#
ORDER BY
  Dcount("*", "ftq1", "sumofunitprice>=" & [sumofunitprice]),
  filtertable1.unitprice DESC
;

Let us know whether your 'Needed' column (UnitPrice) shows in the correct order with all your other data.

Then you can work on the ranking

(I probably won't be able to look in again here until tomorrow - others may well pitch in before then)

 

[Space Cowboy]

First try this:

SELECT
  ftq1.odate,
  filtertable1.orderid,
  ftq1.sumofunitprice,
  Dcount("*", "ftq1", "sumofunitprice>=" & [sumofunitprice]) AS DayRank,
  filtertable1.unitprice
FROM filtertable1
INNER JOIN ftq1
        ON filtertable1.orderid = ftq1.orderid
WHERE ftq1.odate = #2024-05-27#
ORDER BY
  Dcount("*", "ftq1", "sumofunitprice>=" & [sumofunitprice]),
  filtertable1.unitprice DESC
;

Let us know whether your 'Needed' column (UnitPrice) shows in the correct order with all your other data.

Then you can work on the ranking

(I probably won't be able to look in again here until tomorrow - others may well pitch in before then)

Yes that works on unit price, I had it like that using DESC. However the dayrank has gone to pot.

 

[Space Cowboy]

The day rank messes up as soon as the data table includes extra days.

The extra dates taken out.

 

[cheekybuddha]

The day rank messes up as soon as the data table includes extra days.

So you need to add the date criteria to your DCount() as well:

SELECT
  ftq1.odate,
  filtertable1.orderid,
  ftq1.sumofunitprice,
  DCount("*", "ftq1", "sumofunitprice >= " & [sumofunitprice] & " AND odate = #2024-05-27#") AS DayRank,
  filtertable1.unitprice
FROM filtertable1
INNER JOIN ftq1
        ON filtertable1.orderid = ftq1.orderid
WHERE ftq1.odate = #2024-05-27#
ORDER BY
  DCount("*", "ftq1", "sumofunitprice >= " & [sumofunitprice] & " AND odate = #2024-05-27#"),
  filtertable1.unitprice DESC
;

 

[cheekybuddha]

Including PriceRank might look like this:

SELECT
  ftq1.odate,
  filtertable1.orderid,
  ftq1.sumofunitprice,
  DCount("*", "ftq1", "sumofunitprice >= " & [sumofunitprice] & " AND odate = #2024-05-27#") AS DayRank,
  filtertable1.unitprice,
  DCount("*", "filtertable1", "unitprice <= " & filtertable1.unitprice & " AND odate = #2024-05-27#") AS PriceRank
FROM filtertable1
INNER JOIN ftq1
        ON filtertable1.orderid = ftq1.orderid
WHERE ftq1.odate = #2024-05-27#
ORDER BY
  DCount("*", "ftq1", "sumofunitprice >= " & [sumofunitprice] & " AND odate = #2024-05-27#"),
  filtertable1.unitprice DESC
;

 

[Space Cowboy]

Good Morning Good People,

@cheekybuddha
What had I done wrong to cause the data mismatch error? I am trying to understand and Learn
I think that I have called a number field a string where as CJ original code the field was a text field

cname='
OrderId='

does this act differently on a text or number field?
What is the apostrophe doing?

 

[cheekybuddha]

Your original expression has 2 (perhaps 3) issues:

Dcount("*", "filtertable1", "unitprice>=" & [unitprice] & "and orderid='" & [filtertable1].[orderid] & "'") AS PriceRank

First, you need to add a space between the value you inject for UnitPrice and the 'AND' of the next part of the criteria clause:

Dcount("*", "filtertable1", "unitprice>=" & [unitprice] & " and orderid='" & [filtertable1].[orderid] & "'") AS PriceRank
                                                           ^
                                                           |
                                                  space reuired here

Then, as you have guessed, with the addition of quotes around the value, you are passing orderid as a string rather than a number in the second part of the criteria clause:

Dcount("*", "filtertable1", "unitprice>=" & [unitprice] & "and orderid='" & [filtertable1].[orderid] & "'") AS PriceRank
                                                                       ^                             ^^^^^
                                                                       |                             |||||
                                                                Use these to wrap string values, not numbers

Finally, in order to reverse the ranking consistent with the UnitPrice ordering, you might need to change the comparison order for UnitPrice:

Dcount("*", "filtertable1", "unitprice>=" & [unitprice] & " and orderid= " & [filtertable1].[orderid]) AS PriceRank
                                      ^^
                                      ||
                        se Less Than, rather than Greater Than

So I guess you want:

Dcount("*", "filtertable1", "unitprice <= " & filtertable1.unitprice & " AND orderid= " & filtertable1.orderid) AS PriceRank

In your query:

SELECT
  ftq1.odate,
  filtertable1.orderid,
  ftq1.sumofunitprice,
  DCount("*", "ftq1", "sumofunitprice >= " & ftq1.sumofunitprice & " AND odate = #2024-05-27#") AS DayRank,
  filtertable1.unitprice,
  Dcount("*", "filtertable1", "unitprice <= " & filtertable1.unitprice & " AND orderid= " & filtertable1.orderid) AS PriceRank
FROM filtertable1
INNER JOIN ftq1
        ON filtertable1.orderid = ftq1.orderid
WHERE ftq1.odate = #2024-05-27#
ORDER BY
  DCount("*", "ftq1", "sumofunitprice >= " & ftq1.sumofunitprice & " AND odate = #2024-05-27#"),
  filtertable1.unitprice DESC
;

 

[Space Cowboy]

@cheekybuddha

Thank you so much for clarifying that and helping me understand.

 

[cheekybuddha]

Other things to note:

Use of domain aggregate functions (DCount(), DSum(), DLookup(), etc) within a query can have a heavy performance impact if the the query returns many rows.

You don't say where you will use this query.

If it is for use on a report, then using built-in report features can be more performant to create rankings then creating the ranking in the underlying recordset.

 

[Space Cowboy]

ta da.........

Now for the tricky part, moving to the next day (28/5/2024) and then performing that same operation on that day.

 

[Space Cowboy]

Other things to note:

Use of domain aggregate functions (DCount(), DSum(), DLookup(), etc) within a query can have a heavy performance impact if the the query returns many rows.

You don't say where you will use this query.

If it is for use on a report, then using built-in report features can be more performant to create rankings then creating the ranking in the underlying recordset.

Hi David,
this is the foundation of a query that will have approx 20 other fields attached to it and will go back 4 or 5 years. I think there will be about 70,000 rows at the end of it. I will then transform to a table where I can perform other queries and generate more specific reports.

I cant make any real progress until I have this sorted out.

Thank you once again my friend.

 

[ebs17]

Dcount("*", "filtertable1", "unitprice <= " & filtertable1.unitprice & " AND orderid= " & filtertable1.orderid) AS PriceRank

This becomes, when viewed daily:

Dcount("*", "filtertable1", "unitprice <= " & filtertable1.unitprice & " AND orderid= " & filtertable1.orderid & " AND odate = " & Format(filtertable1.odate, "\#yyyy\-mm\-dd\#")) AS PriceRank

I think there will be about 70,000 rows at the end of it

The calculation of PriceRank via DCount has a systematic performance problem. My personal limit is 10,000 records, after which the runtime becomes unbearable.

 

[Space Cowboy]

@ebs17
Thank you so much for that! I have transposed the format section from that line into the Dayrank calculation also and it has worked beautifully. I cannot believe how powerful Access is in only a few lines of code and in only a few words really.

To You all, a sincere thank you. The calculations and formatting that you have all helped with in this example will be used by me in different methods in lots of query types, I just hope that my efforts will do your kind and thoughtful approach justice.