Solved TOP

[Space Cowboy]

I am trying to find the six most recent sales dates of a product.

I have tried TOP 6

I am getting lots of blue spinning wheels of death.

I think it maybe because some items have perhaps three or four sales and some perhaps none.

is there a way of going null to six?

 

[plog]

Whats your SQL?

 

[Space Cowboy]

i gave up and deleted everything as I was having to many attempts an had half a dozen different queries messed up

 

[Space Cowboy]

i tried in the main query, in a sub query, a dcount and a dcount in a subquery.

it was giving me a headache so had to leave it for a few days

 

[GPGeorge]

i gave up and deleted everything as I was having to many attempts an had half a dozen different queries messed up

That's the kind of situation where a Pre-development backup of the accdb makes so much sense, along with a Post-Development backup at the end of the work session.

 

[Space Cowboy]

I thought of making a small DB to log my efforts and changes I will have to do that, days of attempted solution thrown away in frustration.

 

[GPGeorge]

I thought of making a small DB to log my efforts and changes I will have to do that, days of attempted solution thrown away in frustration.

It's easier, IMO, just to keep making progressive backups, say every 15 minutes or a longer interval, while making significant changes.

Otherwise, a significant amount of time will go into somehow logging changes. But any method is good if it works for you.

 

[LarryE]

I am trying to find the six most recent sales dates of a product.

I have tried TOP 6

I am getting lots of blue spinning wheels of death.

I think it maybe because some items have perhaps three or four sales and some perhaps none.

is there a way of going null to six?

Maybe it's the table and relationship design. Take a screenshot of your Relationship window and copy it into a new post so maybe someone can give you advice.

 

[Pat Hartman]

I thought of making a small DB to log my efforts and changes I will have to do that, days of attempted solution thrown away in frustration.

There is nothing we can do to help you if you don't post the schema and sample data at a minimum. The SQL you tried is also helpful along with what was wrong with the results.

 

[ebs17]

I am getting lots of blue spinning wheels of death.

How many records does your table have?
Lots of mass makes four work.

I am trying to find the six most recent sales dates of a product.

For 1 product:

SELECT TOP 6
   *
FROM
   TabX
WHERE
   ProductID = 57
ORDER BY
   SalesDate DESC

The SalesDate field and the ProductID field should be indexed.

For all products:

SELECT
   T.*
FROM
   TabX AS T
WHERE
   T.TabXID IN
      (
         SELECT TOP 6
            X.TabXID
         FROM
            TabX AS X
         WHERE
            X.ProductID = T.ProductID
         ORDER BY
            X.SalesDate DESC
      )

Additionally, a unique field is required for each record (TabXID), which is also indexed accordingly.

 

[Space Cowboy]

How many records does your table have?
Lots of mass makes four work.


For 1 product:

SELECT TOP 6
   *
FROM
   TabX
WHERE
   ProductID = 57
ORDER BY
   SalesDate DESC

The SalesDate field and the ProductID field should be indexed.

For all products:

SELECT
   T.*
FROM
   TabX AS T
WHERE
   T.TabXID IN
      (
         SELECT TOP 6
            X.TabXID
         FROM
            TabX AS X
         WHERE
            X.ProductID = T.ProductID
         ORDER BY
            X.SalesDate DESC
      )

Additionally, a unique field is required for each record (TabXID), which is also indexed accordingly.

Thank you Eberhard,

I have managed to get the first bit of code to work!

Thanks

 

[Space Cowboy]

@ebs17
Thank you again Eberhard
Second part of your SQL suggestion is now working perfectly, as I have managed to configure my data as instructed, and has solved this problem, Thank you.
It is all slowly coming together for me.

 

[Space Cowboy]

@ebs17

If you get a few spare moments, would you be so kind as to try and explain for me what is going on in the second SQL statement, I don't understand why we look at the index for the top values and not the dates themselves and also what is the purpose of the comparison between the two alias's?

 

[ebs17]

I'll try it.

Two instances of the same table tabX are used. Instance T stands for the display of all fields externally. The subquery is accessed via the filter. Instance X is used in the subquery for the actual calculation.

WHERE
   X.ProductID = T.ProductID

This expression acts like a grouping. The corresponding top values are determined for each ProductID in the main query (instance T). You are probably familiar with the normal grouping using GROUP BY to determine a maximum or a minimum.

WHERE
   T.TabXID IN
      (
         SELECT TOP 6
            X.TabXID

The aim here is to ensure clarity, both in determining the TOP values and in filtering for the display.

Alternatively, you could use SalesDate. However, if the same content can appear multiple times, the assignment becomes less precise and you quickly get different results than desired.

If there are identical values in SalesDate for the same ProductID, the sorting would have to be made more precise:

ORDER BY
   X.SalesDate DESC,
   X.TabXID

 

[Space Cowboy]

Thank you for helping me understand.