Solved Working with Dates (1 Viewer)

[Space Cowboy]

Hello everyone,

I found this forum through research on working with dates where I found information on "MAX". It didn't quite meet my requirements so I thought to myself why not join the group and try to contribute in some small way.

I have a huge database with some 50 tables and it goes back 10 years. Every time I have used help to calculate bonuses etc. it always gets out, and spoils everything, so I am leaning "the way" myself.

I have figured out how to link tables in query and have tried to simplify the huge amount of data down to "one day" in an attempt to keep the data set manageable while I try to learn what is going on, eventually I will run it on the whole data set.

Here is my problem, that I hope may be solvable using the procedures in queries. I have had to change names etc due to data protection necessities and hope that my breakdown of the problem makes sense and is understandable.

On any day I may have upto 10 sales people working, (not always the same people)
each person can sell upto 10 items of varying value.
(Of course I don not want these numbers to be limiting factors)

Problem one,
I want to aggregate together each persons sales into a total and rank the totals one to 10, highest total sales to lowest.

Problem two,
I need to identify the sales from each person, on that day, and rank them in order one to 10. Highest value to lowest.

I would like to think that its easy to solve, if only I had the right terms to ask the question, but I have come to dead end.

The ultimate conclusion is that I want to generate a report that selects the five most recent dates that each person worked, some may only work once or twice a month, and look at their performance on the days that they worked.

The report, I hope, will ultimately help me to identify the top performers and reward them as such with bonuses and gifts etc.

 

[CJ_London]

I want to aggregate together each persons sales into a total and rank the totals one to 10, highest total sales to lowest.

use an aggregate query - group on sales person, sum, sales, then order by sum sales desc

given your thread title, I assume you want to limit this by date in some way. so you might have a criteria

sale date between format(startdate,"yyyy-mm-dd") and format(enddate,"yyyy-mm-dd")

any dates expressed as text need to be in the SQL format of 'yyyy-mm-dd' or the US format of 'mm/dd/yyyy'. This matters because if you are somewhere where the date format is 'dd/mm/yyyy' a date of 6/5/2024 (6th May) will be interpreted as the US value of 5th June. The one exception is if the string is unambiguous - for example 19./5/2024 will be interpreted correction since there are not 19 months in the year. Note that formatting a date in a control to appear as say '5 June 2024' does not change it's underlying value (which is just a number)

I need to identify the sales from each person, on that day, and rank them in order one to 10. Highest value to lowest.

Not clear what this is meant to be - do you want the highest sales per salesperson or overall. e.g.

per sales person
salesperson..sale
1......................100
1.......................95
1......................70
2.......................80

or
overall
salesperson..sale
1......................100
1.......................95
2.......................80
1......................70

 

[Space Cowboy]

Thank you CJ for trying to assist!
I will attempt to clarify


20/5/24

Rep Sales

Bob 100
Bob 200
Bob 75
Bob 50
Bob 25

Helen 250
Helen 50
Helen 75

Rupert 75
Rupert 25
Rupert 40
Rupert 50
Rupert 50

This is the output I need

Sales total, sales rank, Ord Value , order rank

Bob 400 1 200 1
Bob 400 1 100 2
Bob 400 1 75 3
Bob 400 1 50 4
Bob 400 1 25 5

helen 375 2 250 1
helen 375 2 75 2
helen 375 2 50 3

rupert 240 3 75 1
rupert 240 3 50 2
rupert 240 3 50 2
rupert 240 3 40 3
rupert 240 3 25 4


I will be ok getting the other info from the tables, Ie, order number and part number etc
I will work on the aggregate function that seems like it will do it for the sales person.

Thanks again

 

[CJ_London]

So why is Bob 400 and not 450?

 

[Space Cowboy]

my bad addition!

you get it though?

 

[Space Cowboy]

Bob	20/5/24	13/4/24	7/2/24	10/12/23	14/1/23
sales rank	1	1	2	3	1

for the dates thing, I need to pull out the five most recent dates that bob worked for us and his sales rank on that date
"MAX" can only get me the most recent.
Helen and Rupert may have worked totally different dates, but for the concise report I need their five most recent dates.

 

[CJ_London]

sql might me along these lines - you didn't provide table names so I just used Table3

1. create a initial query Q1

SELECT Table3.Rep, Sum(Table3.Sale) AS SumOfSale
FROM Table3
GROUP BY Table3.Rep

and then a second one referencing it

SELECT Q1.Rep, DCount("*","Q1","SumofSale>=" & [sumofsale]) AS RepRank, Q1.SumOfSale, Table3.Sale, DCount("*","Table3","Sale>=" & [sale] & " AND Rep='" & [Q1].[Rep] & "'") AS SaleRank
FROM Q1 INNER JOIN Table3 ON Q1.Rep = Table3.Rep
ORDER BY DCount("*","Q1","SumofSale>=" & [sumofsale]), DCount("*","Table3","Sale>=" & [sale] & " AND Rep='" & [Q1].[Rep] & "'");

produces this result
Query49

Rep	RepRank	SumOfSale	Sale	SaleRank
Bob	1	450​	200​	1
Bob	1	450​	100​	2
Bob	1	450​	75​	3
Bob	1	450​	50​	4
Bob	1	450​	25​	5
Helen	2	375​	250​	1
Helen	2	375​	75​	2
Helen	2	375​	50​	3
Rupert	3	240​	75​	1
Rupert	3	240​	50​	3
Rupert	3	240​	50​	3
Rupert	3	240​	40​	4
Rupert	3	240​	25​	5

 

[Space Cowboy]

Thank you so much CJ, I will try to wrap my head around that!

 

[CJ_London]

I need to pull out the five most recent dates that bob worked for us and his sales rank on that date

use the TOP predicate combined with an order by

so five most recent would be

SELECT TOP 5 * FROM Q1 WHERE REP ='Bob' ORDER by SaleDate Desc

my Q1 does not include a date field so this is just an illustration

if you include a saledate then that needs to be in (what I called) table3 and you will need to join on that as well as rep in the second query.

Edit - and to get it to display horizontally per post #6 use a crosstab query on the above query, including the repRank calculation from my earlier post (modified for sale date) - rep would be row heading, saledate would be column heading and repRank the value

 

[Space Cowboy]

use the TOP predicate combined with an order by

so five most recent would be

SELECT TOP 5 * FROM Q1 WHERE REP ='Bob' ORDER by SaleDate Desc

my Q1 does not include a date field so this is just an illustration

if you include a saledate then that needs to be in (what I called) table3 and you will need to join on that as well as rep in the second query.

Thanks again, I am new to all this so what you are doing in minutes could take me weeks, because I need to try to understand whats going on.

 

[CJ_London]

takes time to learn

take small steps, understand what you are doing at each step before moving on to the next

If you need detailed help, provide realistic data and use meaningful names. Always better to show example data and the outcome required rather than describing it.

 

[Space Cowboy]

takes time to learn

take small steps, understand what you are doing at each step before moving on to the next

If you need detailed help, provide realistic data and use meaningful names. Always better to show example data and the outcome required rather than describing it.

Thanks, I take pride in the fact that I am still capable of learning something new despite the daily cramming of my brain over the last 59 years.
Your advice is the way that I always try to proceed, I just cannot move forward until I understand what I am doing.
Sometimes I wish that I was just a cut and paste guy. LOL

 

[gemma-the-husky]

If you have multiple characteristics you want to see, I might create a table with all the employees, and add columns for all the statistics you want to see.

Total sales, days worked, average per day etc etc.

Then present the table as a datasheet and then the user can sort/filter using each column as required.

You would need to execute a series of queries to populate each column one at a time.

 

[Space Cowboy]

sql might me along these lines - you didn't provide table names so I just used Table3

1. create a initial query Q1

SELECT Table3.Rep, Sum(Table3.Sale) AS SumOfSale
FROM Table3
GROUP BY Table3.Rep

and then a second one referencing it

SELECT Q1.Rep, DCount("*","Q1","SumofSale>=" & [sumofsale]) AS RepRank, Q1.SumOfSale, Table3.Sale, DCount("*","Table3","Sale>=" & [sale] & " AND Rep='" & [Q1].[Rep] & "'") AS SaleRank
FROM Q1 INNER JOIN Table3 ON Q1.Rep = Table3.Rep
ORDER BY DCount("*","Q1","SumofSale>=" & [sumofsale]), DCount("*","Table3","Sale>=" & [sale] & " AND Rep='" & [Q1].[Rep] & "'");

produces this result
Query49

Rep	RepRank	SumOfSale	Sale	SaleRank
Bob	1	450​	200​	1
Bob	1	450​	100​	2
Bob	1	450​	75​	3
Bob	1	450​	50​	4
Bob	1	450​	25​	5
Helen	2	375​	250​	1
Helen	2	375​	75​	2
Helen	2	375​	50​	3
Rupert	3	240​	75​	1
Rupert	3	240​	50​	3
Rupert	3	240​	50​	3
Rupert	3	240​	40​	4
Rupert	3	240​	25​	5

Hi CJ,
Apologies for not being in touch, we have been away a couple of days.
I have stared to dig into this a bit and have hit a snag.
My "sale" column data has a pound sign in front of the sale figure; £200, £75 etc
When listing the sale rank it is going 75,50,25,200,100 etc
It seems to be sorting first number then second rather than the whole number.
Help please.

 

[MajP]

Looks like that field is a text field and not a currency field. That is the way a text field will sort. what is the field type.

 

[MajP]

FYI, you can convert the field to a numeric in the query to sort, but the long term is to fix the field.

 

[Space Cowboy]

hello MajP,
thank you for trying to assist.
I am stuck with the data as presented in the table and have no opportunity to rectify it.
HOW?
I am a complete novice so need an idiot guide.

 

[Space Cowboy]

Also, for future reference and to keep within rules etc.
should I keep all my problems in the one thread or start new threads for every problem.
I don't expect CJ to be a personal tutor but he has set me on a path.

 

[Gasman]

New thread for each problem.

 

[MajP]

Assume I stored values in a text field, and all of them start with a GBP symbol (I used $ since easier to type). If you sort it, it will sort character by character like text.

You would get this
Query1 Query1

Query1 Query1

Field1
$12,500
$7000
$825
$9.95
$99

Since the this is a text field you can use the mid function to return everything after the first character.

mid([Field1],1)

.95, 1250, 700 etc
You can then use the convert to currency function to make it a numeric value

CCur(Mid([Field1],1))

To do this in a query go to a blank column and type a new name followed by the formula and then sort on this new column.

ConvertedCurrency: CCur(Mid([Field1],1))

ConvertedCurrency
$9.95​
$99.00​
$825.00​
$7,000.00​
$12,500.00​