'Prospect'
'Marketing'
I have created a query
Code:
Points Origines: IIf([Origines]='Prospect',1,0)
Lisa B
Last Activity: Sep 24 '11, 07:58 AM
Joined: Jan 8 '11
Location:
-
Text to Number, then sum in a query
I have a table with a combo box,[Origines] two choices
'Prospect'
'Marketing'
I have created a query
However when I try to SUM how many 'Prospect' i have per ID it is adding in the 'Marketing' and Null's too -
This may help too?
...Code:SELECT AliasSumOfFacturationEquipe.SumOfFacturation, AliasSumOfFacturationEquipe.Equipe, ((Select Count(*) FROM QryDistinctFacturationEquipe Where [SumOffacturation]>AliasSumofFacturationEquipe.[SumOffacturation])+1) AS Rank, IIf([rank]=1,6,IIf([rank]=2,4,IIf([rank]=3,3,IIf([rank]=4,2,IIf([rank]=5,1,0))))) AS Points FROM QrySumOfFacturationEquipe AS AliasSumOfFacturationEquipe;
-
Hi again
I am successfully ranking several queries now, however, I don't want it to rank if the value is 0, I want it to return a zero ranking
This is my expression
So, what do I add to tell it to return a 0 rank if the SumOffacturatio n=0?Code:Rank: ((Select Count(*) FROM QryDistinctFacturationEquipe Where [SumOffacturation]>AliasSumofFacturationEquipe.[SumOffacturation])+1)
thank...Leave a comment:
-
It didn't like the qryRankSales, I think because I had to alias the qrySumOfSales table (and a spurious bracket(
this is what i've ended up with - which HOORAY i 'think' is working
Can't...Code:SELECT AliasSum.FKVendeurID, AliasSum.[SumOfMachines Sold], ((Select Count(*) FROM qryDistinctSales Where [SumOfMachines Sold]>AliasSum.[SumOfMachines Sold])+1) AS Rank FROM qrySumofSales AS AliasSum;
Leave a comment:
-
I need to Sum the number of machines sold per Vendeur for a date range - and rank who has sold the most within that date range
Currently Vendeurs now appearing more than once and not being ranked correctly
http://i305.photobucket.com/albums/n...machineres.png
DO i need two distinct queries?
Distinct
...Code:SELECT DISTINCT [Weekly Machines Sales data].FKVendeurID, Sum([Weekly Machines
Leave a comment:
-
SOrry thought I had mentioned it
Tie problem is when it is not adding the next rank number for tie, i.e
1
2
2
4
5
5
7
rather than
1
2
2
3
4
5
5
6
7Leave a comment:
-
It is Summing Dates fine, however i now have the tie problem again :(
Rqrysales
...Code:SELECT Sum([Weekly Machines Sales data].[Machines Sold]) AS [SumOfMachines Sold], [Weekly Machines Sales data].FKVendeurID, [Weekly Machines Sales data].[Date de Vente] FROM [Weekly Machines Sales data] GROUP BY [Weekly Machines Sales data].FKVendeurID, [Weekly Machines Sales data].[Date de Vente] HAVING ((([Weekly Machines Sales
Leave a comment:
-
when i put your WHERE clause in the select statment for the rnaking
Rank:Rank: (Select count(*) from [Distinct]
Where [SumOfMachines Sold] > [SumMach1].[SumOfMachines Sold])+1
it doesn't work, so i put it in the criterira for the Date field in the queryLeave a comment:
-
mispost can' deleteLeave a comment:
-
have added another duplicate field in the query that has the ranking (as when you select where from the total row it un-shows it)
[Date de Vente] selected where for the total row and put the expression Between [Forms]![RankMachines]![StartDate] And [Forms]![RankMachines]![EndDate]
for a query of same startdate and endate i'm getting one result which is fine but rank 4!Leave a comment:
-
HOW?!
RqrySales
Weekly Machines Sales Data (table)
[Date de Vente]
[Machines Sold] Summed and Descending
[FKVendeurID]
Distinct - Properties set to unique value
Weekly Machines Sales Data (table)
[Machines Sold] Summed Descending
[Date de Vente] - not shown
[FKVendeurID] - not shown
RankingMachines Qry -
using Rqrysales - aliased...Leave a comment:
-
The grouping by date is fine
BUT the ranking is not starting at 1 if there is a sale of higher value earlier than the date chosen
i.e.
Sale Date 12/1/2011 - Value 12
Sale Date 14/1/2011 - Value 7
Sale Date 15/1/2011 - Value 4
Salte Date 16/1/2011 - Value 6
Ranking for date Range 13/1/2011 - 16/1/2011
Sale Date 14/1/2011 - Rank 2
Sale Date 15/1/2011...Leave a comment:
-
I have managed to work out the problem with the summing for the dates, but the ranking is not starting at 1 if there is a higher sales value before that dateLeave a comment:
-
I am unsure as to what the last x should be
all working perfectly accept i can't seem to sum the machines sold per vendeur per date
So i tried to adapt it
I have created two queries
Rqrysales
This sums the number of mahcines sold by each vendeur
I then use this query to create a new query called RankingMachines based on the Rqrysales query - created an alias...Leave a comment:
-
I have uploaded a copy of my dbase to skydrive http://cid-4490afdc094900ec.skydrive...DC094900EC!368Leave a comment:
-
I am unsure as to what the last x is after the final AS
so far i have written this
Rank: (Select [VendeurID], ((Select count(*) From [Weekly Machines Sales Data]
WHERE [Machines Sold] > [Weekly Machines Sales data].[Machines Sold])+1)
AS Rank
FROM [Weekly Machines Sales data])
ASLeave a comment:
-
How to create a report from a date range in a query?
I have managed to create a Report from a form that displays data per date
In the query criteria for the date it references the form
[Forms]![equipe_form]![Date de Vente]
The report is based on the results of the query as per the information entered in the form
so far so good
However, I want the user to be able to select a date range in the form - what do i enter in the query... -
Solved
Me.Emailtxt = DLookup("[Email]", "[Vendeurs]", "VendeurID = " & Me.VendeurID)Leave a comment:
-
The Equipes are in a ComboBox (Paris, Beneluxe, Marseille, Lyon,, Callcentre) so i think this is why it may be returning an error
So I tried with just a txt box - Email
Me.Emailtxt = DLookup("[Email]", "[Vendeurs]", "VendeurID =Forms![Vendeurs]![VendurID]"
this only works (no errors) if the Vendeurs tbl is open - and is not returnign the Email address i Me.Email.txt
...Leave a comment:
-
Hi I looked at the tutorial you posted and tried to adapt your code - The Me.Equipetxt only updates for the first selection made in the combo table and the code for the Me.Emailtxt doesn't work
I've read that I may need a string, but honestly don't know where to start to input the info for this
All help very gratefully received
I have a combo box
SELECT [Vendeurs].[VendeurID], [Vendeurs].[Last...Leave a comment:
No activity results to display
Leave a comment: