Solved Working with Dates

[Space Cowboy]

Hi Maj,
It is displaying exactly the same as the referenced column.
But when sorted it is correct order.
Thank you My Friend

 

[MajP]

It is displaying exactly the same as the referenced column.

The reason it shows the same is because it converted it to a currency and then automatically applied formatting. That does not mean the currency symbol is being saved. A currency is jut a number saved to 4 decimal places. However Access will automatically apply currency formatting to the field. The pound symbol is just a format of the data using your regional settings. If I ran that same query I would not see dollar symbol even though your text field stored a GBP symbol.

 

[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

Thanks CJ,
This has worked perfectly once I sorted the text numbers out!

 

[Space Cowboy]

The reason it shows the same is because it converted it to a currency and then automatically applied formatting. That does not mean the currency symbol is being saved. A currency is jut a number saved to 4 decimal places. However Access will automatically apply currency formatting to the field. The pound symbol is just a format of the data using your regional settings. If I ran that same query I would not see dollar symbol even though your text field stored a GBP symbol.

Thanks for the concise explanation MajP
Can the mid function, Seems very handy, be used to split and extract any part of a string?

 

[CJ_London]

Can the mid function, Seems very handy, be used to split and extract any part of a string?

Yes - Google ‘vba mid function’ to find out more.

you just need to know where to start and where to finish

there are other options such as the left and right functions. These will work in a query but there Is also the split method which only works in vba

 

[MajP]

This one is pretty thorough

The Ultimate Guide to VBA String Functions - Excel Macro Mastery

A complete guide to using string functions in VBA. This covers all the VBA string functions including Instr, InstrRev, StrComp, Format and more.

excelmacromastery.com

 

[Space Cowboy]

@CJ_London

I am making progress, i have followed your instruction and achieved the desired result for that day.
My next objective (break things down into smaller problems I thought) was to achieve the desired results over a longer time period so selected about a week to keep the data output to a manageable size until I am confident that it works satisfactorily.

My thought process was to compile the data for the last three years and then analyze each rep from the compiled sales data.

I have successfully made the initial table (your table3) and the second query to identify the daily sales total for each Rep. This Query has succesfully split the reps rank by day.


CODE :
SELECT QFULL1.odate, QFULL1.cname, Sum(QFULL1.rprize1) AS SumOfPrize
FROM QFULL1
GROUP BY QFULL1.odate, QFULL1.cname;

The issue that I have(thanks for taking the time to get this far) it that when the calculations to rank each days individual sale, by rep, then the sorting does not rank within each day but treats the data as a set and "ranks" the whole period. Not each rep on each date.

CODE
SELECT QFULL2.odate, QFULL2.cname, DCount("*","QFULL2","SumOfPrize>=" & [sumofprize]) AS DayRank, QFULL2.SumOfPrize, QFULL1.rprize1, DCount("*","QFULL1","rprize1>=" & [rprize1] & " AND cname='" & [QFULL2].[cname] & "'") AS PrizeRank
FROM QFULL1 INNER JOIN QFULL2 ON (QFULL1.cname = QFULL2.cname) AND (QFULL1.odate = QFULL2.odate)
ORDER BY DCount("*","QFULL2","SumOfPrize>=" & [sumofprize]), DCount("*","QFULL1","rprize1>=" & [rprize1] & " AND cname='" & [QFULL2].[cname] & "'");

I need to tie the ranking to the day, then rep, then sale price, then item rank. exactly as in your excellent table you created but by each day.

 

[MajP]

If you want to make your SQL easy to read, there are lots of free SQL Formatters. Here is one

Instant SQL Formatter & Beautifier Online

Free online sql formatting tool, beautify sql code instantly for SQL Server, Oracle, DB2, MySQL, Sybase, Access and MDX

www.dpriver.com

Just drop it in. Then use the code Tags above in the thread edit menu.

SELECT qfull2.odate,
       qfull2.cname,
       Dcount("*", "qfull2", "sumofprize>=" & [sumofprize]) AS DayRank,
       qfull2.sumofprize,
       qfull1.rprize1,
       Dcount("*", "qfull1", "rprize1>=" & [rprize1] & "and cname='" &
                             [qfull2].[cname] & "'")        AS PrizeRank
FROM   qfull1
       INNER JOIN qfull2
               ON ( qfull1.cname = qfull2.cname )
                  AND ( qfull1.odate = qfull2.odate )
ORDER  BY Dcount("*", "qfull2", "sumofprize>=" & [sumofprize]),
          Dcount("*", "qfull1", "rprize1>=" & [rprize1] & "and cname='" &
                                [qfull2].[cname] & "'")

 

[Space Cowboy]

Thank you MajP

I had been wondering how you all managed with such long lines of code!

Will the code in that format still "run" or is it for display purposes only?

 

[MajP]

Will the code in that format still "run" or is it for display purposes only?

Yes, but if you open it again in design view access will unfortunately reformat into compact style. That is nice in SQL Server because you can maintain the nice formatting.

 

[CJ_London]

when you use the code tags - choose SQL for colour

SELECT qfull2.odate,
       qfull2.cname,
       Dcount("*", "qfull2", "sumofprize>=" & [sumofprize]) AS DayRank,
       qfull2.sumofprize,
       qfull1.rprize1,
       Dcount("*", "qfull1", "rprize1>=" & [rprize1] & "and cname='" &
                             [qfull2].[cname] & "'")        AS PrizeRank
FROM   qfull1
       INNER JOIN qfull2
               ON ( qfull1.cname = qfull2.cname )
                  AND ( qfull1.odate = qfull2.odate )
ORDER  BY Dcount("*", "qfull2", "sumofprize>=" & [sumofprize]),
          Dcount("*", "qfull1", "rprize1>=" & [rprize1] & "and cname='" &
                                [qfull2].[cname] & "'")

 

[CJ_London]

My next objective (break things down into smaller problems I thought) was to achieve the desired results over a longer time period

when building an app you need to think of the end requirement. Moving towards it incrementally by all means but keeping it in mind. Otherwise you risk going down a blind alley and have to retrace your steps.

I'm struggling to determine the end requirement from your initial posts which mentioned a day, then was modified to 5 days. Now we seem to be talking about 3 years.

when the calculations to rank each days individual sale, by rep, then the sorting does not rank within each day but treats the data as a set and "ranks" the whole period.

means what exactly? What is a 'period'?

you do need to sort by qfull2.odate which you are not doing but beyond that, provide some example data and the outcome required from that example data - and in a form that users can download to test - (could be a zipped excel file, could be a zipped .accdb)

 

[Space Cowboy]

Apologies CJ, i am not being deliberately obtuse, cautious yes. I was taught to be cautious. Thank you for your patience.

To get the "five most recent days" data i determined that the most recent days each rep worked will almost certainly be contained in the last three years. So my logic was to create that table and then extract the "already calculated figures" the five most recent for each rep.
I don't know if that makes sense or not and maybe well wide of the mark logically.

The "period" is the seven day data set created by query1 and query 2, instead of looking at each day it seems to be lumping the whole lot together.

I will try a sort on the date.

 

[Space Cowboy]

data output "jumbled up"

As you can see its a bit mixed up.
On the "dayrank" column helen should be ranked 1 on 21/52024, norman 2, horace 3, billy 4 etc
The column day rank appears to be operating on the whole data set not differentiating each day.

​

 

[CJ_London]

showing what you currently get as a result might be useful down the line, but does not help to resolve your issue since I can't see the source data and still not clear what you actually want to see as a result. If you can't provide what I've asked for, not sure I can provide any more help.

 

[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,

I want this but for each date in any given time period.
The code you helped with is perfect for 1 day.
I need it to loop through the dates and generate this for each date.
The attached document (Post34) shows what I currently getting

 

[CJ_London]

If you can’t provide the data per post 35, I can’t help. Good luck with your project but I’m going to leave it to others to see if they understand what you actually require since so far as I can see the result in post 34 meets your requirement

 

[Space Cowboy]

Thanks for your help CJ
Appreciate 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.

Hello @CJ_London ,

at the time I did not realize how beautiful the code that you made for me was. It is magnificent. I have learned so much from it.

DCount("*","Q1","PrizeConvert>=" & [PrizeConvert] & " AND drcname='" & [Q2].[drcname] & "'")

in the criteria above I have a feeling, could be well wide of the mark, that you have identified the field drcname as a string. Fingers crossed I am on the right lines.
If I need the field to be identified as number what do i do?

 

[The_Doc_Man]

Hello @CJ_London ,

at the time I did not realize how beautiful the code that you made for me was. It is magnificent. I have learned so much from it.

DCount("*","Q1","PrizeConvert>=" & [PrizeConvert] & " AND drcname='" & [Q2].[drcname] & "'")

in the criteria above I have a feeling, could be well wide of the mark, that you have identified the field drcname as a string. Fingers crossed I am on the right lines.
If I need the field to be identified as number what do i do?

If drcname is numeric, look for and remove the single-quote marks.

DCount("*","Q1","PrizeConvert>=" & [PrizeConvert] & " AND drcname=" & [Q2].[drcname])

Compare this to what CJ showed you and watch for those little single-quote markes. Particularly when they appear in concatenation sequences (with the &) they can be hard to see. Note also that the trailing "'" got removed completely because once you removed the single quote you were left with an empty string "" which you could just drop.

Whenever you see single quotes (apostrophes if you prefer that name) inside a double-quoted string that is an argument for a function (in this case DCOUNT), you need to understand string behavior in function or subroutine arguments. The formal action of the call STRIPS away the double quotes from quoted arguments. Therefore if you need to pass something that remains quoted AFTER going through argument processing, you do what CJ did and embed single quotes that will remain behind after that layer of quote-stripping. It gets a LOT more complex if it happens that the argument will be passed to a second-level routine from inside the first-level routine.